Hi.
While working on the patch for partition pruning for declarative
partitioned tables, I noticed that default range partition will fail to be
included in a plan in certain cases due to pruning by constraint exclusion.
Consider a multi-column range-partitioned table:
create table mc2p (a int, b int) partition by range (a, b);
create table mc2p_default partition of mc2p default;
create table mc2p0 partition of mc2p
for values from (minvalue, minvalue) to (1, 1);
create table mc2p2 partition of mc2p
for values from (1, 1) to (maxvalue, maxvalue);
-- add a row with null b and check that it enters the default partition
insert into mc2p values (2);
INSERT 0 1
select tableoid::regclass, * from mc2p;
tableoid | a | b
--------------+---+---
mc2p_default | 2 |
(1 row)
-- but selecting like this doesn't work
select tableoid::regclass, * from mc2p where a = 2;
tableoid | a | b
----------+---+---
(0 rows)
because:
explain (costs off) select tableoid::regclass, * from mc2p where a = 2;
QUERY PLAN
--------------------------------------
Result
-> Append
-> Seq Scan on mc2p2
Filter: (a = 2)
(4 rows)
If you look at the default partition's constraint, which is as follows:
NOT (
((a < 1) OR ((a = 1) AND (b < 1)))
OR
((a > 1) OR ((a = 1) AND (b >= 1)))
)
you'll notice that it doesn't explicitly say that the default partition
allows rows where a is null or b is null or both are null. Given that,
constraint exclusion will end up concluding that the default partition's
constraint is refuted by a = 2.
The attached will make the constraint to look like:
NOT (
a IS NOT NULL
OR
b IS NOT NULL
((a < 1) OR ((a = 1) AND (b < 1)))
OR
((a > 1) OR ((a = 1) AND (b >= 1)))
)
Now since b IS NULL (which, btw, is NOT (b IS NOT NULL)) fails to be
refuted, as a whole, the whole constraint is not refuted. So, we get the
correct result:
select tableoid::regclass, * from mc2p where a = 2;
tableoid | a | b
--------------+---+---
mc2p_default | 2 |
(1 row)
explain (costs off) select tableoid::regclass, * from mc2p where a = 2;
QUERY PLAN
--------------------------------------
Result
-> Append
-> Seq Scan on mc2p2
Filter: (a = 2)
-> Seq Scan on mc2p_default
Filter: (a = 2)
(6 rows)
Attached patches. Thoughts?
Thanks,
Amit
From 150f2d75313a7cd262e099cb75b24510ca588f44 Mon Sep 17 00:00:00 2001
From: amit <[email protected]>
Date: Fri, 17 Nov 2017 14:00:42 +0900
Subject: [PATCH 1/2] Add default partition case in inheritance testing
---
src/test/regress/expected/inherit.out | 29 +++++++++++++++++++----------
src/test/regress/sql/inherit.sql | 9 +++++----
2 files changed, 24 insertions(+), 14 deletions(-)
diff --git a/src/test/regress/expected/inherit.out
b/src/test/regress/expected/inherit.out
index c698faff2f..a202caeb25 100644
--- a/src/test/regress/expected/inherit.out
+++ b/src/test/regress/expected/inherit.out
@@ -1853,13 +1853,14 @@ drop table range_list_parted;
-- check that constraint exclusion is able to cope with the partition
-- constraint emitted for multi-column range partitioned tables
create table mcrparted (a int, b int, c int) partition by range (a, abs(b), c);
+create table mcrparted_def partition of mcrparted default;
create table mcrparted0 partition of mcrparted for values from (minvalue,
minvalue, minvalue) to (1, 1, 1);
create table mcrparted1 partition of mcrparted for values from (1, 1, 1) to
(10, 5, 10);
create table mcrparted2 partition of mcrparted for values from (10, 5, 10) to
(10, 10, 10);
create table mcrparted3 partition of mcrparted for values from (11, 1, 1) to
(20, 10, 10);
create table mcrparted4 partition of mcrparted for values from (20, 10, 10) to
(20, 20, 20);
create table mcrparted5 partition of mcrparted for values from (20, 20, 20) to
(maxvalue, maxvalue, maxvalue);
-explain (costs off) select * from mcrparted where a = 0; -- scans
mcrparted0
+explain (costs off) select * from mcrparted where a = 0; -- scans
mcrparted0, mcrparted_def
QUERY PLAN
------------------------------
Append
@@ -1867,7 +1868,7 @@ explain (costs off) select * from mcrparted where a = 0;
-- scans mcrparted0
Filter: (a = 0)
(3 rows)
-explain (costs off) select * from mcrparted where a = 10 and abs(b) < 5;
-- scans mcrparted1
+explain (costs off) select * from mcrparted where a = 10 and abs(b) < 5;
-- scans mcrparted1, mcrparted_def
QUERY PLAN
---------------------------------------------
Append
@@ -1875,7 +1876,7 @@ explain (costs off) select * from mcrparted where a = 10
and abs(b) < 5; -- scan
Filter: ((a = 10) AND (abs(b) < 5))
(3 rows)
-explain (costs off) select * from mcrparted where a = 10 and abs(b) = 5;
-- scans mcrparted1, mcrparted2
+explain (costs off) select * from mcrparted where a = 10 and abs(b) = 5;
-- scans mcrparted1, mcrparted2, mcrparted_def
QUERY PLAN
---------------------------------------------
Append
@@ -1883,11 +1884,13 @@ explain (costs off) select * from mcrparted where a =
10 and abs(b) = 5; -- scan
Filter: ((a = 10) AND (abs(b) = 5))
-> Seq Scan on mcrparted2
Filter: ((a = 10) AND (abs(b) = 5))
-(5 rows)
+ -> Seq Scan on mcrparted_def
+ Filter: ((a = 10) AND (abs(b) = 5))
+(7 rows)
explain (costs off) select * from mcrparted where abs(b) = 5; -- scans all
partitions
- QUERY PLAN
-------------------------------
+ QUERY PLAN
+---------------------------------
Append
-> Seq Scan on mcrparted0
Filter: (abs(b) = 5)
@@ -1899,7 +1902,9 @@ explain (costs off) select * from mcrparted where abs(b)
= 5; -- scans all parti
Filter: (abs(b) = 5)
-> Seq Scan on mcrparted5
Filter: (abs(b) = 5)
-(11 rows)
+ -> Seq Scan on mcrparted_def
+ Filter: (abs(b) = 5)
+(13 rows)
explain (costs off) select * from mcrparted where a > -1; -- scans all
partitions
QUERY PLAN
@@ -1917,7 +1922,9 @@ explain (costs off) select * from mcrparted where a > -1;
-- scans all partition
Filter: (a > '-1'::integer)
-> Seq Scan on mcrparted5
Filter: (a > '-1'::integer)
-(13 rows)
+ -> Seq Scan on mcrparted_def
+ Filter: (a > '-1'::integer)
+(15 rows)
explain (costs off) select * from mcrparted where a = 20 and abs(b) = 10 and c
> 10; -- scans mcrparted4
QUERY PLAN
@@ -1927,7 +1934,7 @@ explain (costs off) select * from mcrparted where a = 20
and abs(b) = 10 and c >
Filter: ((c > 10) AND (a = 20) AND (abs(b) = 10))
(3 rows)
-explain (costs off) select * from mcrparted where a = 20 and c > 20; -- scans
mcrparted3, mcrparte4, mcrparte5
+explain (costs off) select * from mcrparted where a = 20 and c > 20; -- scans
mcrparted3, mcrparte4, mcrparte5, mcrparted_def
QUERY PLAN
-----------------------------------------
Append
@@ -1937,7 +1944,9 @@ explain (costs off) select * from mcrparted where a = 20
and c > 20; -- scans mc
Filter: ((c > 20) AND (a = 20))
-> Seq Scan on mcrparted5
Filter: ((c > 20) AND (a = 20))
-(7 rows)
+ -> Seq Scan on mcrparted_def
+ Filter: ((c > 20) AND (a = 20))
+(9 rows)
drop table mcrparted;
-- check that partitioned table Appends cope with being referenced in
diff --git a/src/test/regress/sql/inherit.sql b/src/test/regress/sql/inherit.sql
index 169d0dc0f5..c71febffc2 100644
--- a/src/test/regress/sql/inherit.sql
+++ b/src/test/regress/sql/inherit.sql
@@ -664,19 +664,20 @@ drop table range_list_parted;
-- check that constraint exclusion is able to cope with the partition
-- constraint emitted for multi-column range partitioned tables
create table mcrparted (a int, b int, c int) partition by range (a, abs(b), c);
+create table mcrparted_def partition of mcrparted default;
create table mcrparted0 partition of mcrparted for values from (minvalue,
minvalue, minvalue) to (1, 1, 1);
create table mcrparted1 partition of mcrparted for values from (1, 1, 1) to
(10, 5, 10);
create table mcrparted2 partition of mcrparted for values from (10, 5, 10) to
(10, 10, 10);
create table mcrparted3 partition of mcrparted for values from (11, 1, 1) to
(20, 10, 10);
create table mcrparted4 partition of mcrparted for values from (20, 10, 10) to
(20, 20, 20);
create table mcrparted5 partition of mcrparted for values from (20, 20, 20) to
(maxvalue, maxvalue, maxvalue);
-explain (costs off) select * from mcrparted where a = 0; -- scans
mcrparted0
-explain (costs off) select * from mcrparted where a = 10 and abs(b) < 5;
-- scans mcrparted1
-explain (costs off) select * from mcrparted where a = 10 and abs(b) = 5;
-- scans mcrparted1, mcrparted2
+explain (costs off) select * from mcrparted where a = 0; -- scans
mcrparted0, mcrparted_def
+explain (costs off) select * from mcrparted where a = 10 and abs(b) < 5;
-- scans mcrparted1, mcrparted_def
+explain (costs off) select * from mcrparted where a = 10 and abs(b) = 5;
-- scans mcrparted1, mcrparted2, mcrparted_def
explain (costs off) select * from mcrparted where abs(b) = 5; -- scans all
partitions
explain (costs off) select * from mcrparted where a > -1; -- scans all
partitions
explain (costs off) select * from mcrparted where a = 20 and abs(b) = 10 and c
> 10; -- scans mcrparted4
-explain (costs off) select * from mcrparted where a = 20 and c > 20; -- scans
mcrparted3, mcrparte4, mcrparte5
+explain (costs off) select * from mcrparted where a = 20 and c > 20; -- scans
mcrparted3, mcrparte4, mcrparte5, mcrparted_def
drop table mcrparted;
-- check that partitioned table Appends cope with being referenced in
--
2.11.0
From 3311e6b20c287a02c69ddee57d21e7b3cfd3de61 Mon Sep 17 00:00:00 2001
From: amit <[email protected]>
Date: Tue, 31 Oct 2017 16:26:55 +0900
Subject: [PATCH 2/2] Tweak default range partition's constraint a little
When using as a predicate, it's useful for it explicitly say that
the default range partition might contain nulls, because non-default
range partitions can't.
---
src/backend/catalog/partition.c | 29 +++++++++++++++++++++++------
src/test/regress/expected/inherit.out | 12 ++++++++----
src/test/regress/expected/update.out | 2 +-
3 files changed, 32 insertions(+), 11 deletions(-)
diff --git a/src/backend/catalog/partition.c b/src/backend/catalog/partition.c
index ce29ba2eda..d46592c06e 100644
--- a/src/backend/catalog/partition.c
+++ b/src/backend/catalog/partition.c
@@ -2133,12 +2133,29 @@ get_qual_for_range(Relation parent, PartitionBoundSpec
*spec,
if (or_expr_args != NIL)
{
- /* OR all the non-default partition constraints; then
negate it */
- result = lappend(result,
-
list_length(or_expr_args) > 1
- ?
makeBoolExpr(OR_EXPR, or_expr_args, -1)
- :
linitial(or_expr_args));
- result = list_make1(makeBoolExpr(NOT_EXPR, result, -1));
+ Expr *other_parts_constr;
+
+ /*
+ * Combine the constraints obtained for non-default
partitions
+ * using OR. As requested, each of the OR's args
doesn't include
+ * the NOT NULL test for partition keys (which is to
avoid its
+ * useless repetition). Add the same now.
+ */
+ other_parts_constr =
+ makeBoolExpr(AND_EXPR,
+
lappend(get_range_nulltest(key),
+
list_length(or_expr_args) > 1
+
? makeBoolExpr(OR_EXPR, or_expr_args,
+
-1)
+
: linitial(or_expr_args)),
+ -1);
+
+ /*
+ * Finally, the default partition contains everything
*NOT*
+ * contained in the non-default partitions.
+ */
+ result = list_make1(makeBoolExpr(NOT_EXPR,
+
list_make1(other_parts_constr), -1));
}
return result;
diff --git a/src/test/regress/expected/inherit.out
b/src/test/regress/expected/inherit.out
index a202caeb25..fac7b62f9c 100644
--- a/src/test/regress/expected/inherit.out
+++ b/src/test/regress/expected/inherit.out
@@ -1861,12 +1861,14 @@ create table mcrparted3 partition of mcrparted for
values from (11, 1, 1) to (20
create table mcrparted4 partition of mcrparted for values from (20, 10, 10) to
(20, 20, 20);
create table mcrparted5 partition of mcrparted for values from (20, 20, 20) to
(maxvalue, maxvalue, maxvalue);
explain (costs off) select * from mcrparted where a = 0; -- scans
mcrparted0, mcrparted_def
- QUERY PLAN
-------------------------------
+ QUERY PLAN
+---------------------------------
Append
-> Seq Scan on mcrparted0
Filter: (a = 0)
-(3 rows)
+ -> Seq Scan on mcrparted_def
+ Filter: (a = 0)
+(5 rows)
explain (costs off) select * from mcrparted where a = 10 and abs(b) < 5;
-- scans mcrparted1, mcrparted_def
QUERY PLAN
@@ -1874,7 +1876,9 @@ explain (costs off) select * from mcrparted where a = 10
and abs(b) < 5; -- scan
Append
-> Seq Scan on mcrparted1
Filter: ((a = 10) AND (abs(b) < 5))
-(3 rows)
+ -> Seq Scan on mcrparted_def
+ Filter: ((a = 10) AND (abs(b) < 5))
+(5 rows)
explain (costs off) select * from mcrparted where a = 10 and abs(b) = 5;
-- scans mcrparted1, mcrparted2, mcrparted_def
QUERY PLAN
diff --git a/src/test/regress/expected/update.out
b/src/test/regress/expected/update.out
index a4fe96112e..b69ceaa75e 100644
--- a/src/test/regress/expected/update.out
+++ b/src/test/regress/expected/update.out
@@ -227,7 +227,7 @@ create table part_def partition of range_parted default;
a | text | | | | extended | |
b | integer | | | | plain | |
Partition of: range_parted DEFAULT
-Partition constraint: (NOT (((a = 'a'::text) AND (b >= 1) AND (b < 10)) OR ((a
= 'a'::text) AND (b >= 10) AND (b < 20)) OR ((a = 'b'::text) AND (b >= 1) AND
(b < 10)) OR ((a = 'b'::text) AND (b >= 10) AND (b < 20))))
+Partition constraint: (NOT ((a IS NOT NULL) AND (b IS NOT NULL) AND (((a =
'a'::text) AND (b >= 1) AND (b < 10)) OR ((a = 'a'::text) AND (b >= 10) AND (b
< 20)) OR ((a = 'b'::text) AND (b >= 1) AND (b < 10)) OR ((a = 'b'::text) AND
(b >= 10) AND (b < 20)))))
insert into range_parted values ('c', 9);
-- ok
--
2.11.0
