| -- Test bitmap AND and OR |
| SET default_statistics_target=1000; |
| -- Encourage index use |
| SET seq_page_cost=100; |
| -- Generate enough data that we can test the lossy bitmaps. |
| -- There's 55 tuples per page in the table. 53 is just |
| -- below 55, so that an index scan with qual a = constant |
| -- will return at least one hit per page. 59 is just above |
| -- 55, so that an index scan with qual b = constant will return |
| -- hits on most but not all pages. 53 and 59 are prime, so that |
| -- there's a maximum number of a,b combinations in the table. |
| -- That allows us to test all the different combinations of |
| -- lossy and non-lossy pages with the minimum amount of data |
| CREATE TABLE bmscantest (a int, b int, t text); |
| NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'a' as the Apache Cloudberry 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 bmscantest |
| SELECT (r%53), (r%59), 'foooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooo' |
| FROM generate_series(1,70000) r; |
| CREATE INDEX i_bmtest_a ON bmscantest(a); |
| CREATE INDEX i_bmtest_b ON bmscantest(b); |
| -- We want to use bitmapscans. With default settings, the planner currently |
| -- chooses a bitmap scan for the queries below anyway, but let's make sure. |
| set enable_indexscan=false; |
| set enable_seqscan=false; |
| -- Lower work_mem to trigger use of lossy bitmaps |
| set work_mem = 64; |
| -- Test bitmap-and. |
| SELECT count(*) FROM bmscantest WHERE a = 1 AND b = 1; |
| count |
| ------- |
| 23 |
| (1 row) |
| |
| -- Test bitmap-or. |
| SELECT count(*) FROM bmscantest WHERE a = 1 OR b = 1; |
| count |
| ------- |
| 2485 |
| (1 row) |
| |
| -- Test mix BitmapOp load of on-disk bitmap index scan and in-memory bitmap index scan: |
| -- 1) heap |
| CREATE TABLE bmscantest2 (a int, b int, c int, d int, t text); |
| NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'a' as the Apache Cloudberry 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 bmscantest2 |
| SELECT (r%53), (r%59), (r%53), (r%59), 'foooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooo' |
| FROM generate_series(1,70000) r; |
| ANALYZE bmscantest2; |
| CREATE INDEX i_bmtest2_a ON bmscantest2 USING BITMAP(a); |
| CREATE INDEX i_bmtest2_b ON bmscantest2 USING BITMAP(b); |
| CREATE INDEX i_bmtest2_c ON bmscantest2(c); |
| CREATE INDEX i_bmtest2_d ON bmscantest2(d); |
| EXPLAIN SELECT count(*) FROM bmscantest2 WHERE a = 1 AND b = 1 AND c = 1; |
| QUERY PLAN |
| ------------------------------------------------------------------------------------------- |
| Aggregate (cost=0.00..6.35 rows=1 width=8) |
| -> Gather Motion 1:1 (slice1; segments: 1) (cost=0.00..6.35 rows=2 width=1) |
| -> Index Scan using i_bmtest2_c on bmscantest2 (cost=0.00..6.35 rows=2 width=1) |
| Index Cond: (c = 1) |
| Filter: ((a = 1) AND (b = 1)) |
| Optimizer: Pivotal Optimizer (GPORCA) version 3.95.0 |
| (6 rows) |
| |
| SELECT count(*) FROM bmscantest2 WHERE a = 1 AND b = 1 AND c = 1; |
| count |
| ------- |
| 23 |
| (1 row) |
| |
| SELECT count(*) FROM bmscantest2 WHERE a = 1 AND (b = 1 OR c = 1) AND d = 1; |
| count |
| ------- |
| 23 |
| (1 row) |
| |
| EXPLAIN SELECT count(*) FROM bmscantest2 WHERE a = 1 OR b = 1 OR c = 1; |
| QUERY PLAN |
| -------------------------------------------------------------------------------------------------------- |
| Finalize Aggregate (cost=0.00..431.20 rows=1 width=8) |
| -> Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..431.20 rows=1 width=8) |
| -> Partial Aggregate (cost=0.00..431.20 rows=1 width=8) |
| -> Bitmap Heap Scan on bmscantest2 (cost=0.00..431.20 rows=856 width=1) |
| Recheck Cond: ((a = 1) OR (b = 1) OR (c = 1)) |
| -> BitmapOr (cost=0.00..0.00 rows=0 width=0) |
| -> BitmapOr (cost=0.00..0.00 rows=0 width=0) |
| -> Bitmap Index Scan on i_bmtest2_a (cost=0.00..0.00 rows=0 width=0) |
| Index Cond: (a = 1) |
| -> Bitmap Index Scan on i_bmtest2_b (cost=0.00..0.00 rows=0 width=0) |
| Index Cond: (b = 1) |
| -> Bitmap Index Scan on i_bmtest2_c (cost=0.00..0.00 rows=0 width=0) |
| Index Cond: (c = 1) |
| Optimizer: Pivotal Optimizer (GPORCA) version 3.82.0 |
| (14 rows) |
| |
| SELECT count(*) FROM bmscantest2 WHERE a = 1 OR b = 1 OR c = 1; |
| count |
| ------- |
| 2485 |
| (1 row) |
| |
| SELECT count(*) FROM bmscantest2 WHERE a = 1 OR (b = 1 AND c = 1) OR d = 1; |
| count |
| ------- |
| 2485 |
| (1 row) |
| |
| -- 2) ao |
| CREATE TABLE bmscantest_ao (a int, b int, c int, d int, t text) WITH(appendonly=true, orientation=row); |
| NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'a' as the Apache Cloudberry 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 bmscantest_ao |
| SELECT (r%53), (r%59), (r%53), (r%59), 'foooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooo' |
| FROM generate_series(1,70000) r; |
| ANALYZE bmscantest_ao; |
| CREATE INDEX i_bmtest_ao_a ON bmscantest_ao USING BITMAP(a); |
| CREATE INDEX i_bmtest_ao_b ON bmscantest_ao USING BITMAP(b); |
| CREATE INDEX i_bmtest_ao_c ON bmscantest_ao(c); |
| CREATE INDEX i_bmtest_ao_d ON bmscantest_ao(d); |
| EXPLAIN SELECT count(*) FROM bmscantest_ao WHERE a = 1 AND b = 1 AND c = 1; |
| QUERY PLAN |
| ---------------------------------------------------------------------------------------------------- |
| Aggregate (cost=0.00..431.06 rows=1 width=8) |
| -> Gather Motion 1:1 (slice1; segments: 1) (cost=0.00..431.06 rows=2 width=1) |
| -> Bitmap Heap Scan on bmscantest_ao (cost=0.00..431.06 rows=1 width=1) |
| Recheck Cond: ((b = 1) AND (a = 1) AND (c = 1)) |
| -> BitmapAnd (cost=0.00..0.00 rows=0 width=0) |
| -> BitmapAnd (cost=0.00..0.00 rows=0 width=0) |
| -> Bitmap Index Scan on i_bmtest_ao_b (cost=0.00..0.00 rows=0 width=0) |
| Index Cond: (b = 1) |
| -> Bitmap Index Scan on i_bmtest_ao_a (cost=0.00..0.00 rows=0 width=0) |
| Index Cond: (a = 1) |
| -> Bitmap Index Scan on i_bmtest_ao_c (cost=0.00..0.00 rows=0 width=0) |
| Index Cond: (c = 1) |
| Optimizer: Pivotal Optimizer (GPORCA) version 3.72.0 |
| (13 rows) |
| |
| SELECT count(*) FROM bmscantest_ao WHERE a = 1 AND b = 1 AND c = 1; |
| count |
| ------- |
| 23 |
| (1 row) |
| |
| EXPLAIN SELECT count(*) FROM bmscantest_ao WHERE a = 1 AND (b = 1 OR c = 1) AND d = 1; |
| QUERY PLAN |
| ---------------------------------------------------------------------------------------------------- |
| Aggregate (cost=0.00..431.06 rows=1 width=8) |
| -> Gather Motion 1:1 (slice1; segments: 1) (cost=0.00..431.06 rows=3 width=1) |
| -> Bitmap Heap Scan on bmscantest_ao (cost=0.00..431.06 rows=1 width=1) |
| Recheck Cond: (((b = 1) OR (c = 1)) AND ((d = 1) AND (a = 1))) |
| -> BitmapAnd (cost=0.00..0.00 rows=0 width=0) |
| -> BitmapOr (cost=0.00..0.00 rows=0 width=0) |
| -> Bitmap Index Scan on i_bmtest_ao_b (cost=0.00..0.00 rows=0 width=0) |
| Index Cond: (b = 1) |
| -> Bitmap Index Scan on i_bmtest_ao_c (cost=0.00..0.00 rows=0 width=0) |
| Index Cond: (c = 1) |
| -> BitmapAnd (cost=0.00..0.00 rows=0 width=0) |
| -> Bitmap Index Scan on i_bmtest_ao_d (cost=0.00..0.00 rows=0 width=0) |
| Index Cond: (d = 1) |
| -> Bitmap Index Scan on i_bmtest_ao_a (cost=0.00..0.00 rows=0 width=0) |
| Index Cond: (a = 1) |
| Optimizer: Pivotal Optimizer (GPORCA) |
| (16 rows) |
| |
| SELECT count(*) FROM bmscantest_ao WHERE a = 1 AND (b = 1 OR c = 1) AND d = 1; |
| count |
| ------- |
| 23 |
| (1 row) |
| |
| EXPLAIN SELECT count(*) FROM bmscantest_ao WHERE a = 1 OR b = 1 OR c = 1; |
| QUERY PLAN |
| ---------------------------------------------------------------------------------------------------------- |
| Finalize Aggregate (cost=0.00..431.20 rows=1 width=8) |
| -> Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..431.20 rows=1 width=8) |
| -> Partial Aggregate (cost=0.00..431.20 rows=1 width=8) |
| -> Bitmap Heap Scan on bmscantest_ao (cost=0.00..431.20 rows=856 width=1) |
| Recheck Cond: ((a = 1) OR (b = 1) OR (c = 1)) |
| -> BitmapOr (cost=0.00..0.00 rows=0 width=0) |
| -> BitmapOr (cost=0.00..0.00 rows=0 width=0) |
| -> Bitmap Index Scan on i_bmtest_ao_a (cost=0.00..0.00 rows=0 width=0) |
| Index Cond: (a = 1) |
| -> Bitmap Index Scan on i_bmtest_ao_b (cost=0.00..0.00 rows=0 width=0) |
| Index Cond: (b = 1) |
| -> Bitmap Index Scan on i_bmtest_ao_c (cost=0.00..0.00 rows=0 width=0) |
| Index Cond: (c = 1) |
| Optimizer: Pivotal Optimizer (GPORCA) |
| (14 rows) |
| |
| SELECT count(*) FROM bmscantest_ao WHERE a = 1 OR b = 1 OR c = 1; |
| count |
| ------- |
| 2485 |
| (1 row) |
| |
| EXPLAIN SELECT count(*) FROM bmscantest_ao WHERE a = 1 OR (b = 1 AND c = 1) OR d = 1; |
| QUERY PLAN |
| ---------------------------------------------------------------------------------------------------------------- |
| Finalize Aggregate (cost=0.00..431.17 rows=1 width=8) |
| -> Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..431.17 rows=1 width=8) |
| -> Partial Aggregate (cost=0.00..431.17 rows=1 width=8) |
| -> Bitmap Heap Scan on bmscantest_ao (cost=0.00..431.17 rows=669 width=1) |
| Recheck Cond: ((a = 1) OR ((b = 1) AND (c = 1)) OR (d = 1)) |
| -> BitmapOr (cost=0.00..0.00 rows=0 width=0) |
| -> BitmapOr (cost=0.00..0.00 rows=0 width=0) |
| -> Bitmap Index Scan on i_bmtest_ao_a (cost=0.00..0.00 rows=0 width=0) |
| Index Cond: (a = 1) |
| -> BitmapAnd (cost=0.00..0.00 rows=0 width=0) |
| -> Bitmap Index Scan on i_bmtest_ao_b (cost=0.00..0.00 rows=0 width=0) |
| Index Cond: (b = 1) |
| -> Bitmap Index Scan on i_bmtest_ao_c (cost=0.00..0.00 rows=0 width=0) |
| Index Cond: (c = 1) |
| -> Bitmap Index Scan on i_bmtest_ao_d (cost=0.00..0.00 rows=0 width=0) |
| Index Cond: (d = 1) |
| Optimizer: Pivotal Optimizer (GPORCA) |
| (17 rows) |
| |
| SELECT count(*) FROM bmscantest_ao WHERE a = 1 OR (b = 1 AND c = 1) OR d = 1; |
| count |
| ------- |
| 2485 |
| (1 row) |
| |
| -- 3) aocs |
| CREATE TABLE bmscantest_aocs (a int, b int, c int, d int, t text) WITH(appendonly=true, orientation=column); |
| NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'a' as the Apache Cloudberry 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 bmscantest_aocs |
| SELECT (r%53), (r%59), (r%53), (r%59), 'foooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooo' |
| FROM generate_series(1,70000) r; |
| ANALYZE bmscantest_aocs; |
| CREATE INDEX i_bmtest_aocs_a ON bmscantest_aocs USING BITMAP(a); |
| CREATE INDEX i_bmtest_aocs_b ON bmscantest_aocs USING BITMAP(b); |
| CREATE INDEX i_bmtest_aocs_c ON bmscantest_aocs(c); |
| CREATE INDEX i_bmtest_aocs_d ON bmscantest_aocs(d); |
| EXPLAIN SELECT count(*) FROM bmscantest_aocs WHERE a = 1 AND b = 1 AND c = 1; |
| QUERY PLAN |
| ------------------------------------------------------------------------------------------------------ |
| Aggregate (cost=0.00..431.06 rows=1 width=8) |
| -> Gather Motion 1:1 (slice1; segments: 1) (cost=0.00..431.06 rows=2 width=1) |
| -> Bitmap Heap Scan on bmscantest_aocs (cost=0.00..431.06 rows=1 width=1) |
| Recheck Cond: ((b = 1) AND (a = 1) AND (c = 1)) |
| -> BitmapAnd (cost=0.00..0.00 rows=0 width=0) |
| -> BitmapAnd (cost=0.00..0.00 rows=0 width=0) |
| -> Bitmap Index Scan on i_bmtest_aocs_b (cost=0.00..0.00 rows=0 width=0) |
| Index Cond: (b = 1) |
| -> Bitmap Index Scan on i_bmtest_aocs_a (cost=0.00..0.00 rows=0 width=0) |
| Index Cond: (a = 1) |
| -> Bitmap Index Scan on i_bmtest_aocs_c (cost=0.00..0.00 rows=0 width=0) |
| Index Cond: (c = 1) |
| Optimizer: Pivotal Optimizer (GPORCA) |
| (13 rows) |
| |
| SELECT count(*) FROM bmscantest_aocs WHERE a = 1 AND b = 1 AND c = 1; |
| count |
| ------- |
| 23 |
| (1 row) |
| |
| EXPLAIN SELECT count(*) FROM bmscantest_aocs WHERE a = 1 AND (b = 1 OR c = 1) AND d = 1; |
| QUERY PLAN |
| ------------------------------------------------------------------------------------------------------ |
| Aggregate (cost=0.00..431.06 rows=1 width=8) |
| -> Gather Motion 1:1 (slice1; segments: 1) (cost=0.00..431.06 rows=3 width=1) |
| -> Bitmap Heap Scan on bmscantest_aocs (cost=0.00..431.06 rows=1 width=1) |
| Recheck Cond: (((b = 1) OR (c = 1)) AND ((d = 1) AND (a = 1))) |
| -> BitmapAnd (cost=0.00..0.00 rows=0 width=0) |
| -> BitmapOr (cost=0.00..0.00 rows=0 width=0) |
| -> Bitmap Index Scan on i_bmtest_aocs_b (cost=0.00..0.00 rows=0 width=0) |
| Index Cond: (b = 1) |
| -> Bitmap Index Scan on i_bmtest_aocs_c (cost=0.00..0.00 rows=0 width=0) |
| Index Cond: (c = 1) |
| -> BitmapAnd (cost=0.00..0.00 rows=0 width=0) |
| -> Bitmap Index Scan on i_bmtest_aocs_d (cost=0.00..0.00 rows=0 width=0) |
| Index Cond: (d = 1) |
| -> Bitmap Index Scan on i_bmtest_aocs_a (cost=0.00..0.00 rows=0 width=0) |
| Index Cond: (a = 1) |
| Optimizer: Pivotal Optimizer (GPORCA) |
| (16 rows) |
| |
| SELECT count(*) FROM bmscantest_aocs WHERE a = 1 AND (b = 1 OR c = 1) AND d = 1; |
| count |
| ------- |
| 23 |
| (1 row) |
| |
| EXPLAIN SELECT count(*) FROM bmscantest_aocs WHERE a = 1 OR b = 1 OR c = 1; |
| QUERY PLAN |
| ------------------------------------------------------------------------------------------------------------ |
| Finalize Aggregate (cost=0.00..431.20 rows=1 width=8) |
| -> Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..431.20 rows=1 width=8) |
| -> Partial Aggregate (cost=0.00..431.20 rows=1 width=8) |
| -> Bitmap Heap Scan on bmscantest_aocs (cost=0.00..431.20 rows=856 width=1) |
| Recheck Cond: ((a = 1) OR (b = 1) OR (c = 1)) |
| -> BitmapOr (cost=0.00..0.00 rows=0 width=0) |
| -> BitmapOr (cost=0.00..0.00 rows=0 width=0) |
| -> Bitmap Index Scan on i_bmtest_aocs_a (cost=0.00..0.00 rows=0 width=0) |
| Index Cond: (a = 1) |
| -> Bitmap Index Scan on i_bmtest_aocs_b (cost=0.00..0.00 rows=0 width=0) |
| Index Cond: (b = 1) |
| -> Bitmap Index Scan on i_bmtest_aocs_c (cost=0.00..0.00 rows=0 width=0) |
| Index Cond: (c = 1) |
| Optimizer: Pivotal Optimizer (GPORCA) |
| (14 rows) |
| |
| SELECT count(*) FROM bmscantest_aocs WHERE a = 1 OR b = 1 OR c = 1; |
| count |
| ------- |
| 2485 |
| (1 row) |
| |
| EXPLAIN SELECT count(*) FROM bmscantest_aocs WHERE a = 1 OR (b = 1 AND c = 1) OR d = 1; |
| QUERY PLAN |
| ------------------------------------------------------------------------------------------------------------------ |
| Finalize Aggregate (cost=0.00..431.17 rows=1 width=8) |
| -> Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..431.17 rows=1 width=8) |
| -> Partial Aggregate (cost=0.00..431.17 rows=1 width=8) |
| -> Bitmap Heap Scan on bmscantest_aocs (cost=0.00..431.17 rows=669 width=1) |
| Recheck Cond: ((a = 1) OR ((b = 1) AND (c = 1)) OR (d = 1)) |
| -> BitmapOr (cost=0.00..0.00 rows=0 width=0) |
| -> BitmapOr (cost=0.00..0.00 rows=0 width=0) |
| -> Bitmap Index Scan on i_bmtest_aocs_a (cost=0.00..0.00 rows=0 width=0) |
| Index Cond: (a = 1) |
| -> BitmapAnd (cost=0.00..0.00 rows=0 width=0) |
| -> Bitmap Index Scan on i_bmtest_aocs_b (cost=0.00..0.00 rows=0 width=0) |
| Index Cond: (b = 1) |
| -> Bitmap Index Scan on i_bmtest_aocs_c (cost=0.00..0.00 rows=0 width=0) |
| Index Cond: (c = 1) |
| -> Bitmap Index Scan on i_bmtest_aocs_d (cost=0.00..0.00 rows=0 width=0) |
| Index Cond: (d = 1) |
| Optimizer: Pivotal Optimizer (GPORCA) |
| (17 rows) |
| |
| SELECT count(*) FROM bmscantest_aocs WHERE a = 1 OR (b = 1 AND c = 1) OR d = 1; |
| count |
| ------- |
| 2485 |
| (1 row) |
| |
| -- clean up |
| DROP TABLE bmscantest; |
| DROP TABLE bmscantest_ao; |
| DROP TABLE bmscantest_aocs; |
