Google GitGoogle Git
Sign in
apache/cloudberry/refs/heads/cbdb-postgres-merge/./src/test/regress/expected/partition_prune_1.out
blob: e3bab4811e380a00c154df173f40375e9800888c [file]
--
-- Test partitioning planner code
--
-- GPDB:
-- One of the queries EXPLAINed in this file executes on one or two segments,
-- depending on random choice by the planner. Accept either plan.
-- start_matchsubs
-- m/ Gather Motion [12]:1 \(slice1; segments: [12]\)/
-- s/ Gather Motion [12]:1 \(slice1; segments: [12]\)/ Gather Motion XXX/
-- m/Memory Usage: \d+\w?B/
-- s/Memory Usage: \d+\w?B/Memory Usage: ###B/
-- m/Buckets: \d+/
-- s/Buckets: \d+/Buckets: ###/
-- m/Batches: \d+/
-- s/Batches: \d+/Batches: ###/
-- m/Extra Text: \(seg\d+\)/
-- s/Extra Text: \(seg\d+\)/Extra Text: ###/
-- m/Hash chain length \d+\.\d+ avg, \d+ max/
-- s/Hash chain length \d+\.\d+ avg, \d+ max/Hash chain length ###/
-- m/using \d+ of \d+ buckets/
-- s/using \d+ of \d+ buckets/using ## of ### buckets/
-- m/((0[1-9]|1[0-2])-(0[1-9]|1[0-9]|2[0-9])|(0[13-9]|1[0-2])-30|(0[13578]|1[02])-31)-(?!0000)[0-9]{4}/
-- s/((0[1-9]|1[0-2])-(0[1-9]|1[0-9]|2[0-9])|(0[13-9]|1[0-2])-30|(0[13578]|1[02])-31)-(?!0000)[0-9]{4}/xx-xx-xxxx/
-- m/((Mon|Tue|Wed|Thu|Fri|Sat|Sun) (Jan|Feb|Mar|Apr|May|Jun|Jul|Aug|Sep|Oct|Nov|Dec) (0[1-9]|[12][0-9]|3[01]) ([01][0-9]|2[0-3]):[0-5][0-9]:[0-5][0-9](.[0-9]+)? (?!0000)[0-9]{4}.*)+(['"])/
-- s/((Mon|Tue|Wed|Thu|Fri|Sat|Sun) (Jan|Feb|Mar|Apr|May|Jun|Jul|Aug|Sep|Oct|Nov|Dec) (0[1-9]|[12][0-9]|3[01]) ([01][0-9]|2[0-3]):[0-5][0-9]:[0-5][0-9](.[0-9]+)? (?!0000)[0-9]{4}.*)+(['"])/xxx xx xx xx:xx:xx xxxx"/
-- end_matchsubs
-- Force generic plans to be used for all prepared statements in this file.
set plan_cache_mode = force_generic_plan;
create table lp (a char) partition by list (a);
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 lp_default partition of lp default;
NOTICE: table has parent, setting distribution columns to match parent table
create table lp_ef partition of lp for values in ('e', 'f');
NOTICE: table has parent, setting distribution columns to match parent table
create table lp_ad partition of lp for values in ('a', 'd');
NOTICE: table has parent, setting distribution columns to match parent table
create table lp_bc partition of lp for values in ('b', 'c');
NOTICE: table has parent, setting distribution columns to match parent table
create table lp_g partition of lp for values in ('g');
NOTICE: table has parent, setting distribution columns to match parent table
create table lp_null partition of lp for values in (null);
NOTICE: table has parent, setting distribution columns to match parent table
explain (costs off) select * from lp;
QUERY PLAN
------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Append
-> Seq Scan on lp_ad lp_1
-> Seq Scan on lp_bc lp_2
-> Seq Scan on lp_ef lp_3
-> Seq Scan on lp_g lp_4
-> Seq Scan on lp_null lp_5
-> Seq Scan on lp_default lp_6
Optimizer: Postgres query optimizer
(9 rows)
explain (costs off) select * from lp where a > 'a' and a < 'd';
QUERY PLAN
-----------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Append
-> Seq Scan on lp_bc lp_1
Filter: ((a > 'a'::bpchar) AND (a < 'd'::bpchar))
-> Seq Scan on lp_default lp_2
Filter: ((a > 'a'::bpchar) AND (a < 'd'::bpchar))
Optimizer: Postgres query optimizer
(7 rows)
explain (costs off) select * from lp where a > 'a' and a <= 'd';
QUERY PLAN
------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Append
-> Seq Scan on lp_ad lp_1
Filter: ((a > 'a'::bpchar) AND (a <= 'd'::bpchar))
-> Seq Scan on lp_bc lp_2
Filter: ((a > 'a'::bpchar) AND (a <= 'd'::bpchar))
-> Seq Scan on lp_default lp_3
Filter: ((a > 'a'::bpchar) AND (a <= 'd'::bpchar))
Optimizer: Postgres query optimizer
(9 rows)
explain (costs off) select * from lp where a = 'a';
QUERY PLAN
------------------------------------------
Gather Motion 1:1 (slice1; segments: 1)
-> Seq Scan on lp_ad lp
Filter: (a = 'a'::bpchar)
Optimizer: Postgres query optimizer
(4 rows)
explain (costs off) select * from lp where 'a' = a; /* commuted */
QUERY PLAN
------------------------------------------
Gather Motion 1:1 (slice1; segments: 1)
-> Seq Scan on lp_ad lp
Filter: ('a'::bpchar = a)
Optimizer: Postgres query optimizer
(4 rows)
explain (costs off) select * from lp where a is not null;
QUERY PLAN
------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Append
-> Seq Scan on lp_ad lp_1
Filter: (a IS NOT NULL)
-> Seq Scan on lp_bc lp_2
Filter: (a IS NOT NULL)
-> Seq Scan on lp_ef lp_3
Filter: (a IS NOT NULL)
-> Seq Scan on lp_g lp_4
Filter: (a IS NOT NULL)
-> Seq Scan on lp_default lp_5
Filter: (a IS NOT NULL)
Optimizer: Postgres query optimizer
(13 rows)
explain (costs off) select * from lp where a is null;
QUERY PLAN
------------------------------------------
Gather Motion 1:1 (slice1; segments: 1)
-> Seq Scan on lp_null lp
Filter: (a IS NULL)
Optimizer: Postgres query optimizer
(4 rows)
explain (costs off) select * from lp where a = 'a' or a = 'c';
QUERY PLAN
----------------------------------------------------------------
Gather Motion 1:1 (slice1; segments: 1)
-> Append
-> Seq Scan on lp_ad lp_1
Filter: ((a = 'a'::bpchar) OR (a = 'c'::bpchar))
-> Seq Scan on lp_bc lp_2
Filter: ((a = 'a'::bpchar) OR (a = 'c'::bpchar))
Optimizer: Postgres query optimizer
(7 rows)
explain (costs off) select * from lp where a is not null and (a = 'a' or a = 'c');
QUERY PLAN
--------------------------------------------------------------------------------------
Gather Motion 1:1 (slice1; segments: 1)
-> Append
-> Seq Scan on lp_ad lp_1
Filter: ((a IS NOT NULL) AND ((a = 'a'::bpchar) OR (a = 'c'::bpchar)))
-> Seq Scan on lp_bc lp_2
Filter: ((a IS NOT NULL) AND ((a = 'a'::bpchar) OR (a = 'c'::bpchar)))
Optimizer: Postgres query optimizer
(7 rows)
explain (costs off) select * from lp where a <> 'g';
QUERY PLAN
------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Append
-> Seq Scan on lp_ad lp_1
Filter: (a <> 'g'::bpchar)
-> Seq Scan on lp_bc lp_2
Filter: (a <> 'g'::bpchar)
-> Seq Scan on lp_ef lp_3
Filter: (a <> 'g'::bpchar)
-> Seq Scan on lp_default lp_4
Filter: (a <> 'g'::bpchar)
Optimizer: Postgres query optimizer
(11 rows)
explain (costs off) select * from lp where a <> 'a' and a <> 'd';
QUERY PLAN
-------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Append
-> Seq Scan on lp_bc lp_1
Filter: ((a <> 'a'::bpchar) AND (a <> 'd'::bpchar))
-> Seq Scan on lp_ef lp_2
Filter: ((a <> 'a'::bpchar) AND (a <> 'd'::bpchar))
-> Seq Scan on lp_g lp_3
Filter: ((a <> 'a'::bpchar) AND (a <> 'd'::bpchar))
-> Seq Scan on lp_default lp_4
Filter: ((a <> 'a'::bpchar) AND (a <> 'd'::bpchar))
Optimizer: Postgres query optimizer
(11 rows)
explain (costs off) select * from lp where a not in ('a', 'd');
QUERY PLAN
------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Append
-> Seq Scan on lp_bc lp_1
Filter: (a <> ALL ('{a,d}'::bpchar[]))
-> Seq Scan on lp_ef lp_2
Filter: (a <> ALL ('{a,d}'::bpchar[]))
-> Seq Scan on lp_g lp_3
Filter: (a <> ALL ('{a,d}'::bpchar[]))
-> Seq Scan on lp_default lp_4
Filter: (a <> ALL ('{a,d}'::bpchar[]))
Optimizer: Postgres query optimizer
(11 rows)
-- collation matches the partitioning collation, pruning works
create table coll_pruning (a text collate "C") partition by list (a);
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 coll_pruning_a partition of coll_pruning for values in ('a');
NOTICE: table has parent, setting distribution columns to match parent table
create table coll_pruning_b partition of coll_pruning for values in ('b');
NOTICE: table has parent, setting distribution columns to match parent table
create table coll_pruning_def partition of coll_pruning default;
NOTICE: table has parent, setting distribution columns to match parent table
explain (costs off) select * from coll_pruning where a collate "C" = 'a' collate "C";
QUERY PLAN
-----------------------------------------------
Gather Motion 1:1 (slice1; segments: 1)
-> Seq Scan on coll_pruning_a coll_pruning
Filter: (a = 'a'::text COLLATE "C")
Optimizer: Postgres query optimizer
(4 rows)
-- collation doesn't match the partitioning collation, no pruning occurs
explain (costs off) select * from coll_pruning where a collate "POSIX" = 'a' collate "POSIX";
QUERY PLAN
---------------------------------------------------------------
Gather Motion 1:1 (slice1; segments: 1)
-> Append
-> Seq Scan on coll_pruning_a coll_pruning_1
Filter: ((a)::text = 'a'::text COLLATE "POSIX")
-> Seq Scan on coll_pruning_b coll_pruning_2
Filter: ((a)::text = 'a'::text COLLATE "POSIX")
-> Seq Scan on coll_pruning_def coll_pruning_3
Filter: ((a)::text = 'a'::text COLLATE "POSIX")
Optimizer: Postgres query optimizer
(9 rows)
create table rlp (a int, b varchar) partition by range (a);
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 rlp_default partition of rlp default partition by list (a);
NOTICE: table has parent, setting distribution columns to match parent table
create table rlp_default_default partition of rlp_default default;
NOTICE: table has parent, setting distribution columns to match parent table
create table rlp_default_10 partition of rlp_default for values in (10);
NOTICE: table has parent, setting distribution columns to match parent table
create table rlp_default_30 partition of rlp_default for values in (30);
NOTICE: table has parent, setting distribution columns to match parent table
create table rlp_default_null partition of rlp_default for values in (null);
NOTICE: table has parent, setting distribution columns to match parent table
create table rlp1 partition of rlp for values from (minvalue) to (1);
NOTICE: table has parent, setting distribution columns to match parent table
create table rlp2 partition of rlp for values from (1) to (10);
NOTICE: table has parent, setting distribution columns to match parent table
create table rlp3 (b varchar, a int) partition by list (b varchar_ops);
NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'b' 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.
-- GPDB: distribution policy must match the parent table.
alter table rlp3 set distributed by (a);
create table rlp3_default partition of rlp3 default;
NOTICE: table has parent, setting distribution columns to match parent table
create table rlp3abcd partition of rlp3 for values in ('ab', 'cd');
NOTICE: table has parent, setting distribution columns to match parent table
create table rlp3efgh partition of rlp3 for values in ('ef', 'gh');
NOTICE: table has parent, setting distribution columns to match parent table
create table rlp3nullxy partition of rlp3 for values in (null, 'xy');
NOTICE: table has parent, setting distribution columns to match parent table
alter table rlp attach partition rlp3 for values from (15) to (20);
create table rlp4 partition of rlp for values from (20) to (30) partition by range (a);
NOTICE: table has parent, setting distribution columns to match parent table
create table rlp4_default partition of rlp4 default;
NOTICE: table has parent, setting distribution columns to match parent table
create table rlp4_1 partition of rlp4 for values from (20) to (25);
NOTICE: table has parent, setting distribution columns to match parent table
create table rlp4_2 partition of rlp4 for values from (25) to (29);
NOTICE: table has parent, setting distribution columns to match parent table
create table rlp5 partition of rlp for values from (31) to (maxvalue) partition by range (a);
NOTICE: table has parent, setting distribution columns to match parent table
create table rlp5_default partition of rlp5 default;
NOTICE: table has parent, setting distribution columns to match parent table
create table rlp5_1 partition of rlp5 for values from (31) to (40);
NOTICE: table has parent, setting distribution columns to match parent table
explain (costs off) select * from rlp where a < 1;
QUERY PLAN
------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Seq Scan on rlp1 rlp
Filter: (a < 1)
Optimizer: Postgres query optimizer
(4 rows)
explain (costs off) select * from rlp where 1 > a; /* commuted */
QUERY PLAN
------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Seq Scan on rlp1 rlp
Filter: (1 > a)
Optimizer: Postgres query optimizer
(4 rows)
explain (costs off) select * from rlp where a <= 1;
QUERY PLAN
------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Append
-> Seq Scan on rlp1 rlp_1
Filter: (a <= 1)
-> Seq Scan on rlp2 rlp_2
Filter: (a <= 1)
Optimizer: Postgres query optimizer
(7 rows)
explain (costs off) select * from rlp where a = 1;
QUERY PLAN
------------------------------------------
Gather Motion 1:1 (slice1; segments: 1)
-> Seq Scan on rlp2 rlp
Filter: (a = 1)
Optimizer: Postgres query optimizer
(4 rows)
explain (costs off) select * from rlp where a = 1::bigint; /* same as above */
QUERY PLAN
------------------------------------------
Gather Motion 1:1 (slice1; segments: 1)
-> Seq Scan on rlp2 rlp
Filter: (a = '1'::bigint)
Optimizer: Postgres query optimizer
(4 rows)
explain (costs off) select * from rlp where a = 1::numeric; /* no pruning */
QUERY PLAN
-----------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Append
-> Seq Scan on rlp1 rlp_1
Filter: ((a)::numeric = '1'::numeric)
-> Seq Scan on rlp2 rlp_2
Filter: ((a)::numeric = '1'::numeric)
-> Seq Scan on rlp3abcd rlp_3
Filter: ((a)::numeric = '1'::numeric)
-> Seq Scan on rlp3efgh rlp_4
Filter: ((a)::numeric = '1'::numeric)
-> Seq Scan on rlp3nullxy rlp_5
Filter: ((a)::numeric = '1'::numeric)
-> Seq Scan on rlp3_default rlp_6
Filter: ((a)::numeric = '1'::numeric)
-> Seq Scan on rlp4_1 rlp_7
Filter: ((a)::numeric = '1'::numeric)
-> Seq Scan on rlp4_2 rlp_8
Filter: ((a)::numeric = '1'::numeric)
-> Seq Scan on rlp4_default rlp_9
Filter: ((a)::numeric = '1'::numeric)
-> Seq Scan on rlp5_1 rlp_10
Filter: ((a)::numeric = '1'::numeric)
-> Seq Scan on rlp5_default rlp_11
Filter: ((a)::numeric = '1'::numeric)
-> Seq Scan on rlp_default_10 rlp_12
Filter: ((a)::numeric = '1'::numeric)
-> Seq Scan on rlp_default_30 rlp_13
Filter: ((a)::numeric = '1'::numeric)
-> Seq Scan on rlp_default_null rlp_14
Filter: ((a)::numeric = '1'::numeric)
-> Seq Scan on rlp_default_default rlp_15
Filter: ((a)::numeric = '1'::numeric)
Optimizer: Postgres query optimizer
(33 rows)
explain (costs off) select * from rlp where a <= 10;
QUERY PLAN
---------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Append
-> Seq Scan on rlp1 rlp_1
Filter: (a <= 10)
-> Seq Scan on rlp2 rlp_2
Filter: (a <= 10)
-> Seq Scan on rlp_default_10 rlp_3
Filter: (a <= 10)
-> Seq Scan on rlp_default_default rlp_4
Filter: (a <= 10)
Optimizer: Postgres query optimizer
(11 rows)
explain (costs off) select * from rlp where a > 10;
QUERY PLAN
----------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Append
-> Seq Scan on rlp3abcd rlp_1
Filter: (a > 10)
-> Seq Scan on rlp3efgh rlp_2
Filter: (a > 10)
-> Seq Scan on rlp3nullxy rlp_3
Filter: (a > 10)
-> Seq Scan on rlp3_default rlp_4
Filter: (a > 10)
-> Seq Scan on rlp4_1 rlp_5
Filter: (a > 10)
-> Seq Scan on rlp4_2 rlp_6
Filter: (a > 10)
-> Seq Scan on rlp4_default rlp_7
Filter: (a > 10)
-> Seq Scan on rlp5_1 rlp_8
Filter: (a > 10)
-> Seq Scan on rlp5_default rlp_9
Filter: (a > 10)
-> Seq Scan on rlp_default_30 rlp_10
Filter: (a > 10)
-> Seq Scan on rlp_default_default rlp_11
Filter: (a > 10)
Optimizer: Postgres query optimizer
(25 rows)
explain (costs off) select * from rlp where a < 15;
QUERY PLAN
---------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Append
-> Seq Scan on rlp1 rlp_1
Filter: (a < 15)
-> Seq Scan on rlp2 rlp_2
Filter: (a < 15)
-> Seq Scan on rlp_default_10 rlp_3
Filter: (a < 15)
-> Seq Scan on rlp_default_default rlp_4
Filter: (a < 15)
Optimizer: Postgres query optimizer
(11 rows)
explain (costs off) select * from rlp where a <= 15;
QUERY PLAN
---------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Append
-> Seq Scan on rlp1 rlp_1
Filter: (a <= 15)
-> Seq Scan on rlp2 rlp_2
Filter: (a <= 15)
-> Seq Scan on rlp3abcd rlp_3
Filter: (a <= 15)
-> Seq Scan on rlp3efgh rlp_4
Filter: (a <= 15)
-> Seq Scan on rlp3nullxy rlp_5
Filter: (a <= 15)
-> Seq Scan on rlp3_default rlp_6
Filter: (a <= 15)
-> Seq Scan on rlp_default_10 rlp_7
Filter: (a <= 15)
-> Seq Scan on rlp_default_default rlp_8
Filter: (a <= 15)
Optimizer: Postgres query optimizer
(19 rows)
explain (costs off) select * from rlp where a > 15 and b = 'ab';
QUERY PLAN
---------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Append
-> Seq Scan on rlp3abcd rlp_1
Filter: ((a > 15) AND ((b)::text = 'ab'::text))
-> Seq Scan on rlp4_1 rlp_2
Filter: ((a > 15) AND ((b)::text = 'ab'::text))
-> Seq Scan on rlp4_2 rlp_3
Filter: ((a > 15) AND ((b)::text = 'ab'::text))
-> Seq Scan on rlp4_default rlp_4
Filter: ((a > 15) AND ((b)::text = 'ab'::text))
-> Seq Scan on rlp5_1 rlp_5
Filter: ((a > 15) AND ((b)::text = 'ab'::text))
-> Seq Scan on rlp5_default rlp_6
Filter: ((a > 15) AND ((b)::text = 'ab'::text))
-> Seq Scan on rlp_default_30 rlp_7
Filter: ((a > 15) AND ((b)::text = 'ab'::text))
-> Seq Scan on rlp_default_default rlp_8
Filter: ((a > 15) AND ((b)::text = 'ab'::text))
Optimizer: Postgres query optimizer
(19 rows)
explain (costs off) select * from rlp where a = 16;
QUERY PLAN
--------------------------------------------
Gather Motion 1:1 (slice1; segments: 1)
-> Append
-> Seq Scan on rlp3abcd rlp_1
Filter: (a = 16)
-> Seq Scan on rlp3efgh rlp_2
Filter: (a = 16)
-> Seq Scan on rlp3nullxy rlp_3
Filter: (a = 16)
-> Seq Scan on rlp3_default rlp_4
Filter: (a = 16)
Optimizer: Postgres query optimizer
(11 rows)
explain (costs off) select * from rlp where a = 16 and b in ('not', 'in', 'here');
QUERY PLAN
----------------------------------------------------------------------------
Gather Motion 1:1 (slice1; segments: 1)
-> Seq Scan on rlp3_default rlp
Filter: ((a = 16) AND ((b)::text = ANY ('{not,in,here}'::text[])))
Optimizer: Postgres query optimizer
(4 rows)
explain (costs off) select * from rlp where a = 16 and b < 'ab';
QUERY PLAN
---------------------------------------------------------
Gather Motion 1:1 (slice1; segments: 1)
-> Seq Scan on rlp3_default rlp
Filter: (((b)::text < 'ab'::text) AND (a = 16))
Optimizer: Postgres query optimizer
(4 rows)
explain (costs off) select * from rlp where a = 16 and b <= 'ab';
QUERY PLAN
----------------------------------------------------------------
Gather Motion 1:1 (slice1; segments: 1)
-> Append
-> Seq Scan on rlp3abcd rlp_1
Filter: (((b)::text <= 'ab'::text) AND (a = 16))
-> Seq Scan on rlp3_default rlp_2
Filter: (((b)::text <= 'ab'::text) AND (a = 16))
Optimizer: Postgres query optimizer
(7 rows)
explain (costs off) select * from rlp where a = 16 and b is null;
QUERY PLAN
--------------------------------------------
Gather Motion 1:1 (slice1; segments: 1)
-> Seq Scan on rlp3nullxy rlp
Filter: ((b IS NULL) AND (a = 16))
Optimizer: Postgres query optimizer
(4 rows)
explain (costs off) select * from rlp where a = 16 and b is not null;
QUERY PLAN
------------------------------------------------------
Gather Motion 1:1 (slice1; segments: 1)
-> Append
-> Seq Scan on rlp3abcd rlp_1
Filter: ((b IS NOT NULL) AND (a = 16))
-> Seq Scan on rlp3efgh rlp_2
Filter: ((b IS NOT NULL) AND (a = 16))
-> Seq Scan on rlp3nullxy rlp_3
Filter: ((b IS NOT NULL) AND (a = 16))
-> Seq Scan on rlp3_default rlp_4
Filter: ((b IS NOT NULL) AND (a = 16))
Optimizer: Postgres query optimizer
(11 rows)
explain (costs off) select * from rlp where a is null;
QUERY PLAN
------------------------------------------
Gather Motion 1:1 (slice1; segments: 1)
-> Seq Scan on rlp_default_null rlp
Filter: (a IS NULL)
Optimizer: Postgres query optimizer
(4 rows)
explain (costs off) select * from rlp where a is not null;
QUERY PLAN
----------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Append
-> Seq Scan on rlp1 rlp_1
Filter: (a IS NOT NULL)
-> Seq Scan on rlp2 rlp_2
Filter: (a IS NOT NULL)
-> Seq Scan on rlp3abcd rlp_3
Filter: (a IS NOT NULL)
-> Seq Scan on rlp3efgh rlp_4
Filter: (a IS NOT NULL)
-> Seq Scan on rlp3nullxy rlp_5
Filter: (a IS NOT NULL)
-> Seq Scan on rlp3_default rlp_6
Filter: (a IS NOT NULL)
-> Seq Scan on rlp4_1 rlp_7
Filter: (a IS NOT NULL)
-> Seq Scan on rlp4_2 rlp_8
Filter: (a IS NOT NULL)
-> Seq Scan on rlp4_default rlp_9
Filter: (a IS NOT NULL)
-> Seq Scan on rlp5_1 rlp_10
Filter: (a IS NOT NULL)
-> Seq Scan on rlp5_default rlp_11
Filter: (a IS NOT NULL)
-> Seq Scan on rlp_default_10 rlp_12
Filter: (a IS NOT NULL)
-> Seq Scan on rlp_default_30 rlp_13
Filter: (a IS NOT NULL)
-> Seq Scan on rlp_default_default rlp_14
Filter: (a IS NOT NULL)
Optimizer: Postgres query optimizer
(31 rows)
explain (costs off) select * from rlp where a > 30;
QUERY PLAN
---------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Append
-> Seq Scan on rlp5_1 rlp_1
Filter: (a > 30)
-> Seq Scan on rlp5_default rlp_2
Filter: (a > 30)
-> Seq Scan on rlp_default_default rlp_3
Filter: (a > 30)
Optimizer: Postgres query optimizer
(9 rows)
explain (costs off) select * from rlp where a = 30; /* only default is scanned */
QUERY PLAN
------------------------------------------
Gather Motion 1:1 (slice1; segments: 1)
-> Seq Scan on rlp_default_30 rlp
Filter: (a = 30)
Optimizer: Postgres query optimizer
(4 rows)
explain (costs off) select * from rlp where a <= 31;
QUERY PLAN
----------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Append
-> Seq Scan on rlp1 rlp_1
Filter: (a <= 31)
-> Seq Scan on rlp2 rlp_2
Filter: (a <= 31)
-> Seq Scan on rlp3abcd rlp_3
Filter: (a <= 31)
-> Seq Scan on rlp3efgh rlp_4
Filter: (a <= 31)
-> Seq Scan on rlp3nullxy rlp_5
Filter: (a <= 31)
-> Seq Scan on rlp3_default rlp_6
Filter: (a <= 31)
-> Seq Scan on rlp4_1 rlp_7
Filter: (a <= 31)
-> Seq Scan on rlp4_2 rlp_8
Filter: (a <= 31)
-> Seq Scan on rlp4_default rlp_9
Filter: (a <= 31)
-> Seq Scan on rlp5_1 rlp_10
Filter: (a <= 31)
-> Seq Scan on rlp_default_10 rlp_11
Filter: (a <= 31)
-> Seq Scan on rlp_default_30 rlp_12
Filter: (a <= 31)
-> Seq Scan on rlp_default_default rlp_13
Filter: (a <= 31)
Optimizer: Postgres query optimizer
(29 rows)
explain (costs off) select * from rlp where a = 1 or a = 7;
QUERY PLAN
------------------------------------------
Gather Motion 2:1 (slice1; segments: 2)
-> Seq Scan on rlp2 rlp
Filter: ((a = 1) OR (a = 7))
Optimizer: Postgres query optimizer
(4 rows)
explain (costs off) select * from rlp where a = 1 or b = 'ab';
QUERY PLAN
-------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Append
-> Seq Scan on rlp1 rlp_1
Filter: ((a = 1) OR ((b)::text = 'ab'::text))
-> Seq Scan on rlp2 rlp_2
Filter: ((a = 1) OR ((b)::text = 'ab'::text))
-> Seq Scan on rlp3abcd rlp_3
Filter: ((a = 1) OR ((b)::text = 'ab'::text))
-> Seq Scan on rlp4_1 rlp_4
Filter: ((a = 1) OR ((b)::text = 'ab'::text))
-> Seq Scan on rlp4_2 rlp_5
Filter: ((a = 1) OR ((b)::text = 'ab'::text))
-> Seq Scan on rlp4_default rlp_6
Filter: ((a = 1) OR ((b)::text = 'ab'::text))
-> Seq Scan on rlp5_1 rlp_7
Filter: ((a = 1) OR ((b)::text = 'ab'::text))
-> Seq Scan on rlp5_default rlp_8
Filter: ((a = 1) OR ((b)::text = 'ab'::text))
-> Seq Scan on rlp_default_10 rlp_9
Filter: ((a = 1) OR ((b)::text = 'ab'::text))
-> Seq Scan on rlp_default_30 rlp_10
Filter: ((a = 1) OR ((b)::text = 'ab'::text))
-> Seq Scan on rlp_default_null rlp_11
Filter: ((a = 1) OR ((b)::text = 'ab'::text))
-> Seq Scan on rlp_default_default rlp_12
Filter: ((a = 1) OR ((b)::text = 'ab'::text))
Optimizer: Postgres query optimizer
(27 rows)
explain (costs off) select * from rlp where a > 20 and a < 27;
QUERY PLAN
-----------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Append
-> Seq Scan on rlp4_1 rlp_1
Filter: ((a > 20) AND (a < 27))
-> Seq Scan on rlp4_2 rlp_2
Filter: ((a > 20) AND (a < 27))
Optimizer: Postgres query optimizer
(7 rows)
explain (costs off) select * from rlp where a = 29;
QUERY PLAN
------------------------------------------
Gather Motion 1:1 (slice1; segments: 1)
-> Seq Scan on rlp4_default rlp
Filter: (a = 29)
Optimizer: Postgres query optimizer
(4 rows)
explain (costs off) select * from rlp where a >= 29;
QUERY PLAN
---------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Append
-> Seq Scan on rlp4_default rlp_1
Filter: (a >= 29)
-> Seq Scan on rlp5_1 rlp_2
Filter: (a >= 29)
-> Seq Scan on rlp5_default rlp_3
Filter: (a >= 29)
-> Seq Scan on rlp_default_30 rlp_4
Filter: (a >= 29)
-> Seq Scan on rlp_default_default rlp_5
Filter: (a >= 29)
Optimizer: Postgres query optimizer
(13 rows)
explain (costs off) select * from rlp where a < 1 or (a > 20 and a < 25);
QUERY PLAN
------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Append
-> Seq Scan on rlp1 rlp_1
Filter: ((a < 1) OR ((a > 20) AND (a < 25)))
-> Seq Scan on rlp4_1 rlp_2
Filter: ((a < 1) OR ((a > 20) AND (a < 25)))
Optimizer: Postgres query optimizer
(7 rows)
-- where clause contradicts sub-partition's constraint
explain (costs off) select * from rlp where a = 20 or a = 40;
QUERY PLAN
----------------------------------------------
Gather Motion 1:1 (slice1; segments: 1)
-> Append
-> Seq Scan on rlp4_1 rlp_1
Filter: ((a = 20) OR (a = 40))
-> Seq Scan on rlp5_default rlp_2
Filter: ((a = 20) OR (a = 40))
Optimizer: Postgres query optimizer
(7 rows)
explain (costs off) select * from rlp3 where a = 20; /* empty */
QUERY PLAN
-------------------------------------
Result
One-Time Filter: false
Optimizer: Postgres query optimizer
(3 rows)
-- redundant clauses are eliminated
explain (costs off) select * from rlp where a > 1 and a = 10; /* only default */
QUERY PLAN
------------------------------------------
Gather Motion 1:1 (slice1; segments: 1)
-> Seq Scan on rlp_default_10 rlp
Filter: ((a > 1) AND (a = 10))
Optimizer: Postgres query optimizer
(4 rows)
explain (costs off) select * from rlp where a > 1 and a >=15; /* rlp3 onwards, including default */
QUERY PLAN
----------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Append
-> Seq Scan on rlp3abcd rlp_1
Filter: ((a > 1) AND (a >= 15))
-> Seq Scan on rlp3efgh rlp_2
Filter: ((a > 1) AND (a >= 15))
-> Seq Scan on rlp3nullxy rlp_3
Filter: ((a > 1) AND (a >= 15))
-> Seq Scan on rlp3_default rlp_4
Filter: ((a > 1) AND (a >= 15))
-> Seq Scan on rlp4_1 rlp_5
Filter: ((a > 1) AND (a >= 15))
-> Seq Scan on rlp4_2 rlp_6
Filter: ((a > 1) AND (a >= 15))
-> Seq Scan on rlp4_default rlp_7
Filter: ((a > 1) AND (a >= 15))
-> Seq Scan on rlp5_1 rlp_8
Filter: ((a > 1) AND (a >= 15))
-> Seq Scan on rlp5_default rlp_9
Filter: ((a > 1) AND (a >= 15))
-> Seq Scan on rlp_default_30 rlp_10
Filter: ((a > 1) AND (a >= 15))
-> Seq Scan on rlp_default_default rlp_11
Filter: ((a > 1) AND (a >= 15))
Optimizer: Postgres query optimizer
(25 rows)
explain (costs off) select * from rlp where a = 1 and a = 3; /* empty */
QUERY PLAN
-------------------------------------
Result
One-Time Filter: false
Optimizer: Postgres query optimizer
(3 rows)
explain (costs off) select * from rlp where (a = 1 and a = 3) or (a > 1 and a = 15);
QUERY PLAN
-------------------------------------------------------------------------
Gather Motion 1:1 (slice1; segments: 1)
-> Append
-> Seq Scan on rlp2 rlp_1
Filter: (((a = 1) AND (a = 3)) OR ((a > 1) AND (a = 15)))
-> Seq Scan on rlp3abcd rlp_2
Filter: (((a = 1) AND (a = 3)) OR ((a > 1) AND (a = 15)))
-> Seq Scan on rlp3efgh rlp_3
Filter: (((a = 1) AND (a = 3)) OR ((a > 1) AND (a = 15)))
-> Seq Scan on rlp3nullxy rlp_4
Filter: (((a = 1) AND (a = 3)) OR ((a > 1) AND (a = 15)))
-> Seq Scan on rlp3_default rlp_5
Filter: (((a = 1) AND (a = 3)) OR ((a > 1) AND (a = 15)))
Optimizer: Postgres query optimizer
(13 rows)
-- multi-column keys
create table mc3p (a int, b int, c int) partition by range (a, abs(b), c);
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 mc3p_default partition of mc3p default;
NOTICE: table has parent, setting distribution columns to match parent table
create table mc3p0 partition of mc3p for values from (minvalue, minvalue, minvalue) to (1, 1, 1);
NOTICE: table has parent, setting distribution columns to match parent table
create table mc3p1 partition of mc3p for values from (1, 1, 1) to (10, 5, 10);
NOTICE: table has parent, setting distribution columns to match parent table
create table mc3p2 partition of mc3p for values from (10, 5, 10) to (10, 10, 10);
NOTICE: table has parent, setting distribution columns to match parent table
create table mc3p3 partition of mc3p for values from (10, 10, 10) to (10, 10, 20);
NOTICE: table has parent, setting distribution columns to match parent table
create table mc3p4 partition of mc3p for values from (10, 10, 20) to (10, maxvalue, maxvalue);
NOTICE: table has parent, setting distribution columns to match parent table
create table mc3p5 partition of mc3p for values from (11, 1, 1) to (20, 10, 10);
NOTICE: table has parent, setting distribution columns to match parent table
create table mc3p6 partition of mc3p for values from (20, 10, 10) to (20, 20, 20);
NOTICE: table has parent, setting distribution columns to match parent table
create table mc3p7 partition of mc3p for values from (20, 20, 20) to (maxvalue, maxvalue, maxvalue);
NOTICE: table has parent, setting distribution columns to match parent table
explain (costs off) select * from mc3p where a = 1;
QUERY PLAN
---------------------------------------------
Gather Motion 1:1 (slice1; segments: 1)
-> Append
-> Seq Scan on mc3p0 mc3p_1
Filter: (a = 1)
-> Seq Scan on mc3p1 mc3p_2
Filter: (a = 1)
-> Seq Scan on mc3p_default mc3p_3
Filter: (a = 1)
Optimizer: Postgres query optimizer
(9 rows)
explain (costs off) select * from mc3p where a = 1 and abs(b) < 1;
QUERY PLAN
--------------------------------------------------
Gather Motion 1:1 (slice1; segments: 1)
-> Append
-> Seq Scan on mc3p0 mc3p_1
Filter: ((a = 1) AND (abs(b) < 1))
-> Seq Scan on mc3p_default mc3p_2
Filter: ((a = 1) AND (abs(b) < 1))
Optimizer: Postgres query optimizer
(7 rows)
explain (costs off) select * from mc3p where a = 1 and abs(b) = 1;
QUERY PLAN
--------------------------------------------------
Gather Motion 1:1 (slice1; segments: 1)
-> Append
-> Seq Scan on mc3p0 mc3p_1
Filter: ((a = 1) AND (abs(b) = 1))
-> Seq Scan on mc3p1 mc3p_2
Filter: ((a = 1) AND (abs(b) = 1))
-> Seq Scan on mc3p_default mc3p_3
Filter: ((a = 1) AND (abs(b) = 1))
Optimizer: Postgres query optimizer
(9 rows)
explain (costs off) select * from mc3p where a = 1 and abs(b) = 1 and c < 8;
QUERY PLAN
--------------------------------------------------------------
Gather Motion 1:1 (slice1; segments: 1)
-> Append
-> Seq Scan on mc3p0 mc3p_1
Filter: ((c < 8) AND (a = 1) AND (abs(b) = 1))
-> Seq Scan on mc3p1 mc3p_2
Filter: ((c < 8) AND (a = 1) AND (abs(b) = 1))
Optimizer: Postgres query optimizer
(7 rows)
explain (costs off) select * from mc3p where a = 10 and abs(b) between 5 and 35;
QUERY PLAN
-----------------------------------------------------------------------
Gather Motion 1:1 (slice1; segments: 1)
-> Append
-> Seq Scan on mc3p1 mc3p_1
Filter: ((a = 10) AND (abs(b) >= 5) AND (abs(b) <= 35))
-> Seq Scan on mc3p2 mc3p_2
Filter: ((a = 10) AND (abs(b) >= 5) AND (abs(b) <= 35))
-> Seq Scan on mc3p3 mc3p_3
Filter: ((a = 10) AND (abs(b) >= 5) AND (abs(b) <= 35))
-> Seq Scan on mc3p4 mc3p_4
Filter: ((a = 10) AND (abs(b) >= 5) AND (abs(b) <= 35))
-> Seq Scan on mc3p_default mc3p_5
Filter: ((a = 10) AND (abs(b) >= 5) AND (abs(b) <= 35))
Optimizer: Postgres query optimizer
(13 rows)
explain (costs off) select * from mc3p where a > 10;
QUERY PLAN
---------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Append
-> Seq Scan on mc3p5 mc3p_1
Filter: (a > 10)
-> Seq Scan on mc3p6 mc3p_2
Filter: (a > 10)
-> Seq Scan on mc3p7 mc3p_3
Filter: (a > 10)
-> Seq Scan on mc3p_default mc3p_4
Filter: (a > 10)
Optimizer: Postgres query optimizer
(11 rows)
explain (costs off) select * from mc3p where a >= 10;
QUERY PLAN
---------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Append
-> Seq Scan on mc3p1 mc3p_1
Filter: (a >= 10)
-> Seq Scan on mc3p2 mc3p_2
Filter: (a >= 10)
-> Seq Scan on mc3p3 mc3p_3
Filter: (a >= 10)
-> Seq Scan on mc3p4 mc3p_4
Filter: (a >= 10)
-> Seq Scan on mc3p5 mc3p_5
Filter: (a >= 10)
-> Seq Scan on mc3p6 mc3p_6
Filter: (a >= 10)
-> Seq Scan on mc3p7 mc3p_7
Filter: (a >= 10)
-> Seq Scan on mc3p_default mc3p_8
Filter: (a >= 10)
Optimizer: Postgres query optimizer
(19 rows)
explain (costs off) select * from mc3p where a < 10;
QUERY PLAN
---------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Append
-> Seq Scan on mc3p0 mc3p_1
Filter: (a < 10)
-> Seq Scan on mc3p1 mc3p_2
Filter: (a < 10)
-> Seq Scan on mc3p_default mc3p_3
Filter: (a < 10)
Optimizer: Postgres query optimizer
(9 rows)
explain (costs off) select * from mc3p where a <= 10 and abs(b) < 10;
QUERY PLAN
-----------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Append
-> Seq Scan on mc3p0 mc3p_1
Filter: ((a <= 10) AND (abs(b) < 10))
-> Seq Scan on mc3p1 mc3p_2
Filter: ((a <= 10) AND (abs(b) < 10))
-> Seq Scan on mc3p2 mc3p_3
Filter: ((a <= 10) AND (abs(b) < 10))
-> Seq Scan on mc3p_default mc3p_4
Filter: ((a <= 10) AND (abs(b) < 10))
Optimizer: Postgres query optimizer
(11 rows)
explain (costs off) select * from mc3p where a = 11 and abs(b) = 0;
QUERY PLAN
---------------------------------------------
Gather Motion 1:1 (slice1; segments: 1)
-> Seq Scan on mc3p_default mc3p
Filter: ((a = 11) AND (abs(b) = 0))
Optimizer: Postgres query optimizer
(4 rows)
explain (costs off) select * from mc3p where a = 20 and abs(b) = 10 and c = 100;
QUERY PLAN
------------------------------------------------------------
Gather Motion 1:1 (slice1; segments: 1)
-> Seq Scan on mc3p6 mc3p
Filter: ((a = 20) AND (c = 100) AND (abs(b) = 10))
Optimizer: Postgres query optimizer
(4 rows)
explain (costs off) select * from mc3p where a > 20;
QUERY PLAN
---------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Append
-> Seq Scan on mc3p7 mc3p_1
Filter: (a > 20)
-> Seq Scan on mc3p_default mc3p_2
Filter: (a > 20)
Optimizer: Postgres query optimizer
(7 rows)
explain (costs off) select * from mc3p where a >= 20;
QUERY PLAN
---------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Append
-> Seq Scan on mc3p5 mc3p_1
Filter: (a >= 20)
-> Seq Scan on mc3p6 mc3p_2
Filter: (a >= 20)
-> Seq Scan on mc3p7 mc3p_3
Filter: (a >= 20)
-> Seq Scan on mc3p_default mc3p_4
Filter: (a >= 20)
Optimizer: Postgres query optimizer
(11 rows)
explain (costs off) select * from mc3p where (a = 1 and abs(b) = 1 and c = 1) or (a = 10 and abs(b) = 5 and c = 10) or (a > 11 and a < 20);
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Append
-> Seq Scan on mc3p1 mc3p_1
Filter: (((a = 1) AND (abs(b) = 1) AND (c = 1)) OR ((a = 10) AND (abs(b) = 5) AND (c = 10)) OR ((a > 11) AND (a < 20)))
-> Seq Scan on mc3p2 mc3p_2
Filter: (((a = 1) AND (abs(b) = 1) AND (c = 1)) OR ((a = 10) AND (abs(b) = 5) AND (c = 10)) OR ((a > 11) AND (a < 20)))
-> Seq Scan on mc3p5 mc3p_3
Filter: (((a = 1) AND (abs(b) = 1) AND (c = 1)) OR ((a = 10) AND (abs(b) = 5) AND (c = 10)) OR ((a > 11) AND (a < 20)))
-> Seq Scan on mc3p_default mc3p_4
Filter: (((a = 1) AND (abs(b) = 1) AND (c = 1)) OR ((a = 10) AND (abs(b) = 5) AND (c = 10)) OR ((a > 11) AND (a < 20)))
Optimizer: Postgres query optimizer
(11 rows)
explain (costs off) select * from mc3p where (a = 1 and abs(b) = 1 and c = 1) or (a = 10 and abs(b) = 5 and c = 10) or (a > 11 and a < 20) or a < 1;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Append
-> Seq Scan on mc3p0 mc3p_1
Filter: (((a = 1) AND (abs(b) = 1) AND (c = 1)) OR ((a = 10) AND (abs(b) = 5) AND (c = 10)) OR ((a > 11) AND (a < 20)) OR (a < 1))
-> Seq Scan on mc3p1 mc3p_2
Filter: (((a = 1) AND (abs(b) = 1) AND (c = 1)) OR ((a = 10) AND (abs(b) = 5) AND (c = 10)) OR ((a > 11) AND (a < 20)) OR (a < 1))
-> Seq Scan on mc3p2 mc3p_3
Filter: (((a = 1) AND (abs(b) = 1) AND (c = 1)) OR ((a = 10) AND (abs(b) = 5) AND (c = 10)) OR ((a > 11) AND (a < 20)) OR (a < 1))
-> Seq Scan on mc3p5 mc3p_4
Filter: (((a = 1) AND (abs(b) = 1) AND (c = 1)) OR ((a = 10) AND (abs(b) = 5) AND (c = 10)) OR ((a > 11) AND (a < 20)) OR (a < 1))
-> Seq Scan on mc3p_default mc3p_5
Filter: (((a = 1) AND (abs(b) = 1) AND (c = 1)) OR ((a = 10) AND (abs(b) = 5) AND (c = 10)) OR ((a > 11) AND (a < 20)) OR (a < 1))
Optimizer: Postgres query optimizer
(13 rows)
explain (costs off) select * from mc3p where (a = 1 and abs(b) = 1 and c = 1) or (a = 10 and abs(b) = 5 and c = 10) or (a > 11 and a < 20) or a < 1 or a = 1;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Append
-> Seq Scan on mc3p0 mc3p_1
Filter: (((a = 1) AND (abs(b) = 1) AND (c = 1)) OR ((a = 10) AND (abs(b) = 5) AND (c = 10)) OR ((a > 11) AND (a < 20)) OR (a < 1) OR (a = 1))
-> Seq Scan on mc3p1 mc3p_2
Filter: (((a = 1) AND (abs(b) = 1) AND (c = 1)) OR ((a = 10) AND (abs(b) = 5) AND (c = 10)) OR ((a > 11) AND (a < 20)) OR (a < 1) OR (a = 1))
-> Seq Scan on mc3p2 mc3p_3
Filter: (((a = 1) AND (abs(b) = 1) AND (c = 1)) OR ((a = 10) AND (abs(b) = 5) AND (c = 10)) OR ((a > 11) AND (a < 20)) OR (a < 1) OR (a = 1))
-> Seq Scan on mc3p5 mc3p_4
Filter: (((a = 1) AND (abs(b) = 1) AND (c = 1)) OR ((a = 10) AND (abs(b) = 5) AND (c = 10)) OR ((a > 11) AND (a < 20)) OR (a < 1) OR (a = 1))
-> Seq Scan on mc3p_default mc3p_5
Filter: (((a = 1) AND (abs(b) = 1) AND (c = 1)) OR ((a = 10) AND (abs(b) = 5) AND (c = 10)) OR ((a > 11) AND (a < 20)) OR (a < 1) OR (a = 1))
Optimizer: Postgres query optimizer
(13 rows)
explain (costs off) select * from mc3p where a = 1 or abs(b) = 1 or c = 1;
QUERY PLAN
------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Append
-> Seq Scan on mc3p0 mc3p_1
Filter: ((a = 1) OR (abs(b) = 1) OR (c = 1))
-> Seq Scan on mc3p1 mc3p_2
Filter: ((a = 1) OR (abs(b) = 1) OR (c = 1))
-> Seq Scan on mc3p2 mc3p_3
Filter: ((a = 1) OR (abs(b) = 1) OR (c = 1))
-> Seq Scan on mc3p3 mc3p_4
Filter: ((a = 1) OR (abs(b) = 1) OR (c = 1))
-> Seq Scan on mc3p4 mc3p_5
Filter: ((a = 1) OR (abs(b) = 1) OR (c = 1))
-> Seq Scan on mc3p5 mc3p_6
Filter: ((a = 1) OR (abs(b) = 1) OR (c = 1))
-> Seq Scan on mc3p6 mc3p_7
Filter: ((a = 1) OR (abs(b) = 1) OR (c = 1))
-> Seq Scan on mc3p7 mc3p_8
Filter: ((a = 1) OR (abs(b) = 1) OR (c = 1))
-> Seq Scan on mc3p_default mc3p_9
Filter: ((a = 1) OR (abs(b) = 1) OR (c = 1))
Optimizer: Postgres query optimizer
(21 rows)
explain (costs off) select * from mc3p where (a = 1 and abs(b) = 1) or (a = 10 and abs(b) = 10);
QUERY PLAN
------------------------------------------------------------------------------------
Gather Motion 2:1 (slice1; segments: 2)
-> Append
-> Seq Scan on mc3p0 mc3p_1
Filter: (((a = 1) AND (abs(b) = 1)) OR ((a = 10) AND (abs(b) = 10)))
-> Seq Scan on mc3p1 mc3p_2
Filter: (((a = 1) AND (abs(b) = 1)) OR ((a = 10) AND (abs(b) = 10)))
-> Seq Scan on mc3p2 mc3p_3
Filter: (((a = 1) AND (abs(b) = 1)) OR ((a = 10) AND (abs(b) = 10)))
-> Seq Scan on mc3p3 mc3p_4
Filter: (((a = 1) AND (abs(b) = 1)) OR ((a = 10) AND (abs(b) = 10)))
-> Seq Scan on mc3p4 mc3p_5
Filter: (((a = 1) AND (abs(b) = 1)) OR ((a = 10) AND (abs(b) = 10)))
-> Seq Scan on mc3p_default mc3p_6
Filter: (((a = 1) AND (abs(b) = 1)) OR ((a = 10) AND (abs(b) = 10)))
Optimizer: Postgres query optimizer
(15 rows)
explain (costs off) select * from mc3p where (a = 1 and abs(b) = 1) or (a = 10 and abs(b) = 9);
QUERY PLAN
-----------------------------------------------------------------------------------
Gather Motion 2:1 (slice1; segments: 2)
-> Append
-> Seq Scan on mc3p0 mc3p_1
Filter: (((a = 1) AND (abs(b) = 1)) OR ((a = 10) AND (abs(b) = 9)))
-> Seq Scan on mc3p1 mc3p_2
Filter: (((a = 1) AND (abs(b) = 1)) OR ((a = 10) AND (abs(b) = 9)))
-> Seq Scan on mc3p2 mc3p_3
Filter: (((a = 1) AND (abs(b) = 1)) OR ((a = 10) AND (abs(b) = 9)))
-> Seq Scan on mc3p_default mc3p_4
Filter: (((a = 1) AND (abs(b) = 1)) OR ((a = 10) AND (abs(b) = 9)))
Optimizer: Postgres query optimizer
(11 rows)
-- a simpler multi-column keys case
create table mc2p (a int, b int) partition by range (a, 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 mc2p_default partition of mc2p default;
NOTICE: table has parent, setting distribution columns to match parent table
create table mc2p0 partition of mc2p for values from (minvalue, minvalue) to (1, minvalue);
NOTICE: table has parent, setting distribution columns to match parent table
create table mc2p1 partition of mc2p for values from (1, minvalue) to (1, 1);
NOTICE: table has parent, setting distribution columns to match parent table
create table mc2p2 partition of mc2p for values from (1, 1) to (2, minvalue);
NOTICE: table has parent, setting distribution columns to match parent table
create table mc2p3 partition of mc2p for values from (2, minvalue) to (2, 1);
NOTICE: table has parent, setting distribution columns to match parent table
create table mc2p4 partition of mc2p for values from (2, 1) to (2, maxvalue);
NOTICE: table has parent, setting distribution columns to match parent table
create table mc2p5 partition of mc2p for values from (2, maxvalue) to (maxvalue, maxvalue);
NOTICE: table has parent, setting distribution columns to match parent table
explain (costs off) select * from mc2p where a < 2;
QUERY PLAN
---------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Append
-> Seq Scan on mc2p0 mc2p_1
Filter: (a < 2)
-> Seq Scan on mc2p1 mc2p_2
Filter: (a < 2)
-> Seq Scan on mc2p2 mc2p_3
Filter: (a < 2)
-> Seq Scan on mc2p_default mc2p_4
Filter: (a < 2)
Optimizer: Postgres query optimizer
(11 rows)
explain (costs off) select * from mc2p where a = 2 and b < 1;
QUERY PLAN
------------------------------------------
Gather Motion 1:1 (slice1; segments: 1)
-> Seq Scan on mc2p3 mc2p
Filter: ((b < 1) AND (a = 2))
Optimizer: Postgres query optimizer
(4 rows)
explain (costs off) select * from mc2p where a > 1;
QUERY PLAN
---------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Append
-> Seq Scan on mc2p2 mc2p_1
Filter: (a > 1)
-> Seq Scan on mc2p3 mc2p_2
Filter: (a > 1)
-> Seq Scan on mc2p4 mc2p_3
Filter: (a > 1)
-> Seq Scan on mc2p5 mc2p_4
Filter: (a > 1)
-> Seq Scan on mc2p_default mc2p_5
Filter: (a > 1)
Optimizer: Postgres query optimizer
(13 rows)
explain (costs off) select * from mc2p where a = 1 and b > 1;
QUERY PLAN
------------------------------------------
Gather Motion 1:1 (slice1; segments: 1)
-> Seq Scan on mc2p2 mc2p
Filter: ((b > 1) AND (a = 1))
Optimizer: Postgres query optimizer
(4 rows)
-- all partitions but the default one should be pruned
explain (costs off) select * from mc2p where a = 1 and b is null;
QUERY PLAN
-------------------------------------------
Gather Motion 1:1 (slice1; segments: 1)
-> Seq Scan on mc2p_default mc2p
Filter: ((b IS NULL) AND (a = 1))
Optimizer: Postgres query optimizer
(4 rows)
explain (costs off) select * from mc2p where a is null and b is null;
QUERY PLAN
-----------------------------------------------
Gather Motion 1:1 (slice1; segments: 1)
-> Seq Scan on mc2p_default mc2p
Filter: ((a IS NULL) AND (b IS NULL))
Optimizer: Postgres query optimizer
(4 rows)
explain (costs off) select * from mc2p where a is null and b = 1;
QUERY PLAN
-------------------------------------------
Gather Motion 1:1 (slice1; segments: 1)
-> Seq Scan on mc2p_default mc2p
Filter: ((a IS NULL) AND (b = 1))
Optimizer: Postgres query optimizer
(4 rows)
explain (costs off) select * from mc2p where a is null;
QUERY PLAN
------------------------------------------
Gather Motion 1:1 (slice1; segments: 1)
-> Seq Scan on mc2p_default mc2p
Filter: (a IS NULL)
Optimizer: Postgres query optimizer
(4 rows)
explain (costs off) select * from mc2p where b is null;
QUERY PLAN
------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Seq Scan on mc2p_default mc2p
Filter: (b IS NULL)
Optimizer: Postgres query optimizer
(4 rows)
-- boolean partitioning
create table boolpart (a bool) partition by list (a);
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 boolpart_default partition of boolpart default;
NOTICE: table has parent, setting distribution columns to match parent table
create table boolpart_t partition of boolpart for values in ('true');
NOTICE: table has parent, setting distribution columns to match parent table
create table boolpart_f partition of boolpart for values in ('false');
NOTICE: table has parent, setting distribution columns to match parent table
insert into boolpart values (true), (false), (null);
explain (costs off) select * from boolpart where a in (true, false);
QUERY PLAN
------------------------------------------------------
Gather Motion 1:1 (slice1; segments: 1)
-> Append
-> Seq Scan on boolpart_f boolpart_1
Filter: (a = ANY ('{t,f}'::boolean[]))
-> Seq Scan on boolpart_t boolpart_2
Filter: (a = ANY ('{t,f}'::boolean[]))
Optimizer: Postgres query optimizer
(7 rows)
explain (costs off) select * from boolpart where a = false;
QUERY PLAN
------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Seq Scan on boolpart_f boolpart
Filter: (NOT a)
Optimizer: Postgres query optimizer
(4 rows)
explain (costs off) select * from boolpart where not a = false;
QUERY PLAN
------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Seq Scan on boolpart_t boolpart
Filter: a
Optimizer: Postgres query optimizer
(4 rows)
explain (costs off) select * from boolpart where a is true or a is not true;
QUERY PLAN
--------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Append
-> Seq Scan on boolpart_f boolpart_1
Filter: ((a IS TRUE) OR (a IS NOT TRUE))
-> Seq Scan on boolpart_t boolpart_2
Filter: ((a IS TRUE) OR (a IS NOT TRUE))
-> Seq Scan on boolpart_default boolpart_3
Filter: ((a IS TRUE) OR (a IS NOT TRUE))
Optimizer: Postgres query optimizer
(9 rows)
explain (costs off) select * from boolpart where a is not true;
QUERY PLAN
-----------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Append
-> Seq Scan on boolpart_f boolpart_1
Filter: (a IS NOT TRUE)
-> Seq Scan on boolpart_default boolpart_2
Filter: (a IS NOT TRUE)
Optimizer: Postgres query optimizer
(7 rows)
explain (costs off) select * from boolpart where a is not true and a is not false;
QUERY PLAN
--------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Seq Scan on boolpart_default boolpart
Filter: ((a IS NOT TRUE) AND (a IS NOT FALSE))
Optimizer: Postgres query optimizer
(4 rows)
explain (costs off) select * from boolpart where a is unknown;
QUERY PLAN
-----------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Append
-> Seq Scan on boolpart_f boolpart_1
Filter: (a IS UNKNOWN)
-> Seq Scan on boolpart_t boolpart_2
Filter: (a IS UNKNOWN)
-> Seq Scan on boolpart_default boolpart_3
Filter: (a IS UNKNOWN)
Optimizer: Postgres query optimizer
(9 rows)
explain (costs off) select * from boolpart where a is not unknown;
QUERY PLAN
-----------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Append
-> Seq Scan on boolpart_f boolpart_1
Filter: (a IS NOT UNKNOWN)
-> Seq Scan on boolpart_t boolpart_2
Filter: (a IS NOT UNKNOWN)
-> Seq Scan on boolpart_default boolpart_3
Filter: (a IS NOT UNKNOWN)
Optimizer: Postgres query optimizer
(9 rows)
select * from boolpart where a in (true, false);
a
---
f
t
(2 rows)
select * from boolpart where a = false;
a
---
f
(1 row)
select * from boolpart where not a = false;
a
---
t
(1 row)
select * from boolpart where a is true or a is not true;
a
---
f
t
(3 rows)
select * from boolpart where a is not true;
a
---
f
(2 rows)
select * from boolpart where a is not true and a is not false;
a
---
(1 row)
select * from boolpart where a is unknown;
a
---
(1 row)
select * from boolpart where a is not unknown;
a
---
f
t
(2 rows)
-- try some other permutations with a NULL partition instead of a DEFAULT
delete from boolpart where a is null;
create table boolpart_null partition of boolpart for values in (null);
NOTICE: table has parent, setting distribution columns to match parent table
insert into boolpart values(null);
explain (costs off) select * from boolpart where a is not true;
QUERY PLAN
--------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Append
-> Seq Scan on boolpart_f boolpart_1
Filter: (a IS NOT TRUE)
-> Seq Scan on boolpart_null boolpart_2
Filter: (a IS NOT TRUE)
Optimizer: Postgres query optimizer
(7 rows)
explain (costs off) select * from boolpart where a is not true and a is not false;
QUERY PLAN
--------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Seq Scan on boolpart_null boolpart
Filter: ((a IS NOT TRUE) AND (a IS NOT FALSE))
Optimizer: Postgres query optimizer
(4 rows)
explain (costs off) select * from boolpart where a is not false;
QUERY PLAN
--------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Append
-> Seq Scan on boolpart_t boolpart_1
Filter: (a IS NOT FALSE)
-> Seq Scan on boolpart_null boolpart_2
Filter: (a IS NOT FALSE)
Optimizer: Postgres query optimizer
(7 rows)
select * from boolpart where a is not true;
a
---
f
(2 rows)
select * from boolpart where a is not true and a is not false;
a
---
(1 row)
select * from boolpart where a is not false;
a
---
t
(2 rows)
-- inverse boolean partitioning - a seemingly unlikely design, but we've got
-- code for it, so we'd better test it.
create table iboolpart (a bool) partition by list ((not a));
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 iboolpart_default partition of iboolpart default;
NOTICE: table has parent, setting distribution columns to match parent table
create table iboolpart_f partition of iboolpart for values in ('true');
NOTICE: table has parent, setting distribution columns to match parent table
create table iboolpart_t partition of iboolpart for values in ('false');
NOTICE: table has parent, setting distribution columns to match parent table
insert into iboolpart values (true), (false), (null);
explain (costs off) select * from iboolpart where a in (true, false);
QUERY PLAN
-------------------------------------------------------
Gather Motion 1:1 (slice1; segments: 1)
-> Append
-> Seq Scan on iboolpart_t iboolpart_1
Filter: (a = ANY ('{t,f}'::boolean[]))
-> Seq Scan on iboolpart_f iboolpart_2
Filter: (a = ANY ('{t,f}'::boolean[]))
-> Seq Scan on iboolpart_default iboolpart_3
Filter: (a = ANY ('{t,f}'::boolean[]))
Optimizer: Postgres query optimizer
(9 rows)
explain (costs off) select * from iboolpart where a = false;
QUERY PLAN
------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Seq Scan on iboolpart_f iboolpart
Filter: (NOT a)
Optimizer: Postgres query optimizer
(4 rows)
explain (costs off) select * from iboolpart where not a = false;
QUERY PLAN
------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Seq Scan on iboolpart_t iboolpart
Filter: a
Optimizer: Postgres query optimizer
(4 rows)
explain (costs off) select * from iboolpart where a is true or a is not true;
QUERY PLAN
--------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Append
-> Seq Scan on iboolpart_t iboolpart_1
Filter: ((a IS TRUE) OR (a IS NOT TRUE))
-> Seq Scan on iboolpart_f iboolpart_2
Filter: ((a IS TRUE) OR (a IS NOT TRUE))
-> Seq Scan on iboolpart_default iboolpart_3
Filter: ((a IS TRUE) OR (a IS NOT TRUE))
Optimizer: Postgres query optimizer
(9 rows)
explain (costs off) select * from iboolpart where a is not true;
QUERY PLAN
-------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Append
-> Seq Scan on iboolpart_t iboolpart_1
Filter: (a IS NOT TRUE)
-> Seq Scan on iboolpart_f iboolpart_2
Filter: (a IS NOT TRUE)
-> Seq Scan on iboolpart_default iboolpart_3
Filter: (a IS NOT TRUE)
Optimizer: Postgres query optimizer
(9 rows)
explain (costs off) select * from iboolpart where a is not true and a is not false;
QUERY PLAN
--------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Append
-> Seq Scan on iboolpart_t iboolpart_1
Filter: ((a IS NOT TRUE) AND (a IS NOT FALSE))
-> Seq Scan on iboolpart_f iboolpart_2
Filter: ((a IS NOT TRUE) AND (a IS NOT FALSE))
-> Seq Scan on iboolpart_default iboolpart_3
Filter: ((a IS NOT TRUE) AND (a IS NOT FALSE))
Optimizer: Postgres query optimizer
(9 rows)
explain (costs off) select * from iboolpart where a is unknown;
QUERY PLAN
-------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Append
-> Seq Scan on iboolpart_t iboolpart_1
Filter: (a IS UNKNOWN)
-> Seq Scan on iboolpart_f iboolpart_2
Filter: (a IS UNKNOWN)
-> Seq Scan on iboolpart_default iboolpart_3
Filter: (a IS UNKNOWN)
Optimizer: Postgres query optimizer
(9 rows)
explain (costs off) select * from iboolpart where a is not unknown;
QUERY PLAN
-------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Append
-> Seq Scan on iboolpart_t iboolpart_1
Filter: (a IS NOT UNKNOWN)
-> Seq Scan on iboolpart_f iboolpart_2
Filter: (a IS NOT UNKNOWN)
-> Seq Scan on iboolpart_default iboolpart_3
Filter: (a IS NOT UNKNOWN)
Optimizer: Postgres query optimizer
(9 rows)
select * from iboolpart where a in (true, false);
a
---
t
f
(2 rows)
select * from iboolpart where a = false;
a
---
f
(1 row)
select * from iboolpart where not a = false;
a
---
t
(1 row)
select * from iboolpart where a is true or a is not true;
a
---
t
f
(3 rows)
select * from iboolpart where a is not true;
a
---
f
(2 rows)
select * from iboolpart where a is not true and a is not false;
a
---
(1 row)
select * from iboolpart where a is unknown;
a
---
(1 row)
select * from iboolpart where a is not unknown;
a
---
t
f
(2 rows)
-- Try some other permutations with a NULL partition instead of a DEFAULT
delete from iboolpart where a is null;
create table iboolpart_null partition of iboolpart for values in (null);
NOTICE: table has parent, setting distribution columns to match parent table
insert into iboolpart values(null);
-- Pruning shouldn't take place for these. Just check the result is correct
select * from iboolpart where a is not true;
a
---
f
(2 rows)
select * from iboolpart where a is not true and a is not false;
a
---
(1 row)
select * from iboolpart where a is not false;
a
---
t
(2 rows)
create table boolrangep (a bool, b bool, c int) partition by range (a,b,c);
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 boolrangep_tf partition of boolrangep for values from ('true', 'false', 0) to ('true', 'false', 100);
NOTICE: table has parent, setting distribution columns to match parent table
create table boolrangep_ft partition of boolrangep for values from ('false', 'true', 0) to ('false', 'true', 100);
NOTICE: table has parent, setting distribution columns to match parent table
create table boolrangep_ff1 partition of boolrangep for values from ('false', 'false', 0) to ('false', 'false', 50);
NOTICE: table has parent, setting distribution columns to match parent table
create table boolrangep_ff2 partition of boolrangep for values from ('false', 'false', 50) to ('false', 'false', 100);
NOTICE: table has parent, setting distribution columns to match parent table
create table boolrangep_null partition of boolrangep default;
NOTICE: table has parent, setting distribution columns to match parent table
-- try a more complex case that's been known to trip up pruning in the past
explain (costs off) select * from boolrangep where not a and not b and c = 25;
QUERY PLAN
----------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Seq Scan on boolrangep_ff1 boolrangep
Filter: ((NOT a) AND (NOT b) AND (c = 25))
Optimizer: Postgres query optimizer
(4 rows)
-- ensure we prune boolrangep_tf
explain (costs off) select * from boolrangep where a is not true and not b and c = 25;
QUERY PLAN
------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Append
-> Seq Scan on boolrangep_ff1 boolrangep_1
Filter: ((a IS NOT TRUE) AND (NOT b) AND (c = 25))
-> Seq Scan on boolrangep_ff2 boolrangep_2
Filter: ((a IS NOT TRUE) AND (NOT b) AND (c = 25))
-> Seq Scan on boolrangep_ft boolrangep_3
Filter: ((a IS NOT TRUE) AND (NOT b) AND (c = 25))
-> Seq Scan on boolrangep_null boolrangep_4
Filter: ((a IS NOT TRUE) AND (NOT b) AND (c = 25))
Optimizer: Postgres query optimizer
(11 rows)
-- ensure we prune everything apart from boolrangep_tf and boolrangep_null
explain (costs off) select * from boolrangep where a is not false and not b and c = 25;
QUERY PLAN
-------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Append
-> Seq Scan on boolrangep_tf boolrangep_1
Filter: ((a IS NOT FALSE) AND (NOT b) AND (c = 25))
-> Seq Scan on boolrangep_null boolrangep_2
Filter: ((a IS NOT FALSE) AND (NOT b) AND (c = 25))
Optimizer: Postgres query optimizer
(7 rows)
-- test scalar-to-array operators
create table coercepart (a varchar) partition by list (a);
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 coercepart_ab partition of coercepart for values in ('ab');
NOTICE: table has parent, setting distribution columns to match parent table
create table coercepart_bc partition of coercepart for values in ('bc');
NOTICE: table has parent, setting distribution columns to match parent table
create table coercepart_cd partition of coercepart for values in ('cd');
NOTICE: table has parent, setting distribution columns to match parent table
explain (costs off) select * from coercepart where a in ('ab', to_char(125, '999'));
QUERY PLAN
-----------------------------------------------------------
Gather Motion 2:1 (slice1; segments: 2)
-> Seq Scan on coercepart_ab coercepart
Filter: ((a)::text = ANY ('{ab," 125"}'::text[]))
Optimizer: Postgres query optimizer
(4 rows)
explain (costs off) select * from coercepart where a ~ any ('{ab}');
QUERY PLAN
----------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Append
-> Seq Scan on coercepart_ab coercepart_1
Filter: ((a)::text ~ ANY ('{ab}'::text[]))
-> Seq Scan on coercepart_bc coercepart_2
Filter: ((a)::text ~ ANY ('{ab}'::text[]))
-> Seq Scan on coercepart_cd coercepart_3
Filter: ((a)::text ~ ANY ('{ab}'::text[]))
Optimizer: Postgres query optimizer
(9 rows)
explain (costs off) select * from coercepart where a !~ all ('{ab}');
QUERY PLAN
-----------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Append
-> Seq Scan on coercepart_ab coercepart_1
Filter: ((a)::text !~ ALL ('{ab}'::text[]))
-> Seq Scan on coercepart_bc coercepart_2
Filter: ((a)::text !~ ALL ('{ab}'::text[]))
-> Seq Scan on coercepart_cd coercepart_3
Filter: ((a)::text !~ ALL ('{ab}'::text[]))
Optimizer: Postgres query optimizer
(9 rows)
explain (costs off) select * from coercepart where a ~ any ('{ab,bc}');
QUERY PLAN
-------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Append
-> Seq Scan on coercepart_ab coercepart_1
Filter: ((a)::text ~ ANY ('{ab,bc}'::text[]))
-> Seq Scan on coercepart_bc coercepart_2
Filter: ((a)::text ~ ANY ('{ab,bc}'::text[]))
-> Seq Scan on coercepart_cd coercepart_3
Filter: ((a)::text ~ ANY ('{ab,bc}'::text[]))
Optimizer: Postgres query optimizer
(9 rows)
explain (costs off) select * from coercepart where a !~ all ('{ab,bc}');
QUERY PLAN
--------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Append
-> Seq Scan on coercepart_ab coercepart_1
Filter: ((a)::text !~ ALL ('{ab,bc}'::text[]))
-> Seq Scan on coercepart_bc coercepart_2
Filter: ((a)::text !~ ALL ('{ab,bc}'::text[]))
-> Seq Scan on coercepart_cd coercepart_3
Filter: ((a)::text !~ ALL ('{ab,bc}'::text[]))
Optimizer: Postgres query optimizer
(9 rows)
explain (costs off) select * from coercepart where a = any ('{ab,bc}');
QUERY PLAN
-------------------------------------------------------------
Gather Motion 2:1 (slice1; segments: 2)
-> Append
-> Seq Scan on coercepart_ab coercepart_1
Filter: ((a)::text = ANY ('{ab,bc}'::text[]))
-> Seq Scan on coercepart_bc coercepart_2
Filter: ((a)::text = ANY ('{ab,bc}'::text[]))
Optimizer: Postgres query optimizer
(7 rows)
explain (costs off) select * from coercepart where a = any ('{ab,null}');
QUERY PLAN
---------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Seq Scan on coercepart_ab coercepart
Filter: ((a)::text = ANY ('{ab,NULL}'::text[]))
Optimizer: Postgres query optimizer
(4 rows)
explain (costs off) select * from coercepart where a = any (null::text[]);
QUERY PLAN
-------------------------------------
Result
One-Time Filter: false
Optimizer: Postgres query optimizer
(3 rows)
explain (costs off) select * from coercepart where a = all ('{ab}');
QUERY PLAN
----------------------------------------------------
Gather Motion 1:1 (slice1; segments: 1)
-> Seq Scan on coercepart_ab coercepart
Filter: ((a)::text = ALL ('{ab}'::text[]))
Optimizer: Postgres query optimizer
(4 rows)
explain (costs off) select * from coercepart where a = all ('{ab,bc}');
QUERY PLAN
-------------------------------------
Result
One-Time Filter: false
Optimizer: Postgres query optimizer
(3 rows)
explain (costs off) select * from coercepart where a = all ('{ab,null}');
QUERY PLAN
-------------------------------------
Result
One-Time Filter: false
Optimizer: Postgres query optimizer
(3 rows)
explain (costs off) select * from coercepart where a = all (null::text[]);
QUERY PLAN
-------------------------------------
Result
One-Time Filter: false
Optimizer: Postgres query optimizer
(3 rows)
drop table coercepart;
CREATE TABLE part (a INT, b INT) PARTITION BY LIST (a);
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 part_p1 PARTITION OF part FOR VALUES IN (-2,-1,0,1,2);
NOTICE: table has parent, setting distribution columns to match parent table
CREATE TABLE part_p2 PARTITION OF part DEFAULT PARTITION BY RANGE(a);
NOTICE: table has parent, setting distribution columns to match parent table
CREATE TABLE part_p2_p1 PARTITION OF part_p2 DEFAULT;
NOTICE: table has parent, setting distribution columns to match parent table
CREATE TABLE part_rev (b INT, c INT, a INT);
NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'b' 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.
-- The distribution key must be the same for partition tables.
ALTER TABLE part_rev SET DISTRIBUTED BY(a);
ALTER TABLE part ATTACH PARTITION part_rev FOR VALUES IN (3); -- fail
ERROR: table "part_rev" contains column "c" not found in parent "part"
DETAIL: The new partition may contain only the columns present in parent.
ALTER TABLE part_rev DROP COLUMN c;
ALTER TABLE part ATTACH PARTITION part_rev FOR VALUES IN (3); -- now it's ok
INSERT INTO part VALUES (-1,-1), (1,1), (2,NULL), (NULL,-2),(NULL,NULL);
EXPLAIN (COSTS OFF) SELECT tableoid::regclass as part, a, b FROM part WHERE a IS NULL ORDER BY 1, 2, 3;
QUERY PLAN
---------------------------------------------------------------
Gather Motion 1:1 (slice1; segments: 1)
Merge Key: ((part.tableoid)::regclass), part.a, part.b
-> Sort
Sort Key: ((part.tableoid)::regclass), part.a, part.b
-> Seq Scan on part_p2_p1 part
Filter: (a IS NULL)
Optimizer: Postgres query optimizer
(7 rows)
EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM part p(x) ORDER BY x;
QUERY PLAN
-----------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
Output: p.x, p.b
Merge Key: p.x
-> Sort
Output: p.x, p.b
Sort Key: p.x
-> Append
-> Seq Scan on public.part_p1 p_1
Output: p_1.x, p_1.b
-> Seq Scan on public.part_rev p_2
Output: p_2.x, p_2.b
-> Seq Scan on public.part_p2_p1 p_3
Output: p_3.x, p_3.b
Settings: plan_cache_mode = 'force_generic_plan'
Optimizer: Postgres query optimizer
(15 rows)
--
-- some more cases
--
--
-- pruning for partitioned table appearing inside a sub-query
--
-- pruning won't work for mc3p, because some keys are Params
explain (costs off) select * from mc2p t1, lateral (select count(*) from mc3p t2 where t2.a = t1.b and abs(t2.b) = 1 and t2.c = 1) s where t1.a = 1;
QUERY PLAN
--------------------------------------------------------------------------------
Nested Loop
-> Gather Motion 1:1 (slice1; segments: 1)
-> Append
-> Seq Scan on mc2p1 t1_1
Filter: (a = 1)
-> Seq Scan on mc2p2 t1_2
Filter: (a = 1)
-> Seq Scan on mc2p_default t1_3
Filter: (a = 1)
-> Materialize
-> Aggregate
-> Result
Filter: (t2.a = t1.b)
-> Materialize
-> Gather Motion 3:1 (slice2; segments: 3)
-> Append
-> Seq Scan on mc3p0 t2_1
Filter: ((c = 1) AND (abs(b) = 1))
-> Seq Scan on mc3p1 t2_2
Filter: ((c = 1) AND (abs(b) = 1))
-> Seq Scan on mc3p2 t2_3
Filter: ((c = 1) AND (abs(b) = 1))
-> Seq Scan on mc3p3 t2_4
Filter: ((c = 1) AND (abs(b) = 1))
-> Seq Scan on mc3p4 t2_5
Filter: ((c = 1) AND (abs(b) = 1))
-> Seq Scan on mc3p5 t2_6
Filter: ((c = 1) AND (abs(b) = 1))
-> Seq Scan on mc3p6 t2_7
Filter: ((c = 1) AND (abs(b) = 1))
-> Seq Scan on mc3p7 t2_8
Filter: ((c = 1) AND (abs(b) = 1))
-> Seq Scan on mc3p_default t2_9
Filter: ((c = 1) AND (abs(b) = 1))
Optimizer: Postgres query optimizer
(35 rows)
-- pruning should work fine, because values for a prefix of keys (a, b) are
-- available
explain (costs off) select * from mc2p t1, lateral (select count(*) from mc3p t2 where t2.c = t1.b and abs(t2.b) = 1 and t2.a = 1) s where t1.a = 1;
QUERY PLAN
--------------------------------------------------------------------------------
Nested Loop
-> Gather Motion 1:1 (slice1; segments: 1)
-> Append
-> Seq Scan on mc2p1 t1_1
Filter: (a = 1)
-> Seq Scan on mc2p2 t1_2
Filter: (a = 1)
-> Seq Scan on mc2p_default t1_3
Filter: (a = 1)
-> Materialize
-> Aggregate
-> Result
Filter: (t2.c = t1.b)
-> Materialize
-> Gather Motion 1:1 (slice2; segments: 1)
-> Append
-> Seq Scan on mc3p0 t2_1
Filter: ((a = 1) AND (abs(b) = 1))
-> Seq Scan on mc3p1 t2_2
Filter: ((a = 1) AND (abs(b) = 1))
-> Seq Scan on mc3p_default t2_3
Filter: ((a = 1) AND (abs(b) = 1))
Optimizer: Postgres query optimizer
(23 rows)
-- also here, because values for all keys are provided
explain (costs off) select * from mc2p t1, lateral (select count(*) from mc3p t2 where t2.a = 1 and abs(t2.b) = 1 and t2.c = 1) s where t1.a = 1;
QUERY PLAN
--------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Nested Loop
-> Redistribute Motion 1:3 (slice2; segments: 1)
Hash Key: 1
-> Aggregate
-> Gather Motion 1:1 (slice3; segments: 1)
-> Seq Scan on mc3p1 t2
Filter: ((a = 1) AND (c = 1) AND (abs(b) = 1))
-> Append
-> Seq Scan on mc2p1 t1_1
Filter: (a = 1)
-> Seq Scan on mc2p2 t1_2
Filter: (a = 1)
-> Seq Scan on mc2p_default t1_3
Filter: (a = 1)
Optimizer: Postgres query optimizer
(16 rows)
--
-- pruning with clauses containing <> operator
--
-- doesn't prune range partitions
create table rp (a int) partition by range (a);
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 rp0 partition of rp for values from (minvalue) to (1);
NOTICE: table has parent, setting distribution columns to match parent table
create table rp1 partition of rp for values from (1) to (2);
NOTICE: table has parent, setting distribution columns to match parent table
create table rp2 partition of rp for values from (2) to (maxvalue);
NOTICE: table has parent, setting distribution columns to match parent table
explain (costs off) select * from rp where a <> 1;
QUERY PLAN
------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Append
-> Seq Scan on rp0 rp_1
Filter: (a <> 1)
-> Seq Scan on rp1 rp_2
Filter: (a <> 1)
-> Seq Scan on rp2 rp_3
Filter: (a <> 1)
Optimizer: Postgres query optimizer
(9 rows)
explain (costs off) select * from rp where a <> 1 and a <> 2;
QUERY PLAN
-----------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Append
-> Seq Scan on rp0 rp_1
Filter: ((a <> 1) AND (a <> 2))
-> Seq Scan on rp1 rp_2
Filter: ((a <> 1) AND (a <> 2))
-> Seq Scan on rp2 rp_3
Filter: ((a <> 1) AND (a <> 2))
Optimizer: Postgres query optimizer
(9 rows)
-- null partition should be eliminated due to strict <> clause.
explain (costs off) select * from lp where a <> 'a';
QUERY PLAN
------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Append
-> Seq Scan on lp_ad lp_1
Filter: (a <> 'a'::bpchar)
-> Seq Scan on lp_bc lp_2
Filter: (a <> 'a'::bpchar)
-> Seq Scan on lp_ef lp_3
Filter: (a <> 'a'::bpchar)
-> Seq Scan on lp_g lp_4
Filter: (a <> 'a'::bpchar)
-> Seq Scan on lp_default lp_5
Filter: (a <> 'a'::bpchar)
Optimizer: Postgres query optimizer
(13 rows)
-- ensure we detect contradictions in clauses; a can't be NULL and NOT NULL.
explain (costs off) select * from lp where a <> 'a' and a is null;
QUERY PLAN
-------------------------------------
Result
One-Time Filter: false
Optimizer: Postgres query optimizer
(3 rows)
explain (costs off) select * from lp where (a <> 'a' and a <> 'd') or a is null;
QUERY PLAN
------------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Append
-> Seq Scan on lp_bc lp_1
Filter: (((a <> 'a'::bpchar) AND (a <> 'd'::bpchar)) OR (a IS NULL))
-> Seq Scan on lp_ef lp_2
Filter: (((a <> 'a'::bpchar) AND (a <> 'd'::bpchar)) OR (a IS NULL))
-> Seq Scan on lp_g lp_3
Filter: (((a <> 'a'::bpchar) AND (a <> 'd'::bpchar)) OR (a IS NULL))
-> Seq Scan on lp_null lp_4
Filter: (((a <> 'a'::bpchar) AND (a <> 'd'::bpchar)) OR (a IS NULL))
-> Seq Scan on lp_default lp_5
Filter: (((a <> 'a'::bpchar) AND (a <> 'd'::bpchar)) OR (a IS NULL))
Optimizer: Postgres query optimizer
(13 rows)
-- check that it also works for a partitioned table that's not root,
-- which in this case are partitions of rlp that are themselves
-- list-partitioned on b
explain (costs off) select * from rlp where a = 15 and b <> 'ab' and b <> 'cd' and b <> 'xy' and b is not null;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------
Gather Motion 1:1 (slice1; segments: 1)
-> Append
-> Seq Scan on rlp3efgh rlp_1
Filter: ((b IS NOT NULL) AND ((b)::text <> 'ab'::text) AND ((b)::text <> 'cd'::text) AND ((b)::text <> 'xy'::text) AND (a = 15))
-> Seq Scan on rlp3_default rlp_2
Filter: ((b IS NOT NULL) AND ((b)::text <> 'ab'::text) AND ((b)::text <> 'cd'::text) AND ((b)::text <> 'xy'::text) AND (a = 15))
Optimizer: Postgres query optimizer
(7 rows)
--
-- different collations for different keys with same expression
--
create table coll_pruning_multi (a text) partition by range (substr(a, 1) collate "POSIX", substr(a, 1) collate "C");
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 coll_pruning_multi1 partition of coll_pruning_multi for values from ('a', 'a') to ('a', 'e');
NOTICE: table has parent, setting distribution columns to match parent table
create table coll_pruning_multi2 partition of coll_pruning_multi for values from ('a', 'e') to ('a', 'z');
NOTICE: table has parent, setting distribution columns to match parent table
create table coll_pruning_multi3 partition of coll_pruning_multi for values from ('b', 'a') to ('b', 'e');
NOTICE: table has parent, setting distribution columns to match parent table
-- no pruning, because no value for the leading key
explain (costs off) select * from coll_pruning_multi where substr(a, 1) = 'e' collate "C";
QUERY PLAN
------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Append
-> Seq Scan on coll_pruning_multi1 coll_pruning_multi_1
Filter: (substr(a, 1) = 'e'::text COLLATE "C")
-> Seq Scan on coll_pruning_multi2 coll_pruning_multi_2
Filter: (substr(a, 1) = 'e'::text COLLATE "C")
-> Seq Scan on coll_pruning_multi3 coll_pruning_multi_3
Filter: (substr(a, 1) = 'e'::text COLLATE "C")
Optimizer: Postgres query optimizer
(9 rows)
-- pruning, with a value provided for the leading key
explain (costs off) select * from coll_pruning_multi where substr(a, 1) = 'a' collate "POSIX";
QUERY PLAN
------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Append
-> Seq Scan on coll_pruning_multi1 coll_pruning_multi_1
Filter: (substr(a, 1) = 'a'::text COLLATE "POSIX")
-> Seq Scan on coll_pruning_multi2 coll_pruning_multi_2
Filter: (substr(a, 1) = 'a'::text COLLATE "POSIX")
Optimizer: Postgres query optimizer
(7 rows)
-- pruning, with values provided for both keys
explain (costs off) select * from coll_pruning_multi where substr(a, 1) = 'e' collate "C" and substr(a, 1) = 'a' collate "POSIX";
QUERY PLAN
---------------------------------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Seq Scan on coll_pruning_multi2 coll_pruning_multi
Filter: ((substr(a, 1) = 'e'::text COLLATE "C") AND (substr(a, 1) = 'a'::text COLLATE "POSIX"))
Optimizer: Postgres query optimizer
(4 rows)
--
-- LIKE operators don't prune
--
create table like_op_noprune (a text) partition by list (a);
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 like_op_noprune1 partition of like_op_noprune for values in ('ABC');
NOTICE: table has parent, setting distribution columns to match parent table
create table like_op_noprune2 partition of like_op_noprune for values in ('BCD');
NOTICE: table has parent, setting distribution columns to match parent table
explain (costs off) select * from like_op_noprune where a like '%BC';
QUERY PLAN
------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Append
-> Seq Scan on like_op_noprune1 like_op_noprune_1
Filter: (a ~~ '%BC'::text)
-> Seq Scan on like_op_noprune2 like_op_noprune_2
Filter: (a ~~ '%BC'::text)
Optimizer: Postgres query optimizer
(7 rows)
--
-- tests wherein clause value requires a cross-type comparison function
--
create table lparted_by_int2 (a smallint) partition by list (a);
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 lparted_by_int2_1 partition of lparted_by_int2 for values in (1);
NOTICE: table has parent, setting distribution columns to match parent table
create table lparted_by_int2_16384 partition of lparted_by_int2 for values in (16384);
NOTICE: table has parent, setting distribution columns to match parent table
explain (costs off) select * from lparted_by_int2 where a = 100_000_000_000_000;
QUERY PLAN
-------------------------------------
Result
One-Time Filter: false
Optimizer: Postgres query optimizer
(3 rows)
create table rparted_by_int2 (a smallint) partition by range (a);
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 rparted_by_int2_1 partition of rparted_by_int2 for values from (1) to (10);
NOTICE: table has parent, setting distribution columns to match parent table
create table rparted_by_int2_16384 partition of rparted_by_int2 for values from (10) to (16384);
NOTICE: table has parent, setting distribution columns to match parent table
-- all partitions pruned
explain (costs off) select * from rparted_by_int2 where a > 100_000_000_000_000;
QUERY PLAN
-------------------------------------
Result
One-Time Filter: false
Optimizer: Postgres query optimizer
(3 rows)
create table rparted_by_int2_maxvalue partition of rparted_by_int2 for values from (16384) to (maxvalue);
NOTICE: table has parent, setting distribution columns to match parent table
-- all partitions but rparted_by_int2_maxvalue pruned
explain (costs off) select * from rparted_by_int2 where a > 100_000_000_000_000;
QUERY PLAN
------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Seq Scan on rparted_by_int2_maxvalue rparted_by_int2
Filter: (a > '100000000000000'::bigint)
Optimizer: Postgres query optimizer
(4 rows)
drop table lp, coll_pruning, rlp, mc3p, mc2p, boolpart, iboolpart, boolrangep, rp, coll_pruning_multi, like_op_noprune, lparted_by_int2, rparted_by_int2;
-- check that AlternativeSubPlan within a pruning expression gets cleaned up
create table asptab (id int primary key) partition by range (id);
create table asptab0 partition of asptab for values from (0) to (1);
NOTICE: table has parent, setting distribution columns to match parent table
create table asptab1 partition of asptab for values from (1) to (2);
NOTICE: table has parent, setting distribution columns to match parent table
explain (costs off)
select * from
(select exists (select 1 from int4_tbl tinner where f1 = touter.f1) as b
from int4_tbl touter) ss,
asptab
where asptab.id > ss.b::int;
QUERY PLAN
-----------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Nested Loop
-> Broadcast Motion 3:3 (slice3; segments: 3)
-> Seq Scan on int4_tbl touter
SubPlan 3
-> Result
Filter: (tinner_1.f1 = touter.f1)
-> Materialize
-> Broadcast Motion 3:3 (slice4; segments: 3)
-> Seq Scan on int4_tbl tinner_1
-> Append
-> Index Only Scan using asptab0_pkey on asptab0 asptab_1
Index Cond: (id > (((SubPlan 3)))::integer)
-> Index Only Scan using asptab1_pkey on asptab1 asptab_2
Index Cond: (id > (((SubPlan 3)))::integer)
SubPlan 2
-> Broadcast Motion 3:3 (slice2; segments: 3)
-> Seq Scan on int4_tbl tinner
Optimizer: Postgres query optimizer
(19 rows)
drop table asptab;
--
-- Test Partition pruning for HASH partitioning
--
-- Use hand-rolled hash functions and operator classes to get predictable
-- result on different machines. See the definitions of
-- part_part_test_int4_ops and part_test_text_ops in insert.sql.
--
create table hp (a int, b text, c int)
partition by hash (a part_test_int4_ops, b part_test_text_ops);
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 4, remainder 0);
NOTICE: table has parent, setting distribution columns to match parent table
create table hp3 partition of hp for values with (modulus 4, remainder 3);
NOTICE: table has parent, setting distribution columns to match parent table
create table hp1 partition of hp for values with (modulus 4, remainder 1);
NOTICE: table has parent, setting distribution columns to match parent table
create table hp2 partition of hp for values with (modulus 4, remainder 2);
NOTICE: table has parent, setting distribution columns to match parent table
insert into hp values (null, null, 0);
insert into hp values (1, null, 1);
insert into hp values (1, 'xxx', 2);
insert into hp values (null, 'xxx', 3);
insert into hp values (2, 'xxx', 4);
insert into hp values (1, 'abcde', 5);
select tableoid::regclass, * from hp order by c;
tableoid | a | b | c
----------+---+-------+---
hp0 | | | 0
hp1 | 1 | | 1
hp0 | 1 | xxx | 2
hp2 | | xxx | 3
hp3 | 2 | xxx | 4
hp2 | 1 | abcde | 5
(6 rows)
-- partial keys won't prune, nor would non-equality conditions
explain (costs off) select * from hp where a = 1;
QUERY PLAN
------------------------------------------
Gather Motion 1:1 (slice1; segments: 1)
-> Append
-> Seq Scan on hp0 hp_1
Filter: (a = 1)
-> Seq Scan on hp1 hp_2
Filter: (a = 1)
-> Seq Scan on hp2 hp_3
Filter: (a = 1)
-> Seq Scan on hp3 hp_4
Filter: (a = 1)
Optimizer: Postgres query optimizer
(11 rows)
explain (costs off) select * from hp where b = 'xxx';
QUERY PLAN
------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Append
-> Seq Scan on hp0 hp_1
Filter: (b = 'xxx'::text)
-> Seq Scan on hp1 hp_2
Filter: (b = 'xxx'::text)
-> Seq Scan on hp2 hp_3
Filter: (b = 'xxx'::text)
-> Seq Scan on hp3 hp_4
Filter: (b = 'xxx'::text)
Optimizer: Postgres query optimizer
(11 rows)
explain (costs off) select * from hp where a is null;
QUERY PLAN
------------------------------------------
Gather Motion 1:1 (slice1; segments: 1)
-> Append
-> Seq Scan on hp0 hp_1
Filter: (a IS NULL)
-> Seq Scan on hp1 hp_2
Filter: (a IS NULL)
-> Seq Scan on hp2 hp_3
Filter: (a IS NULL)
-> Seq Scan on hp3 hp_4
Filter: (a IS NULL)
Optimizer: Postgres query optimizer
(11 rows)
explain (costs off) select * from hp where b is null;
QUERY PLAN
------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Append
-> Seq Scan on hp0 hp_1
Filter: (b IS NULL)
-> Seq Scan on hp1 hp_2
Filter: (b IS NULL)
-> Seq Scan on hp2 hp_3
Filter: (b IS NULL)
-> Seq Scan on hp3 hp_4
Filter: (b IS NULL)
Optimizer: Postgres query optimizer
(11 rows)
explain (costs off) select * from hp where a < 1 and b = 'xxx';
QUERY PLAN
-------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Append
-> Seq Scan on hp0 hp_1
Filter: ((a < 1) AND (b = 'xxx'::text))
-> Seq Scan on hp1 hp_2
Filter: ((a < 1) AND (b = 'xxx'::text))
-> Seq Scan on hp2 hp_3
Filter: ((a < 1) AND (b = 'xxx'::text))
-> Seq Scan on hp3 hp_4
Filter: ((a < 1) AND (b = 'xxx'::text))
Optimizer: Postgres query optimizer
(11 rows)
explain (costs off) select * from hp where a <> 1 and b = 'yyy';
QUERY PLAN
--------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Append
-> Seq Scan on hp0 hp_1
Filter: ((a <> 1) AND (b = 'yyy'::text))
-> Seq Scan on hp1 hp_2
Filter: ((a <> 1) AND (b = 'yyy'::text))
-> Seq Scan on hp2 hp_3
Filter: ((a <> 1) AND (b = 'yyy'::text))
-> Seq Scan on hp3 hp_4
Filter: ((a <> 1) AND (b = 'yyy'::text))
Optimizer: Postgres query optimizer
(11 rows)
explain (costs off) select * from hp where a <> 1 and b <> 'xxx';
QUERY PLAN
---------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Append
-> Seq Scan on hp0 hp_1
Filter: ((a <> 1) AND (b <> 'xxx'::text))
-> Seq Scan on hp1 hp_2
Filter: ((a <> 1) AND (b <> 'xxx'::text))
-> Seq Scan on hp2 hp_3
Filter: ((a <> 1) AND (b <> 'xxx'::text))
-> Seq Scan on hp3 hp_4
Filter: ((a <> 1) AND (b <> 'xxx'::text))
Optimizer: Postgres query optimizer
(11 rows)
-- pruning should work if either a value or a IS NULL clause is provided for
-- each of the keys
explain (costs off) select * from hp where a is null and b is null;
QUERY PLAN
-----------------------------------------------
Gather Motion 1:1 (slice1; segments: 1)
-> Seq Scan on hp0 hp
Filter: ((a IS NULL) AND (b IS NULL))
Optimizer: Postgres query optimizer
(4 rows)
explain (costs off) select * from hp where a = 1 and b is null;
QUERY PLAN
-------------------------------------------
Gather Motion 1:1 (slice1; segments: 1)
-> Seq Scan on hp1 hp
Filter: ((b IS NULL) AND (a = 1))
Optimizer: Postgres query optimizer
(4 rows)
explain (costs off) select * from hp where a = 1 and b = 'xxx';
QUERY PLAN
-------------------------------------------------
Gather Motion 1:1 (slice1; segments: 1)
-> Seq Scan on hp0 hp
Filter: ((a = 1) AND (b = 'xxx'::text))
Optimizer: Postgres query optimizer
(4 rows)
explain (costs off) select * from hp where a is null and b = 'xxx';
QUERY PLAN
-----------------------------------------------------
Gather Motion 1:1 (slice1; segments: 1)
-> Seq Scan on hp2 hp
Filter: ((a IS NULL) AND (b = 'xxx'::text))
Optimizer: Postgres query optimizer
(4 rows)
explain (costs off) select * from hp where a = 2 and b = 'xxx';
QUERY PLAN
-------------------------------------------------
Gather Motion 1:1 (slice1; segments: 1)
-> Seq Scan on hp3 hp
Filter: ((a = 2) AND (b = 'xxx'::text))
Optimizer: Postgres query optimizer
(4 rows)
explain (costs off) select * from hp where a = 1 and b = 'abcde';
QUERY PLAN
---------------------------------------------------
Gather Motion 1:1 (slice1; segments: 1)
-> Seq Scan on hp2 hp
Filter: ((a = 1) AND (b = 'abcde'::text))
Optimizer: Postgres query optimizer
(4 rows)
explain (costs off) select * from hp where (a = 1 and b = 'abcde') or (a = 2 and b = 'xxx') or (a is null and b is null);
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------
Gather Motion 2:1 (slice1; segments: 2)
-> Append
-> Seq Scan on hp0 hp_1
Filter: (((a = 1) AND (b = 'abcde'::text)) OR ((a = 2) AND (b = 'xxx'::text)) OR ((a IS NULL) AND (b IS NULL)))
-> Seq Scan on hp2 hp_2
Filter: (((a = 1) AND (b = 'abcde'::text)) OR ((a = 2) AND (b = 'xxx'::text)) OR ((a IS NULL) AND (b IS NULL)))
-> Seq Scan on hp3 hp_3
Filter: (((a = 1) AND (b = 'abcde'::text)) OR ((a = 2) AND (b = 'xxx'::text)) OR ((a IS NULL) AND (b IS NULL)))
Optimizer: Postgres query optimizer
(9 rows)
-- test pruning when not all the partitions exist
drop table hp1;
drop table hp3;
explain (costs off) select * from hp where a = 1 and b = 'abcde';
QUERY PLAN
---------------------------------------------------
Gather Motion 1:1 (slice1; segments: 1)
-> Seq Scan on hp2 hp
Filter: ((a = 1) AND (b = 'abcde'::text))
Optimizer: Postgres query optimizer
(4 rows)
explain (costs off) select * from hp where a = 1 and b = 'abcde' and
(c = 2 or c = 3);
QUERY PLAN
----------------------------------------------------------------------------
Gather Motion 1:1 (slice1; segments: 1)
-> Seq Scan on hp2 hp
Filter: ((a = 1) AND (b = 'abcde'::text) AND ((c = 2) OR (c = 3)))
Optimizer: Postgres query optimizer
(4 rows)
drop table hp2;
explain (costs off) select * from hp where a = 1 and b = 'abcde' and
(c = 2 or c = 3);
QUERY PLAN
-------------------------------------
Result
One-Time Filter: false
Optimizer: Postgres query optimizer
(3 rows)
--
-- Test runtime partition pruning
--
create table ab (a int not null, b int not null) partition by list (a);
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 ab_a2 partition of ab for values in(2) partition by list (b);
NOTICE: table has parent, setting distribution columns to match parent table
create table ab_a2_b1 partition of ab_a2 for values in (1);
NOTICE: table has parent, setting distribution columns to match parent table
create table ab_a2_b2 partition of ab_a2 for values in (2);
NOTICE: table has parent, setting distribution columns to match parent table
create table ab_a2_b3 partition of ab_a2 for values in (3);
NOTICE: table has parent, setting distribution columns to match parent table
create table ab_a1 partition of ab for values in(1) partition by list (b);
NOTICE: table has parent, setting distribution columns to match parent table
create table ab_a1_b1 partition of ab_a1 for values in (1);
NOTICE: table has parent, setting distribution columns to match parent table
create table ab_a1_b2 partition of ab_a1 for values in (2);
NOTICE: table has parent, setting distribution columns to match parent table
create table ab_a1_b3 partition of ab_a1 for values in (3);
NOTICE: table has parent, setting distribution columns to match parent table
create table ab_a3 partition of ab for values in(3) partition by list (b);
NOTICE: table has parent, setting distribution columns to match parent table
create table ab_a3_b1 partition of ab_a3 for values in (1);
NOTICE: table has parent, setting distribution columns to match parent table
create table ab_a3_b2 partition of ab_a3 for values in (2);
NOTICE: table has parent, setting distribution columns to match parent table
create table ab_a3_b3 partition of ab_a3 for values in (3);
NOTICE: table has parent, setting distribution columns to match parent table
-- Disallow index only scans as concurrent transactions may stop visibility
-- bits being set causing "Heap Fetches" to be unstable in the EXPLAIN ANALYZE
-- output.
set enable_indexonlyscan = off;
prepare ab_q1 (int, int, int) as
select * from ab where a between $1 and $2 and b <= $3;
explain (analyze, costs off, summary off, timing off) execute ab_q1 (2, 2, 3);
QUERY PLAN
------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3) (actual rows=0 loops=1)
-> Append (actual rows=0 loops=1)
Subplans Removed: 6
-> Seq Scan on ab_a2_b1 ab_1 (actual rows=0 loops=1)
Filter: ((a >= $1) AND (a <= $2) AND (b <= $3))
-> Seq Scan on ab_a2_b2 ab_2 (actual rows=0 loops=1)
Filter: ((a >= $1) AND (a <= $2) AND (b <= $3))
-> Seq Scan on ab_a2_b3 ab_3 (actual rows=0 loops=1)
Filter: ((a >= $1) AND (a <= $2) AND (b <= $3))
Optimizer: Postgres query optimizer
(10 rows)
explain (analyze, costs off, summary off, timing off) execute ab_q1 (1, 2, 3);
QUERY PLAN
------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3) (actual rows=0 loops=1)
-> Append (actual rows=0 loops=1)
Subplans Removed: 3
-> Seq Scan on ab_a1_b1 ab_1 (actual rows=0 loops=1)
Filter: ((a >= $1) AND (a <= $2) AND (b <= $3))
-> Seq Scan on ab_a1_b2 ab_2 (actual rows=0 loops=1)
Filter: ((a >= $1) AND (a <= $2) AND (b <= $3))
-> Seq Scan on ab_a1_b3 ab_3 (actual rows=0 loops=1)
Filter: ((a >= $1) AND (a <= $2) AND (b <= $3))
-> Seq Scan on ab_a2_b1 ab_4 (actual rows=0 loops=1)
Filter: ((a >= $1) AND (a <= $2) AND (b <= $3))
-> Seq Scan on ab_a2_b2 ab_5 (actual rows=0 loops=1)
Filter: ((a >= $1) AND (a <= $2) AND (b <= $3))
-> Seq Scan on ab_a2_b3 ab_6 (actual rows=0 loops=1)
Filter: ((a >= $1) AND (a <= $2) AND (b <= $3))
Optimizer: Postgres query optimizer
(16 rows)
deallocate ab_q1;
-- Runtime pruning after optimizer pruning
prepare ab_q1 (int, int) as
select a from ab where a between $1 and $2 and b < 3;
explain (analyze, costs off, summary off, timing off) execute ab_q1 (2, 2);
QUERY PLAN
------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3) (actual rows=0 loops=1)
-> Append (actual rows=0 loops=1)
Subplans Removed: 4
-> Seq Scan on ab_a2_b1 ab_1 (actual rows=0 loops=1)
Filter: ((a >= $1) AND (a <= $2) AND (b < 3))
-> Seq Scan on ab_a2_b2 ab_2 (actual rows=0 loops=1)
Filter: ((a >= $1) AND (a <= $2) AND (b < 3))
Optimizer: Postgres query optimizer
(8 rows)
explain (analyze, costs off, summary off, timing off) execute ab_q1 (2, 4);
QUERY PLAN
------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3) (actual rows=0 loops=1)
-> Append (actual rows=0 loops=1)
Subplans Removed: 2
-> Seq Scan on ab_a2_b1 ab_1 (actual rows=0 loops=1)
Filter: ((a >= $1) AND (a <= $2) AND (b < 3))
-> Seq Scan on ab_a2_b2 ab_2 (actual rows=0 loops=1)
Filter: ((a >= $1) AND (a <= $2) AND (b < 3))
-> Seq Scan on ab_a3_b1 ab_3 (actual rows=0 loops=1)
Filter: ((a >= $1) AND (a <= $2) AND (b < 3))
-> Seq Scan on ab_a3_b2 ab_4 (actual rows=0 loops=1)
Filter: ((a >= $1) AND (a <= $2) AND (b < 3))
Optimizer: Postgres query optimizer
(12 rows)
-- Ensure a mix of PARAM_EXTERN and PARAM_EXEC Params work together at
-- different levels of partitioning.
prepare ab_q2 (int, int) as
select a from ab where a between $1 and $2 and b < (select 3);
explain (analyze, costs off, summary off, timing off) execute ab_q2 (2, 2);
QUERY PLAN
------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3) (actual rows=0 loops=1)
InitPlan 1 (returns $0) (slice2)
-> Result (actual rows=1 loops=1)
-> Append (actual rows=0 loops=1)
Subplans Removed: 6
-> Seq Scan on ab_a2_b1 ab_1 (actual rows=0 loops=1)
Filter: ((a >= $1) AND (a <= $2) AND (b < $0))
-> Seq Scan on ab_a2_b2 ab_2 (actual rows=0 loops=1)
Filter: ((a >= $1) AND (a <= $2) AND (b < $0))
-> Seq Scan on ab_a2_b3 ab_3 (never executed)
Filter: ((a >= $1) AND (a <= $2) AND (b < $0))
Optimizer: Postgres query optimizer
(12 rows)
-- As above, but swap the PARAM_EXEC Param to the first partition level
prepare ab_q3 (int, int) as
select a from ab where b between $1 and $2 and a < (select 3);
explain (analyze, costs off, summary off, timing off) execute ab_q3 (2, 2);
QUERY PLAN
------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3) (actual rows=0 loops=1)
InitPlan 1 (returns $0) (slice2)
-> Result (actual rows=1 loops=1)
-> Append (actual rows=0 loops=1)
Subplans Removed: 6
-> Seq Scan on ab_a1_b2 ab_1 (actual rows=0 loops=1)
Filter: ((b >= $1) AND (b <= $2) AND (a < $0))
-> Seq Scan on ab_a2_b2 ab_2 (actual rows=0 loops=1)
Filter: ((b >= $1) AND (b <= $2) AND (a < $0))
-> Seq Scan on ab_a3_b2 ab_3 (never executed)
Filter: ((b >= $1) AND (b <= $2) AND (a < $0))
Optimizer: Postgres query optimizer
(12 rows)
--
-- Test runtime pruning with hash partitioned tables
--
-- recreate partitions dropped above
create table hp1 partition of hp for values with (modulus 4, remainder 1);
NOTICE: table has parent, setting distribution columns to match parent table
create table hp2 partition of hp for values with (modulus 4, remainder 2);
NOTICE: table has parent, setting distribution columns to match parent table
create table hp3 partition of hp for values with (modulus 4, remainder 3);
NOTICE: table has parent, setting distribution columns to match parent table
-- Ensure we correctly prune unneeded partitions when there is an IS NULL qual
prepare hp_q1 (text) as
select * from hp where a is null and b = $1;
explain (costs off) execute hp_q1('xxx');
QUERY PLAN
--------------------------------------------------
Gather Motion 1:1 (slice1; segments: 1)
-> Append
Subplans Removed: 3
-> Seq Scan on hp2 hp_1
Filter: ((a IS NULL) AND (b = $1))
Optimizer: Postgres query optimizer
(6 rows)
deallocate hp_q1;
drop table hp;
-- Test a backwards Append scan
create table list_part (a int) partition by list (a);
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 list_part1 partition of list_part for values in (1);
NOTICE: table has parent, setting distribution columns to match parent table
create table list_part2 partition of list_part for values in (2);
NOTICE: table has parent, setting distribution columns to match parent table
create table list_part3 partition of list_part for values in (3);
NOTICE: table has parent, setting distribution columns to match parent table
create table list_part4 partition of list_part for values in (4);
NOTICE: table has parent, setting distribution columns to match parent table
insert into list_part select generate_series(1,4);
begin;
-- Don't select an actual value out of the table as the order of the Append's
-- subnodes may not be stable.
declare cur SCROLL CURSOR for select 1 from list_part where a > (select 1) and a < (select 4);
-- move beyond the final row
move 3 from cur;
-- Ensure we get two rows.
fetch backward all from cur;
ERROR: backward scan is not supported in this version of Apache Cloudberry
commit;
begin;
-- Test run-time pruning using stable functions
create function list_part_fn(int) returns int as $$ begin return $1; end;$$ language plpgsql stable;
-- Ensure pruning works using a stable function containing no Vars
explain (analyze, costs off, summary off, timing off) select * from list_part where a = list_part_fn(1);
QUERY PLAN
------------------------------------------------------------------
Gather Motion 1:1 (slice1; segments: 1) (actual rows=1 loops=1)
-> Seq Scan on list_part1 list_part (actual rows=1 loops=1)
Filter: (a = 1)
Optimizer: Postgres query optimizer
(4 rows)
-- Ensure pruning does not take place when the function has a Var parameter
explain (analyze, costs off, summary off, timing off) select * from list_part where a = list_part_fn(a);
QUERY PLAN
------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3) (actual rows=4 loops=1)
-> Append (actual rows=3 loops=1)
-> Seq Scan on list_part1 list_part_1 (actual rows=1 loops=1)
Filter: (a = list_part_fn(a))
-> Seq Scan on list_part2 list_part_2 (actual rows=1 loops=1)
Filter: (a = list_part_fn(a))
-> Seq Scan on list_part3 list_part_3 (actual rows=1 loops=1)
Filter: (a = list_part_fn(a))
-> Seq Scan on list_part4 list_part_4 (actual rows=1 loops=1)
Filter: (a = list_part_fn(a))
Optimizer: Postgres query optimizer
(11 rows)
-- Ensure pruning does not take place when the expression contains a Var.
explain (analyze, costs off, summary off, timing off) select * from list_part where a = list_part_fn(1) + a;
QUERY PLAN
------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3) (actual rows=0 loops=1)
-> Append (actual rows=0 loops=1)
-> Seq Scan on list_part1 list_part_1 (actual rows=0 loops=1)
Filter: (a = (1 + a))
-> Seq Scan on list_part2 list_part_2 (actual rows=0 loops=1)
Filter: (a = (1 + a))
Rows Removed by Filter: 1
-> Seq Scan on list_part3 list_part_3 (actual rows=0 loops=1)
Filter: (a = (1 + a))
Rows Removed by Filter: 1
-> Seq Scan on list_part4 list_part_4 (actual rows=0 loops=1)
Filter: (a = (1 + a))
Rows Removed by Filter: 1
Optimizer: Postgres query optimizer
(14 rows)
rollback;
drop table list_part;
-- Parallel append
-- Parallel queries won't necessarily get as many workers as the planner
-- asked for. This affects not only the "Workers Launched:" field of EXPLAIN
-- results, but also row counts and loop counts for parallel scans, Gathers,
-- and everything in between. This function filters out the values we can't
-- rely on to be stable.
-- This removes enough info that you might wonder why bother with EXPLAIN
-- ANALYZE at all. The answer is that we need to see '(never executed)'
-- notations because that's the only way to verify runtime pruning.
create function explain_parallel_append(text) returns setof text
language plpgsql as
$$
declare
ln text;
begin
set local enable_parallel = off;
for ln in
execute format('explain (analyze, costs off, summary off, timing off) %s',
$1)
loop
ln := regexp_replace(ln, 'Workers Launched: \d+', 'Workers Launched: N');
ln := regexp_replace(ln, 'actual rows=\d+ loops=\d+', 'actual rows=N loops=N');
ln := regexp_replace(ln, 'Rows Removed by Filter: \d+', 'Rows Removed by Filter: N');
return next ln;
end loop;
reset enable_parallel;
end;
$$;
prepare ab_q4 (int, int) as
select avg(a) from ab where a between $1 and $2 and b < 4;
-- Encourage use of parallel plans
set parallel_setup_cost = 0;
set parallel_tuple_cost = 0;
set min_parallel_table_scan_size = 0;
set max_parallel_workers_per_gather = 2;
select explain_parallel_append('execute ab_q4 (2, 2)');
explain_parallel_append
---------------------------------------------------------------------------
Finalize Aggregate (actual rows=N loops=N)
-> Gather Motion 3:1 (slice1; segments: 3) (actual rows=N loops=N)
-> Partial Aggregate (actual rows=N loops=N)
-> Append (actual rows=N loops=N)
Subplans Removed: 6
-> Seq Scan on ab_a2_b1 ab_1 (actual rows=N loops=N)
Filter: ((a >= $1) AND (a <= $2) AND (b < 4))
-> Seq Scan on ab_a2_b2 ab_2 (actual rows=N loops=N)
Filter: ((a >= $1) AND (a <= $2) AND (b < 4))
-> Seq Scan on ab_a2_b3 ab_3 (actual rows=N loops=N)
Filter: ((a >= $1) AND (a <= $2) AND (b < 4))
Optimizer: Postgres query optimizer
(12 rows)
-- Test run-time pruning with IN lists.
prepare ab_q5 (int, int, int) as
select avg(a) from ab where a in($1,$2,$3) and b < 4;
select explain_parallel_append('execute ab_q5 (1, 1, 1)');
explain_parallel_append
-------------------------------------------------------------------------------
Finalize Aggregate (actual rows=N loops=N)
-> Gather Motion 3:1 (slice1; segments: 3) (actual rows=N loops=N)
-> Partial Aggregate (actual rows=N loops=N)
-> Append (actual rows=N loops=N)
Subplans Removed: 6
-> Seq Scan on ab_a1_b1 ab_1 (actual rows=N loops=N)
Filter: ((b < 4) AND (a = ANY (ARRAY[$1, $2, $3])))
-> Seq Scan on ab_a1_b2 ab_2 (actual rows=N loops=N)
Filter: ((b < 4) AND (a = ANY (ARRAY[$1, $2, $3])))
-> Seq Scan on ab_a1_b3 ab_3 (actual rows=N loops=N)
Filter: ((b < 4) AND (a = ANY (ARRAY[$1, $2, $3])))
Optimizer: Postgres query optimizer
(12 rows)
select explain_parallel_append('execute ab_q5 (2, 3, 3)');
explain_parallel_append
-------------------------------------------------------------------------------
Finalize Aggregate (actual rows=N loops=N)
-> Gather Motion 3:1 (slice1; segments: 3) (actual rows=N loops=N)
-> Partial Aggregate (actual rows=N loops=N)
-> Append (actual rows=N loops=N)
Subplans Removed: 3
-> Seq Scan on ab_a2_b1 ab_1 (actual rows=N loops=N)
Filter: ((b < 4) AND (a = ANY (ARRAY[$1, $2, $3])))
-> Seq Scan on ab_a2_b2 ab_2 (actual rows=N loops=N)
Filter: ((b < 4) AND (a = ANY (ARRAY[$1, $2, $3])))
-> Seq Scan on ab_a2_b3 ab_3 (actual rows=N loops=N)
Filter: ((b < 4) AND (a = ANY (ARRAY[$1, $2, $3])))
-> Seq Scan on ab_a3_b1 ab_4 (actual rows=N loops=N)
Filter: ((b < 4) AND (a = ANY (ARRAY[$1, $2, $3])))
-> Seq Scan on ab_a3_b2 ab_5 (actual rows=N loops=N)
Filter: ((b < 4) AND (a = ANY (ARRAY[$1, $2, $3])))
-> Seq Scan on ab_a3_b3 ab_6 (actual rows=N loops=N)
Filter: ((b < 4) AND (a = ANY (ARRAY[$1, $2, $3])))
Optimizer: Postgres query optimizer
(18 rows)
-- Try some params whose values do not belong to any partition.
select explain_parallel_append('execute ab_q5 (33, 44, 55)');
explain_parallel_append
------------------------------------------------------------------------
Finalize Aggregate (actual rows=N loops=N)
-> Gather Motion 3:1 (slice1; segments: 3) (actual rows=N loops=N)
-> Partial Aggregate (actual rows=N loops=N)
-> Append (actual rows=N loops=N)
Subplans Removed: 9
Optimizer: Postgres query optimizer
(6 rows)
-- Test Parallel Append with PARAM_EXEC Params
select explain_parallel_append('select count(*) from ab where (a = (select 1) or a = (select 3)) and b = 2');
explain_parallel_append
---------------------------------------------------------------------------
Finalize Aggregate (actual rows=N loops=N)
InitPlan 1 (returns $0) (slice2)
-> Result (actual rows=N loops=N)
InitPlan 2 (returns $1) (slice3)
-> Result (actual rows=N loops=N)
-> Gather Motion 3:1 (slice1; segments: 3) (actual rows=N loops=N)
-> Partial Aggregate (actual rows=N loops=N)
-> Append (actual rows=N loops=N)
-> Seq Scan on ab_a1_b2 ab_1 (actual rows=N loops=N)
Filter: ((b = 2) AND ((a = $0) OR (a = $1)))
-> Seq Scan on ab_a2_b2 ab_2 (never executed)
Filter: ((b = 2) AND ((a = $0) OR (a = $1)))
-> Seq Scan on ab_a3_b2 ab_3 (actual rows=N loops=N)
Filter: ((b = 2) AND ((a = $0) OR (a = $1)))
Optimizer: Postgres query optimizer
(15 rows)
-- Test pruning during parallel nested loop query
create table lprt_a (a int not null);
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 some values we won't find in ab
insert into lprt_a select 0 from generate_series(1,100);
-- and insert some values that we should find.
insert into lprt_a values(1),(1);
analyze lprt_a;
create index ab_a2_b1_a_idx on ab_a2_b1 (a);
create index ab_a2_b2_a_idx on ab_a2_b2 (a);
create index ab_a2_b3_a_idx on ab_a2_b3 (a);
create index ab_a1_b1_a_idx on ab_a1_b1 (a);
create index ab_a1_b2_a_idx on ab_a1_b2 (a);
create index ab_a1_b3_a_idx on ab_a1_b3 (a);
create index ab_a3_b1_a_idx on ab_a3_b1 (a);
create index ab_a3_b2_a_idx on ab_a3_b2 (a);
create index ab_a3_b3_a_idx on ab_a3_b3 (a);
set enable_hashjoin = 0;
set enable_mergejoin = 0;
set enable_memoize = 0;
select explain_parallel_append('select avg(ab.a) from ab inner join lprt_a a on ab.a = a.a where a.a in(0, 0, 1)');
explain_parallel_append
-------------------------------------------------------------------------------------------------------
Finalize Aggregate (actual rows=N loops=N)
-> Gather Motion 1:1 (slice1; segments: 1) (actual rows=N loops=N)
-> Partial Aggregate (actual rows=N loops=N)
-> Merge Join (actual rows=N loops=N)
Merge Cond: (ab.a = a.a)
-> Sort (actual rows=N loops=N)
Sort Key: ab.a
Sort Method: quicksort Memory: 25kB
-> Append (actual rows=N loops=N)
Partition Selectors: $0
-> Bitmap Heap Scan on ab_a1_b1 ab_1 (actual rows=N loops=N)
Recheck Cond: (a = ANY ('{0,0,1}'::integer[]))
-> Bitmap Index Scan on ab_a1_b1_a_idx (actual rows=N loops=N)
Index Cond: (a = ANY ('{0,0,1}'::integer[]))
-> Bitmap Heap Scan on ab_a1_b2 ab_2 (actual rows=N loops=N)
Recheck Cond: (a = ANY ('{0,0,1}'::integer[]))
-> Bitmap Index Scan on ab_a1_b2_a_idx (actual rows=N loops=N)
Index Cond: (a = ANY ('{0,0,1}'::integer[]))
-> Bitmap Heap Scan on ab_a1_b3 ab_3 (actual rows=N loops=N)
Recheck Cond: (a = ANY ('{0,0,1}'::integer[]))
-> Bitmap Index Scan on ab_a1_b3_a_idx (actual rows=N loops=N)
Index Cond: (a = ANY ('{0,0,1}'::integer[]))
-> Sort (actual rows=N loops=N)
Sort Key: a.a
Sort Method: quicksort Memory: 25kB
-> Partition Selector (selector id: $0) (actual rows=N loops=N)
-> Seq Scan on lprt_a a (actual rows=N loops=N)
Filter: (a = ANY ('{0,0,1}'::integer[]))
Optimizer: Postgres query optimizer
(29 rows)
-- Ensure the same partitions are pruned when we make the nested loop
-- parameter an Expr rather than a plain Param.
select explain_parallel_append('select avg(ab.a) from ab inner join lprt_a a on ab.a = a.a + 0 where a.a in(0, 0, 1)');
explain_parallel_append
--------------------------------------------------------------------------------------------------------
Finalize Aggregate (actual rows=N loops=N)
-> Gather Motion 3:1 (slice1; segments: 3) (actual rows=N loops=N)
-> Partial Aggregate (actual rows=N loops=N)
-> Nested Loop (actual rows=N loops=N)
-> Redistribute Motion 1:3 (slice2; segments: 1) (actual rows=N loops=N)
Hash Key: (a.a + 0)
-> Seq Scan on lprt_a a (actual rows=N loops=N)
Filter: (a = ANY ('{0,0,1}'::integer[]))
-> Append (actual rows=N loops=N)
-> Index Scan using ab_a1_b1_a_idx on ab_a1_b1 ab_1 (actual rows=N loops=N)
Index Cond: (a = (a.a + 0))
-> Index Scan using ab_a1_b2_a_idx on ab_a1_b2 ab_2 (actual rows=N loops=N)
Index Cond: (a = (a.a + 0))
-> Index Scan using ab_a1_b3_a_idx on ab_a1_b3 ab_3 (actual rows=N loops=N)
Index Cond: (a = (a.a + 0))
-> Index Scan using ab_a2_b1_a_idx on ab_a2_b1 ab_4 (never executed)
Index Cond: (a = (a.a + 0))
-> Index Scan using ab_a2_b2_a_idx on ab_a2_b2 ab_5 (never executed)
Index Cond: (a = (a.a + 0))
-> Index Scan using ab_a2_b3_a_idx on ab_a2_b3 ab_6 (never executed)
Index Cond: (a = (a.a + 0))
-> Index Scan using ab_a3_b1_a_idx on ab_a3_b1 ab_7 (never executed)
Index Cond: (a = (a.a + 0))
-> Index Scan using ab_a3_b2_a_idx on ab_a3_b2 ab_8 (never executed)
Index Cond: (a = (a.a + 0))
-> Index Scan using ab_a3_b3_a_idx on ab_a3_b3 ab_9 (never executed)
Index Cond: (a = (a.a + 0))
Optimizer: Postgres query optimizer
(28 rows)
insert into lprt_a values(3),(3);
select explain_parallel_append('select avg(ab.a) from ab inner join lprt_a a on ab.a = a.a where a.a in(1, 0, 3)');
explain_parallel_append
-------------------------------------------------------------------------------------------------------
Finalize Aggregate (actual rows=N loops=N)
-> Gather Motion 2:1 (slice1; segments: 2) (actual rows=N loops=N)
-> Partial Aggregate (actual rows=N loops=N)
-> Merge Join (actual rows=N loops=N)
Merge Cond: (ab.a = a.a)
-> Sort (actual rows=N loops=N)
Sort Key: ab.a
Sort Method: quicksort Memory: 50kB
-> Append (actual rows=N loops=N)
Partition Selectors: $0
-> Bitmap Heap Scan on ab_a1_b1 ab_1 (actual rows=N loops=N)
Recheck Cond: (a = ANY ('{1,0,3}'::integer[]))
-> Bitmap Index Scan on ab_a1_b1_a_idx (actual rows=N loops=N)
Index Cond: (a = ANY ('{1,0,3}'::integer[]))
-> Bitmap Heap Scan on ab_a1_b2 ab_2 (actual rows=N loops=N)
Recheck Cond: (a = ANY ('{1,0,3}'::integer[]))
-> Bitmap Index Scan on ab_a1_b2_a_idx (actual rows=N loops=N)
Index Cond: (a = ANY ('{1,0,3}'::integer[]))
-> Bitmap Heap Scan on ab_a1_b3 ab_3 (actual rows=N loops=N)
Recheck Cond: (a = ANY ('{1,0,3}'::integer[]))
-> Bitmap Index Scan on ab_a1_b3_a_idx (actual rows=N loops=N)
Index Cond: (a = ANY ('{1,0,3}'::integer[]))
-> Bitmap Heap Scan on ab_a3_b1 ab_4 (actual rows=N loops=N)
Recheck Cond: (a = ANY ('{1,0,3}'::integer[]))
-> Bitmap Index Scan on ab_a3_b1_a_idx (actual rows=N loops=N)
Index Cond: (a = ANY ('{1,0,3}'::integer[]))
-> Bitmap Heap Scan on ab_a3_b2 ab_5 (actual rows=N loops=N)
Recheck Cond: (a = ANY ('{1,0,3}'::integer[]))
-> Bitmap Index Scan on ab_a3_b2_a_idx (actual rows=N loops=N)
Index Cond: (a = ANY ('{1,0,3}'::integer[]))
-> Bitmap Heap Scan on ab_a3_b3 ab_6 (actual rows=N loops=N)
Recheck Cond: (a = ANY ('{1,0,3}'::integer[]))
-> Bitmap Index Scan on ab_a3_b3_a_idx (actual rows=N loops=N)
Index Cond: (a = ANY ('{1,0,3}'::integer[]))
-> Sort (actual rows=N loops=N)
Sort Key: a.a
Sort Method: quicksort Memory: 50kB
-> Partition Selector (selector id: $0) (actual rows=N loops=N)
-> Seq Scan on lprt_a a (actual rows=N loops=N)
Filter: (a = ANY ('{1,0,3}'::integer[]))
Optimizer: Postgres query optimizer
(41 rows)
select explain_parallel_append('select avg(ab.a) from ab inner join lprt_a a on ab.a = a.a where a.a in(1, 0, 0)');
explain_parallel_append
-------------------------------------------------------------------------------------------------------
Finalize Aggregate (actual rows=N loops=N)
-> Gather Motion 1:1 (slice1; segments: 1) (actual rows=N loops=N)
-> Partial Aggregate (actual rows=N loops=N)
-> Merge Join (actual rows=N loops=N)
Merge Cond: (ab.a = a.a)
-> Sort (actual rows=N loops=N)
Sort Key: ab.a
Sort Method: quicksort Memory: 25kB
-> Append (actual rows=N loops=N)
Partition Selectors: $0
-> Bitmap Heap Scan on ab_a1_b1 ab_1 (actual rows=N loops=N)
Recheck Cond: (a = ANY ('{1,0,0}'::integer[]))
-> Bitmap Index Scan on ab_a1_b1_a_idx (actual rows=N loops=N)
Index Cond: (a = ANY ('{1,0,0}'::integer[]))
-> Bitmap Heap Scan on ab_a1_b2 ab_2 (actual rows=N loops=N)
Recheck Cond: (a = ANY ('{1,0,0}'::integer[]))
-> Bitmap Index Scan on ab_a1_b2_a_idx (actual rows=N loops=N)
Index Cond: (a = ANY ('{1,0,0}'::integer[]))
-> Bitmap Heap Scan on ab_a1_b3 ab_3 (actual rows=N loops=N)
Recheck Cond: (a = ANY ('{1,0,0}'::integer[]))
-> Bitmap Index Scan on ab_a1_b3_a_idx (actual rows=N loops=N)
Index Cond: (a = ANY ('{1,0,0}'::integer[]))
-> Sort (actual rows=N loops=N)
Sort Key: a.a
Sort Method: quicksort Memory: 25kB
-> Partition Selector (selector id: $0) (actual rows=N loops=N)
-> Seq Scan on lprt_a a (actual rows=N loops=N)
Filter: (a = ANY ('{1,0,0}'::integer[]))
Optimizer: Postgres query optimizer
(29 rows)
delete from lprt_a where a = 1;
select explain_parallel_append('select avg(ab.a) from ab inner join lprt_a a on ab.a = a.a where a.a in(1, 0, 0)');
explain_parallel_append
------------------------------------------------------------------------------------------------
Finalize Aggregate (actual rows=N loops=N)
-> Gather Motion 1:1 (slice1; segments: 1) (actual rows=N loops=N)
-> Partial Aggregate (actual rows=N loops=N)
-> Merge Join (actual rows=N loops=N)
Merge Cond: (ab.a = a.a)
-> Sort (actual rows=N loops=N)
Sort Key: ab.a
Sort Method: quicksort Memory: 25kB
-> Append (actual rows=N loops=N)
Partition Selectors: $0
-> Bitmap Heap Scan on ab_a1_b1 ab_1 (never executed)
Recheck Cond: (a = ANY ('{1,0,0}'::integer[]))
-> Bitmap Index Scan on ab_a1_b1_a_idx (never executed)
Index Cond: (a = ANY ('{1,0,0}'::integer[]))
-> Bitmap Heap Scan on ab_a1_b2 ab_2 (never executed)
Recheck Cond: (a = ANY ('{1,0,0}'::integer[]))
-> Bitmap Index Scan on ab_a1_b2_a_idx (never executed)
Index Cond: (a = ANY ('{1,0,0}'::integer[]))
-> Bitmap Heap Scan on ab_a1_b3 ab_3 (never executed)
Recheck Cond: (a = ANY ('{1,0,0}'::integer[]))
-> Bitmap Index Scan on ab_a1_b3_a_idx (never executed)
Index Cond: (a = ANY ('{1,0,0}'::integer[]))
-> Sort (actual rows=N loops=N)
Sort Key: a.a
Sort Method: quicksort Memory: 25kB
-> Partition Selector (selector id: $0) (actual rows=N loops=N)
-> Seq Scan on lprt_a a (actual rows=N loops=N)
Filter: (a = ANY ('{1,0,0}'::integer[]))
Optimizer: Postgres query optimizer
(29 rows)
reset enable_hashjoin;
reset enable_mergejoin;
reset enable_memoize;
reset parallel_setup_cost;
reset parallel_tuple_cost;
reset min_parallel_table_scan_size;
reset max_parallel_workers_per_gather;
-- Test run-time partition pruning with an initplan
explain (analyze, costs off, summary off, timing off)
select * from ab where a = (select max(a) from lprt_a) and b = (select max(a)-1 from lprt_a);
QUERY PLAN
---------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3) (actual rows=0 loops=1)
InitPlan 1 (returns $0) (slice2)
-> Finalize Aggregate (actual rows=1 loops=1)
-> Gather Motion 3:1 (slice3; segments: 3) (actual rows=3 loops=1)
-> Partial Aggregate (actual rows=1 loops=1)
-> Seq Scan on lprt_a (actual rows=100 loops=1)
InitPlan 2 (returns $1) (slice4)
-> Finalize Aggregate (actual rows=1 loops=1)
-> Gather Motion 3:1 (slice5; segments: 3) (actual rows=3 loops=1)
-> Partial Aggregate (actual rows=1 loops=1)
-> Seq Scan on lprt_a lprt_a_1 (actual rows=100 loops=1)
-> Append (actual rows=0 loops=1)
-> Bitmap Heap Scan on ab_a1_b1 ab_1 (never executed)
Recheck Cond: (a = $0)
Filter: (b = $1)
-> Bitmap Index Scan on ab_a1_b1_a_idx (never executed)
Index Cond: (a = $0)
-> Bitmap Heap Scan on ab_a1_b2 ab_2 (never executed)
Recheck Cond: (a = $0)
Filter: (b = $1)
-> Bitmap Index Scan on ab_a1_b2_a_idx (never executed)
Index Cond: (a = $0)
-> Bitmap Heap Scan on ab_a1_b3 ab_3 (never executed)
Recheck Cond: (a = $0)
Filter: (b = $1)
-> Bitmap Index Scan on ab_a1_b3_a_idx (never executed)
Index Cond: (a = $0)
-> Bitmap Heap Scan on ab_a2_b1 ab_4 (never executed)
Recheck Cond: (a = $0)
Filter: (b = $1)
-> Bitmap Index Scan on ab_a2_b1_a_idx (never executed)
Index Cond: (a = $0)
-> Bitmap Heap Scan on ab_a2_b2 ab_5 (never executed)
Recheck Cond: (a = $0)
Filter: (b = $1)
-> Bitmap Index Scan on ab_a2_b2_a_idx (never executed)
Index Cond: (a = $0)
-> Bitmap Heap Scan on ab_a2_b3 ab_6 (never executed)
Recheck Cond: (a = $0)
Filter: (b = $1)
-> Bitmap Index Scan on ab_a2_b3_a_idx (never executed)
Index Cond: (a = $0)
-> Bitmap Heap Scan on ab_a3_b1 ab_7 (never executed)
Recheck Cond: (a = $0)
Filter: (b = $1)
-> Bitmap Index Scan on ab_a3_b1_a_idx (never executed)
Index Cond: (a = $0)
-> Bitmap Heap Scan on ab_a3_b2 ab_8 (actual rows=0 loops=1)
Recheck Cond: (a = $0)
Filter: (b = $1)
-> Bitmap Index Scan on ab_a3_b2_a_idx (actual rows=0 loops=1)
Index Cond: (a = $0)
-> Bitmap Heap Scan on ab_a3_b3 ab_9 (never executed)
Recheck Cond: (a = $0)
Filter: (b = $1)
-> Bitmap Index Scan on ab_a3_b3_a_idx (never executed)
Index Cond: (a = $0)
Optimizer: Postgres query optimizer
(58 rows)
-- Test run-time partition pruning with UNION ALL parents
explain (analyze, costs off, summary off, timing off)
select * from (select * from ab where a = 1 union all select * from ab) ab where b = (select 1);
QUERY PLAN
-------------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3) (actual rows=0 loops=1)
InitPlan 1 (returns $0) (slice2)
-> Result (actual rows=1 loops=1)
-> Append (actual rows=0 loops=1)
-> Append (actual rows=0 loops=1)
-> Bitmap Heap Scan on ab_a1_b1 ab_11 (actual rows=0 loops=1)
Recheck Cond: (a = 1)
Filter: (b = $0)
-> Bitmap Index Scan on ab_a1_b1_a_idx (actual rows=0 loops=1)
Index Cond: (a = 1)
-> Bitmap Heap Scan on ab_a1_b2 ab_12 (never executed)
Recheck Cond: (a = 1)
Filter: (b = $0)
-> Bitmap Index Scan on ab_a1_b2_a_idx (never executed)
Index Cond: (a = 1)
-> Bitmap Heap Scan on ab_a1_b3 ab_13 (never executed)
Recheck Cond: (a = 1)
Filter: (b = $0)
-> Bitmap Index Scan on ab_a1_b3_a_idx (never executed)
Index Cond: (a = 1)
-> Seq Scan on ab_a1_b1 ab_1 (actual rows=0 loops=1)
Filter: (b = $0)
-> Seq Scan on ab_a1_b2 ab_2 (never executed)
Filter: (b = $0)
-> Seq Scan on ab_a1_b3 ab_3 (never executed)
Filter: (b = $0)
-> Seq Scan on ab_a2_b1 ab_4 (actual rows=0 loops=1)
Filter: (b = $0)
-> Seq Scan on ab_a2_b2 ab_5 (never executed)
Filter: (b = $0)
-> Seq Scan on ab_a2_b3 ab_6 (never executed)
Filter: (b = $0)
-> Seq Scan on ab_a3_b1 ab_7 (actual rows=0 loops=1)
Filter: (b = $0)
-> Seq Scan on ab_a3_b2 ab_8 (never executed)
Filter: (b = $0)
-> Seq Scan on ab_a3_b3 ab_9 (never executed)
Filter: (b = $0)
Optimizer: Postgres query optimizer
(39 rows)
-- A case containing a UNION ALL with a non-partitioned child.
explain (analyze, costs off, summary off, timing off)
select * from (select * from ab where a = 1 union all (values(10,5)) union all select * from ab) ab where b = (select 1);
QUERY PLAN
-------------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3) (actual rows=0 loops=1)
InitPlan 1 (returns $0) (slice2)
-> Result (actual rows=1 loops=1)
-> Append (actual rows=0 loops=1)
-> Append (actual rows=0 loops=1)
-> Bitmap Heap Scan on ab_a1_b1 ab_11 (actual rows=0 loops=1)
Recheck Cond: (a = 1)
Filter: (b = $0)
-> Bitmap Index Scan on ab_a1_b1_a_idx (actual rows=0 loops=1)
Index Cond: (a = 1)
-> Bitmap Heap Scan on ab_a1_b2 ab_12 (never executed)
Recheck Cond: (a = 1)
Filter: (b = $0)
-> Bitmap Index Scan on ab_a1_b2_a_idx (never executed)
Index Cond: (a = 1)
-> Bitmap Heap Scan on ab_a1_b3 ab_13 (never executed)
Recheck Cond: (a = 1)
Filter: (b = $0)
-> Bitmap Index Scan on ab_a1_b3_a_idx (never executed)
Index Cond: (a = 1)
-> Result (actual rows=0 loops=1)
One-Time Filter: (gp_execution_segment() = 2)
-> Result (actual rows=0 loops=1)
One-Time Filter: (5 = $0)
-> Seq Scan on ab_a1_b1 ab_1 (actual rows=0 loops=1)
Filter: (b = $0)
-> Seq Scan on ab_a1_b2 ab_2 (never executed)
Filter: (b = $0)
-> Seq Scan on ab_a1_b3 ab_3 (never executed)
Filter: (b = $0)
-> Seq Scan on ab_a2_b1 ab_4 (actual rows=0 loops=1)
Filter: (b = $0)
-> Seq Scan on ab_a2_b2 ab_5 (never executed)
Filter: (b = $0)
-> Seq Scan on ab_a2_b3 ab_6 (never executed)
Filter: (b = $0)
-> Seq Scan on ab_a3_b1 ab_7 (actual rows=0 loops=1)
Filter: (b = $0)
-> Seq Scan on ab_a3_b2 ab_8 (never executed)
Filter: (b = $0)
-> Seq Scan on ab_a3_b3 ab_9 (never executed)
Filter: (b = $0)
Optimizer: Postgres query optimizer
(43 rows)
-- Another UNION ALL test, but containing a mix of exec init and exec run-time pruning.
create table xy_1 (x int, y int);
NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'x' 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 xy_1 values(100,-10);
set enable_bitmapscan = 0;
set enable_indexscan = 0;
prepare ab_q6 as
select * from (
select tableoid::regclass,a,b from ab
union all
select tableoid::regclass,x,y from xy_1
union all
select tableoid::regclass,a,b from ab
) ab where a = $1 and b = (select -10);
-- Ensure the xy_1 subplan is not pruned.
explain (analyze, costs off, summary off, timing off) execute ab_q6(1);
QUERY PLAN
------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3) (actual rows=0 loops=1)
InitPlan 1 (returns $0) (slice2)
-> Result (actual rows=1 loops=1)
-> Append (actual rows=0 loops=1)
Subplans Removed: 12
-> Seq Scan on ab_a1_b1 ab_1 (never executed)
Filter: ((a = $1) AND (b = $0))
-> Seq Scan on ab_a1_b2 ab_2 (never executed)
Filter: ((a = $1) AND (b = $0))
-> Seq Scan on ab_a1_b3 ab_3 (never executed)
Filter: ((a = $1) AND (b = $0))
-> Seq Scan on xy_1 (actual rows=0 loops=1)
Filter: ((x = $1) AND (y = $0))
-> Seq Scan on ab_a1_b1 ab_4 (never executed)
Filter: ((a = $1) AND (b = $0))
-> Seq Scan on ab_a1_b2 ab_5 (never executed)
Filter: ((a = $1) AND (b = $0))
-> Seq Scan on ab_a1_b3 ab_6 (never executed)
Filter: ((a = $1) AND (b = $0))
Optimizer: Postgres query optimizer
(20 rows)
-- Ensure we see just the xy_1 row.
execute ab_q6(100);
tableoid | a | b
----------+-----+-----
xy_1 | 100 | -10
(1 row)
reset enable_bitmapscan;
reset enable_indexscan;
deallocate ab_q1;
deallocate ab_q2;
deallocate ab_q3;
deallocate ab_q4;
deallocate ab_q5;
deallocate ab_q6;
-- UPDATE on a partition subtree has been seen to have problems.
insert into ab values (1,2);
explain (analyze, costs off, summary off, timing off)
update ab_a1 set b = 3 from ab where ab.a = 1 and ab.a = ab_a1.a;
QUERY PLAN
-------------------------------------------------------------------------------------------
Update on ab_a1 (actual rows=0 loops=1)
Update on ab_a1_b1 ab_a1_1
Update on ab_a1_b2 ab_a1_2
Update on ab_a1_b3 ab_a1_3
-> Nested Loop (actual rows=1 loops=1)
-> Append (actual rows=1 loops=1)
-> Bitmap Heap Scan on ab_a1_b1 ab_a1_1 (actual rows=0 loops=1)
Recheck Cond: (a = 1)
-> Bitmap Index Scan on ab_a1_b1_a_idx (actual rows=0 loops=1)
Index Cond: (a = 1)
-> Bitmap Heap Scan on ab_a1_b2 ab_a1_2 (actual rows=1 loops=1)
Recheck Cond: (a = 1)
-> Bitmap Index Scan on ab_a1_b2_a_idx (actual rows=1 loops=1)
Index Cond: (a = 1)
-> Bitmap Heap Scan on ab_a1_b3 ab_a1_3 (actual rows=0 loops=1)
Recheck Cond: (a = 1)
-> Bitmap Index Scan on ab_a1_b3_a_idx (actual rows=1 loops=1)
Index Cond: (a = 1)
-> Materialize (actual rows=1 loops=1)
-> Append (actual rows=1 loops=1)
-> Bitmap Heap Scan on ab_a1_b1 ab_1 (actual rows=0 loops=1)
Recheck Cond: (a = 1)
-> Bitmap Index Scan on ab_a1_b1_a_idx (actual rows=0 loops=1)
Index Cond: (a = 1)
-> Bitmap Heap Scan on ab_a1_b2 ab_2 (actual rows=1 loops=1)
Recheck Cond: (a = 1)
-> Bitmap Index Scan on ab_a1_b2_a_idx (actual rows=1 loops=1)
Index Cond: (a = 1)
-> Bitmap Heap Scan on ab_a1_b3 ab_3 (actual rows=0 loops=1)
Recheck Cond: (a = 1)
-> Bitmap Index Scan on ab_a1_b3_a_idx (actual rows=1 loops=1)
Index Cond: (a = 1)
Optimizer: Postgres query optimizer
(33 rows)
table ab;
a | b
---+---
1 | 3
(1 row)
-- Test UPDATE where source relation has run-time pruning enabled
truncate ab;
insert into ab values (1, 1), (1, 2), (1, 3), (2, 1);
explain (analyze, costs off, summary off, timing off)
update ab_a1 set b = 3 from ab_a2 where ab_a2.b = (select 1);
QUERY PLAN
---------------------------------------------------------------------------------
Update on ab_a1 (actual rows=0 loops=1)
Update on ab_a1_b1 ab_a1_1
Update on ab_a1_b2 ab_a1_2
Update on ab_a1_b3 ab_a1_3
InitPlan 1 (returns $0) (slice2)
-> Result (actual rows=1 loops=1)
-> Nested Loop (actual rows=3 loops=1)
-> Broadcast Motion 3:3 (slice1; segments: 3) (actual rows=1 loops=1)
-> Append (actual rows=1 loops=1)
-> Seq Scan on ab_a2_b1 ab_a2_1 (actual rows=1 loops=1)
Filter: (b = $0)
-> Seq Scan on ab_a2_b2 ab_a2_2 (never executed)
Filter: (b = $0)
-> Seq Scan on ab_a2_b3 ab_a2_3 (never executed)
Filter: (b = $0)
-> Materialize (actual rows=3 loops=1)
-> Append (actual rows=3 loops=1)
-> Seq Scan on ab_a1_b1 ab_a1_1 (actual rows=1 loops=1)
-> Seq Scan on ab_a1_b2 ab_a1_2 (actual rows=1 loops=1)
-> Seq Scan on ab_a1_b3 ab_a1_3 (actual rows=1 loops=1)
Optimizer: Postgres query optimizer
(21 rows)
select tableoid::regclass, * from ab;
tableoid | a | b
----------+---+---
ab_a1_b3 | 1 | 3
ab_a1_b3 | 1 | 3
ab_a1_b3 | 1 | 3
ab_a2_b1 | 2 | 1
(4 rows)
drop table ab, lprt_a;
-- Join
create table tbl1(col1 int);
NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'col1' 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 tbl1 values (501), (505);
analyze tbl1;
-- Basic table
create table tprt (col1 int) partition by range (col1);
NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'col1' 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 tprt_1 partition of tprt for values from (1) to (501);
NOTICE: table has parent, setting distribution columns to match parent table
create table tprt_2 partition of tprt for values from (501) to (1001);
NOTICE: table has parent, setting distribution columns to match parent table
create table tprt_3 partition of tprt for values from (1001) to (2001);
NOTICE: table has parent, setting distribution columns to match parent table
create table tprt_4 partition of tprt for values from (2001) to (3001);
NOTICE: table has parent, setting distribution columns to match parent table
create table tprt_5 partition of tprt for values from (3001) to (4001);
NOTICE: table has parent, setting distribution columns to match parent table
create table tprt_6 partition of tprt for values from (4001) to (5001);
NOTICE: table has parent, setting distribution columns to match parent table
create index tprt1_idx on tprt_1 (col1);
create index tprt2_idx on tprt_2 (col1);
create index tprt3_idx on tprt_3 (col1);
create index tprt4_idx on tprt_4 (col1);
create index tprt5_idx on tprt_5 (col1);
create index tprt6_idx on tprt_6 (col1);
insert into tprt values (10), (20), (501), (502), (505), (1001), (4500);
set enable_hashjoin = off;
set enable_mergejoin = off;
set enable_seqscan=off;
explain (analyze, costs off, summary off, timing off)
select * from tbl1 join tprt on tbl1.col1 > tprt.col1;
QUERY PLAN
---------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3) (actual rows=6 loops=1)
-> Nested Loop (actual rows=2 loops=1)
-> Broadcast Motion 3:3 (slice2; segments: 3) (actual rows=2 loops=1)
-> Seq Scan on tbl1 (actual rows=1 loops=1)
-> Append (actual rows=1 loops=2)
-> Bitmap Heap Scan on tprt_1 (actual rows=1 loops=2)
Recheck Cond: (tbl1.col1 > col1)
-> Bitmap Index Scan on tprt1_idx (actual rows=1 loops=2)
Index Cond: (col1 < tbl1.col1)
-> Bitmap Heap Scan on tprt_2 (actual rows=2 loops=1)
Recheck Cond: (tbl1.col1 > col1)
-> Bitmap Index Scan on tprt2_idx (actual rows=2 loops=1)
Index Cond: (col1 < tbl1.col1)
-> Bitmap Heap Scan on tprt_3 (never executed)
Recheck Cond: (tbl1.col1 > col1)
-> Bitmap Index Scan on tprt3_idx (never executed)
Index Cond: (col1 < tbl1.col1)
-> Bitmap Heap Scan on tprt_4 (never executed)
Recheck Cond: (tbl1.col1 > col1)
-> Bitmap Index Scan on tprt4_idx (never executed)
Index Cond: (col1 < tbl1.col1)
-> Bitmap Heap Scan on tprt_5 (never executed)
Recheck Cond: (tbl1.col1 > col1)
-> Bitmap Index Scan on tprt5_idx (never executed)
Index Cond: (col1 < tbl1.col1)
-> Bitmap Heap Scan on tprt_6 (never executed)
Recheck Cond: (tbl1.col1 > col1)
-> Bitmap Index Scan on tprt6_idx (never executed)
Index Cond: (col1 < tbl1.col1)
Optimizer: Postgres query optimizer
(30 rows)
explain (analyze, costs off, summary off, timing off)
select * from tbl1 join tprt on tbl1.col1 = tprt.col1;
QUERY PLAN
--------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3) (actual rows=2 loops=1)
-> Nested Loop (actual rows=1 loops=1)
-> Seq Scan on tbl1 (actual rows=1 loops=1)
-> Append (actual rows=1 loops=1)
-> Bitmap Heap Scan on tprt_1 (never executed)
Recheck Cond: (col1 = tbl1.col1)
-> Bitmap Index Scan on tprt1_idx (never executed)
Index Cond: (col1 = tbl1.col1)
-> Bitmap Heap Scan on tprt_2 (actual rows=1 loops=1)
Recheck Cond: (col1 = tbl1.col1)
-> Bitmap Index Scan on tprt2_idx (actual rows=1 loops=1)
Index Cond: (col1 = tbl1.col1)
-> Bitmap Heap Scan on tprt_3 (never executed)
Recheck Cond: (col1 = tbl1.col1)
-> Bitmap Index Scan on tprt3_idx (never executed)
Index Cond: (col1 = tbl1.col1)
-> Bitmap Heap Scan on tprt_4 (never executed)
Recheck Cond: (col1 = tbl1.col1)
-> Bitmap Index Scan on tprt4_idx (never executed)
Index Cond: (col1 = tbl1.col1)
-> Bitmap Heap Scan on tprt_5 (never executed)
Recheck Cond: (col1 = tbl1.col1)
-> Bitmap Index Scan on tprt5_idx (never executed)
Index Cond: (col1 = tbl1.col1)
-> Bitmap Heap Scan on tprt_6 (never executed)
Recheck Cond: (col1 = tbl1.col1)
-> Bitmap Index Scan on tprt6_idx (never executed)
Index Cond: (col1 = tbl1.col1)
Optimizer: Postgres query optimizer
(29 rows)
select tbl1.col1, tprt.col1 from tbl1
inner join tprt on tbl1.col1 > tprt.col1
order by tbl1.col1, tprt.col1;
col1 | col1
------+------
501 | 10
501 | 20
505 | 10
505 | 20
505 | 501
505 | 502
(6 rows)
select tbl1.col1, tprt.col1 from tbl1
inner join tprt on tbl1.col1 = tprt.col1
order by tbl1.col1, tprt.col1;
col1 | col1
------+------
501 | 501
505 | 505
(2 rows)
-- Multiple partitions
insert into tbl1 values (1001), (1010), (1011);
explain (analyze, costs off, summary off, timing off)
select * from tbl1 inner join tprt on tbl1.col1 > tprt.col1;
QUERY PLAN
---------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3) (actual rows=23 loops=1)
-> Nested Loop (actual rows=10 loops=1)
-> Broadcast Motion 3:3 (slice2; segments: 3) (actual rows=5 loops=1)
-> Seq Scan on tbl1 (actual rows=3 loops=1)
-> Append (actual rows=2 loops=5)
-> Bitmap Heap Scan on tprt_1 (actual rows=1 loops=5)
Recheck Cond: (tbl1.col1 > col1)
-> Bitmap Index Scan on tprt1_idx (actual rows=1 loops=5)
Index Cond: (col1 < tbl1.col1)
-> Bitmap Heap Scan on tprt_2 (actual rows=2 loops=4)
Recheck Cond: (tbl1.col1 > col1)
-> Bitmap Index Scan on tprt2_idx (actual rows=2 loops=4)
Index Cond: (col1 < tbl1.col1)
-> Bitmap Heap Scan on tprt_3 (actual rows=1 loops=2)
Recheck Cond: (tbl1.col1 > col1)
-> Bitmap Index Scan on tprt3_idx (actual rows=1 loops=2)
Index Cond: (col1 < tbl1.col1)
-> Bitmap Heap Scan on tprt_4 (never executed)
Recheck Cond: (tbl1.col1 > col1)
-> Bitmap Index Scan on tprt4_idx (never executed)
Index Cond: (col1 < tbl1.col1)
-> Bitmap Heap Scan on tprt_5 (never executed)
Recheck Cond: (tbl1.col1 > col1)
-> Bitmap Index Scan on tprt5_idx (never executed)
Index Cond: (col1 < tbl1.col1)
-> Bitmap Heap Scan on tprt_6 (never executed)
Recheck Cond: (tbl1.col1 > col1)
-> Bitmap Index Scan on tprt6_idx (never executed)
Index Cond: (col1 < tbl1.col1)
Optimizer: Postgres query optimizer
(30 rows)
explain (analyze, costs off, summary off, timing off)
select * from tbl1 inner join tprt on tbl1.col1 = tprt.col1;
QUERY PLAN
--------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3) (actual rows=3 loops=1)
-> Nested Loop (actual rows=2 loops=1)
-> Seq Scan on tbl1 (actual rows=3 loops=1)
-> Append (actual rows=1 loops=3)
-> Bitmap Heap Scan on tprt_1 (never executed)
Recheck Cond: (col1 = tbl1.col1)
-> Bitmap Index Scan on tprt1_idx (never executed)
Index Cond: (col1 = tbl1.col1)
-> Bitmap Heap Scan on tprt_2 (actual rows=1 loops=1)
Recheck Cond: (col1 = tbl1.col1)
-> Bitmap Index Scan on tprt2_idx (actual rows=1 loops=1)
Index Cond: (col1 = tbl1.col1)
-> Bitmap Heap Scan on tprt_3 (actual rows=0 loops=2)
Recheck Cond: (col1 = tbl1.col1)
-> Bitmap Index Scan on tprt3_idx (actual rows=0 loops=2)
Index Cond: (col1 = tbl1.col1)
-> Bitmap Heap Scan on tprt_4 (never executed)
Recheck Cond: (col1 = tbl1.col1)
-> Bitmap Index Scan on tprt4_idx (never executed)
Index Cond: (col1 = tbl1.col1)
-> Bitmap Heap Scan on tprt_5 (never executed)
Recheck Cond: (col1 = tbl1.col1)
-> Bitmap Index Scan on tprt5_idx (never executed)
Index Cond: (col1 = tbl1.col1)
-> Bitmap Heap Scan on tprt_6 (never executed)
Recheck Cond: (col1 = tbl1.col1)
-> Bitmap Index Scan on tprt6_idx (never executed)
Index Cond: (col1 = tbl1.col1)
Optimizer: Postgres query optimizer
(29 rows)
select tbl1.col1, tprt.col1 from tbl1
inner join tprt on tbl1.col1 > tprt.col1
order by tbl1.col1, tprt.col1;
col1 | col1
------+------
501 | 10
501 | 20
505 | 10
505 | 20
505 | 501
505 | 502
1001 | 10
1001 | 20
1001 | 501
1001 | 502
1001 | 505
1010 | 10
1010 | 20
1010 | 501
1010 | 502
1010 | 505
1010 | 1001
1011 | 10
1011 | 20
1011 | 501
1011 | 502
1011 | 505
1011 | 1001
(23 rows)
select tbl1.col1, tprt.col1 from tbl1
inner join tprt on tbl1.col1 = tprt.col1
order by tbl1.col1, tprt.col1;
col1 | col1
------+------
501 | 501
505 | 505
1001 | 1001
(3 rows)
-- Last partition
delete from tbl1;
insert into tbl1 values (4400);
explain (analyze, costs off, summary off, timing off)
select * from tbl1 join tprt on tbl1.col1 < tprt.col1;
QUERY PLAN
---------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3) (actual rows=1 loops=1)
-> Nested Loop (actual rows=1 loops=1)
-> Broadcast Motion 3:3 (slice2; segments: 3) (actual rows=1 loops=1)
-> Seq Scan on tbl1 (actual rows=1 loops=1)
-> Append (actual rows=1 loops=1)
-> Bitmap Heap Scan on tprt_1 (never executed)
Recheck Cond: (tbl1.col1 < col1)
-> Bitmap Index Scan on tprt1_idx (never executed)
Index Cond: (col1 > tbl1.col1)
-> Bitmap Heap Scan on tprt_2 (never executed)
Recheck Cond: (tbl1.col1 < col1)
-> Bitmap Index Scan on tprt2_idx (never executed)
Index Cond: (col1 > tbl1.col1)
-> Bitmap Heap Scan on tprt_3 (never executed)
Recheck Cond: (tbl1.col1 < col1)
-> Bitmap Index Scan on tprt3_idx (never executed)
Index Cond: (col1 > tbl1.col1)
-> Bitmap Heap Scan on tprt_4 (never executed)
Recheck Cond: (tbl1.col1 < col1)
-> Bitmap Index Scan on tprt4_idx (never executed)
Index Cond: (col1 > tbl1.col1)
-> Bitmap Heap Scan on tprt_5 (never executed)
Recheck Cond: (tbl1.col1 < col1)
-> Bitmap Index Scan on tprt5_idx (never executed)
Index Cond: (col1 > tbl1.col1)
-> Bitmap Heap Scan on tprt_6 (actual rows=1 loops=1)
Recheck Cond: (tbl1.col1 < col1)
-> Bitmap Index Scan on tprt6_idx (actual rows=1 loops=1)
Index Cond: (col1 > tbl1.col1)
Optimizer: Postgres query optimizer
(30 rows)
select tbl1.col1, tprt.col1 from tbl1
inner join tprt on tbl1.col1 < tprt.col1
order by tbl1.col1, tprt.col1;
col1 | col1
------+------
4400 | 4500
(1 row)
reset enable_seqscan;
-- No matching partition
delete from tbl1;
insert into tbl1 values (10000);
explain (analyze, costs off, summary off, timing off)
select * from tbl1 join tprt on tbl1.col1 = tprt.col1;
QUERY PLAN
-------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3) (actual rows=0 loops=1)
-> Nested Loop (actual rows=0 loops=1)
-> Seq Scan on tbl1 (actual rows=1 loops=1)
-> Append (actual rows=0 loops=1)
-> Bitmap Heap Scan on tprt_1 (never executed)
Recheck Cond: (col1 = tbl1.col1)
-> Bitmap Index Scan on tprt1_idx (never executed)
Index Cond: (col1 = tbl1.col1)
-> Bitmap Heap Scan on tprt_2 (never executed)
Recheck Cond: (col1 = tbl1.col1)
-> Bitmap Index Scan on tprt2_idx (never executed)
Index Cond: (col1 = tbl1.col1)
-> Bitmap Heap Scan on tprt_3 (never executed)
Recheck Cond: (col1 = tbl1.col1)
-> Bitmap Index Scan on tprt3_idx (never executed)
Index Cond: (col1 = tbl1.col1)
-> Bitmap Heap Scan on tprt_4 (never executed)
Recheck Cond: (col1 = tbl1.col1)
-> Bitmap Index Scan on tprt4_idx (never executed)
Index Cond: (col1 = tbl1.col1)
-> Bitmap Heap Scan on tprt_5 (never executed)
Recheck Cond: (col1 = tbl1.col1)
-> Bitmap Index Scan on tprt5_idx (never executed)
Index Cond: (col1 = tbl1.col1)
-> Bitmap Heap Scan on tprt_6 (never executed)
Recheck Cond: (col1 = tbl1.col1)
-> Bitmap Index Scan on tprt6_idx (never executed)
Index Cond: (col1 = tbl1.col1)
Optimizer: Postgres query optimizer
(29 rows)
select tbl1.col1, tprt.col1 from tbl1
inner join tprt on tbl1.col1 = tprt.col1
order by tbl1.col1, tprt.col1;
col1 | col1
------+------
(0 rows)
drop table tbl1, tprt;
-- Test with columns defined in varying orders between each level
create table part_abc (a int not null, b int not null, c int not null) partition by list (a);
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 part_bac (b int not null, a int not null, c int not null) partition by list (b);
NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'b' 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 part_cab (c int not null, a int not null, b int not null) partition by list (c);
NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'c' 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 part_abc_p1 (a int not null, b int not null, c int not null);
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.
-- GPDB: the distribution keys must be the same in all parts of partition
-- hierarchy.
alter table part_bac set distributed by (a);
alter table part_cab set distributed by (a);
alter table part_abc attach partition part_bac for values in(1);
alter table part_bac attach partition part_cab for values in(2);
alter table part_cab attach partition part_abc_p1 for values in(3);
prepare part_abc_q1 (int, int, int) as
select * from part_abc where a = $1 and b = $2 and c = $3;
-- Single partition should be scanned.
explain (analyze, costs off, summary off, timing off) execute part_abc_q1 (1, 2, 3);
QUERY PLAN
------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3) (actual rows=0 loops=1)
-> Seq Scan on part_abc_p1 part_abc (actual rows=0 loops=1)
Filter: ((a = $1) AND (b = $2) AND (c = $3))
Optimizer: Postgres query optimizer
(4 rows)
deallocate part_abc_q1;
drop table part_abc;
-- Ensure that an Append node properly handles a sub-partitioned table
-- matching without any of its leaf partitions matching the clause.
create table listp (a int, b int) partition by list (a);
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 listp_1 partition of listp for values in(1) partition by list (b);
NOTICE: table has parent, setting distribution columns to match parent table
create table listp_1_1 partition of listp_1 for values in(1);
NOTICE: table has parent, setting distribution columns to match parent table
create table listp_2 partition of listp for values in(2) partition by list (b);
NOTICE: table has parent, setting distribution columns to match parent table
create table listp_2_1 partition of listp_2 for values in(2);
NOTICE: table has parent, setting distribution columns to match parent table
select * from listp where b = 1;
a | b
---+---
(0 rows)
-- Ensure that an Append node properly can handle selection of all first level
-- partitions before finally detecting the correct set of 2nd level partitions
-- which match the given parameter.
prepare q1 (int,int) as select * from listp where b in ($1,$2);
explain (analyze, costs off, summary off, timing off) execute q1 (1,1);
QUERY PLAN
-------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3) (actual rows=0 loops=1)
-> Append (actual rows=0 loops=1)
Subplans Removed: 1
-> Seq Scan on listp_1_1 listp_1 (actual rows=0 loops=1)
Filter: (b = ANY (ARRAY[$1, $2]))
Optimizer: Postgres query optimizer
(6 rows)
explain (analyze, costs off, summary off, timing off) execute q1 (2,2);
QUERY PLAN
-------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3) (actual rows=0 loops=1)
-> Append (actual rows=0 loops=1)
Subplans Removed: 1
-> Seq Scan on listp_2_1 listp_1 (actual rows=0 loops=1)
Filter: (b = ANY (ARRAY[$1, $2]))
Optimizer: Postgres query optimizer
(6 rows)
-- Try with no matching partitions.
explain (analyze, costs off, summary off, timing off) execute q1 (0,0);
QUERY PLAN
------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3) (actual rows=0 loops=1)
-> Append (actual rows=0 loops=1)
Subplans Removed: 2
Optimizer: Postgres query optimizer
(4 rows)
deallocate q1;
-- Test more complex cases where a not-equal condition further eliminates partitions.
prepare q1 (int,int,int,int) as select * from listp where b in($1,$2) and $3 <> b and $4 <> b;
-- Both partitions allowed by IN clause, but one disallowed by <> clause
explain (analyze, costs off, summary off, timing off) execute q1 (1,2,2,0);
QUERY PLAN
-------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3) (actual rows=0 loops=1)
-> Append (actual rows=0 loops=1)
Subplans Removed: 1
-> Seq Scan on listp_1_1 listp_1 (actual rows=0 loops=1)
Filter: ((b = ANY (ARRAY[$1, $2])) AND ($3 <> b) AND ($4 <> b))
Optimizer: Postgres query optimizer
(6 rows)
-- Both partitions allowed by IN clause, then both excluded again by <> clauses.
explain (analyze, costs off, summary off, timing off) execute q1 (1,2,2,1);
QUERY PLAN
------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3) (actual rows=0 loops=1)
-> Append (actual rows=0 loops=1)
Subplans Removed: 2
Optimizer: Postgres query optimizer
(4 rows)
-- Ensure Params that evaluate to NULL properly prune away all partitions
explain (analyze, costs off, summary off, timing off)
select * from listp where a = (select null::int);
QUERY PLAN
------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3) (actual rows=0 loops=1)
InitPlan 1 (returns $0) (slice2)
-> Result (actual rows=1 loops=1)
-> Append (actual rows=0 loops=1)
-> Seq Scan on listp_1_1 listp_1 (never executed)
Filter: (a = $0)
-> Seq Scan on listp_2_1 listp_2 (never executed)
Filter: (a = $0)
Optimizer: Postgres query optimizer
(9 rows)
drop table listp;
--
-- check that stable query clauses are only used in run-time pruning
-- because a is the distributed key by default in stable_qual_pruning,
-- to get consistent results, we make the table distributed randomly.
--
create table stable_qual_pruning (a timestamp) distributed randomly partition by range (a);
create table stable_qual_pruning1 partition of stable_qual_pruning
for values from ('2000-01-01') to ('2000-02-01');
NOTICE: table has parent, setting distribution columns to match parent table
create table stable_qual_pruning2 partition of stable_qual_pruning
for values from ('2000-02-01') to ('2000-03-01');
NOTICE: table has parent, setting distribution columns to match parent table
create table stable_qual_pruning3 partition of stable_qual_pruning
for values from ('3000-02-01') to ('3000-03-01');
NOTICE: table has parent, setting distribution columns to match parent table
-- comparison against a stable value requires run-time pruning
explain (analyze, costs off, summary off, timing off)
select * from stable_qual_pruning where a < localtimestamp;
QUERY PLAN
--------------------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3) (actual rows=0 loops=1)
-> Append (actual rows=0 loops=1)
-> Seq Scan on stable_qual_pruning1 stable_qual_pruning_1 (actual rows=0 loops=1)
Filter: (a < 'Sun Nov 30 04:01:02.765508 2025'::timestamp without time zone)
-> Seq Scan on stable_qual_pruning2 stable_qual_pruning_2 (actual rows=0 loops=1)
Filter: (a < 'Sun Nov 30 04:01:02.765508 2025'::timestamp without time zone)
Optimizer: Postgres query optimizer
(7 rows)
-- timestamp < timestamptz comparison is only stable, not immutable
explain (analyze, costs off, summary off, timing off)
select * from stable_qual_pruning where a < '2000-02-01'::timestamptz;
QUERY PLAN
--------------------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3) (actual rows=0 loops=1)
-> Append (actual rows=0 loops=1)
Subplans Removed: 2
-> Seq Scan on stable_qual_pruning1 stable_qual_pruning_1 (actual rows=0 loops=1)
Filter: (a < 'Tue Feb 01 00:00:00 2000 PST'::timestamp with time zone)
Optimizer: Postgres query optimizer
(6 rows)
-- check ScalarArrayOp cases
explain (analyze, costs off, summary off, timing off)
select * from stable_qual_pruning
where a = any(array['2010-02-01', '2020-01-01']::timestamp[]);
QUERY PLAN
-------------------------------------
Result (actual rows=0 loops=1)
One-Time Filter: false
Optimizer: Postgres query optimizer
(3 rows)
explain (analyze, costs off, summary off, timing off)
select * from stable_qual_pruning
where a = any(array['2000-02-01', '2010-01-01']::timestamp[]);
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3) (actual rows=0 loops=1)
-> Seq Scan on stable_qual_pruning2 stable_qual_pruning (actual rows=0 loops=1)
Filter: (a = ANY ('{"Tue Feb 01 00:00:00 2000","Fri Jan 01 00:00:00 2010"}'::timestamp without time zone[]))
Optimizer: Postgres query optimizer
(4 rows)
explain (analyze, costs off, summary off, timing off)
select * from stable_qual_pruning
where a = any(array['2000-02-01', localtimestamp]::timestamp[]);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3) (actual rows=0 loops=1)
-> Seq Scan on stable_qual_pruning2 stable_qual_pruning (actual rows=0 loops=1)
Filter: (a = ANY ('{"Tue Feb 01 00:00:00 2000","Sun Nov 30 04:01:02.771151 2025"}'::timestamp without time zone[]))
Optimizer: Postgres query optimizer
(4 rows)
explain (analyze, costs off, summary off, timing off)
select * from stable_qual_pruning
where a = any(array['2010-02-01', '2020-01-01']::timestamptz[]);
QUERY PLAN
------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3) (actual rows=0 loops=1)
-> Append (actual rows=0 loops=1)
Subplans Removed: 3
Optimizer: Postgres query optimizer
(4 rows)
explain (analyze, costs off, summary off, timing off)
select * from stable_qual_pruning
where a = any(array['2000-02-01', '2010-01-01']::timestamptz[]);
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3) (actual rows=0 loops=1)
-> Append (actual rows=0 loops=1)
Subplans Removed: 2
-> Seq Scan on stable_qual_pruning2 stable_qual_pruning_1 (actual rows=0 loops=1)
Filter: (a = ANY ('{"Tue Feb 01 00:00:00 2000 PST","Fri Jan 01 00:00:00 2010 PST"}'::timestamp with time zone[]))
Optimizer: Postgres query optimizer
(6 rows)
explain (analyze, costs off, summary off, timing off)
select * from stable_qual_pruning
where a = any(null::timestamptz[]);
QUERY PLAN
--------------------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3) (actual rows=0 loops=1)
-> Append (actual rows=0 loops=1)
-> Seq Scan on stable_qual_pruning1 stable_qual_pruning_1 (actual rows=0 loops=1)
Filter: (a = ANY (NULL::timestamp with time zone[]))
-> Seq Scan on stable_qual_pruning2 stable_qual_pruning_2 (actual rows=0 loops=1)
Filter: (a = ANY (NULL::timestamp with time zone[]))
-> Seq Scan on stable_qual_pruning3 stable_qual_pruning_3 (actual rows=0 loops=1)
Filter: (a = ANY (NULL::timestamp with time zone[]))
Optimizer: Postgres query optimizer
(9 rows)
drop table stable_qual_pruning;
--
-- Check that pruning with composite range partitioning works correctly when
-- it must ignore clauses for trailing keys once it has seen a clause with
-- non-inclusive operator for an earlier key
--
create table mc3p (a int, b int, c int) partition by range (a, abs(b), c);
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 mc3p0 partition of mc3p
for values from (0, 0, 0) to (0, maxvalue, maxvalue);
NOTICE: table has parent, setting distribution columns to match parent table
create table mc3p1 partition of mc3p
for values from (1, 1, 1) to (2, minvalue, minvalue);
NOTICE: table has parent, setting distribution columns to match parent table
create table mc3p2 partition of mc3p
for values from (2, minvalue, minvalue) to (3, maxvalue, maxvalue);
NOTICE: table has parent, setting distribution columns to match parent table
insert into mc3p values (0, 1, 1), (1, 1, 1), (2, 1, 1);
explain (analyze, costs off, summary off, timing off)
select * from mc3p where a < 3 and abs(b) = 1;
QUERY PLAN
------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3) (actual rows=3 loops=1)
-> Append (actual rows=2 loops=1)
-> Seq Scan on mc3p0 mc3p_1 (actual rows=1 loops=1)
Filter: ((a < 3) AND (abs(b) = 1))
-> Seq Scan on mc3p1 mc3p_2 (actual rows=1 loops=1)
Filter: ((a < 3) AND (abs(b) = 1))
-> Seq Scan on mc3p2 mc3p_3 (actual rows=1 loops=1)
Filter: ((a < 3) AND (abs(b) = 1))
Optimizer: Postgres query optimizer
(9 rows)
--
-- Check that pruning with composite range partitioning works correctly when
-- a combination of runtime parameters is specified, not all of whose values
-- are available at the same time
--
prepare ps1 as
select * from mc3p where a = $1 and abs(b) < (select 3);
explain (analyze, costs off, summary off, timing off)
execute ps1(1);
QUERY PLAN
------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3) (actual rows=1 loops=1)
InitPlan 1 (returns $0) (slice2)
-> Result (actual rows=1 loops=1)
-> Append (actual rows=1 loops=1)
Subplans Removed: 2
-> Seq Scan on mc3p1 mc3p_1 (actual rows=1 loops=1)
Filter: ((a = $1) AND (abs(b) < $0))
Optimizer: Postgres query optimizer
(8 rows)
deallocate ps1;
prepare ps2 as
select * from mc3p where a <= $1 and abs(b) < (select 3);
explain (analyze, costs off, summary off, timing off)
execute ps2(1);
QUERY PLAN
------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3) (actual rows=2 loops=1)
InitPlan 1 (returns $0) (slice2)
-> Result (actual rows=1 loops=1)
-> Append (actual rows=2 loops=1)
Subplans Removed: 1
-> Seq Scan on mc3p0 mc3p_1 (actual rows=1 loops=1)
Filter: ((a <= $1) AND (abs(b) < $0))
-> Seq Scan on mc3p1 mc3p_2 (actual rows=1 loops=1)
Filter: ((a <= $1) AND (abs(b) < $0))
Optimizer: Postgres query optimizer
(10 rows)
deallocate ps2;
drop table mc3p;
-- Ensure runtime pruning works with initplans params with boolean types
create table boolvalues (value bool not null);
NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'value' 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 boolvalues values('t'),('f');
create table boolp (a bool) partition by list (a);
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 boolp_t partition of boolp for values in('t');
NOTICE: table has parent, setting distribution columns to match parent table
create table boolp_f partition of boolp for values in('f');
NOTICE: table has parent, setting distribution columns to match parent table
explain (analyze, costs off, summary off, timing off)
select * from boolp where a = (select value from boolvalues where value);
QUERY PLAN
--------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3) (actual rows=0 loops=1)
InitPlan 1 (returns $0) (slice2)
-> Gather Motion 3:1 (slice3; segments: 3) (actual rows=1 loops=1)
-> Seq Scan on boolvalues (actual rows=1 loops=1)
Filter: value
Rows Removed by Filter: 1
-> Append (actual rows=0 loops=1)
-> Seq Scan on boolp_f boolp_1 (never executed)
Filter: (a = $0)
-> Seq Scan on boolp_t boolp_2 (actual rows=0 loops=1)
Filter: (a = $0)
Optimizer: Postgres query optimizer
(12 rows)
explain (analyze, costs off, summary off, timing off)
select * from boolp where a = (select value from boolvalues where not value);
QUERY PLAN
--------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3) (actual rows=0 loops=1)
InitPlan 1 (returns $0) (slice2)
-> Gather Motion 3:1 (slice3; segments: 3) (actual rows=1 loops=1)
-> Seq Scan on boolvalues (actual rows=1 loops=1)
Filter: (NOT value)
Rows Removed by Filter: 1
-> Append (actual rows=0 loops=1)
-> Seq Scan on boolp_f boolp_1 (actual rows=0 loops=1)
Filter: (a = $0)
-> Seq Scan on boolp_t boolp_2 (never executed)
Filter: (a = $0)
Optimizer: Postgres query optimizer
(12 rows)
drop table boolp;
--
-- Test run-time pruning of MergeAppend subnodes
--
set enable_seqscan = off;
set enable_sort = off;
create table ma_test (a int, b int) partition by range (a);
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 ma_test_p1 partition of ma_test for values from (0) to (10);
NOTICE: table has parent, setting distribution columns to match parent table
create table ma_test_p2 partition of ma_test for values from (10) to (20);
NOTICE: table has parent, setting distribution columns to match parent table
create table ma_test_p3 partition of ma_test for values from (20) to (30);
NOTICE: table has parent, setting distribution columns to match parent table
insert into ma_test select x,x from generate_series(0,29) t(x);
create index on ma_test (b);
analyze ma_test;
prepare mt_q1 (int) as select a from ma_test where a >= $1 and a % 10 = 5 order by b;
explain (analyze, costs off, summary off, timing off) execute mt_q1(15);
QUERY PLAN
-----------------------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3) (actual rows=2 loops=1)
Merge Key: ma_test.b
-> Merge Append (actual rows=1 loops=1)
Sort Key: ma_test.b
Subplans Removed: 1
-> Index Scan using ma_test_p2_b_idx on ma_test_p2 ma_test_1 (actual rows=1 loops=1)
Filter: ((a >= $1) AND ((a % 10) = 5))
Rows Removed by Filter: 1
-> Index Scan using ma_test_p3_b_idx on ma_test_p3 ma_test_2 (actual rows=1 loops=1)
Filter: ((a >= $1) AND ((a % 10) = 5))
Rows Removed by Filter: 2
Optimizer: Postgres query optimizer
(12 rows)
execute mt_q1(15);
a
----
15
25
(2 rows)
explain (analyze, costs off, summary off, timing off) execute mt_q1(25);
QUERY PLAN
-----------------------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3) (actual rows=1 loops=1)
Merge Key: ma_test.b
-> Merge Append (actual rows=1 loops=1)
Sort Key: ma_test.b
Subplans Removed: 2
-> Index Scan using ma_test_p3_b_idx on ma_test_p3 ma_test_1 (actual rows=1 loops=1)
Filter: ((a >= $1) AND ((a % 10) = 5))
Rows Removed by Filter: 2
Optimizer: Postgres query optimizer
(9 rows)
execute mt_q1(25);
a
----
25
(1 row)
-- Ensure MergeAppend behaves correctly when no subplans match
explain (analyze, costs off, summary off, timing off) execute mt_q1(35);
QUERY PLAN
------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3) (actual rows=0 loops=1)
Merge Key: ma_test.b
-> Merge Append (actual rows=0 loops=1)
Sort Key: ma_test.b
Subplans Removed: 3
Optimizer: Postgres query optimizer
(6 rows)
execute mt_q1(35);
a
---
(0 rows)
deallocate mt_q1;
prepare mt_q2 (int) as select * from ma_test where a >= $1 order by b limit 1;
-- Ensure output list looks sane when the MergeAppend has no subplans.
explain (analyze, verbose, costs off, summary off, timing off) execute mt_q2 (35);
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (actual rows=0 loops=1)
Output: ma_test.a, ma_test.b
-> Gather Motion 3:1 (slice1; segments: 3) (actual rows=0 loops=1)
Output: ma_test.a, ma_test.b
Merge Key: ma_test.b
-> Limit (actual rows=0 loops=1)
Output: ma_test.a, ma_test.b
-> Merge Append (actual rows=0 loops=1)
Sort Key: ma_test.b
Subplans Removed: 3
Settings: plan_cache_mode = 'force_generic_plan', enable_indexonlyscan = 'off', enable_hashjoin = 'off', enable_mergejoin = 'off', enable_seqscan = 'off', enable_sort = 'off'
Optimizer: Postgres query optimizer
(12 rows)
deallocate mt_q2;
-- ensure initplan params properly prune partitions
explain (analyze, costs off, summary off, timing off) select * from ma_test where a >= (select min(b) from ma_test_p2) order by b;
QUERY PLAN
-----------------------------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3) (actual rows=20 loops=1)
Merge Key: ma_test.b
InitPlan 2 (returns $1) (slice2)
-> Result (actual rows=1 loops=1)
InitPlan 1 (returns $0) (slice3)
-> Limit (actual rows=1 loops=1)
-> Gather Motion 3:1 (slice4; segments: 3) (actual rows=1 loops=1)
Merge Key: ma_test_p2.b
-> Index Scan using ma_test_p2_b_idx on ma_test_p2 (actual rows=5 loops=1)
Index Cond: (b IS NOT NULL)
-> Merge Append (actual rows=8 loops=1)
Sort Key: ma_test.b
-> Index Scan using ma_test_p1_b_idx on ma_test_p1 ma_test_1 (never executed)
Filter: (a >= $1)
-> Index Scan using ma_test_p2_b_idx on ma_test_p2 ma_test_2 (actual rows=5 loops=1)
Filter: (a >= $1)
-> Index Scan using ma_test_p3_b_idx on ma_test_p3 ma_test_3 (actual rows=4 loops=1)
Filter: (a >= $1)
Optimizer: Postgres query optimizer
(19 rows)
reset enable_seqscan;
reset enable_sort;
drop table ma_test;
reset enable_indexonlyscan;
--
-- check that pruning works properly when the partition key is of a
-- pseudotype
--
-- array type list partition key
create table pp_arrpart (a int[]) partition by list (a);
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 pp_arrpart1 partition of pp_arrpart for values in ('{1}');
NOTICE: table has parent, setting distribution columns to match parent table
create table pp_arrpart2 partition of pp_arrpart for values in ('{2, 3}', '{4, 5}');
NOTICE: table has parent, setting distribution columns to match parent table
explain (costs off) select * from pp_arrpart where a = '{1}';
QUERY PLAN
------------------------------------------
Gather Motion 1:1 (slice1; segments: 1)
-> Seq Scan on pp_arrpart1 pp_arrpart
Filter: (a = '{1}'::integer[])
Optimizer: Postgres query optimizer
(4 rows)
explain (costs off) select * from pp_arrpart where a = '{1, 2}';
QUERY PLAN
-------------------------------------
Result
One-Time Filter: false
Optimizer: Postgres query optimizer
(3 rows)
explain (costs off) select * from pp_arrpart where a in ('{4, 5}', '{1}');
QUERY PLAN
----------------------------------------------------------------------------
Gather Motion 2:1 (slice1; segments: 2)
-> Append
-> Seq Scan on pp_arrpart1 pp_arrpart_1
Filter: ((a = '{4,5}'::integer[]) OR (a = '{1}'::integer[]))
-> Seq Scan on pp_arrpart2 pp_arrpart_2
Filter: ((a = '{4,5}'::integer[]) OR (a = '{1}'::integer[]))
Optimizer: Postgres query optimizer
(7 rows)
explain (costs off) update pp_arrpart set a = a where a = '{1}';
QUERY PLAN
--------------------------------------------
Update on pp_arrpart
Update on pp_arrpart1 pp_arrpart_1
-> Seq Scan on pp_arrpart1 pp_arrpart_1
Filter: (a = '{1}'::integer[])
Optimizer: Postgres query optimizer
(5 rows)
explain (costs off) delete from pp_arrpart where a = '{1}';
QUERY PLAN
--------------------------------------------
Delete on pp_arrpart
Delete on pp_arrpart1 pp_arrpart_1
-> Seq Scan on pp_arrpart1 pp_arrpart_1
Filter: (a = '{1}'::integer[])
Optimizer: Postgres query optimizer
(5 rows)
drop table pp_arrpart;
-- array type hash partition key
create table pph_arrpart (a int[]) partition by hash (a);
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 pph_arrpart1 partition of pph_arrpart for values with (modulus 2, remainder 0);
NOTICE: table has parent, setting distribution columns to match parent table
create table pph_arrpart2 partition of pph_arrpart for values with (modulus 2, remainder 1);
NOTICE: table has parent, setting distribution columns to match parent table
insert into pph_arrpart values ('{1}'), ('{1, 2}'), ('{4, 5}');
select tableoid::regclass, * from pph_arrpart order by 1;
tableoid | a
--------------+-------
pph_arrpart1 | {1,2}
pph_arrpart1 | {4,5}
pph_arrpart2 | {1}
(3 rows)
explain (costs off) select * from pph_arrpart where a = '{1}';
QUERY PLAN
--------------------------------------------
Gather Motion 1:1 (slice1; segments: 1)
-> Seq Scan on pph_arrpart2 pph_arrpart
Filter: (a = '{1}'::integer[])
Optimizer: Postgres query optimizer
(4 rows)
explain (costs off) select * from pph_arrpart where a = '{1, 2}';
QUERY PLAN
--------------------------------------------
Gather Motion 1:1 (slice1; segments: 1)
-> Seq Scan on pph_arrpart1 pph_arrpart
Filter: (a = '{1,2}'::integer[])
Optimizer: Postgres query optimizer
(4 rows)
explain (costs off) select * from pph_arrpart where a in ('{4, 5}', '{1}');
QUERY PLAN
----------------------------------------------------------------------------
Gather Motion 2:1 (slice1; segments: 2)
-> Append
-> Seq Scan on pph_arrpart1 pph_arrpart_1
Filter: ((a = '{4,5}'::integer[]) OR (a = '{1}'::integer[]))
-> Seq Scan on pph_arrpart2 pph_arrpart_2
Filter: ((a = '{4,5}'::integer[]) OR (a = '{1}'::integer[]))
Optimizer: Postgres query optimizer
(7 rows)
drop table pph_arrpart;
-- enum type list partition key
create type pp_colors as enum ('green', 'blue', 'black');
create table pp_enumpart (a pp_colors) partition by list (a);
create table pp_enumpart_green partition of pp_enumpart for values in ('green');
NOTICE: table has parent, setting distribution columns to match parent table
create table pp_enumpart_blue partition of pp_enumpart for values in ('blue');
NOTICE: table has parent, setting distribution columns to match parent table
explain (costs off) select * from pp_enumpart where a = 'blue';
QUERY PLAN
------------------------------------------------
Gather Motion 1:1 (slice1; segments: 1)
-> Seq Scan on pp_enumpart_blue pp_enumpart
Filter: (a = 'blue'::pp_colors)
Optimizer: Postgres query optimizer
(4 rows)
explain (costs off) select * from pp_enumpart where a = 'black';
QUERY PLAN
-------------------------------------
Result
One-Time Filter: false
Optimizer: Postgres query optimizer
(3 rows)
drop table pp_enumpart;
drop type pp_colors;
-- record type as partition key
create type pp_rectype as (a int, b int);
create table pp_recpart (a pp_rectype) partition by list (a);
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 pp_recpart_11 partition of pp_recpart for values in ('(1,1)');
NOTICE: table has parent, setting distribution columns to match parent table
create table pp_recpart_23 partition of pp_recpart for values in ('(2,3)');
NOTICE: table has parent, setting distribution columns to match parent table
explain (costs off) select * from pp_recpart where a = '(1,1)'::pp_rectype;
QUERY PLAN
--------------------------------------------
Gather Motion 1:1 (slice1; segments: 1)
-> Seq Scan on pp_recpart_11 pp_recpart
Filter: (a = '(1,1)'::pp_rectype)
Optimizer: Postgres query optimizer
(4 rows)
explain (costs off) select * from pp_recpart where a = '(1,2)'::pp_rectype;
QUERY PLAN
-------------------------------------
Result
One-Time Filter: false
Optimizer: Postgres query optimizer
(3 rows)
drop table pp_recpart;
drop type pp_rectype;
-- range type partition key
create table pp_intrangepart (a int4range) partition by list (a);
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 pp_intrangepart12 partition of pp_intrangepart for values in ('[1,2]');
NOTICE: table has parent, setting distribution columns to match parent table
create table pp_intrangepart2inf partition of pp_intrangepart for values in ('[2,)');
NOTICE: table has parent, setting distribution columns to match parent table
explain (costs off) select * from pp_intrangepart where a = '[1,2]'::int4range;
QUERY PLAN
-----------------------------------------------------
Gather Motion 1:1 (slice1; segments: 1)
-> Seq Scan on pp_intrangepart12 pp_intrangepart
Filter: (a = '[1,3)'::int4range)
Optimizer: Postgres query optimizer
(4 rows)
explain (costs off) select * from pp_intrangepart where a = '(1,2)'::int4range;
QUERY PLAN
-------------------------------------
Result
One-Time Filter: false
Optimizer: Postgres query optimizer
(3 rows)
drop table pp_intrangepart;
--
-- Ensure the enable_partition_prune GUC properly disables partition pruning.
--
create table pp_lp (a int, value int) partition by list (a);
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 pp_lp1 partition of pp_lp for values in(1);
NOTICE: table has parent, setting distribution columns to match parent table
create table pp_lp2 partition of pp_lp for values in(2);
NOTICE: table has parent, setting distribution columns to match parent table
explain (costs off) select * from pp_lp where a = 1;
QUERY PLAN
------------------------------------------
Gather Motion 1:1 (slice1; segments: 1)
-> Seq Scan on pp_lp1 pp_lp
Filter: (a = 1)
Optimizer: Postgres query optimizer
(4 rows)
explain (costs off) update pp_lp set value = 10 where a = 1;
QUERY PLAN
-------------------------------------
Update on pp_lp
Update on pp_lp1 pp_lp_1
-> Seq Scan on pp_lp1 pp_lp_1
Filter: (a = 1)
Optimizer: Postgres query optimizer
(5 rows)
explain (costs off) delete from pp_lp where a = 1;
QUERY PLAN
-------------------------------------
Delete on pp_lp
Delete on pp_lp1 pp_lp_1
-> Seq Scan on pp_lp1 pp_lp_1
Filter: (a = 1)
Optimizer: Postgres query optimizer
(5 rows)
set enable_partition_pruning = off;
set constraint_exclusion = 'partition'; -- this should not affect the result.
explain (costs off) select * from pp_lp where a = 1;
QUERY PLAN
------------------------------------------
Gather Motion 1:1 (slice1; segments: 1)
-> Append
-> Seq Scan on pp_lp1 pp_lp_1
Filter: (a = 1)
-> Seq Scan on pp_lp2 pp_lp_2
Filter: (a = 1)
Optimizer: Postgres query optimizer
(7 rows)
explain (costs off) update pp_lp set value = 10 where a = 1;
QUERY PLAN
----------------------------------------
Update on pp_lp
Update on pp_lp1 pp_lp_1
Update on pp_lp2 pp_lp_2
-> Append
-> Seq Scan on pp_lp1 pp_lp_1
Filter: (a = 1)
-> Seq Scan on pp_lp2 pp_lp_2
Filter: (a = 1)
Optimizer: Postgres query optimizer
(9 rows)
explain (costs off) delete from pp_lp where a = 1;
QUERY PLAN
----------------------------------------
Delete on pp_lp
Delete on pp_lp1 pp_lp_1
Delete on pp_lp2 pp_lp_2
-> Append
-> Seq Scan on pp_lp1 pp_lp_1
Filter: (a = 1)
-> Seq Scan on pp_lp2 pp_lp_2
Filter: (a = 1)
Optimizer: Postgres query optimizer
(9 rows)
set constraint_exclusion = 'off'; -- this should not affect the result.
explain (costs off) select * from pp_lp where a = 1;
QUERY PLAN
------------------------------------------
Gather Motion 1:1 (slice1; segments: 1)
-> Append
-> Seq Scan on pp_lp1 pp_lp_1
Filter: (a = 1)
-> Seq Scan on pp_lp2 pp_lp_2
Filter: (a = 1)
Optimizer: Postgres query optimizer
(7 rows)
explain (costs off) update pp_lp set value = 10 where a = 1;
QUERY PLAN
----------------------------------------
Update on pp_lp
Update on pp_lp1 pp_lp_1
Update on pp_lp2 pp_lp_2
-> Append
-> Seq Scan on pp_lp1 pp_lp_1
Filter: (a = 1)
-> Seq Scan on pp_lp2 pp_lp_2
Filter: (a = 1)
Optimizer: Postgres query optimizer
(9 rows)
explain (costs off) delete from pp_lp where a = 1;
QUERY PLAN
----------------------------------------
Delete on pp_lp
Delete on pp_lp1 pp_lp_1
Delete on pp_lp2 pp_lp_2
-> Append
-> Seq Scan on pp_lp1 pp_lp_1
Filter: (a = 1)
-> Seq Scan on pp_lp2 pp_lp_2
Filter: (a = 1)
Optimizer: Postgres query optimizer
(9 rows)
drop table pp_lp;
-- Ensure enable_partition_prune does not affect non-partitioned tables.
create table inh_lp (a int, value int);
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 inh_lp1 (a int, value int, check(a = 1)) inherits (inh_lp);
NOTICE: table has parent, setting distribution columns to match parent table
NOTICE: merging column "a" with inherited definition
NOTICE: merging column "value" with inherited definition
create table inh_lp2 (a int, value int, check(a = 2)) inherits (inh_lp);
NOTICE: table has parent, setting distribution columns to match parent table
NOTICE: merging column "a" with inherited definition
NOTICE: merging column "value" with inherited definition
set constraint_exclusion = 'partition';
-- inh_lp2 should be removed in the following 3 cases.
explain (costs off) select * from inh_lp where a = 1;
QUERY PLAN
------------------------------------------
Gather Motion 1:1 (slice1; segments: 1)
-> Append
-> Seq Scan on inh_lp inh_lp_1
Filter: (a = 1)
-> Seq Scan on inh_lp1 inh_lp_2
Filter: (a = 1)
Optimizer: Postgres query optimizer
(7 rows)
explain (costs off) update inh_lp set value = 10 where a = 1;
QUERY PLAN
------------------------------------------------
Update on inh_lp
Update on inh_lp inh_lp_1
Update on inh_lp1 inh_lp_2
-> Result
-> Append
-> Seq Scan on inh_lp inh_lp_1
Filter: (a = 1)
-> Seq Scan on inh_lp1 inh_lp_2
Filter: (a = 1)
Optimizer: Postgres query optimizer
(10 rows)
explain (costs off) delete from inh_lp where a = 1;
QUERY PLAN
------------------------------------------
Delete on inh_lp
Delete on inh_lp inh_lp_1
Delete on inh_lp1 inh_lp_2
-> Append
-> Seq Scan on inh_lp inh_lp_1
Filter: (a = 1)
-> Seq Scan on inh_lp1 inh_lp_2
Filter: (a = 1)
Optimizer: Postgres query optimizer
(9 rows)
-- Ensure we don't exclude normal relations when we only expect to exclude
-- inheritance children
explain (costs off) update inh_lp1 set value = 10 where a = 2;
QUERY PLAN
-------------------------------------
Update on inh_lp1
-> Seq Scan on inh_lp1
Filter: (a = 2)
Optimizer: Postgres query optimizer
(4 rows)
drop table inh_lp cascade;
NOTICE: drop cascades to 2 other objects
DETAIL: drop cascades to table inh_lp1
drop cascades to table inh_lp2
reset enable_partition_pruning;
reset constraint_exclusion;
-- Check pruning for a partition tree containing only temporary relations
create temp table pp_temp_parent (a int) partition by list (a);
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 temp table pp_temp_part_1 partition of pp_temp_parent for values in (1);
NOTICE: table has parent, setting distribution columns to match parent table
create temp table pp_temp_part_def partition of pp_temp_parent default;
NOTICE: table has parent, setting distribution columns to match parent table
explain (costs off) select * from pp_temp_parent where true;
QUERY PLAN
-----------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Append
-> Seq Scan on pp_temp_part_1 pp_temp_parent_1
-> Seq Scan on pp_temp_part_def pp_temp_parent_2
Optimizer: Postgres query optimizer
(5 rows)
explain (costs off) select * from pp_temp_parent where a = 2;
QUERY PLAN
---------------------------------------------------
Gather Motion 1:1 (slice1; segments: 1)
-> Seq Scan on pp_temp_part_def pp_temp_parent
Filter: (a = 2)
Optimizer: Postgres query optimizer
(4 rows)
drop table pp_temp_parent;
-- Stress run-time partition pruning a bit more, per bug reports
create temp table p (a int, b int, c int) partition by list (a);
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 temp table p1 partition of p for values in (1);
NOTICE: table has parent, setting distribution columns to match parent table
create temp table p2 partition of p for values in (2);
NOTICE: table has parent, setting distribution columns to match parent table
create temp table q (a int, b int, c int) partition by list (a);
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 temp table q1 partition of q for values in (1) partition by list (b);
NOTICE: table has parent, setting distribution columns to match parent table
create temp table q11 partition of q1 for values in (1) partition by list (c);
NOTICE: table has parent, setting distribution columns to match parent table
create temp table q111 partition of q11 for values in (1);
NOTICE: table has parent, setting distribution columns to match parent table
create temp table q2 partition of q for values in (2) partition by list (b);
NOTICE: table has parent, setting distribution columns to match parent table
create temp table q21 partition of q2 for values in (1);
NOTICE: table has parent, setting distribution columns to match parent table
create temp table q22 partition of q2 for values in (2);
NOTICE: table has parent, setting distribution columns to match parent table
insert into q22 values (2, 2, 3);
-- GPDB: This is the query that needs the "matchsubs" rule at the top of the file
-- The constant third branch of the UNION is executed at random segment. If the
-- randomly chosen segment is the same as the one that needed for the other branches,
-- the whole query runs on that one segment. Otherwise, it runs on two segments.
-- The choice is made randomly, so accept both plans.
explain (costs off)
select *
from (
select * from p
union all
select * from q1
union all
select 1, 1, 1
) s(a, b, c)
where s.a = 1 and s.b = 1 and s.c = (select 1);
QUERY PLAN
-------------------------------------------------------------
Gather Motion 2:1 (slice1; segments: 2)
InitPlan 1 (returns $0) (slice2)
-> Result
-> Append
-> Seq Scan on p1 p
Filter: ((a = 1) AND (b = 1) AND (c = $0))
-> Seq Scan on q111 q1
Filter: ((a = 1) AND (b = 1) AND (c = $0))
-> Result
One-Time Filter: (gp_execution_segment() = 2)
-> Result
One-Time Filter: (1 = $0)
Optimizer: Postgres query optimizer
(13 rows)
select *
from (
select * from p
union all
select * from q1
union all
select 1, 1, 1
) s(a, b, c)
where s.a = 1 and s.b = 1 and s.c = (select 1);
a | b | c
---+---+---
1 | 1 | 1
(1 row)
prepare q (int, int) as
select *
from (
select * from p
union all
select * from q1
union all
select 1, 1, 1
) s(a, b, c)
where s.a = $1 and s.b = $2 and s.c = (select 1);
explain (costs off) execute q (1, 1);
QUERY PLAN
---------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
InitPlan 1 (returns $0) (slice2)
-> Result
-> Append
Subplans Removed: 1
-> Seq Scan on p1 p
Filter: ((a = $1) AND (b = $2) AND (c = $0))
-> Seq Scan on q111 q1
Filter: ((a = $1) AND (b = $2) AND (c = $0))
-> Result
One-Time Filter: (gp_execution_segment() = 2)
-> Result
One-Time Filter: ((1 = $1) AND (1 = $2) AND (1 = $0))
Optimizer: Postgres query optimizer
(14 rows)
execute q (1, 1);
a | b | c
---+---+---
1 | 1 | 1
(1 row)
drop table p, q;
-- Ensure run-time pruning works correctly when we match a partitioned table
-- on the first level but find no matching partitions on the second level.
create table listp (a int, b int) partition by list (a);
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 listp1 partition of listp for values in(1);
NOTICE: table has parent, setting distribution columns to match parent table
create table listp2 partition of listp for values in(2) partition by list(b);
NOTICE: table has parent, setting distribution columns to match parent table
create table listp2_10 partition of listp2 for values in (10);
NOTICE: table has parent, setting distribution columns to match parent table
explain (analyze, costs off, summary off, timing off)
select * from listp where a = (select 2) and b <> 10;
QUERY PLAN
------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3) (actual rows=0 loops=1)
InitPlan 1 (returns $0) (slice2)
-> Result (actual rows=1 loops=1)
-> Seq Scan on listp1 listp (actual rows=0 loops=1)
Filter: ((b <> 10) AND (a = $0))
Optimizer: Postgres query optimizer
(6 rows)
--
-- check that a partition directly accessed in a query is excluded with
-- constraint_exclusion = on
--
-- turn off partition pruning, so that it doesn't interfere
set enable_partition_pruning to off;
-- setting constraint_exclusion to 'partition' disables exclusion
set constraint_exclusion to 'partition';
explain (costs off) select * from listp1 where a = 2;
QUERY PLAN
------------------------------------------
Gather Motion 1:1 (slice1; segments: 1)
-> Seq Scan on listp1
Filter: (a = 2)
Optimizer: Postgres query optimizer
(4 rows)
explain (costs off) update listp1 set a = 1 where a = 2;
QUERY PLAN
---------------------------------------------------------------
Update on listp1
-> Explicit Redistribute Motion 1:3 (slice1; segments: 1)
-> Split Update
-> Seq Scan on listp1
Filter: (a = 2)
Optimizer: Postgres query optimizer
(6 rows)
-- constraint exclusion enabled
set constraint_exclusion to 'on';
explain (costs off) select * from listp1 where a = 2;
QUERY PLAN
-------------------------------------
Result
One-Time Filter: false
Optimizer: Postgres query optimizer
(3 rows)
explain (costs off) update listp1 set a = 1 where a = 2;
QUERY PLAN
-------------------------------------
Update on listp1
-> Result
One-Time Filter: false
Optimizer: Postgres query optimizer
(4 rows)
reset constraint_exclusion;
reset enable_partition_pruning;
drop table listp;
-- Ensure run-time pruning works correctly for nested Append nodes
set parallel_setup_cost to 0;
set parallel_tuple_cost to 0;
create table listp (a int) partition by list(a);
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 listp_12 partition of listp for values in(1,2) partition by list(a);
NOTICE: table has parent, setting distribution columns to match parent table
create table listp_12_1 partition of listp_12 for values in(1);
NOTICE: table has parent, setting distribution columns to match parent table
create table listp_12_2 partition of listp_12 for values in(2);
NOTICE: table has parent, setting distribution columns to match parent table
-- Force the 2nd subnode of the Append to be non-parallel. This results in
-- a nested Append node because the mixed parallel / non-parallel paths cannot
-- be pulled into the top-level Append.
alter table listp_12_1 set (parallel_workers = 0);
-- Ensure that listp_12_2 is not scanned. (The nested Append is not seen in
-- the plan as it's pulled in setref.c due to having just a single subnode).
select explain_parallel_append('select * from listp where a = (select 1);');
explain_parallel_append
--------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3) (actual rows=N loops=N)
InitPlan 1 (returns $0) (slice2)
-> Result (actual rows=N loops=N)
-> Append (actual rows=N loops=N)
-> Seq Scan on listp_12_1 listp_1 (actual rows=N loops=N)
Filter: (a = $0)
-> Seq Scan on listp_12_2 listp_2 (never executed)
Filter: (a = $0)
Optimizer: Postgres query optimizer
(9 rows)
-- Like the above but throw some more complexity at the planner by adding
-- a UNION ALL. We expect both sides of the union not to scan the
-- non-required partitions.
select explain_parallel_append(
'select * from listp where a = (select 1)
union all
select * from listp where a = (select 2);');
explain_parallel_append
--------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3) (actual rows=N loops=N)
-> Append (actual rows=N loops=N)
-> Append (actual rows=N loops=N)
InitPlan 1 (returns $0) (slice2)
-> Result (actual rows=N loops=N)
-> Seq Scan on listp_12_1 listp_1 (actual rows=N loops=N)
Filter: (a = $0)
-> Seq Scan on listp_12_2 listp_2 (never executed)
Filter: (a = $0)
-> Append (actual rows=N loops=N)
InitPlan 2 (returns $1) (slice3)
-> Result (actual rows=N loops=N)
-> Seq Scan on listp_12_1 listp_4 (never executed)
Filter: (a = $1)
-> Seq Scan on listp_12_2 listp_5 (actual rows=N loops=N)
Filter: (a = $1)
Optimizer: Postgres query optimizer
(17 rows)
drop table listp;
reset parallel_tuple_cost;
reset parallel_setup_cost;
-- Test case for run-time pruning with a nested Merge Append
set enable_sort to 0;
create table rangep (a int, b int) partition by range (a);
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 rangep_0_to_100 partition of rangep for values from (0) to (100) partition by list (b);
NOTICE: table has parent, setting distribution columns to match parent table
-- We need 3 sub-partitions. 1 to validate pruning worked and another two
-- because a single remaining partition would be pulled up to the main Append.
create table rangep_0_to_100_1 partition of rangep_0_to_100 for values in(1);
NOTICE: table has parent, setting distribution columns to match parent table
create table rangep_0_to_100_2 partition of rangep_0_to_100 for values in(2);
NOTICE: table has parent, setting distribution columns to match parent table
create table rangep_0_to_100_3 partition of rangep_0_to_100 for values in(3);
NOTICE: table has parent, setting distribution columns to match parent table
create table rangep_100_to_200 partition of rangep for values from (100) to (200);
NOTICE: table has parent, setting distribution columns to match parent table
create index on rangep (a);
-- Ensure run-time pruning works on the nested Merge Append
explain (analyze on, costs off, timing off, summary off)
select * from rangep where b IN((select 1),(select 2)) order by a;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3) (actual rows=0 loops=1)
Merge Key: rangep.a
InitPlan 1 (returns $0) (slice2)
-> Result (actual rows=1 loops=1)
InitPlan 2 (returns $1) (slice3)
-> Result (actual rows=1 loops=1)
-> Append (actual rows=0 loops=1)
-> Merge Append (actual rows=0 loops=1)
Sort Key: rangep_2.a
-> Index Scan using rangep_0_to_100_1_a_idx on rangep_0_to_100_1 rangep_2 (actual rows=0 loops=1)
Filter: (b = ANY (ARRAY[$0, $1]))
-> Index Scan using rangep_0_to_100_2_a_idx on rangep_0_to_100_2 rangep_3 (actual rows=0 loops=1)
Filter: (b = ANY (ARRAY[$0, $1]))
-> Index Scan using rangep_0_to_100_3_a_idx on rangep_0_to_100_3 rangep_4 (never executed)
Filter: (b = ANY (ARRAY[$0, $1]))
-> Index Scan using rangep_100_to_200_a_idx on rangep_100_to_200 rangep_5 (actual rows=0 loops=1)
Filter: (b = ANY (ARRAY[$0, $1]))
Optimizer: Postgres query optimizer
(18 rows)
reset enable_sort;
drop table rangep;
--
-- Check that gen_prune_steps_from_opexps() works well for various cases of
-- clauses for different partition keys
--
create table rp_prefix_test1 (a int, b varchar) partition by range(a, 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_prefix_test1_p1 partition of rp_prefix_test1 for values from (1, 'a') to (1, 'b');
NOTICE: table has parent, setting distribution columns to match parent table
create table rp_prefix_test1_p2 partition of rp_prefix_test1 for values from (2, 'a') to (2, 'b');
NOTICE: table has parent, setting distribution columns to match parent table
-- Don't call get_steps_using_prefix() with the last partition key b plus
-- an empty prefix
explain (costs off) select * from rp_prefix_test1 where a <= 1 and b = 'a';
QUERY PLAN
--------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Seq Scan on rp_prefix_test1_p1 rp_prefix_test1
Filter: ((a <= 1) AND ((b)::text = 'a'::text))
Optimizer: Postgres query optimizer
(4 rows)
create table rp_prefix_test2 (a int, b int, c int) partition by range(a, b, c);
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_prefix_test2_p1 partition of rp_prefix_test2 for values from (1, 1, 0) to (1, 1, 10);
NOTICE: table has parent, setting distribution columns to match parent table
create table rp_prefix_test2_p2 partition of rp_prefix_test2 for values from (2, 2, 0) to (2, 2, 10);
NOTICE: table has parent, setting distribution columns to match parent table
-- Don't call get_steps_using_prefix() with the last partition key c plus
-- an invalid prefix (ie, b = 1)
explain (costs off) select * from rp_prefix_test2 where a <= 1 and b = 1 and c >= 0;
QUERY PLAN
------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Seq Scan on rp_prefix_test2_p1 rp_prefix_test2
Filter: ((a <= 1) AND (c >= 0) AND (b = 1))
Optimizer: Postgres query optimizer
(4 rows)
create table rp_prefix_test3 (a int, b int, c int, d int) partition by range(a, b, c, d);
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_prefix_test3_p1 partition of rp_prefix_test3 for values from (1, 1, 1, 0) to (1, 1, 1, 10);
NOTICE: table has parent, setting distribution columns to match parent table
create table rp_prefix_test3_p2 partition of rp_prefix_test3 for values from (2, 2, 2, 0) to (2, 2, 2, 10);
NOTICE: table has parent, setting distribution columns to match parent table
-- Test that get_steps_using_prefix() handles a prefix that contains multiple
-- clauses for the partition key b (ie, b >= 1 and b >= 2)
explain (costs off) select * from rp_prefix_test3 where a >= 1 and b >= 1 and b >= 2 and c >= 2 and d >= 0;
QUERY PLAN
--------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Seq Scan on rp_prefix_test3_p2 rp_prefix_test3
Filter: ((a >= 1) AND (b >= 1) AND (b >= 2) AND (c >= 2) AND (d >= 0))
Optimizer: Postgres query optimizer
(4 rows)
-- Test that get_steps_using_prefix() handles a prefix that contains multiple
-- clauses for the partition key b (ie, b >= 1 and b = 2) (This also tests
-- that the caller arranges clauses in that prefix in the required order)
explain (costs off) select * from rp_prefix_test3 where a >= 1 and b >= 1 and b = 2 and c = 2 and d >= 0;
QUERY PLAN
-------------------------------------------------------------------------------------------
Gather Motion 3:1 (slice1; segments: 3)
-> Seq Scan on rp_prefix_test3_p2 rp_prefix_test3
Filter: ((a >= 1) AND (b >= 1) AND (d >= 0) AND (c >= 1) AND (b = 2) AND (c = 2))
Optimizer: Postgres query optimizer
(4 rows)
drop table rp_prefix_test1;
drop table rp_prefix_test2;
drop table rp_prefix_test3;
--
-- Test that get_steps_using_prefix() handles IS NULL clauses correctly
--
create table hp_prefix_test (a int, b int, c int, d int)
partition by hash (a part_test_int4_ops, b part_test_int4_ops, c part_test_int4_ops, d part_test_int4_ops);
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 8 partitions
select 'create table hp_prefix_test_p' || x::text || ' partition of hp_prefix_test for values with (modulus 8, remainder ' || x::text || ');'
from generate_Series(0,7) x;
?column?
------------------------------------------------------------------------------------------------------
create table hp_prefix_test_p0 partition of hp_prefix_test for values with (modulus 8, remainder 0);
create table hp_prefix_test_p1 partition of hp_prefix_test for values with (modulus 8, remainder 1);
create table hp_prefix_test_p2 partition of hp_prefix_test for values with (modulus 8, remainder 2);
create table hp_prefix_test_p3 partition of hp_prefix_test for values with (modulus 8, remainder 3);
create table hp_prefix_test_p4 partition of hp_prefix_test for values with (modulus 8, remainder 4);
create table hp_prefix_test_p5 partition of hp_prefix_test for values with (modulus 8, remainder 5);
create table hp_prefix_test_p6 partition of hp_prefix_test for values with (modulus 8, remainder 6);
create table hp_prefix_test_p7 partition of hp_prefix_test for values with (modulus 8, remainder 7);
(8 rows)
\gexec
create table hp_prefix_test_p0 partition of hp_prefix_test for values with (modulus 8, remainder 0);
NOTICE: table has parent, setting distribution columns to match parent table
create table hp_prefix_test_p1 partition of hp_prefix_test for values with (modulus 8, remainder 1);
NOTICE: table has parent, setting distribution columns to match parent table
create table hp_prefix_test_p2 partition of hp_prefix_test for values with (modulus 8, remainder 2);
NOTICE: table has parent, setting distribution columns to match parent table
create table hp_prefix_test_p3 partition of hp_prefix_test for values with (modulus 8, remainder 3);
NOTICE: table has parent, setting distribution columns to match parent table
create table hp_prefix_test_p4 partition of hp_prefix_test for values with (modulus 8, remainder 4);
NOTICE: table has parent, setting distribution columns to match parent table
create table hp_prefix_test_p5 partition of hp_prefix_test for values with (modulus 8, remainder 5);
NOTICE: table has parent, setting distribution columns to match parent table
create table hp_prefix_test_p6 partition of hp_prefix_test for values with (modulus 8, remainder 6);
NOTICE: table has parent, setting distribution columns to match parent table
create table hp_prefix_test_p7 partition of hp_prefix_test for values with (modulus 8, remainder 7);
NOTICE: table has parent, setting distribution columns to match parent table
-- insert 16 rows, one row for each test to perform.
insert into hp_prefix_test
select
case a when 0 then null else 1 end,
case b when 0 then null else 2 end,
case c when 0 then null else 3 end,
case d when 0 then null else 4 end
from
generate_series(0,1) a,
generate_series(0,1) b,
generate_Series(0,1) c,
generate_Series(0,1) d;
-- Ensure partition pruning works correctly for each combination of IS NULL
-- and equality quals. This may seem a little excessive, but there have been
-- a number of bugs in this area over the years. We make use of row only
-- output to reduce the size of the expected results.
\t on
select
'explain (costs off) select tableoid::regclass,* from hp_prefix_test where ' ||
string_agg(c.colname || case when g.s & (1 << c.colpos) = 0 then ' is null' else ' = ' || (colpos+1)::text end, ' and ' order by c.colpos)
from (values('a',0),('b',1),('c',2),('d',3)) c(colname, colpos), generate_Series(0,15) g(s)
group by g.s
order by g.s;
explain (costs off) select tableoid::regclass,* from hp_prefix_test where a is null and b is null and c is null and d is null
explain (costs off) select tableoid::regclass,* from hp_prefix_test where a = 1 and b is null and c is null and d is null
explain (costs off) select tableoid::regclass,* from hp_prefix_test where a is null and b = 2 and c is null and d is null
explain (costs off) select tableoid::regclass,* from hp_prefix_test where a = 1 and b = 2 and c is null and d is null
explain (costs off) select tableoid::regclass,* from hp_prefix_test where a is null and b is null and c = 3 and d is null
explain (costs off) select tableoid::regclass,* from hp_prefix_test where a = 1 and b is null and c = 3 and d is null
explain (costs off) select tableoid::regclass,* from hp_prefix_test where a is null and b = 2 and c = 3 and d is null
explain (costs off) select tableoid::regclass,* from hp_prefix_test where a = 1 and b = 2 and c = 3 and d is null
explain (costs off) select tableoid::regclass,* from hp_prefix_test where a is null and b is null and c is null and d = 4
explain (costs off) select tableoid::regclass,* from hp_prefix_test where a = 1 and b is null and c is null and d = 4
explain (costs off) select tableoid::regclass,* from hp_prefix_test where a is null and b = 2 and c is null and d = 4
explain (costs off) select tableoid::regclass,* from hp_prefix_test where a = 1 and b = 2 and c is null and d = 4
explain (costs off) select tableoid::regclass,* from hp_prefix_test where a is null and b is null and c = 3 and d = 4
explain (costs off) select tableoid::regclass,* from hp_prefix_test where a = 1 and b is null and c = 3 and d = 4
explain (costs off) select tableoid::regclass,* from hp_prefix_test where a is null and b = 2 and c = 3 and d = 4
explain (costs off) select tableoid::regclass,* from hp_prefix_test where a = 1 and b = 2 and c = 3 and d = 4
\gexec
explain (costs off) select tableoid::regclass,* from hp_prefix_test where a is null and b is null and c is null and d is null
Gather Motion 2:1 (slice1; segments: 2)
-> Parallel Seq Scan on hp_prefix_test_p0 hp_prefix_test
Filter: ((a IS NULL) AND (b IS NULL) AND (c IS NULL) AND (d IS NULL))
Optimizer: Postgres query optimizer
explain (costs off) select tableoid::regclass,* from hp_prefix_test where a = 1 and b is null and c is null and d is null
Gather Motion 2:1 (slice1; segments: 2)
-> Parallel Seq Scan on hp_prefix_test_p1 hp_prefix_test
Filter: ((b IS NULL) AND (c IS NULL) AND (d IS NULL) AND (a = 1))
Optimizer: Postgres query optimizer
explain (costs off) select tableoid::regclass,* from hp_prefix_test where a is null and b = 2 and c is null and d is null
Gather Motion 2:1 (slice1; segments: 2)
-> Parallel Seq Scan on hp_prefix_test_p2 hp_prefix_test
Filter: ((a IS NULL) AND (c IS NULL) AND (d IS NULL) AND (b = 2))
Optimizer: Postgres query optimizer
explain (costs off) select tableoid::regclass,* from hp_prefix_test where a = 1 and b = 2 and c is null and d is null
Gather Motion 2:1 (slice1; segments: 2)
-> Parallel Seq Scan on hp_prefix_test_p4 hp_prefix_test
Filter: ((c IS NULL) AND (d IS NULL) AND (a = 1) AND (b = 2))
Optimizer: Postgres query optimizer
explain (costs off) select tableoid::regclass,* from hp_prefix_test where a is null and b is null and c = 3 and d is null
Gather Motion 2:1 (slice1; segments: 2)
-> Parallel Seq Scan on hp_prefix_test_p3 hp_prefix_test
Filter: ((a IS NULL) AND (b IS NULL) AND (d IS NULL) AND (c = 3))
Optimizer: Postgres query optimizer
explain (costs off) select tableoid::regclass,* from hp_prefix_test where a = 1 and b is null and c = 3 and d is null
Gather Motion 2:1 (slice1; segments: 2)
-> Parallel Seq Scan on hp_prefix_test_p7 hp_prefix_test
Filter: ((b IS NULL) AND (d IS NULL) AND (a = 1) AND (c = 3))
Optimizer: Postgres query optimizer
explain (costs off) select tableoid::regclass,* from hp_prefix_test where a is null and b = 2 and c = 3 and d is null
Gather Motion 2:1 (slice1; segments: 2)
-> Parallel Seq Scan on hp_prefix_test_p4 hp_prefix_test
Filter: ((a IS NULL) AND (d IS NULL) AND (b = 2) AND (c = 3))
Optimizer: Postgres query optimizer
explain (costs off) select tableoid::regclass,* from hp_prefix_test where a = 1 and b = 2 and c = 3 and d is null
Gather Motion 2:1 (slice1; segments: 2)
-> Parallel Seq Scan on hp_prefix_test_p5 hp_prefix_test
Filter: ((d IS NULL) AND (a = 1) AND (b = 2) AND (c = 3))
Optimizer: Postgres query optimizer
explain (costs off) select tableoid::regclass,* from hp_prefix_test where a is null and b is null and c is null and d = 4
Gather Motion 2:1 (slice1; segments: 2)
-> Parallel Seq Scan on hp_prefix_test_p4 hp_prefix_test
Filter: ((a IS NULL) AND (b IS NULL) AND (c IS NULL) AND (d = 4))
Optimizer: Postgres query optimizer
explain (costs off) select tableoid::regclass,* from hp_prefix_test where a = 1 and b is null and c is null and d = 4
Gather Motion 2:1 (slice1; segments: 2)
-> Parallel Seq Scan on hp_prefix_test_p6 hp_prefix_test
Filter: ((b IS NULL) AND (c IS NULL) AND (a = 1) AND (d = 4))
Optimizer: Postgres query optimizer
explain (costs off) select tableoid::regclass,* from hp_prefix_test where a is null and b = 2 and c is null and d = 4
Gather Motion 2:1 (slice1; segments: 2)
-> Parallel Seq Scan on hp_prefix_test_p5 hp_prefix_test
Filter: ((a IS NULL) AND (c IS NULL) AND (b = 2) AND (d = 4))
Optimizer: Postgres query optimizer
explain (costs off) select tableoid::regclass,* from hp_prefix_test where a = 1 and b = 2 and c is null and d = 4
Gather Motion 2:1 (slice1; segments: 2)
-> Parallel Seq Scan on hp_prefix_test_p6 hp_prefix_test
Filter: ((c IS NULL) AND (a = 1) AND (b = 2) AND (d = 4))
Optimizer: Postgres query optimizer
explain (costs off) select tableoid::regclass,* from hp_prefix_test where a is null and b is null and c = 3 and d = 4
Gather Motion 2:1 (slice1; segments: 2)
-> Parallel Seq Scan on hp_prefix_test_p4 hp_prefix_test
Filter: ((a IS NULL) AND (b IS NULL) AND (c = 3) AND (d = 4))
Optimizer: Postgres query optimizer
explain (costs off) select tableoid::regclass,* from hp_prefix_test where a = 1 and b is null and c = 3 and d = 4
Gather Motion 2:1 (slice1; segments: 2)
-> Parallel Seq Scan on hp_prefix_test_p5 hp_prefix_test
Filter: ((b IS NULL) AND (a = 1) AND (c = 3) AND (d = 4))
Optimizer: Postgres query optimizer
explain (costs off) select tableoid::regclass,* from hp_prefix_test where a is null and b = 2 and c = 3 and d = 4
Gather Motion 2:1 (slice1; segments: 2)
-> Parallel Seq Scan on hp_prefix_test_p6 hp_prefix_test
Filter: ((a IS NULL) AND (b = 2) AND (c = 3) AND (d = 4))
Optimizer: Postgres query optimizer
explain (costs off) select tableoid::regclass,* from hp_prefix_test where a = 1 and b = 2 and c = 3 and d = 4
Gather Motion 2:1 (slice1; segments: 2)
-> Parallel Seq Scan on hp_prefix_test_p4 hp_prefix_test
Filter: ((a = 1) AND (b = 2) AND (c = 3) AND (d = 4))
Optimizer: Postgres query optimizer
-- And ensure we get exactly 1 row from each. Again, all 16 possible combinations.
select
'select tableoid::regclass,* from hp_prefix_test where ' ||
string_agg(c.colname || case when g.s & (1 << c.colpos) = 0 then ' is null' else ' = ' || (colpos+1)::text end, ' and ' order by c.colpos)
from (values('a',0),('b',1),('c',2),('d',3)) c(colname, colpos), generate_Series(0,15) g(s)
group by g.s
order by g.s;
select tableoid::regclass,* from hp_prefix_test where a is null and b is null and c is null and d is null
select tableoid::regclass,* from hp_prefix_test where a = 1 and b is null and c is null and d is null
select tableoid::regclass,* from hp_prefix_test where a is null and b = 2 and c is null and d is null
select tableoid::regclass,* from hp_prefix_test where a = 1 and b = 2 and c is null and d is null
select tableoid::regclass,* from hp_prefix_test where a is null and b is null and c = 3 and d is null
select tableoid::regclass,* from hp_prefix_test where a = 1 and b is null and c = 3 and d is null
select tableoid::regclass,* from hp_prefix_test where a is null and b = 2 and c = 3 and d is null
select tableoid::regclass,* from hp_prefix_test where a = 1 and b = 2 and c = 3 and d is null
select tableoid::regclass,* from hp_prefix_test where a is null and b is null and c is null and d = 4
select tableoid::regclass,* from hp_prefix_test where a = 1 and b is null and c is null and d = 4
select tableoid::regclass,* from hp_prefix_test where a is null and b = 2 and c is null and d = 4
select tableoid::regclass,* from hp_prefix_test where a = 1 and b = 2 and c is null and d = 4
select tableoid::regclass,* from hp_prefix_test where a is null and b is null and c = 3 and d = 4
select tableoid::regclass,* from hp_prefix_test where a = 1 and b is null and c = 3 and d = 4
select tableoid::regclass,* from hp_prefix_test where a is null and b = 2 and c = 3 and d = 4
select tableoid::regclass,* from hp_prefix_test where a = 1 and b = 2 and c = 3 and d = 4
\gexec
select tableoid::regclass,* from hp_prefix_test where a is null and b is null and c is null and d is null
hp_prefix_test_p0 | | | |
select tableoid::regclass,* from hp_prefix_test where a = 1 and b is null and c is null and d is null
hp_prefix_test_p1 | 1 | | |
select tableoid::regclass,* from hp_prefix_test where a is null and b = 2 and c is null and d is null
hp_prefix_test_p2 | | 2 | |
select tableoid::regclass,* from hp_prefix_test where a = 1 and b = 2 and c is null and d is null
hp_prefix_test_p4 | 1 | 2 | |
select tableoid::regclass,* from hp_prefix_test where a is null and b is null and c = 3 and d is null
hp_prefix_test_p3 | | | 3 |
select tableoid::regclass,* from hp_prefix_test where a = 1 and b is null and c = 3 and d is null
hp_prefix_test_p7 | 1 | | 3 |
select tableoid::regclass,* from hp_prefix_test where a is null and b = 2 and c = 3 and d is null
hp_prefix_test_p4 | | 2 | 3 |
select tableoid::regclass,* from hp_prefix_test where a = 1 and b = 2 and c = 3 and d is null
hp_prefix_test_p5 | 1 | 2 | 3 |
select tableoid::regclass,* from hp_prefix_test where a is null and b is null and c is null and d = 4
hp_prefix_test_p4 | | | | 4
select tableoid::regclass,* from hp_prefix_test where a = 1 and b is null and c is null and d = 4
hp_prefix_test_p6 | 1 | | | 4
select tableoid::regclass,* from hp_prefix_test where a is null and b = 2 and c is null and d = 4
hp_prefix_test_p5 | | 2 | | 4
select tableoid::regclass,* from hp_prefix_test where a = 1 and b = 2 and c is null and d = 4
hp_prefix_test_p6 | 1 | 2 | | 4
select tableoid::regclass,* from hp_prefix_test where a is null and b is null and c = 3 and d = 4
hp_prefix_test_p4 | | | 3 | 4
select tableoid::regclass,* from hp_prefix_test where a = 1 and b is null and c = 3 and d = 4
hp_prefix_test_p5 | 1 | | 3 | 4
select tableoid::regclass,* from hp_prefix_test where a is null and b = 2 and c = 3 and d = 4
hp_prefix_test_p6 | | 2 | 3 | 4
select tableoid::regclass,* from hp_prefix_test where a = 1 and b = 2 and c = 3 and d = 4
hp_prefix_test_p4 | 1 | 2 | 3 | 4
\t off
drop table hp_prefix_test;
--
-- Check that gen_partprune_steps() detects self-contradiction from clauses
-- regardless of the order of the clauses (Here we use a custom operator to
-- prevent the equivclass.c machinery from reordering the clauses)
--
create operator === (
leftarg = int4,
rightarg = int4,
procedure = int4eq,
commutator = ===,
hashes
);
create operator class part_test_int4_ops2
for type int4
using hash as
operator 1 ===,
function 2 part_hashint4_noop(int4, int8);
create table hp_contradict_test (a int, b int) partition by hash (a part_test_int4_ops2, b part_test_int4_ops2);
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 hp_contradict_test_p1 partition of hp_contradict_test for values with (modulus 2, remainder 0);
NOTICE: table has parent, setting distribution columns to match parent table
create table hp_contradict_test_p2 partition of hp_contradict_test for values with (modulus 2, remainder 1);
NOTICE: table has parent, setting distribution columns to match parent table
explain (costs off) select * from hp_contradict_test where a is null and a === 1 and b === 1;
QUERY PLAN
-------------------------------------
Result
One-Time Filter: false
Optimizer: Postgres query optimizer
(3 rows)
explain (costs off) select * from hp_contradict_test where a === 1 and b === 1 and a is null;
QUERY PLAN
-------------------------------------
Result
One-Time Filter: false
Optimizer: Postgres query optimizer
(3 rows)
drop table hp_contradict_test;
drop operator class part_test_int4_ops2 using hash;
drop operator ===(int4, int4);