src/test/regress/expected/co_nestloop_idxscan_optimizer.out - cloudberry - Git at Google

 --
 -- Nested loop join with index scan on CO table, test for MPP-17658
 --
 -- The test should also make sure the AO/AOCO table's bitmap state
 -- get re-init in BitmapHeapScanState if the current scan on AO/AOCO table
 -- not finished, and after ExecReScanBitmapHeapScan get called which will free
 -- current bitmap state.
 -- If the scan read all from AO/AOCO, the bitmap state in BitmapHeapScanState
 -- always get re-init, so this case is not considered.
 -- This is test through Nested Loop Semi Join, since it garentees that if
 -- find a match, a new outer slot is request, which the inner plan may not
 -- read all tuples. The inner plan of the Nested Loop Semi Join is Bitmap
 -- Heap Scan. So for a new outer slot, the inner plan need to rescan from
 -- the begining.
 --
 create schema co_nestloop_idxscan;
 create table co_nestloop_idxscan.foo (id bigint, data text) with (appendonly=true, orientation=column)
 distributed by (id);
 create table co_nestloop_idxscan.bar (id bigint) distributed by (id);
 set optimizer_enable_indexonlyscan = off;
 -- Changing the text to be smaller doesn't repro the issue
 insert into co_nestloop_idxscan.foo select i, repeat('xxxxxxxxxx', 100000) from generate_series(1,50) i;
 insert into co_nestloop_idxscan.bar values (1);
 analyze co_nestloop_idxscan.foo;
 analyze co_nestloop_idxscan.bar;
 create index foo_id_idx on co_nestloop_idxscan.foo(id);
 -- test with hash join
 explain select f.id from co_nestloop_idxscan.foo f, co_nestloop_idxscan.bar b where f.id = b.id;
                                      QUERY PLAN
 -------------------------------------------------------------------------------------
  Gather Motion 3:1  (slice1; segments: 3)  (cost=0.00..499.13 rows=1 width=8)
    ->  Nested Loop  (cost=0.00..499.13 rows=1 width=8)
          Join Filter: true
          ->  Seq Scan on bar b  (cost=0.00..431.00 rows=1 width=8)
          ->  Bitmap Heap Scan on foo f  (cost=0.00..68.13 rows=1 width=8)
                Recheck Cond: (id = b.id)
                ->  Bitmap Index Scan on foo_id_idx  (cost=0.00..0.00 rows=0 width=0)
                      Index Cond: (id = b.id)
  Optimizer: Pivotal Optimizer (GPORCA) version 3.72.0
 (9 rows)

 select f.id from co_nestloop_idxscan.foo f, co_nestloop_idxscan.bar b where f.id = b.id;
  id
 ----
   1
 (1 row)

 -- test with nested loop join
 set optimizer_enable_hashjoin = off;
 set enable_hashjoin=off;
 set enable_nestloop=on;
 explain select f.id from co_nestloop_idxscan.foo f, co_nestloop_idxscan.bar b where f.id = b.id;
                                      QUERY PLAN
 -------------------------------------------------------------------------------------
  Gather Motion 3:1  (slice1; segments: 3)  (cost=0.00..499.13 rows=1 width=8)
    ->  Nested Loop  (cost=0.00..499.13 rows=1 width=8)
          Join Filter: true
          ->  Seq Scan on bar b  (cost=0.00..431.00 rows=1 width=8)
          ->  Bitmap Heap Scan on foo f  (cost=0.00..68.13 rows=1 width=8)
                Recheck Cond: (id = b.id)
                ->  Bitmap Index Scan on foo_id_idx  (cost=0.00..0.00 rows=0 width=0)
                      Index Cond: (id = b.id)
  Optimizer: Pivotal Optimizer (GPORCA) version 3.72.0
 (9 rows)

 select f.id from co_nestloop_idxscan.foo f, co_nestloop_idxscan.bar b where f.id = b.id;
  id
 ----
   1
 (1 row)

 -- test with nested loop join and index scan
 set enable_seqscan = off;
 -- start_ignore
 -- Known_opt_diff: OPT-929
 -- end_ignore
 explain select f.id from co_nestloop_idxscan.bar b, co_nestloop_idxscan.foo f where f.id = b.id;
                                      QUERY PLAN
 -------------------------------------------------------------------------------------
  Gather Motion 3:1  (slice1; segments: 3)  (cost=0.00..499.13 rows=1 width=8)
    ->  Nested Loop  (cost=0.00..499.13 rows=1 width=8)
          Join Filter: true
          ->  Seq Scan on bar b  (cost=0.00..431.00 rows=1 width=8)
          ->  Bitmap Heap Scan on foo f  (cost=0.00..68.13 rows=1 width=8)
                Recheck Cond: (id = b.id)
                ->  Bitmap Index Scan on foo_id_idx  (cost=0.00..0.00 rows=0 width=0)
                      Index Cond: (id = b.id)
  Optimizer: Pivotal Optimizer (GPORCA) version 3.72.0
 (9 rows)

 select f.id from co_nestloop_idxscan.foo f, co_nestloop_idxscan.bar b where f.id = b.id;
  id
 ----
   1
 (1 row)

 set optimizer_enable_hashjoin = on;
 -- test with Nested Loop Semi Join for AO/AOCS freed bitmap state get re-init.
 -- Make sure each bitmap index scan contains more than 1 matched tuples,
 -- to make sure rescan frees bitmap state in BitmapHeapScanState. Since if only
 -- 1 tuple matched, the bitmap state in BitmapHeapScanState always get re-init
 -- when read all matched tuples.
 insert into co_nestloop_idxscan.foo select i%10, repeat('xxxxxxxxxx', 100000) from generate_series(1,20) i;
 -- Fill enouth tuples on same segment for the outer relation in next loop join
 -- to make sure rescan get called for inner plan.
 insert into co_nestloop_idxscan.bar values (1);
 -- turn off the optimizer since we can not make the orca generate the same plan with planner.
 set optimizer = off;
 -- The outher plan of the Nested Loop Semi Join should be Seq Scan on bar b.
 -- The inner plain should be a Bitmap Heap Scan on foo f.
 -- So the Bitmap Heap Scan will call ExecReScanBitmapHeapScan for new outer slot.
 set enable_sort=off;
 set enable_hashagg=off;
 explain select b.id from co_nestloop_idxscan.bar b where b.id in (select f.id from co_nestloop_idxscan.foo f where f.id in (1, 2, 3, 4, 5, 6));
                                            QUERY PLAN
 ------------------------------------------------------------------------------------------------
  Gather Motion 3:1  (slice1; segments: 3)  (cost=10000000048.90..10000000054.01 rows=4 width=8)
    ->  Nested Loop Semi Join  (cost=10000000048.90..10000000053.95 rows=2 width=8)
          ->  Seq Scan on bar b  (cost=10000000000.00..10000000001.02 rows=1 width=8)
                Filter: (id = ANY ('{1,2,3,4,5,6}'::bigint[]))
          ->  Bitmap Heap Scan on foo f  (cost=48.90..52.92 rows=1 width=8)
                Recheck Cond: ((id = b.id) AND (id = ANY ('{1,2,3,4,5,6}'::bigint[])))
                ->  Bitmap Index Scan on foo_id_idx  (cost=0.00..48.90 rows=1 width=0)
                      Index Cond: ((id = b.id) AND (id = ANY ('{1,2,3,4,5,6}'::bigint[])))
  Optimizer: Postgres query optimizer
 (9 rows)

 select b.id from co_nestloop_idxscan.bar b where b.id in (select f.id from co_nestloop_idxscan.foo f where f.id in (1, 2, 3, 4, 5, 6));
  id
 ----
   1
   1
 (2 rows)

 -- Also test a similar plan that uses RowIdExpr for duplicate elimination instead of
 -- Nested Loop Semi Join.
 reset enable_sort;
 reset enable_hashagg;
 explain select b.id from co_nestloop_idxscan.bar b where b.id in (select f.id from co_nestloop_idxscan.foo f where f.id in (1, 2, 3, 4, 5, 6));
                                                     QUERY PLAN
 ------------------------------------------------------------------------------------------------------------------
  Gather Motion 3:1  (slice1; segments: 3)  (cost=10000000054.02..10000000054.10 rows=4 width=8)
    ->  HashAggregate  (cost=10000000054.02..10000000054.03 rows=2 width=8)
          Group Key: (RowIdExpr)
          ->  Redistribute Motion 3:3  (slice2; segments: 3)  (cost=10000000048.90..10000000054.01 rows=2 width=8)
                Hash Key: (RowIdExpr)
                ->  Nested Loop  (cost=10000000048.90..10000000053.95 rows=2 width=8)
                      ->  Seq Scan on bar b  (cost=10000000000.00..10000000001.02 rows=1 width=8)
                            Filter: (id = ANY ('{1,2,3,4,5,6}'::bigint[]))
                      ->  Bitmap Heap Scan on foo f  (cost=48.90..52.92 rows=1 width=8)
                            Recheck Cond: ((id = b.id) AND (id = ANY ('{1,2,3,4,5,6}'::bigint[])))
                            ->  Bitmap Index Scan on foo_id_idx  (cost=0.00..48.90 rows=1 width=0)
                                  Index Cond: ((id = b.id) AND (id = ANY ('{1,2,3,4,5,6}'::bigint[])))
  Optimizer: Postgres query optimizer
 (13 rows)

 select b.id from co_nestloop_idxscan.bar b where b.id in (select f.id from co_nestloop_idxscan.foo f where f.id in (1, 2, 3, 4, 5, 6));
  id
 ----
   1
   1
 (2 rows)

 reset optimizer;
 reset optimizer_enable_indexonlyscan;
 drop schema co_nestloop_idxscan cascade;
 NOTICE:  drop cascades to 2 other objects
 DETAIL:  drop cascades to table co_nestloop_idxscan.foo
 drop cascades to table co_nestloop_idxscan.bar
	--
	-- Nested loop join with index scan on CO table, test for MPP-17658
	--
	-- The test should also make sure the AO/AOCO table's bitmap state
	-- get re-init in BitmapHeapScanState if the current scan on AO/AOCO table
	-- not finished, and after ExecReScanBitmapHeapScan get called which will free
	-- current bitmap state.
	-- If the scan read all from AO/AOCO, the bitmap state in BitmapHeapScanState
	-- always get re-init, so this case is not considered.
	-- This is test through Nested Loop Semi Join, since it garentees that if
	-- find a match, a new outer slot is request, which the inner plan may not
	-- read all tuples. The inner plan of the Nested Loop Semi Join is Bitmap
	-- Heap Scan. So for a new outer slot, the inner plan need to rescan from
	-- the begining.
	--
	create schema co_nestloop_idxscan;
	create table co_nestloop_idxscan.foo (id bigint, data text) with (appendonly=true, orientation=column)
	distributed by (id);
	create table co_nestloop_idxscan.bar (id bigint) distributed by (id);
	set optimizer_enable_indexonlyscan = off;
	-- Changing the text to be smaller doesn't repro the issue
	insert into co_nestloop_idxscan.foo select i, repeat('xxxxxxxxxx', 100000) from generate_series(1,50) i;
	insert into co_nestloop_idxscan.bar values (1);
	analyze co_nestloop_idxscan.foo;
	analyze co_nestloop_idxscan.bar;
	create index foo_id_idx on co_nestloop_idxscan.foo(id);
	-- test with hash join
	explain select f.id from co_nestloop_idxscan.foo f, co_nestloop_idxscan.bar b where f.id = b.id;
	QUERY PLAN
	-------------------------------------------------------------------------------------
	Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..499.13 rows=1 width=8)
	-> Nested Loop (cost=0.00..499.13 rows=1 width=8)
	Join Filter: true
	-> Seq Scan on bar b (cost=0.00..431.00 rows=1 width=8)
	-> Bitmap Heap Scan on foo f (cost=0.00..68.13 rows=1 width=8)
	Recheck Cond: (id = b.id)
	-> Bitmap Index Scan on foo_id_idx (cost=0.00..0.00 rows=0 width=0)
	Index Cond: (id = b.id)
	Optimizer: Pivotal Optimizer (GPORCA) version 3.72.0
	(9 rows)

	select f.id from co_nestloop_idxscan.foo f, co_nestloop_idxscan.bar b where f.id = b.id;
	id
	----
	1
	(1 row)

	-- test with nested loop join
	set optimizer_enable_hashjoin = off;
	set enable_hashjoin=off;
	set enable_nestloop=on;
	explain select f.id from co_nestloop_idxscan.foo f, co_nestloop_idxscan.bar b where f.id = b.id;
	QUERY PLAN
	-------------------------------------------------------------------------------------
	Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..499.13 rows=1 width=8)
	-> Nested Loop (cost=0.00..499.13 rows=1 width=8)
	Join Filter: true
	-> Seq Scan on bar b (cost=0.00..431.00 rows=1 width=8)
	-> Bitmap Heap Scan on foo f (cost=0.00..68.13 rows=1 width=8)
	Recheck Cond: (id = b.id)
	-> Bitmap Index Scan on foo_id_idx (cost=0.00..0.00 rows=0 width=0)
	Index Cond: (id = b.id)
	Optimizer: Pivotal Optimizer (GPORCA) version 3.72.0
	(9 rows)

	select f.id from co_nestloop_idxscan.foo f, co_nestloop_idxscan.bar b where f.id = b.id;
	id
	----
	1
	(1 row)

	-- test with nested loop join and index scan
	set enable_seqscan = off;
	-- start_ignore
	-- Known_opt_diff: OPT-929
	-- end_ignore
	explain select f.id from co_nestloop_idxscan.bar b, co_nestloop_idxscan.foo f where f.id = b.id;
	QUERY PLAN
	-------------------------------------------------------------------------------------
	Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..499.13 rows=1 width=8)
	-> Nested Loop (cost=0.00..499.13 rows=1 width=8)
	Join Filter: true
	-> Seq Scan on bar b (cost=0.00..431.00 rows=1 width=8)
	-> Bitmap Heap Scan on foo f (cost=0.00..68.13 rows=1 width=8)
	Recheck Cond: (id = b.id)
	-> Bitmap Index Scan on foo_id_idx (cost=0.00..0.00 rows=0 width=0)
	Index Cond: (id = b.id)
	Optimizer: Pivotal Optimizer (GPORCA) version 3.72.0
	(9 rows)

	select f.id from co_nestloop_idxscan.foo f, co_nestloop_idxscan.bar b where f.id = b.id;
	id
	----
	1
	(1 row)

	set optimizer_enable_hashjoin = on;
	-- test with Nested Loop Semi Join for AO/AOCS freed bitmap state get re-init.
	-- Make sure each bitmap index scan contains more than 1 matched tuples,
	-- to make sure rescan frees bitmap state in BitmapHeapScanState. Since if only
	-- 1 tuple matched, the bitmap state in BitmapHeapScanState always get re-init
	-- when read all matched tuples.
	insert into co_nestloop_idxscan.foo select i%10, repeat('xxxxxxxxxx', 100000) from generate_series(1,20) i;
	-- Fill enouth tuples on same segment for the outer relation in next loop join
	-- to make sure rescan get called for inner plan.
	insert into co_nestloop_idxscan.bar values (1);
	-- turn off the optimizer since we can not make the orca generate the same plan with planner.
	set optimizer = off;
	-- The outher plan of the Nested Loop Semi Join should be Seq Scan on bar b.
	-- The inner plain should be a Bitmap Heap Scan on foo f.
	-- So the Bitmap Heap Scan will call ExecReScanBitmapHeapScan for new outer slot.
	set enable_sort=off;
	set enable_hashagg=off;
	explain select b.id from co_nestloop_idxscan.bar b where b.id in (select f.id from co_nestloop_idxscan.foo f where f.id in (1, 2, 3, 4, 5, 6));
	QUERY PLAN
	------------------------------------------------------------------------------------------------
	Gather Motion 3:1 (slice1; segments: 3) (cost=10000000048.90..10000000054.01 rows=4 width=8)
	-> Nested Loop Semi Join (cost=10000000048.90..10000000053.95 rows=2 width=8)
	-> Seq Scan on bar b (cost=10000000000.00..10000000001.02 rows=1 width=8)
	Filter: (id = ANY ('{1,2,3,4,5,6}'::bigint[]))
	-> Bitmap Heap Scan on foo f (cost=48.90..52.92 rows=1 width=8)
	Recheck Cond: ((id = b.id) AND (id = ANY ('{1,2,3,4,5,6}'::bigint[])))
	-> Bitmap Index Scan on foo_id_idx (cost=0.00..48.90 rows=1 width=0)
	Index Cond: ((id = b.id) AND (id = ANY ('{1,2,3,4,5,6}'::bigint[])))
	Optimizer: Postgres query optimizer
	(9 rows)

	select b.id from co_nestloop_idxscan.bar b where b.id in (select f.id from co_nestloop_idxscan.foo f where f.id in (1, 2, 3, 4, 5, 6));
	id
	----
	1
	1
	(2 rows)

	-- Also test a similar plan that uses RowIdExpr for duplicate elimination instead of
	-- Nested Loop Semi Join.
	reset enable_sort;
	reset enable_hashagg;
	explain select b.id from co_nestloop_idxscan.bar b where b.id in (select f.id from co_nestloop_idxscan.foo f where f.id in (1, 2, 3, 4, 5, 6));
	QUERY PLAN
	------------------------------------------------------------------------------------------------------------------
	Gather Motion 3:1 (slice1; segments: 3) (cost=10000000054.02..10000000054.10 rows=4 width=8)
	-> HashAggregate (cost=10000000054.02..10000000054.03 rows=2 width=8)
	Group Key: (RowIdExpr)
	-> Redistribute Motion 3:3 (slice2; segments: 3) (cost=10000000048.90..10000000054.01 rows=2 width=8)
	Hash Key: (RowIdExpr)
	-> Nested Loop (cost=10000000048.90..10000000053.95 rows=2 width=8)
	-> Seq Scan on bar b (cost=10000000000.00..10000000001.02 rows=1 width=8)
	Filter: (id = ANY ('{1,2,3,4,5,6}'::bigint[]))
	-> Bitmap Heap Scan on foo f (cost=48.90..52.92 rows=1 width=8)
	Recheck Cond: ((id = b.id) AND (id = ANY ('{1,2,3,4,5,6}'::bigint[])))
	-> Bitmap Index Scan on foo_id_idx (cost=0.00..48.90 rows=1 width=0)
	Index Cond: ((id = b.id) AND (id = ANY ('{1,2,3,4,5,6}'::bigint[])))
	Optimizer: Postgres query optimizer
	(13 rows)

	select b.id from co_nestloop_idxscan.bar b where b.id in (select f.id from co_nestloop_idxscan.foo f where f.id in (1, 2, 3, 4, 5, 6));
	id
	----
	1
	1
	(2 rows)

	reset optimizer;
	reset optimizer_enable_indexonlyscan;
	drop schema co_nestloop_idxscan cascade;
	NOTICE: drop cascades to 2 other objects
	DETAIL: drop cascades to table co_nestloop_idxscan.foo
	drop cascades to table co_nestloop_idxscan.bar