This is an automated email from the ASF dual-hosted git repository. yjhjstz pushed a commit to branch main in repository https://gitbox.apache.org/repos/asf/cloudberry.git
commit 9389da31d3c43d6d39b65c07544237bfb34c5cf0 Author: Zhenghua Lyu <[email protected]> AuthorDate: Thu Aug 25 14:20:44 2022 +0800 Move test bitmap_union from isolation2 to regress. This case does not need to be under isolation2 also modify the ansfile in this commit. --- src/test/isolation2/expected/bitmap_union.out | 69 ---------------------- .../isolation2/expected/bitmap_union_optimizer.out | 68 --------------------- src/test/isolation2/isolation2_schedule | 1 - src/test/isolation2/sql/bitmap_union.sql | 27 --------- src/test/regress/expected/bitmap_index.out | 67 +++++++++++++++++++++ .../regress/expected/bitmap_index_optimizer.out | 67 +++++++++++++++++++++ src/test/regress/sql/bitmap_index.sql | 29 +++++++++ 7 files changed, 163 insertions(+), 165 deletions(-) diff --git a/src/test/isolation2/expected/bitmap_union.out b/src/test/isolation2/expected/bitmap_union.out deleted file mode 100644 index e5050e9209..0000000000 --- a/src/test/isolation2/expected/bitmap_union.out +++ /dev/null @@ -1,69 +0,0 @@ --- --- Test union bitmap batch words for multivalues index scan like where x in (x1, x2) or x > v --- which creates BitmapAnd plan on two bitmap indexs that match multiple keys by using in in where clause --- -CREATE TABLE bmunion (a int, b int); -CREATE -INSERT INTO bmunion SELECT (r%53), (r%59) FROM generate_series(1,70000) r; -INSERT 70000 -CREATE INDEX i_bmtest2_a ON bmunion USING BITMAP(a); -CREATE -CREATE INDEX i_bmtest2_b ON bmunion USING BITMAP(b); -CREATE -INSERT INTO bmunion SELECT 53, 1 FROM generate_series(1, 1000); -INSERT 1000 - -SET optimizer_enable_tablescan=OFF; -SET -SET optimizer_enable_dynamictablescan=OFF; -SET --- Inject fault for planner so that it could produce bitMapAnd plan node. -SELECT gp_inject_fault('simulate_bitmap_and', 'skip', dbid) FROM gp_segment_configuration WHERE role = 'p' AND content = -1; - gp_inject_fault ------------------ - Success: -(1 row) -EXPLAIN (COSTS OFF) SELECT count(*) FROM bmunion WHERE a = 53 AND b < 3; - QUERY PLAN ----------------------------------------------------------------- - Finalize Aggregate - -> Gather Motion 3:1 (slice1; segments: 3) - -> Partial Aggregate - -> Bitmap Heap Scan on bmunion - Recheck Cond: ((b < 3) AND (a = 53)) - -> BitmapAnd - -> Bitmap Index Scan on i_bmtest2_b - Index Cond: (b < 3) - -> Bitmap Index Scan on i_bmtest2_a - Index Cond: (a = 53) - Optimizer: Postgres query optimizer -(11 rows) -SELECT gp_inject_fault('simulate_bitmap_and', 'reset', dbid) FROM gp_segment_configuration WHERE role = 'p' AND content = -1; - gp_inject_fault ------------------ - Success: -(1 row) - -SELECT gp_inject_fault('simulate_bitmap_and', 'skip', dbid) FROM gp_segment_configuration WHERE role = 'p' AND content = -1; - gp_inject_fault ------------------ - Success: -(1 row) -SELECT count(*) FROM bmunion WHERE a = 53 AND b < 3; - count -------- - 1000 -(1 row) -SELECT gp_inject_fault('simulate_bitmap_and', 'reset', dbid) FROM gp_segment_configuration WHERE role = 'p' AND content = -1; - gp_inject_fault ------------------ - Success: -(1 row) - -RESET optimizer_enable_tablescan; -RESET -RESET optimizer_enable_dynamictablescan; -RESET - -DROP TABLE bmunion; -DROP diff --git a/src/test/isolation2/expected/bitmap_union_optimizer.out b/src/test/isolation2/expected/bitmap_union_optimizer.out deleted file mode 100644 index 72e27489f1..0000000000 --- a/src/test/isolation2/expected/bitmap_union_optimizer.out +++ /dev/null @@ -1,68 +0,0 @@ --- --- Test union bitmap batch words for multivalues index scan like where x in (x1, x2) or x > v --- which creates BitmapAnd plan on two bitmap indexs that match multiple keys by using in in where clause --- -CREATE TABLE bmunion (a int, b int); -CREATE -INSERT INTO bmunion SELECT (r%53), (r%59) FROM generate_series(1,70000) r; -INSERT 70000 -CREATE INDEX i_bmtest2_a ON bmunion USING BITMAP(a); -CREATE -CREATE INDEX i_bmtest2_b ON bmunion USING BITMAP(b); -CREATE -INSERT INTO bmunion SELECT 53, 1 FROM generate_series(1, 1000); -INSERT 1000 - -SET optimizer_enable_tablescan=OFF; -SET -SET optimizer_enable_dynamictablescan=OFF; -SET --- Inject fault for planner so that it could produce bitMapAnd plan node. -SELECT gp_inject_fault('simulate_bitmap_and', 'skip', dbid) FROM gp_segment_configuration WHERE role = 'p' AND content = -1; - gp_inject_fault ------------------ - Success: -(1 row) -EXPLAIN (COSTS OFF) SELECT count(*) FROM bmunion WHERE a = 53 AND b < 3; - QUERY PLAN ----------------------------------------------------------- - Aggregate - -> Gather Motion 1:1 (slice1; segments: 1) - -> Bitmap Heap Scan on bmunion - Recheck Cond: ((a = 53) AND (b < 3)) - -> BitmapAnd - -> Bitmap Index Scan on i_bmtest2_a - Index Cond: (a = 53) - -> Bitmap Index Scan on i_bmtest2_b - Index Cond: (b < 3) - Optimizer: Pivotal Optimizer (GPORCA) -(10 rows) -SELECT gp_inject_fault('simulate_bitmap_and', 'reset', dbid) FROM gp_segment_configuration WHERE role = 'p' AND content = -1; - gp_inject_fault ------------------ - Success: -(1 row) - -SELECT gp_inject_fault('simulate_bitmap_and', 'skip', dbid) FROM gp_segment_configuration WHERE role = 'p' AND content = -1; - gp_inject_fault ------------------ - Success: -(1 row) -SELECT count(*) FROM bmunion WHERE a = 53 AND b < 3; - count -------- - 1000 -(1 row) -SELECT gp_inject_fault('simulate_bitmap_and', 'reset', dbid) FROM gp_segment_configuration WHERE role = 'p' AND content = -1; - gp_inject_fault ------------------ - Success: -(1 row) - -RESET optimizer_enable_tablescan; -RESET -RESET optimizer_enable_dynamictablescan; -RESET - -DROP TABLE bmunion; -DROP diff --git a/src/test/isolation2/isolation2_schedule b/src/test/isolation2/isolation2_schedule index 87f8f2aff7..1bc943a0a0 100644 --- a/src/test/isolation2/isolation2_schedule +++ b/src/test/isolation2/isolation2_schedule @@ -77,7 +77,6 @@ test: ao_blkdir test: bitmap_index_concurrent test: bitmap_index_crash test: bitmap_update_words_backup_block -test: bitmap_union test: bitmap_index_inspect # below test utilizes fault injectors so it needs to be in a group by itself diff --git a/src/test/isolation2/sql/bitmap_union.sql b/src/test/isolation2/sql/bitmap_union.sql deleted file mode 100644 index 6826835317..0000000000 --- a/src/test/isolation2/sql/bitmap_union.sql +++ /dev/null @@ -1,27 +0,0 @@ --- --- Test union bitmap batch words for multivalues index scan like where x in (x1, x2) or x > v --- which creates BitmapAnd plan on two bitmap indexs that match multiple keys by using in in where clause --- -CREATE TABLE bmunion (a int, b int); -INSERT INTO bmunion - SELECT (r%53), (r%59) - FROM generate_series(1,70000) r; -CREATE INDEX i_bmtest2_a ON bmunion USING BITMAP(a); -CREATE INDEX i_bmtest2_b ON bmunion USING BITMAP(b); -INSERT INTO bmunion SELECT 53, 1 FROM generate_series(1, 1000); - -SET optimizer_enable_tablescan=OFF; -SET optimizer_enable_dynamictablescan=OFF; --- Inject fault for planner so that it could produce bitMapAnd plan node. -SELECT gp_inject_fault('simulate_bitmap_and', 'skip', dbid) FROM gp_segment_configuration WHERE role = 'p' AND content = -1; -EXPLAIN (COSTS OFF) SELECT count(*) FROM bmunion WHERE a = 53 AND b < 3; -SELECT gp_inject_fault('simulate_bitmap_and', 'reset', dbid) FROM gp_segment_configuration WHERE role = 'p' AND content = -1; - -SELECT gp_inject_fault('simulate_bitmap_and', 'skip', dbid) FROM gp_segment_configuration WHERE role = 'p' AND content = -1; -SELECT count(*) FROM bmunion WHERE a = 53 AND b < 3; -SELECT gp_inject_fault('simulate_bitmap_and', 'reset', dbid) FROM gp_segment_configuration WHERE role = 'p' AND content = -1; - -RESET optimizer_enable_tablescan; -RESET optimizer_enable_dynamictablescan; - -DROP TABLE bmunion; diff --git a/src/test/regress/expected/bitmap_index.out b/src/test/regress/expected/bitmap_index.out index bcc778f3f2..81980b7251 100644 --- a/src/test/regress/expected/bitmap_index.out +++ b/src/test/regress/expected/bitmap_index.out @@ -1,3 +1,4 @@ +create extension if not exists gp_inject_fault; SET enable_seqscan = OFF; SET enable_indexscan = ON; SET enable_bitmapscan = ON; @@ -998,3 +999,69 @@ select count(*) from bm_test_ao where i = 2; (1 row) DROP TABLE bm_test_ao; + +-- +-- test union bitmap batch words for multivalues index scan like where x in (x1, x2) or x > v +-- which creates bitmapand plan on two bitmap indexs that match multiple keys by using in in where clause +-- +create table bmunion (a int, b int); +NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'a' as the Greenplum Database data distribution key for this table. +HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. +insert into bmunion + select (r%53), (r%59) + from generate_series(1,70000) r; +create index i_bmtest2_a on bmunion using bitmap(a); +create index i_bmtest2_b on bmunion using bitmap(b); +insert into bmunion select 53, 1 from generate_series(1, 1000); +set optimizer_enable_tablescan=off; +set optimizer_enable_dynamictablescan=off; +-- inject fault for planner so that it could produce bitmapand plan node. +select gp_inject_fault('simulate_bitmap_and', 'skip', dbid) from gp_segment_configuration where role = 'p' and content = -1; + gp_inject_fault +----------------- + Success: +(1 row) + +explain (costs off) select count(*) from bmunion where a = 53 and b < 3; + QUERY PLAN +---------------------------------------------------------------- + Finalize Aggregate + -> Gather Motion 3:1 (slice1; segments: 3) + -> Partial Aggregate + -> Bitmap Heap Scan on bmunion + Recheck Cond: ((b < 3) AND (a = 53)) + -> BitmapAnd + -> Bitmap Index Scan on i_bmtest2_b + Index Cond: (b < 3) + -> Bitmap Index Scan on i_bmtest2_a + Index Cond: (a = 53) + Optimizer: Postgres query optimizer +(11 rows) + +select gp_inject_fault('simulate_bitmap_and', 'reset', dbid) from gp_segment_configuration where role = 'p' and content = -1; + gp_inject_fault +----------------- + Success: +(1 row) + +select gp_inject_fault('simulate_bitmap_and', 'skip', dbid) from gp_segment_configuration where role = 'p' and content = -1; + gp_inject_fault +----------------- + Success: +(1 row) + +select count(*) from bmunion where a = 53 and b < 3; + count +------- + 1000 +(1 row) + +select gp_inject_fault('simulate_bitmap_and', 'reset', dbid) from gp_segment_configuration where role = 'p' and content = -1; + gp_inject_fault +----------------- + Success: +(1 row) + +reset optimizer_enable_tablescan; +reset optimizer_enable_dynamictablescan; +drop table bmunion; diff --git a/src/test/regress/expected/bitmap_index_optimizer.out b/src/test/regress/expected/bitmap_index_optimizer.out index e00a23cfbc..6d828e10da 100644 --- a/src/test/regress/expected/bitmap_index_optimizer.out +++ b/src/test/regress/expected/bitmap_index_optimizer.out @@ -1,3 +1,4 @@ +create extension if not exists gp_inject_fault; SET enable_seqscan = OFF; SET enable_indexscan = ON; SET enable_bitmapscan = ON; @@ -1005,3 +1006,69 @@ select count(*) from bm_test_ao where i = 2; (1 row) DROP TABLE bm_test_ao; + +-- +-- test union bitmap batch words for multivalues index scan like where x in (x1, x2) or x > v +-- which creates bitmapand plan on two bitmap indexs that match multiple keys by using in in where clause +-- +create table bmunion (a int, b int); +NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'a' as the Greenplum Database data distribution key for this table. +HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. +insert into bmunion + select (r%53), (r%59) + from generate_series(1,70000) r; +create index i_bmtest2_a on bmunion using bitmap(a); +create index i_bmtest2_b on bmunion using bitmap(b); +insert into bmunion select 53, 1 from generate_series(1, 1000); +set optimizer_enable_tablescan=off; +set optimizer_enable_dynamictablescan=off; +-- inject fault for planner so that it could produce bitmapand plan node. +select gp_inject_fault('simulate_bitmap_and', 'skip', dbid) from gp_segment_configuration where role = 'p' and content = -1; + gp_inject_fault +----------------- + Success: +(1 row) + +explain (costs off) select count(*) from bmunion where a = 53 and b < 3; + QUERY PLAN +---------------------------------------------------------------- + Finalize Aggregate + -> Gather Motion 1:1 (slice1; segments: 1) + -> Partial Aggregate + -> Bitmap Heap Scan on bmunion + Recheck Cond: ((a = 53) AND (b < 3)) + -> BitmapAnd + -> Bitmap Index Scan on i_bmtest2_a + Index Cond: (a = 53) + -> Bitmap Index Scan on i_bmtest2_b + Index Cond: (b < 3) + Optimizer: Pivotal Optimizer (GPORCA) +(11 rows) + +select gp_inject_fault('simulate_bitmap_and', 'reset', dbid) from gp_segment_configuration where role = 'p' and content = -1; + gp_inject_fault +----------------- + Success: +(1 row) + +select gp_inject_fault('simulate_bitmap_and', 'skip', dbid) from gp_segment_configuration where role = 'p' and content = -1; + gp_inject_fault +----------------- + Success: +(1 row) + +select count(*) from bmunion where a = 53 and b < 3; + count +------- + 1000 +(1 row) + +select gp_inject_fault('simulate_bitmap_and', 'reset', dbid) from gp_segment_configuration where role = 'p' and content = -1; + gp_inject_fault +----------------- + Success: +(1 row) + +reset optimizer_enable_tablescan; +reset optimizer_enable_dynamictablescan; +drop table bmunion; diff --git a/src/test/regress/sql/bitmap_index.sql b/src/test/regress/sql/bitmap_index.sql index bbf65383b8..a4f7b793f0 100644 --- a/src/test/regress/sql/bitmap_index.sql +++ b/src/test/regress/sql/bitmap_index.sql @@ -1,3 +1,5 @@ +create extension if not exists gp_inject_fault; + SET enable_seqscan = OFF; SET enable_indexscan = ON; SET enable_bitmapscan = ON; @@ -419,3 +421,30 @@ explain select count(*) from bm_test_ao where i =2; select count(*) from bm_test_ao where i = 2; DROP TABLE bm_test_ao; +-- +-- test union bitmap batch words for multivalues index scan like where x in (x1, x2) or x > v +-- which creates bitmapand plan on two bitmap indexs that match multiple keys by using in in where clause +-- +create table bmunion (a int, b int); +insert into bmunion + select (r%53), (r%59) + from generate_series(1,70000) r; +create index i_bmtest2_a on bmunion using bitmap(a); +create index i_bmtest2_b on bmunion using bitmap(b); +insert into bmunion select 53, 1 from generate_series(1, 1000); + +set optimizer_enable_tablescan=off; +set optimizer_enable_dynamictablescan=off; +-- inject fault for planner so that it could produce bitmapand plan node. +select gp_inject_fault('simulate_bitmap_and', 'skip', dbid) from gp_segment_configuration where role = 'p' and content = -1; +explain (costs off) select count(*) from bmunion where a = 53 and b < 3; +select gp_inject_fault('simulate_bitmap_and', 'reset', dbid) from gp_segment_configuration where role = 'p' and content = -1; + +select gp_inject_fault('simulate_bitmap_and', 'skip', dbid) from gp_segment_configuration where role = 'p' and content = -1; +select count(*) from bmunion where a = 53 and b < 3; +select gp_inject_fault('simulate_bitmap_and', 'reset', dbid) from gp_segment_configuration where role = 'p' and content = -1; + +reset optimizer_enable_tablescan; +reset optimizer_enable_dynamictablescan; + +drop table bmunion; --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
