0001-Add-new-tests-for-partition-pruning-v3.patch
text/plain
Filename: 0001-Add-new-tests-for-partition-pruning-v3.patch
Type: text/plain
Part: 0
Patch
Format: format-patch
Series: patch v3-0001
Subject: Add new tests for partition-pruning
| File | + | − |
|---|---|---|
| src/test/regress/expected/partition.out | 733 | 0 |
| src/test/regress/parallel_schedule | 1 | 1 |
| src/test/regress/serial_schedule | 1 | 0 |
| src/test/regress/sql/partition.sql | 105 | 0 |
From 823d3bff6e067172da3ba97b771e1bb32681f629 Mon Sep 17 00:00:00 2001
From: amit <amitlangote09@gmail.com>
Date: Tue, 5 Sep 2017 10:28:23 +0900
Subject: [PATCH 1/5] Add new tests for partition-pruning
---
src/test/regress/expected/partition.out | 733 ++++++++++++++++++++++++++++++++
src/test/regress/parallel_schedule | 2 +-
src/test/regress/serial_schedule | 1 +
src/test/regress/sql/partition.sql | 105 +++++
4 files changed, 840 insertions(+), 1 deletion(-)
create mode 100644 src/test/regress/expected/partition.out
create mode 100644 src/test/regress/sql/partition.sql
diff --git a/src/test/regress/expected/partition.out b/src/test/regress/expected/partition.out
new file mode 100644
index 0000000000..61c4596bc7
--- /dev/null
+++ b/src/test/regress/expected/partition.out
@@ -0,0 +1,733 @@
+--
+-- Test partitioning planner code
+--
+create table lp (a char) partition by list (a);
+create table lp_default partition of lp default;
+create table lp_ef partition of lp for values in ('e', 'f');
+create table lp_ad partition of lp for values in ('a', 'd');
+create table lp_bc partition of lp for values in ('b', 'c');
+create table lp_g partition of lp for values in ('g');
+create table lp_null partition of lp for values in (null);
+explain (costs off) select * from lp;
+ QUERY PLAN
+------------------------------
+ Append
+ -> Seq Scan on lp_ad
+ -> Seq Scan on lp_bc
+ -> Seq Scan on lp_ef
+ -> Seq Scan on lp_g
+ -> Seq Scan on lp_null
+ -> Seq Scan on lp_default
+(7 rows)
+
+explain (costs off) select * from lp where a > 'a' and a < 'd';
+ QUERY PLAN
+-----------------------------------------------------------
+ Append
+ -> Seq Scan on lp_bc
+ Filter: ((a > 'a'::bpchar) AND (a < 'd'::bpchar))
+ -> Seq Scan on lp_default
+ Filter: ((a > 'a'::bpchar) AND (a < 'd'::bpchar))
+(5 rows)
+
+explain (costs off) select * from lp where a > 'a' and a <= 'd';
+ QUERY PLAN
+------------------------------------------------------------
+ Append
+ -> Seq Scan on lp_ad
+ Filter: ((a > 'a'::bpchar) AND (a <= 'd'::bpchar))
+ -> Seq Scan on lp_bc
+ Filter: ((a > 'a'::bpchar) AND (a <= 'd'::bpchar))
+ -> Seq Scan on lp_default
+ Filter: ((a > 'a'::bpchar) AND (a <= 'd'::bpchar))
+(7 rows)
+
+explain (costs off) select * from lp where a = 'a';
+ QUERY PLAN
+-----------------------------------
+ Append
+ -> Seq Scan on lp_ad
+ Filter: (a = 'a'::bpchar)
+(3 rows)
+
+explain (costs off) select * from lp where 'a' = a; /* commutates */
+ QUERY PLAN
+-----------------------------------
+ Append
+ -> Seq Scan on lp_ad
+ Filter: ('a'::bpchar = a)
+(3 rows)
+
+explain (costs off) select * from lp where a is not null;
+ QUERY PLAN
+---------------------------------
+ Append
+ -> Seq Scan on lp_ad
+ Filter: (a IS NOT NULL)
+ -> Seq Scan on lp_bc
+ Filter: (a IS NOT NULL)
+ -> Seq Scan on lp_ef
+ Filter: (a IS NOT NULL)
+ -> Seq Scan on lp_g
+ Filter: (a IS NOT NULL)
+ -> Seq Scan on lp_default
+ Filter: (a IS NOT NULL)
+(11 rows)
+
+explain (costs off) select * from lp where a is null;
+ QUERY PLAN
+-----------------------------
+ Append
+ -> Seq Scan on lp_null
+ Filter: (a IS NULL)
+(3 rows)
+
+explain (costs off) select * from lp where a = 'a' or a = 'c';
+ QUERY PLAN
+----------------------------------------------------------
+ Append
+ -> Seq Scan on lp_ad
+ Filter: ((a = 'a'::bpchar) OR (a = 'c'::bpchar))
+ -> Seq Scan on lp_bc
+ Filter: ((a = 'a'::bpchar) OR (a = 'c'::bpchar))
+(5 rows)
+
+explain (costs off) select * from lp where a is not null and (a = 'a' or a = 'c');
+ QUERY PLAN
+--------------------------------------------------------------------------------
+ Append
+ -> Seq Scan on lp_ad
+ Filter: ((a IS NOT NULL) AND ((a = 'a'::bpchar) OR (a = 'c'::bpchar)))
+ -> Seq Scan on lp_bc
+ Filter: ((a IS NOT NULL) AND ((a = 'a'::bpchar) OR (a = 'c'::bpchar)))
+(5 rows)
+
+explain (costs off) select * from lp where a <> 'g';
+ QUERY PLAN
+------------------------------------
+ Append
+ -> Seq Scan on lp_ad
+ Filter: (a <> 'g'::bpchar)
+ -> Seq Scan on lp_bc
+ Filter: (a <> 'g'::bpchar)
+ -> Seq Scan on lp_ef
+ Filter: (a <> 'g'::bpchar)
+ -> Seq Scan on lp_default
+ Filter: (a <> 'g'::bpchar)
+(9 rows)
+
+explain (costs off) select * from lp where a <> 'a' and a <> 'd';
+ QUERY PLAN
+-------------------------------------------------------------
+ Append
+ -> Seq Scan on lp_bc
+ Filter: ((a <> 'a'::bpchar) AND (a <> 'd'::bpchar))
+ -> Seq Scan on lp_ef
+ Filter: ((a <> 'a'::bpchar) AND (a <> 'd'::bpchar))
+ -> Seq Scan on lp_g
+ Filter: ((a <> 'a'::bpchar) AND (a <> 'd'::bpchar))
+ -> Seq Scan on lp_default
+ Filter: ((a <> 'a'::bpchar) AND (a <> 'd'::bpchar))
+(9 rows)
+
+explain (costs off) select * from lp where a not in ('a', 'd');
+ QUERY PLAN
+------------------------------------------------
+ Append
+ -> Seq Scan on lp_bc
+ Filter: (a <> ALL ('{a,d}'::bpchar[]))
+ -> Seq Scan on lp_ef
+ Filter: (a <> ALL ('{a,d}'::bpchar[]))
+ -> Seq Scan on lp_g
+ Filter: (a <> ALL ('{a,d}'::bpchar[]))
+ -> Seq Scan on lp_default
+ Filter: (a <> ALL ('{a,d}'::bpchar[]))
+(9 rows)
+
+-- collation matches the partitioning collation, pruning works
+create table coll_pruning (a text collate "C") partition by list (a);
+create table coll_pruning_a partition of coll_pruning for values in ('a');
+create table coll_pruning_b partition of coll_pruning for values in ('b');
+create table coll_pruning_def partition of coll_pruning default;
+explain (costs off) select * from coll_pruning where a collate "C" = 'a' collate "C";
+ QUERY PLAN
+---------------------------------------------
+ Append
+ -> Seq Scan on coll_pruning_a
+ Filter: (a = 'a'::text COLLATE "C")
+(3 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
+---------------------------------------------------------
+ Append
+ -> Seq Scan on coll_pruning_a
+ Filter: ((a)::text = 'a'::text COLLATE "POSIX")
+ -> Seq Scan on coll_pruning_b
+ Filter: ((a)::text = 'a'::text COLLATE "POSIX")
+ -> Seq Scan on coll_pruning_def
+ Filter: ((a)::text = 'a'::text COLLATE "POSIX")
+(7 rows)
+
+create table rlp (a int, b varchar) partition by range (a);
+create table rlp_default partition of rlp default;
+create table rlp1 partition of rlp for values from (minvalue) to (1);
+create table rlp2 partition of rlp for values from (1) to (10);
+create table rlp3 (b varchar, a int) partition by list (b varchar_ops);
+create table rlp3abcd partition of rlp3 for values in ('ab', 'cd');
+create table rlp3efgh partition of rlp3 for values in ('ef', 'gh');
+create table rlp3nullxy partition of rlp3 for values in (null, 'xy');
+alter table rlp attach partition rlp3 for values from (15) to (20);
+create table rlp4 partition of rlp for values from (20) to (30);
+create table rlp5 partition of rlp for values from (31) to (maxvalue);
+explain (costs off) select * from rlp where a < 1;
+ QUERY PLAN
+-------------------------
+ Append
+ -> Seq Scan on rlp1
+ Filter: (a < 1)
+(3 rows)
+
+explain (costs off) select * from rlp where 1 > a; /* commutates */
+ QUERY PLAN
+-------------------------
+ Append
+ -> Seq Scan on rlp1
+ Filter: (1 > a)
+(3 rows)
+
+explain (costs off) select * from rlp where a <= 1;
+ QUERY PLAN
+-------------------------------
+ Append
+ -> Seq Scan on rlp1
+ Filter: (a <= 1)
+ -> Seq Scan on rlp2
+ Filter: (a <= 1)
+ -> Seq Scan on rlp_default
+ Filter: (a <= 1)
+(7 rows)
+
+explain (costs off) select * from rlp where a = 1;
+ QUERY PLAN
+-------------------------
+ Append
+ -> Seq Scan on rlp2
+ Filter: (a = 1)
+(3 rows)
+
+explain (costs off) select * from rlp where a = 1::bigint; /* same as above */
+ QUERY PLAN
+-----------------------------------
+ Append
+ -> Seq Scan on rlp2
+ Filter: (a = '1'::bigint)
+(3 rows)
+
+explain (costs off) select * from rlp where a = 1::numeric; /* no pruning */
+ QUERY PLAN
+-----------------------------------------------
+ Append
+ -> Seq Scan on rlp1
+ Filter: ((a)::numeric = '1'::numeric)
+ -> Seq Scan on rlp2
+ Filter: ((a)::numeric = '1'::numeric)
+ -> Seq Scan on rlp3abcd
+ Filter: ((a)::numeric = '1'::numeric)
+ -> Seq Scan on rlp3efgh
+ Filter: ((a)::numeric = '1'::numeric)
+ -> Seq Scan on rlp3nullxy
+ Filter: ((a)::numeric = '1'::numeric)
+ -> Seq Scan on rlp4
+ Filter: ((a)::numeric = '1'::numeric)
+ -> Seq Scan on rlp5
+ Filter: ((a)::numeric = '1'::numeric)
+ -> Seq Scan on rlp_default
+ Filter: ((a)::numeric = '1'::numeric)
+(17 rows)
+
+explain (costs off) select * from rlp where a <= 10;
+ QUERY PLAN
+-------------------------------
+ Append
+ -> Seq Scan on rlp1
+ Filter: (a <= 10)
+ -> Seq Scan on rlp2
+ Filter: (a <= 10)
+ -> Seq Scan on rlp_default
+ Filter: (a <= 10)
+(7 rows)
+
+explain (costs off) select * from rlp where a > 10;
+ QUERY PLAN
+-------------------------------
+ Append
+ -> Seq Scan on rlp3abcd
+ Filter: (a > 10)
+ -> Seq Scan on rlp3efgh
+ Filter: (a > 10)
+ -> Seq Scan on rlp3nullxy
+ Filter: (a > 10)
+ -> Seq Scan on rlp4
+ Filter: (a > 10)
+ -> Seq Scan on rlp5
+ Filter: (a > 10)
+ -> Seq Scan on rlp_default
+ Filter: (a > 10)
+(13 rows)
+
+explain (costs off) select * from rlp where a < 15;
+ QUERY PLAN
+-------------------------------
+ Append
+ -> Seq Scan on rlp1
+ Filter: (a < 15)
+ -> Seq Scan on rlp2
+ Filter: (a < 15)
+ -> Seq Scan on rlp_default
+ Filter: (a < 15)
+(7 rows)
+
+explain (costs off) select * from rlp where a <= 15;
+ QUERY PLAN
+-------------------------------
+ Append
+ -> Seq Scan on rlp1
+ Filter: (a <= 15)
+ -> Seq Scan on rlp2
+ Filter: (a <= 15)
+ -> Seq Scan on rlp3abcd
+ Filter: (a <= 15)
+ -> Seq Scan on rlp3efgh
+ Filter: (a <= 15)
+ -> Seq Scan on rlp3nullxy
+ Filter: (a <= 15)
+ -> Seq Scan on rlp_default
+ Filter: (a <= 15)
+(13 rows)
+
+explain (costs off) select * from rlp where a > 15 and b = 'ab';
+ QUERY PLAN
+---------------------------------------------------------
+ Append
+ -> Seq Scan on rlp3abcd
+ Filter: ((a > 15) AND ((b)::text = 'ab'::text))
+ -> Seq Scan on rlp4
+ Filter: ((a > 15) AND ((b)::text = 'ab'::text))
+ -> Seq Scan on rlp5
+ Filter: ((a > 15) AND ((b)::text = 'ab'::text))
+ -> Seq Scan on rlp_default
+ Filter: ((a > 15) AND ((b)::text = 'ab'::text))
+(9 rows)
+
+explain (costs off) select * from rlp where a = 16 and b is null;
+ QUERY PLAN
+--------------------------------------------
+ Append
+ -> Seq Scan on rlp3nullxy
+ Filter: ((b IS NULL) AND (a = 16))
+(3 rows)
+
+explain (costs off) select * from rlp where a = 16 and b is not null;
+ QUERY PLAN
+------------------------------------------------
+ Append
+ -> Seq Scan on rlp3abcd
+ Filter: ((b IS NOT NULL) AND (a = 16))
+ -> Seq Scan on rlp3efgh
+ Filter: ((b IS NOT NULL) AND (a = 16))
+ -> Seq Scan on rlp3nullxy
+ Filter: ((b IS NOT NULL) AND (a = 16))
+(7 rows)
+
+explain (costs off) select * from rlp where a is null; /* while we're on nulls */
+ QUERY PLAN
+-------------------------------
+ Append
+ -> Seq Scan on rlp_default
+ Filter: (a IS NULL)
+(3 rows)
+
+explain (costs off) select * from rlp where a > 30;
+ QUERY PLAN
+-------------------------------
+ Append
+ -> Seq Scan on rlp5
+ Filter: (a > 30)
+ -> Seq Scan on rlp_default
+ Filter: (a > 30)
+(5 rows)
+
+explain (costs off) select * from rlp where a = 30; /* only default is scanned */
+ QUERY PLAN
+-------------------------------
+ Append
+ -> Seq Scan on rlp_default
+ Filter: (a = 30)
+(3 rows)
+
+explain (costs off) select * from rlp where a <= 31;
+ QUERY PLAN
+-------------------------------
+ Append
+ -> Seq Scan on rlp1
+ Filter: (a <= 31)
+ -> Seq Scan on rlp2
+ Filter: (a <= 31)
+ -> Seq Scan on rlp3abcd
+ Filter: (a <= 31)
+ -> Seq Scan on rlp3efgh
+ Filter: (a <= 31)
+ -> Seq Scan on rlp3nullxy
+ Filter: (a <= 31)
+ -> Seq Scan on rlp4
+ Filter: (a <= 31)
+ -> Seq Scan on rlp5
+ Filter: (a <= 31)
+ -> Seq Scan on rlp_default
+ Filter: (a <= 31)
+(17 rows)
+
+explain (costs off) select * from rlp where a = 1 or a = 7;
+ QUERY PLAN
+--------------------------------------
+ Append
+ -> Seq Scan on rlp2
+ Filter: ((a = 1) OR (a = 7))
+(3 rows)
+
+explain (costs off) select * from rlp where a = 1 or b = 'ab';
+ QUERY PLAN
+-------------------------------------------------------
+ Append
+ -> Seq Scan on rlp1
+ Filter: ((a = 1) OR ((b)::text = 'ab'::text))
+ -> Seq Scan on rlp2
+ Filter: ((a = 1) OR ((b)::text = 'ab'::text))
+ -> Seq Scan on rlp3abcd
+ Filter: ((a = 1) OR ((b)::text = 'ab'::text))
+ -> Seq Scan on rlp4
+ Filter: ((a = 1) OR ((b)::text = 'ab'::text))
+ -> Seq Scan on rlp5
+ Filter: ((a = 1) OR ((b)::text = 'ab'::text))
+ -> Seq Scan on rlp_default
+ Filter: ((a = 1) OR ((b)::text = 'ab'::text))
+(13 rows)
+
+-- redundant clauses are eliminated
+explain (costs off) select * from rlp where a > 1 and a = 10; /* only default */
+ QUERY PLAN
+----------------------------------------
+ Append
+ -> Seq Scan on rlp_default
+ Filter: ((a > 1) AND (a = 10))
+(3 rows)
+
+explain (costs off) select * from rlp where a > 1 and a >=15; /* rlp3 onwards, including default */
+ QUERY PLAN
+-----------------------------------------
+ Append
+ -> Seq Scan on rlp3abcd
+ Filter: ((a > 1) AND (a >= 15))
+ -> Seq Scan on rlp3efgh
+ Filter: ((a > 1) AND (a >= 15))
+ -> Seq Scan on rlp3nullxy
+ Filter: ((a > 1) AND (a >= 15))
+ -> Seq Scan on rlp4
+ Filter: ((a > 1) AND (a >= 15))
+ -> Seq Scan on rlp5
+ Filter: ((a > 1) AND (a >= 15))
+ -> Seq Scan on rlp_default
+ Filter: ((a > 1) AND (a >= 15))
+(13 rows)
+
+explain (costs off) select * from rlp where a = 1 and a = 3; /* empty */
+ QUERY PLAN
+--------------------------
+ Result
+ One-Time Filter: false
+(2 rows)
+
+explain (costs off) select * from rlp where (a = 1 and a = 3) or (a > 1 and a = 15);
+ QUERY PLAN
+-------------------------------------------------------------------
+ Append
+ -> Seq Scan on rlp2
+ Filter: (((a = 1) AND (a = 3)) OR ((a > 1) AND (a = 15)))
+ -> Seq Scan on rlp3abcd
+ Filter: (((a = 1) AND (a = 3)) OR ((a > 1) AND (a = 15)))
+ -> Seq Scan on rlp3efgh
+ Filter: (((a = 1) AND (a = 3)) OR ((a > 1) AND (a = 15)))
+ -> Seq Scan on rlp3nullxy
+ Filter: (((a = 1) AND (a = 3)) OR ((a > 1) AND (a = 15)))
+(9 rows)
+
+-- multi-column keys
+create table mc3p (a int, b int, c int) partition by range (a, abs(b), c);
+create table mc3p_default partition of mc3p default;
+create table mc3p0 partition of mc3p for values from (minvalue, minvalue, minvalue) to (1, 1, 1);
+create table mc3p1 partition of mc3p for values from (1, 1, 1) to (10, 5, 10);
+create table mc3p2 partition of mc3p for values from (10, 5, 10) to (10, 10, 10);
+create table mc3p3 partition of mc3p for values from (10, 10, 10) to (10, 10, 20);
+create table mc3p4 partition of mc3p for values from (10, 10, 20) to (10, maxvalue, maxvalue);
+create table mc3p5 partition of mc3p for values from (11, 1, 1) to (20, 10, 10);
+create table mc3p6 partition of mc3p for values from (20, 10, 10) to (20, 20, 20);
+create table mc3p7 partition of mc3p for values from (20, 20, 20) to (maxvalue, maxvalue, maxvalue);
+explain (costs off) select * from mc3p where a = 1;
+ QUERY PLAN
+--------------------------------
+ Append
+ -> Seq Scan on mc3p0
+ Filter: (a = 1)
+ -> Seq Scan on mc3p1
+ Filter: (a = 1)
+ -> Seq Scan on mc3p_default
+ Filter: (a = 1)
+(7 rows)
+
+explain (costs off) select * from mc3p where a = 1 and abs(b) < 1;
+ QUERY PLAN
+--------------------------------------------
+ Append
+ -> Seq Scan on mc3p0
+ Filter: ((a = 1) AND (abs(b) < 1))
+(3 rows)
+
+explain (costs off) select * from mc3p where a = 1 and abs(b) = 1;
+ QUERY PLAN
+--------------------------------------------
+ Append
+ -> Seq Scan on mc3p0
+ Filter: ((a = 1) AND (abs(b) = 1))
+ -> Seq Scan on mc3p1
+ Filter: ((a = 1) AND (abs(b) = 1))
+ -> Seq Scan on mc3p_default
+ Filter: ((a = 1) AND (abs(b) = 1))
+(7 rows)
+
+explain (costs off) select * from mc3p where a = 1 and abs(b) = 1 and c < 8;
+ QUERY PLAN
+--------------------------------------------------------
+ Append
+ -> Seq Scan on mc3p0
+ Filter: ((c < 8) AND (a = 1) AND (abs(b) = 1))
+ -> Seq Scan on mc3p1
+ Filter: ((c < 8) AND (a = 1) AND (abs(b) = 1))
+ -> Seq Scan on mc3p_default
+ Filter: ((c < 8) AND (a = 1) AND (abs(b) = 1))
+(7 rows)
+
+explain (costs off) select * from mc3p where a = 10 and abs(b) between 5 and 35;
+ QUERY PLAN
+-----------------------------------------------------------------
+ Append
+ -> Seq Scan on mc3p1
+ Filter: ((a = 10) AND (abs(b) >= 5) AND (abs(b) <= 35))
+ -> Seq Scan on mc3p2
+ Filter: ((a = 10) AND (abs(b) >= 5) AND (abs(b) <= 35))
+ -> Seq Scan on mc3p3
+ Filter: ((a = 10) AND (abs(b) >= 5) AND (abs(b) <= 35))
+ -> Seq Scan on mc3p4
+ Filter: ((a = 10) AND (abs(b) >= 5) AND (abs(b) <= 35))
+ -> Seq Scan on mc3p_default
+ Filter: ((a = 10) AND (abs(b) >= 5) AND (abs(b) <= 35))
+(11 rows)
+
+explain (costs off) select * from mc3p where a > 10;
+ QUERY PLAN
+--------------------------------
+ Append
+ -> Seq Scan on mc3p5
+ Filter: (a > 10)
+ -> Seq Scan on mc3p6
+ Filter: (a > 10)
+ -> Seq Scan on mc3p7
+ Filter: (a > 10)
+ -> Seq Scan on mc3p_default
+ Filter: (a > 10)
+(9 rows)
+
+explain (costs off) select * from mc3p where a >= 10;
+ QUERY PLAN
+--------------------------------
+ Append
+ -> Seq Scan on mc3p1
+ Filter: (a >= 10)
+ -> Seq Scan on mc3p2
+ Filter: (a >= 10)
+ -> Seq Scan on mc3p3
+ Filter: (a >= 10)
+ -> Seq Scan on mc3p4
+ Filter: (a >= 10)
+ -> Seq Scan on mc3p5
+ Filter: (a >= 10)
+ -> Seq Scan on mc3p6
+ Filter: (a >= 10)
+ -> Seq Scan on mc3p7
+ Filter: (a >= 10)
+ -> Seq Scan on mc3p_default
+ Filter: (a >= 10)
+(17 rows)
+
+explain (costs off) select * from mc3p where a < 10;
+ QUERY PLAN
+--------------------------------
+ Append
+ -> Seq Scan on mc3p0
+ Filter: (a < 10)
+ -> Seq Scan on mc3p1
+ Filter: (a < 10)
+ -> Seq Scan on mc3p_default
+ Filter: (a < 10)
+(7 rows)
+
+explain (costs off) select * from mc3p where a <= 10 and abs(b) < 10;
+ QUERY PLAN
+-----------------------------------------------
+ Append
+ -> Seq Scan on mc3p0
+ Filter: ((a <= 10) AND (abs(b) < 10))
+ -> Seq Scan on mc3p1
+ Filter: ((a <= 10) AND (abs(b) < 10))
+ -> Seq Scan on mc3p2
+ Filter: ((a <= 10) AND (abs(b) < 10))
+ -> Seq Scan on mc3p_default
+ Filter: ((a <= 10) AND (abs(b) < 10))
+(9 rows)
+
+explain (costs off) select * from mc3p where a = 11 and abs(b) = 0; /* empty */
+ QUERY PLAN
+---------------------------------------------
+ Append
+ -> Seq Scan on mc3p_default
+ Filter: ((a = 11) AND (abs(b) = 0))
+(3 rows)
+
+explain (costs off) select * from mc3p where a = 20 and abs(b) = 10 and c = 100;
+ QUERY PLAN
+------------------------------------------------------------
+ Append
+ -> Seq Scan on mc3p6
+ Filter: ((a = 20) AND (c = 100) AND (abs(b) = 10))
+(3 rows)
+
+explain (costs off) select * from mc3p where a > 20;
+ QUERY PLAN
+--------------------------
+ Append
+ -> Seq Scan on mc3p7
+ Filter: (a > 20)
+(3 rows)
+
+explain (costs off) select * from mc3p where a >= 20;
+ QUERY PLAN
+--------------------------------
+ Append
+ -> Seq Scan on mc3p5
+ Filter: (a >= 20)
+ -> Seq Scan on mc3p6
+ Filter: (a >= 20)
+ -> Seq Scan on mc3p7
+ Filter: (a >= 20)
+ -> Seq Scan on mc3p_default
+ Filter: (a >= 20)
+(9 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
+---------------------------------------------------------------------------------------------------------------------------------
+ Append
+ -> Seq Scan on mc3p1
+ 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
+ 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
+ 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)))
+(7 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
+--------------------------------------------------------------------------------------------------------------------------------------------
+ Append
+ -> Seq Scan on mc3p0
+ 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
+ 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
+ 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
+ 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))
+(9 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
+-------------------------------------------------------------------------------------------------------------------------------------------------------
+ Append
+ -> Seq Scan on mc3p0
+ 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
+ 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
+ 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
+ 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
+ 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))
+(11 rows)
+
+explain (costs off) select * from mc3p where a = 1 or abs(b) = 1 or c = 1;
+ QUERY PLAN
+------------------------------------------------------
+ Append
+ -> Seq Scan on mc3p0
+ Filter: ((a = 1) OR (abs(b) = 1) OR (c = 1))
+ -> Seq Scan on mc3p1
+ Filter: ((a = 1) OR (abs(b) = 1) OR (c = 1))
+ -> Seq Scan on mc3p2
+ Filter: ((a = 1) OR (abs(b) = 1) OR (c = 1))
+ -> Seq Scan on mc3p4
+ Filter: ((a = 1) OR (abs(b) = 1) OR (c = 1))
+ -> Seq Scan on mc3p5
+ Filter: ((a = 1) OR (abs(b) = 1) OR (c = 1))
+ -> Seq Scan on mc3p6
+ Filter: ((a = 1) OR (abs(b) = 1) OR (c = 1))
+ -> Seq Scan on mc3p7
+ Filter: ((a = 1) OR (abs(b) = 1) OR (c = 1))
+ -> Seq Scan on mc3p_default
+ Filter: ((a = 1) OR (abs(b) = 1) OR (c = 1))
+(17 rows)
+
+explain (costs off) select * from mc3p where (a = 1 and abs(b) = 1) or (a = 10 and abs(b) = 10);
+ QUERY PLAN
+------------------------------------------------------------------------------
+ Append
+ -> Seq Scan on mc3p0
+ Filter: (((a = 1) AND (abs(b) = 1)) OR ((a = 10) AND (abs(b) = 10)))
+ -> Seq Scan on mc3p1
+ Filter: (((a = 1) AND (abs(b) = 1)) OR ((a = 10) AND (abs(b) = 10)))
+ -> Seq Scan on mc3p2
+ Filter: (((a = 1) AND (abs(b) = 1)) OR ((a = 10) AND (abs(b) = 10)))
+ -> Seq Scan on mc3p3
+ Filter: (((a = 1) AND (abs(b) = 1)) OR ((a = 10) AND (abs(b) = 10)))
+ -> Seq Scan on mc3p4
+ Filter: (((a = 1) AND (abs(b) = 1)) OR ((a = 10) AND (abs(b) = 10)))
+ -> Seq Scan on mc3p_default
+ Filter: (((a = 1) AND (abs(b) = 1)) OR ((a = 10) AND (abs(b) = 10)))
+(13 rows)
+
+explain (costs off) select * from mc3p where (a = 1 and abs(b) = 1) or (a = 10 and abs(b) = 9);
+ QUERY PLAN
+-----------------------------------------------------------------------------
+ Append
+ -> Seq Scan on mc3p0
+ Filter: (((a = 1) AND (abs(b) = 1)) OR ((a = 10) AND (abs(b) = 9)))
+ -> Seq Scan on mc3p1
+ Filter: (((a = 1) AND (abs(b) = 1)) OR ((a = 10) AND (abs(b) = 9)))
+ -> Seq Scan on mc3p2
+ Filter: (((a = 1) AND (abs(b) = 1)) OR ((a = 10) AND (abs(b) = 9)))
+ -> Seq Scan on mc3p_default
+ Filter: (((a = 1) AND (abs(b) = 1)) OR ((a = 10) AND (abs(b) = 9)))
+(9 rows)
+
+drop table lp, coll_pruning, rlp, mc3p;
diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule
index aa5e6af621..38dfe618b5 100644
--- a/src/test/regress/parallel_schedule
+++ b/src/test/regress/parallel_schedule
@@ -60,7 +60,7 @@ test: create_index create_view
# ----------
# Another group of parallel tests
# ----------
-test: create_aggregate create_function_3 create_cast constraints triggers inherit create_table_like typed_table vacuum drop_if_exists updatable_views rolenames roleattributes create_am hash_func
+test: create_aggregate create_function_3 create_cast constraints triggers inherit create_table_like typed_table vacuum drop_if_exists updatable_views rolenames roleattributes create_am hash_func partition
# ----------
# sanity_check does a vacuum, affecting the sort order of SELECT *
diff --git a/src/test/regress/serial_schedule b/src/test/regress/serial_schedule
index 3866314a92..17d88e5ca9 100644
--- a/src/test/regress/serial_schedule
+++ b/src/test/regress/serial_schedule
@@ -71,6 +71,7 @@ test: create_cast
test: constraints
test: triggers
test: inherit
+test: partition
test: create_table_like
test: typed_table
test: vacuum
diff --git a/src/test/regress/sql/partition.sql b/src/test/regress/sql/partition.sql
new file mode 100644
index 0000000000..d716fee69f
--- /dev/null
+++ b/src/test/regress/sql/partition.sql
@@ -0,0 +1,105 @@
+--
+-- Test partitioning planner code
+--
+create table lp (a char) partition by list (a);
+create table lp_default partition of lp default;
+create table lp_ef partition of lp for values in ('e', 'f');
+create table lp_ad partition of lp for values in ('a', 'd');
+create table lp_bc partition of lp for values in ('b', 'c');
+create table lp_g partition of lp for values in ('g');
+create table lp_null partition of lp for values in (null);
+explain (costs off) select * from lp;
+explain (costs off) select * from lp where a > 'a' and a < 'd';
+explain (costs off) select * from lp where a > 'a' and a <= 'd';
+explain (costs off) select * from lp where a = 'a';
+explain (costs off) select * from lp where 'a' = a; /* commutates */
+explain (costs off) select * from lp where a is not null;
+explain (costs off) select * from lp where a is null;
+explain (costs off) select * from lp where a = 'a' or a = 'c';
+explain (costs off) select * from lp where a is not null and (a = 'a' or a = 'c');
+explain (costs off) select * from lp where a <> 'g';
+explain (costs off) select * from lp where a <> 'a' and a <> 'd';
+explain (costs off) select * from lp where a not in ('a', 'd');
+
+-- collation matches the partitioning collation, pruning works
+create table coll_pruning (a text collate "C") partition by list (a);
+create table coll_pruning_a partition of coll_pruning for values in ('a');
+create table coll_pruning_b partition of coll_pruning for values in ('b');
+create table coll_pruning_def partition of coll_pruning default;
+explain (costs off) select * from coll_pruning where a collate "C" = 'a' collate "C";
+-- collation doesn't match the partitioning collation, no pruning occurs
+explain (costs off) select * from coll_pruning where a collate "POSIX" = 'a' collate "POSIX";
+
+create table rlp (a int, b varchar) partition by range (a);
+create table rlp_default partition of rlp default;
+create table rlp1 partition of rlp for values from (minvalue) to (1);
+create table rlp2 partition of rlp for values from (1) to (10);
+
+create table rlp3 (b varchar, a int) partition by list (b varchar_ops);
+create table rlp3abcd partition of rlp3 for values in ('ab', 'cd');
+create table rlp3efgh partition of rlp3 for values in ('ef', 'gh');
+create table rlp3nullxy partition of rlp3 for values in (null, 'xy');
+alter table rlp attach partition rlp3 for values from (15) to (20);
+
+create table rlp4 partition of rlp for values from (20) to (30);
+create table rlp5 partition of rlp for values from (31) to (maxvalue);
+
+explain (costs off) select * from rlp where a < 1;
+explain (costs off) select * from rlp where 1 > a; /* commutates */
+explain (costs off) select * from rlp where a <= 1;
+explain (costs off) select * from rlp where a = 1;
+explain (costs off) select * from rlp where a = 1::bigint; /* same as above */
+explain (costs off) select * from rlp where a = 1::numeric; /* no pruning */
+explain (costs off) select * from rlp where a <= 10;
+explain (costs off) select * from rlp where a > 10;
+explain (costs off) select * from rlp where a < 15;
+explain (costs off) select * from rlp where a <= 15;
+explain (costs off) select * from rlp where a > 15 and b = 'ab';
+explain (costs off) select * from rlp where a = 16 and b is null;
+explain (costs off) select * from rlp where a = 16 and b is not null;
+explain (costs off) select * from rlp where a is null; /* while we're on nulls */
+explain (costs off) select * from rlp where a > 30;
+explain (costs off) select * from rlp where a = 30; /* only default is scanned */
+explain (costs off) select * from rlp where a <= 31;
+explain (costs off) select * from rlp where a = 1 or a = 7;
+explain (costs off) select * from rlp where a = 1 or b = 'ab';
+
+-- redundant clauses are eliminated
+explain (costs off) select * from rlp where a > 1 and a = 10; /* only default */
+explain (costs off) select * from rlp where a > 1 and a >=15; /* rlp3 onwards, including default */
+explain (costs off) select * from rlp where a = 1 and a = 3; /* empty */
+explain (costs off) select * from rlp where (a = 1 and a = 3) or (a > 1 and a = 15);
+
+-- multi-column keys
+create table mc3p (a int, b int, c int) partition by range (a, abs(b), c);
+create table mc3p_default partition of mc3p default;
+create table mc3p0 partition of mc3p for values from (minvalue, minvalue, minvalue) to (1, 1, 1);
+create table mc3p1 partition of mc3p for values from (1, 1, 1) to (10, 5, 10);
+create table mc3p2 partition of mc3p for values from (10, 5, 10) to (10, 10, 10);
+create table mc3p3 partition of mc3p for values from (10, 10, 10) to (10, 10, 20);
+create table mc3p4 partition of mc3p for values from (10, 10, 20) to (10, maxvalue, maxvalue);
+create table mc3p5 partition of mc3p for values from (11, 1, 1) to (20, 10, 10);
+create table mc3p6 partition of mc3p for values from (20, 10, 10) to (20, 20, 20);
+create table mc3p7 partition of mc3p for values from (20, 20, 20) to (maxvalue, maxvalue, maxvalue);
+
+explain (costs off) select * from mc3p where a = 1;
+explain (costs off) select * from mc3p where a = 1 and abs(b) < 1;
+explain (costs off) select * from mc3p where a = 1 and abs(b) = 1;
+explain (costs off) select * from mc3p where a = 1 and abs(b) = 1 and c < 8;
+explain (costs off) select * from mc3p where a = 10 and abs(b) between 5 and 35;
+explain (costs off) select * from mc3p where a > 10;
+explain (costs off) select * from mc3p where a >= 10;
+explain (costs off) select * from mc3p where a < 10;
+explain (costs off) select * from mc3p where a <= 10 and abs(b) < 10;
+explain (costs off) select * from mc3p where a = 11 and abs(b) = 0; /* empty */
+explain (costs off) select * from mc3p where a = 20 and abs(b) = 10 and c = 100;
+explain (costs off) select * from mc3p where a > 20;
+explain (costs off) select * from mc3p where a >= 20;
+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);
+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;
+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;
+explain (costs off) select * from mc3p where a = 1 or abs(b) = 1 or c = 1;
+explain (costs off) select * from mc3p where (a = 1 and abs(b) = 1) or (a = 10 and abs(b) = 10);
+explain (costs off) select * from mc3p where (a = 1 and abs(b) = 1) or (a = 10 and abs(b) = 9);
+
+drop table lp, coll_pruning, rlp, mc3p;
--
2.11.0