| -- 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); |
| 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 |
| ----------------------------------------------------------------------------------------------------- |
| Finalize Aggregate (cost=31.38..31.39 rows=1 width=8) |
| -> Gather Motion 3:1 (slice1; segments: 3) (cost=31.35..31.38 rows=1 width=8) |
| -> Partial Aggregate (cost=31.35..31.36 rows=1 width=8) |
| -> Bitmap Heap Scan on bmscantest2 (cost=27.22..31.24 rows=15 width=0) |
| Recheck Cond: ((b = 1) AND (a = 1) AND (c = 1)) |
| -> BitmapAnd (cost=27.22..27.22 rows=1 width=0) |
| -> Bitmap Index Scan on i_bmtest2_b (cost=0.00..4.29 rows=396 width=0) |
| Index Cond: (b = 1) |
| -> Bitmap Index Scan on i_bmtest2_a (cost=0.00..4.33 rows=441 width=0) |
| Index Cond: (a = 1) |
| -> Bitmap Index Scan on i_bmtest2_c (cost=0.00..18.07 rows=441 width=0) |
| Index Cond: (c = 1) |
| Optimizer: Postgres query optimizer |
| (13 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=442.67..442.68 rows=1 width=8) |
| -> Gather Motion 3:1 (slice1; segments: 3) (cost=442.64..442.67 rows=1 width=8) |
| -> Partial Aggregate (cost=442.64..442.65 rows=1 width=8) |
| -> Bitmap Heap Scan on bmscantest2 (cost=29.23..433.24 rows=1254 width=0) |
| Recheck Cond: ((a = 1) OR (b = 1) OR (c = 1)) |
| -> BitmapOr (cost=29.23..29.23 rows=1277 width=0) |
| -> Bitmap Index Scan on i_bmtest2_a (cost=0.00..4.19 rows=441 width=0) |
| Index Cond: (a = 1) |
| -> Bitmap Index Scan on i_bmtest2_b (cost=0.00..4.15 rows=396 width=0) |
| Index Cond: (b = 1) |
| -> Bitmap Index Scan on i_bmtest2_c (cost=0.00..18.07 rows=441 width=0) |
| Index Cond: (c = 1) |
| Optimizer: Postgres query optimizer |
| (13 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 |
| ------------------------------------------------------------------------------------------------------- |
| Finalize Aggregate (cost=31.38..31.39 rows=1 width=8) |
| -> Gather Motion 3:1 (slice1; segments: 3) (cost=31.35..31.38 rows=1 width=8) |
| -> Partial Aggregate (cost=31.35..31.36 rows=1 width=8) |
| -> Bitmap Heap Scan on bmscantest_ao (cost=27.22..31.24 rows=15 width=0) |
| Recheck Cond: ((b = 1) AND (a = 1) AND (c = 1)) |
| -> BitmapAnd (cost=27.22..27.22 rows=1 width=0) |
| -> Bitmap Index Scan on i_bmtest_ao_b (cost=0.00..4.29 rows=396 width=0) |
| Index Cond: (b = 1) |
| -> Bitmap Index Scan on i_bmtest_ao_a (cost=0.00..4.33 rows=441 width=0) |
| Index Cond: (a = 1) |
| -> Bitmap Index Scan on i_bmtest_ao_c (cost=0.00..18.07 rows=441 width=0) |
| Index Cond: (c = 1) |
| Optimizer: Postgres query optimizer |
| (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 |
| ------------------------------------------------------------------------------------------------------------- |
| Finalize Aggregate (cost=47.80..47.81 rows=1 width=8) |
| -> Gather Motion 3:1 (slice1; segments: 3) (cost=47.74..47.79 rows=3 width=8) |
| -> Partial Aggregate (cost=47.74..47.75 rows=1 width=8) |
| -> Bitmap Heap Scan on bmscantest_ao (cost=43.68..47.70 rows=18 width=0) |
| Recheck Cond: ((a = 1) AND (d = 1) AND ((b = 1) OR (c = 1))) |
| -> BitmapAnd (cost=43.68..43.68 rows=1 width=0) |
| -> Bitmap Index Scan on i_bmtest_ao_a (cost=0.00..4.01 rows=440 width=0) |
| Index Cond: (a = 1) |
| -> Bitmap Index Scan on i_bmtest_ao_d (cost=0.00..17.07 rows=396 width=0) |
| Index Cond: (d = 1) |
| -> BitmapOr (cost=22.09..22.09 rows=836 width=0) |
| -> Bitmap Index Scan on i_bmtest_ao_b (cost=0.00..4.01 rows=396 width=0) |
| Index Cond: (b = 1) |
| -> Bitmap Index Scan on i_bmtest_ao_c (cost=0.00..18.07 rows=440 width=0) |
| Index Cond: (c = 1) |
| Optimizer: Postgres query optimizer |
| (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=401.67..401.68 rows=1 width=8) |
| -> Gather Motion 3:1 (slice1; segments: 3) (cost=401.64..401.67 rows=1 width=8) |
| -> Partial Aggregate (cost=401.64..401.65 rows=1 width=8) |
| -> Bitmap Heap Scan on bmscantest_ao (cost=29.23..392.24 rows=1254 width=0) |
| Recheck Cond: ((a = 1) OR (b = 1) OR (c = 1)) |
| -> BitmapOr (cost=29.23..29.23 rows=1277 width=0) |
| -> Bitmap Index Scan on i_bmtest_ao_a (cost=0.00..4.19 rows=441 width=0) |
| Index Cond: (a = 1) |
| -> Bitmap Index Scan on i_bmtest_ao_b (cost=0.00..4.15 rows=396 width=0) |
| Index Cond: (b = 1) |
| -> Bitmap Index Scan on i_bmtest_ao_c (cost=0.00..18.07 rows=441 width=0) |
| Index Cond: (c = 1) |
| Optimizer: Postgres query optimizer |
| (13 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=399.87..399.88 rows=1 width=8) |
| -> Gather Motion 3:1 (slice1; segments: 3) (cost=399.84..399.87 rows=1 width=8) |
| -> Partial Aggregate (cost=399.84..399.85 rows=1 width=8) |
| -> Bitmap Heap Scan on bmscantest_ao (cost=46.63..393.23 rows=881 width=0) |
| Recheck Cond: ((a = 1) OR ((b = 1) AND (c = 1)) OR (d = 1)) |
| -> BitmapOr (cost=46.63..46.63 rows=844 width=0) |
| -> Bitmap Index Scan on i_bmtest_ao_a (cost=0.00..4.19 rows=441 width=0) |
| Index Cond: (a = 1) |
| -> BitmapAnd (cost=23.80..23.80 rows=8 width=0) |
| -> Bitmap Index Scan on i_bmtest_ao_b (cost=0.00..4.15 rows=396 width=0) |
| Index Cond: (b = 1) |
| -> Bitmap Index Scan on i_bmtest_ao_c (cost=0.00..18.07 rows=441 width=0) |
| Index Cond: (c = 1) |
| -> Bitmap Index Scan on i_bmtest_ao_d (cost=0.00..17.07 rows=396 width=0) |
| Index Cond: (d = 1) |
| Optimizer: Postgres query optimizer |
| (16 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 |
| --------------------------------------------------------------------------------------------------------- |
| Finalize Aggregate (cost=31.38..31.39 rows=1 width=8) |
| -> Gather Motion 3:1 (slice1; segments: 3) (cost=31.35..31.38 rows=1 width=8) |
| -> Partial Aggregate (cost=31.35..31.36 rows=1 width=8) |
| -> Bitmap Heap Scan on bmscantest_aocs (cost=27.22..31.24 rows=15 width=0) |
| Recheck Cond: ((b = 1) AND (a = 1) AND (c = 1)) |
| -> BitmapAnd (cost=27.22..27.22 rows=1 width=0) |
| -> Bitmap Index Scan on i_bmtest_aocs_b (cost=0.00..4.29 rows=396 width=0) |
| Index Cond: (b = 1) |
| -> Bitmap Index Scan on i_bmtest_aocs_a (cost=0.00..4.33 rows=441 width=0) |
| Index Cond: (a = 1) |
| -> Bitmap Index Scan on i_bmtest_aocs_c (cost=0.00..18.07 rows=441 width=0) |
| Index Cond: (c = 1) |
| Optimizer: Postgres query optimizer |
| (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 |
| --------------------------------------------------------------------------------------------------------------- |
| Finalize Aggregate (cost=47.80..47.81 rows=1 width=8) |
| -> Gather Motion 3:1 (slice1; segments: 3) (cost=47.74..47.79 rows=3 width=8) |
| -> Partial Aggregate (cost=47.74..47.75 rows=1 width=8) |
| -> Bitmap Heap Scan on bmscantest_aocs (cost=43.68..47.70 rows=18 width=0) |
| Recheck Cond: ((a = 1) AND (d = 1) AND ((b = 1) OR (c = 1))) |
| -> BitmapAnd (cost=43.68..43.68 rows=1 width=0) |
| -> Bitmap Index Scan on i_bmtest_aocs_a (cost=0.00..4.01 rows=440 width=0) |
| Index Cond: (a = 1) |
| -> Bitmap Index Scan on i_bmtest_aocs_d (cost=0.00..17.07 rows=396 width=0) |
| Index Cond: (d = 1) |
| -> BitmapOr (cost=22.09..22.09 rows=836 width=0) |
| -> Bitmap Index Scan on i_bmtest_aocs_b (cost=0.00..4.01 rows=396 width=0) |
| Index Cond: (b = 1) |
| -> Bitmap Index Scan on i_bmtest_aocs_c (cost=0.00..18.07 rows=440 width=0) |
| Index Cond: (c = 1) |
| Optimizer: Postgres query optimizer |
| (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=371.67..371.68 rows=1 width=8) |
| -> Gather Motion 3:1 (slice1; segments: 3) (cost=371.64..371.67 rows=1 width=8) |
| -> Partial Aggregate (cost=371.64..371.65 rows=1 width=8) |
| -> Bitmap Heap Scan on bmscantest_aocs (cost=29.23..362.24 rows=1254 width=0) |
| Recheck Cond: ((a = 1) OR (b = 1) OR (c = 1)) |
| -> BitmapOr (cost=29.23..29.23 rows=1277 width=0) |
| -> Bitmap Index Scan on i_bmtest_aocs_a (cost=0.00..4.19 rows=441 width=0) |
| Index Cond: (a = 1) |
| -> Bitmap Index Scan on i_bmtest_aocs_b (cost=0.00..4.15 rows=396 width=0) |
| Index Cond: (b = 1) |
| -> Bitmap Index Scan on i_bmtest_aocs_c (cost=0.00..18.07 rows=441 width=0) |
| Index Cond: (c = 1) |
| Optimizer: Postgres query optimizer |
| (13 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=369.87..369.88 rows=1 width=8) |
| -> Gather Motion 3:1 (slice1; segments: 3) (cost=369.84..369.87 rows=1 width=8) |
| -> Partial Aggregate (cost=369.84..369.85 rows=1 width=8) |
| -> Bitmap Heap Scan on bmscantest_aocs (cost=46.63..363.23 rows=881 width=0) |
| Recheck Cond: ((a = 1) OR ((b = 1) AND (c = 1)) OR (d = 1)) |
| -> BitmapOr (cost=46.63..46.63 rows=844 width=0) |
| -> Bitmap Index Scan on i_bmtest_aocs_a (cost=0.00..4.19 rows=441 width=0) |
| Index Cond: (a = 1) |
| -> BitmapAnd (cost=23.80..23.80 rows=8 width=0) |
| -> Bitmap Index Scan on i_bmtest_aocs_b (cost=0.00..4.15 rows=396 width=0) |
| Index Cond: (b = 1) |
| -> Bitmap Index Scan on i_bmtest_aocs_c (cost=0.00..18.07 rows=441 width=0) |
| Index Cond: (c = 1) |
| -> Bitmap Index Scan on i_bmtest_aocs_d (cost=0.00..17.07 rows=396 width=0) |
| Index Cond: (d = 1) |
| Optimizer: Postgres query optimizer |
| (16 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; |
