| CREATE SCHEMA orca_static_pruning; |
| SET search_path TO orca_static_pruning; |
| CREATE TABLE rp (a int, b int, c int) DISTRIBUTED BY (a) PARTITION BY RANGE (b); |
| CREATE TABLE rp0 PARTITION OF rp FOR VALUES FROM (MINVALUE) TO (10); |
| NOTICE: table has parent, setting distribution columns to match parent table |
| CREATE TABLE rp1 PARTITION OF rp FOR VALUES FROM (10) TO (20); |
| NOTICE: table has parent, setting distribution columns to match parent table |
| CREATE TABLE rp2 PARTITION OF rp FOR VALUES FROM (4200) TO (4203); |
| NOTICE: table has parent, setting distribution columns to match parent table |
| INSERT INTO rp VALUES (0, 0, 0), (11, 11, 0), (4201, 4201, 0); |
| SELECT $query$ |
| SELECT * |
| FROM rp |
| WHERE b > 4200 |
| $query$ AS qry \gset |
| SET optimizer_trace_fallback TO on; |
| EXPLAIN (COSTS OFF, VERBOSE) |
| :qry ; |
| QUERY PLAN |
| ---------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) |
| Output: rp.a, rp.b, rp.c |
| -> Seq Scan on orca_static_pruning.rp2 rp |
| Output: rp.a, rp.b, rp.c |
| Filter: (rp.b > 4200) |
| Optimizer: Postgres query optimizer |
| (6 rows) |
| |
| :qry ; |
| a | b | c |
| ------+------+--- |
| 4201 | 4201 | 0 |
| (1 row) |
| |
| SELECT $query$ |
| SELECT * |
| FROM rp |
| WHERE b = 4201 |
| $query$ AS qry \gset |
| EXPLAIN (COSTS OFF, VERBOSE) |
| :qry ; |
| QUERY PLAN |
| ---------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) |
| Output: rp.a, rp.b, rp.c |
| -> Seq Scan on orca_static_pruning.rp2 rp |
| Output: rp.a, rp.b, rp.c |
| Filter: (rp.b = 4201) |
| Optimizer: Postgres query optimizer |
| (6 rows) |
| |
| :qry ; |
| a | b | c |
| ------+------+--- |
| 4201 | 4201 | 0 |
| (1 row) |
| |
| SELECT $query$ |
| SELECT * |
| FROM rp |
| WHERE b IN (4201, 4200) |
| $query$ AS qry \gset |
| EXPLAIN (COSTS OFF, VERBOSE) |
| :qry ; |
| QUERY PLAN |
| --------------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) |
| Output: rp.a, rp.b, rp.c |
| -> Seq Scan on orca_static_pruning.rp2 rp |
| Output: rp.a, rp.b, rp.c |
| Filter: (rp.b = ANY ('{4201,4200}'::integer[])) |
| Optimizer: Postgres query optimizer |
| (6 rows) |
| |
| :qry ; |
| a | b | c |
| ------+------+--- |
| 4201 | 4201 | 0 |
| (1 row) |
| |
| RESET optimizer_trace_fallback; |
| CREATE TABLE lp (a int, b int) DISTRIBUTED BY (a) PARTITION BY LIST (b); |
| CREATE TABLE lp0 PARTITION OF lp FOR VALUES IN (0, 1); |
| NOTICE: table has parent, setting distribution columns to match parent table |
| CREATE TABLE lp1 PARTITION OF lp FOR VALUES IN (10, 11); |
| NOTICE: table has parent, setting distribution columns to match parent table |
| CREATE TABLE lp2 PARTITION OF lp FOR VALUES IN (42, 43); |
| NOTICE: table has parent, setting distribution columns to match parent table |
| INSERT INTO lp VALUES (0, 0), (10, 10), (42, 42); |
| SET optimizer_trace_fallback TO on; |
| SELECT $query$ |
| SELECT * |
| FROM lp |
| WHERE b > 42 |
| $query$ AS qry \gset |
| EXPLAIN (COSTS OFF, VERBOSE) |
| :qry ; |
| QUERY PLAN |
| ---------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) |
| Output: lp.a, lp.b |
| -> Seq Scan on orca_static_pruning.lp2 lp |
| Output: lp.a, lp.b |
| Filter: (lp.b > 42) |
| Optimizer: Postgres query optimizer |
| (6 rows) |
| |
| :qry ; |
| a | b |
| ---+--- |
| (0 rows) |
| |
| SELECT $query$ |
| SELECT * |
| FROM lp |
| WHERE b = 42 |
| $query$ AS qry \gset |
| EXPLAIN (COSTS OFF, VERBOSE) |
| :qry ; |
| QUERY PLAN |
| ---------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) |
| Output: lp.a, lp.b |
| -> Seq Scan on orca_static_pruning.lp2 lp |
| Output: lp.a, lp.b |
| Filter: (lp.b = 42) |
| Optimizer: Postgres query optimizer |
| (6 rows) |
| |
| :qry ; |
| a | b |
| ----+---- |
| 42 | 42 |
| (1 row) |
| |
| RESET optimizer_trace_fallback; |
| CREATE TABLE hp (a int, b int) PARTITION BY HASH (b); |
| 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. |
| CREATE TABLE hp0 PARTITION OF hp FOR VALUES WITH (MODULUS 2, REMAINDER 0); |
| NOTICE: table has parent, setting distribution columns to match parent table |
| CREATE TABLE hp1 PARTITION OF hp FOR VALUES WITH (MODULUS 2, REMAINDER 1); |
| NOTICE: table has parent, setting distribution columns to match parent table |
| INSERT INTO hp VALUES (0, 1), (0, 3), (0, 4), (0, 42); |
| SET optimizer_trace_fallback TO on; |
| SELECT $query$ |
| SELECT * |
| FROM hp |
| WHERE b = 42 |
| $query$ AS qry \gset |
| EXPLAIN (COSTS OFF, VERBOSE) |
| :qry ; |
| QUERY PLAN |
| ---------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) |
| Output: hp.a, hp.b |
| -> Seq Scan on orca_static_pruning.hp0 hp |
| Output: hp.a, hp.b |
| Filter: (hp.b = 42) |
| Optimizer: Postgres query optimizer |
| (6 rows) |
| |
| :qry ; |
| a | b |
| ---+---- |
| 0 | 42 |
| (1 row) |
| |
| RESET optimizer_trace_fallback; |
| CREATE TABLE rp_multi_inds (a int, b int, c int) DISTRIBUTED BY (a) PARTITION BY RANGE (b); |
| CREATE TABLE rp_multi_inds_part1 PARTITION OF rp_multi_inds FOR VALUES FROM (MINVALUE) TO (10); |
| NOTICE: table has parent, setting distribution columns to match parent table |
| CREATE TABLE rp_multi_inds_part2 PARTITION OF rp_multi_inds FOR VALUES FROM (10) TO (20); |
| NOTICE: table has parent, setting distribution columns to match parent table |
| CREATE TABLE rp_multi_inds_part3 PARTITION OF rp_multi_inds FOR VALUES FROM (4201) TO (4203); |
| NOTICE: table has parent, setting distribution columns to match parent table |
| INSERT INTO rp_multi_inds VALUES (0, 0, 0), (11, 11, 11), (4201, 4201, 4201); |
| -- Create an index only on the selected partition |
| CREATE INDEX other_idx ON rp_multi_inds_part2 USING btree(b); |
| -- Create indexes on root table |
| CREATE INDEX rp_btree_idx ON rp_multi_inds USING btree(c); |
| CREATE INDEX rp_bitmap_idx ON rp_multi_inds USING bitmap(b); |
| -- Expect a plan that only uses the two indexes inherited from root |
| SET optimizer_enable_dynamictablescan TO off; |
| SET optimizer_trace_fallback TO on; |
| EXPLAIN (COSTS OFF, VERBOSE) SELECT * FROM rp_multi_inds WHERE b = 11 AND (c = 11 OR c = 4201); |
| QUERY PLAN |
| --------------------------------------------------------------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) |
| Output: rp_multi_inds.a, rp_multi_inds.b, rp_multi_inds.c |
| -> Bitmap Heap Scan on orca_static_pruning.rp_multi_inds_part2 rp_multi_inds |
| Output: rp_multi_inds.a, rp_multi_inds.b, rp_multi_inds.c |
| Recheck Cond: ((rp_multi_inds.b = 11) AND ((rp_multi_inds.c = 11) OR (rp_multi_inds.c = 4201))) |
| -> BitmapAnd |
| -> Bitmap Index Scan on rp_multi_inds_part2_b_idx |
| Index Cond: (rp_multi_inds.b = 11) |
| -> BitmapOr |
| -> Bitmap Index Scan on rp_multi_inds_part2_c_idx |
| Index Cond: (rp_multi_inds.c = 11) |
| -> Bitmap Index Scan on rp_multi_inds_part2_c_idx |
| Index Cond: (rp_multi_inds.c = 4201) |
| Optimizer: Postgres query optimizer |
| Settings: optimizer=off |
| (15 rows) |
| |
| SELECT * FROM rp_multi_inds WHERE b = 11 AND (c = 11 OR c = 4201); |
| a | b | c |
| ----+----+---- |
| 11 | 11 | 11 |
| (1 row) |
| |
| RESET optimizer_trace_fallback; |
| RESET optimizer_enable_dynamictablescan; |
| CREATE TABLE foo (a int, b int) DISTRIBUTED BY (a) PARTITION BY RANGE (b); |
| CREATE TABLE foo_part1 PARTITION OF foo FOR VALUES FROM (MINVALUE) TO (10); |
| NOTICE: table has parent, setting distribution columns to match parent table |
| CREATE TABLE foo_part2 PARTITION OF foo FOR VALUES FROM (10) TO (20); |
| NOTICE: table has parent, setting distribution columns to match parent table |
| CREATE TABLE foo_part3 PARTITION OF foo FOR VALUES FROM (4201) TO (4203); |
| NOTICE: table has parent, setting distribution columns to match parent table |
| CREATE INDEX foo_idx on foo(a); |
| CREATE TABLE bar (a int) DISTRIBUTED BY (a); |
| INSERT INTO foo VALUES (0, 0), (11, 11), (4201, 4201); |
| INSERT INTO bar VALUES (0), (11), (42); |
| SET optimizer_trace_fallback TO on; |
| -- Test ORCA index nested loop join has correct outer ref |
| -- Set below GUCs for planner just to keep parity |
| SET enable_hashjoin TO off; |
| SET enable_mergejoin TO off; |
| SET enable_nestloop TO on; |
| EXPLAIN (COSTS OFF, VERBOSE) SELECT * FROM foo JOIN bar on foo.a = bar.a AND foo.b = 11; |
| QUERY PLAN |
| ------------------------------------------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) |
| Output: foo.a, foo.b, bar.a |
| -> Nested Loop |
| Output: foo.a, foo.b, bar.a |
| Join Filter: (foo.a = bar.a) |
| -> Seq Scan on orca_static_pruning.bar |
| Output: bar.a |
| -> Materialize |
| Output: foo.a, foo.b |
| -> Seq Scan on orca_static_pruning.foo_part2 foo |
| Output: foo.a, foo.b |
| Filter: (foo.b = 11) |
| Optimizer: Postgres query optimizer |
| Settings: enable_hashjoin = 'off', enable_mergejoin = 'off', enable_nestloop = 'on' |
| (14 rows) |
| |
| SELECT * FROM foo JOIN bar on foo.a = bar.a AND foo.b = 11; |
| a | b | a |
| ----+----+---- |
| 11 | 11 | 11 |
| (1 row) |
| |
| RESET enable_hashjoin; |
| RESET enable_mergejoin; |
| RESET enable_nestloop; |
| CREATE TABLE rp_insert (a int, b int) PARTITION BY RANGE (b); |
| 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. |
| CREATE TABLE rp_insert_part_1 PARTITION OF rp_insert FOR VALUES FROM (0) TO (3); |
| NOTICE: table has parent, setting distribution columns to match parent table |
| CREATE TABLE rp_insert_part_2 PARTITION OF rp_insert FOR VALUES FROM (3) TO (6); |
| NOTICE: table has parent, setting distribution columns to match parent table |
| -- The INSERT plans should no longer contain Partition Selector DMLs. |
| EXPLAIN (COSTS OFF, VERBOSE) INSERT INTO rp_insert VALUES (1, 1), (3, 3); |
| QUERY PLAN |
| -------------------------------------------------------------- |
| Insert on orca_static_pruning.rp_insert |
| -> Redistribute Motion 1:3 (slice1; segments: 1) |
| Output: "*VALUES*".column1, "*VALUES*".column2 |
| Hash Key: "*VALUES*".column1 |
| -> Values Scan on "*VALUES*" |
| Output: "*VALUES*".column1, "*VALUES*".column2 |
| Optimizer: Postgres query optimizer |
| Settings: optimizer=off |
| (8 rows) |
| |
| INSERT INTO rp_insert VALUES (1, 1), (3, 3); |
| EXPLAIN (COSTS OFF, VERBOSE) INSERT INTO rp_insert SELECT * FROM rp_insert; |
| QUERY PLAN |
| -------------------------------------------------------------------------- |
| Insert on orca_static_pruning.rp_insert |
| -> Append |
| -> Seq Scan on orca_static_pruning.rp_insert_part_1 rp_insert_2 |
| Output: rp_insert_2.a, rp_insert_2.b |
| -> Seq Scan on orca_static_pruning.rp_insert_part_2 rp_insert_3 |
| Output: rp_insert_3.a, rp_insert_3.b |
| Optimizer: Postgres query optimizer |
| Settings: optimizer=off |
| (8 rows) |
| |
| INSERT INTO rp_insert SELECT * FROM rp_insert; |
| SELECT * FROM rp_insert; |
| a | b |
| ---+--- |
| 3 | 3 |
| 3 | 3 |
| 1 | 1 |
| 1 | 1 |
| (4 rows) |
| |
