Hi,
While looking at a partition pruning bug [1], I noticed something that
started to feel like a regression:
Setup:
create table p (a int) partition by list (a);
create table p1 partition of p for values in (1);
In PG 10:
set constraint_exclusion to on;
explain select * from p1 where a = 2;
QUERY PLAN
──────────────────────────────────────────
Result (cost=0.00..0.00 rows=0 width=4)
One-Time Filter: false
(2 rows)
In PG 11 (and HEAD):
set constraint_exclusion to on;
explain select * from p1 where a = 2;
QUERY PLAN
────────────────────────────────────────────────────
Seq Scan on p1 (cost=0.00..41.88 rows=13 width=4)
Filter: (a = 2)
(2 rows)
That's because get_relation_constraints() no longer (as of PG 11) includes
the partition constraint for SELECT queries. But that's based on an
assumption that partitions are always accessed via parent, so partition
pruning would make loading the partition constraint unnecessary. That's
not always true, as shown in the above example.
Should we fix that? I'm attaching a patch here.
Thanks,
Amit
[1]
https://www.postgresql.org/message-id/00e601d4ca86$932b8bc0$b982a340$@lab.ntt.co.jp
From 97dc0a032426a798d4dbc957783168567ed285ed Mon Sep 17 00:00:00 2001
From: amit <amitlangot...@gmail.com>
Date: Wed, 20 Mar 2019 13:27:37 +0900
Subject: [PATCH v1] Fix planner to load partition constraint in some cases
For select queries that access a partition directly, we should
load the partition constraint so that constraint exclusion can
use it to exclude the partition based on query quals. When
partitions are accessed indirectly via the parent table, it's
unnecessary to load the partition constraint, because partition
pruning will only select those partitions whose partition
constraint satisfies query quals, making it unnecessary to run
constraint exclusion on partitions.
---
src/backend/optimizer/util/plancat.c | 15 ++++++---
src/test/regress/expected/partition_prune.out | 44 +++++++++++++++++++++++++++
src/test/regress/sql/partition_prune.sql | 20 ++++++++++++
3 files changed, 75 insertions(+), 4 deletions(-)
diff --git a/src/backend/optimizer/util/plancat.c
b/src/backend/optimizer/util/plancat.c
index 30f4dc151b..a8ba586565 100644
--- a/src/backend/optimizer/util/plancat.c
+++ b/src/backend/optimizer/util/plancat.c
@@ -1269,11 +1269,18 @@ get_relation_constraints(PlannerInfo *root,
/*
* Append partition predicates, if any.
*
- * For selects, partition pruning uses the parent table's partition
bound
- * descriptor, instead of constraint exclusion which is driven by the
- * individual partition's partition constraint.
+ * For selects, we only need those if the partition is directly
mentioned
+ * in the query, that is not via parent. In case of the latter,
partition
+ * pruning, which uses the parent table's partition bound descriptor,
+ * ensures that we only consider partitions whose partition constraint
+ * satisfy the query quals (or, the two don't contradict each other), so
+ * loading them is pointless.
+ *
+ * For updates and deletes, we always need those for performing
partition
+ * pruning using constraint exclusion, but, only if pruning is enabled.
*/
- if (enable_partition_pruning && root->parse->commandType != CMD_SELECT)
+ if ((root->parse->commandType == CMD_SELECT && !IS_OTHER_REL(rel)) ||
+ (root->parse->commandType != CMD_SELECT &&
enable_partition_pruning))
{
List *pcqual = RelationGetPartitionQual(relation);
diff --git a/src/test/regress/expected/partition_prune.out
b/src/test/regress/expected/partition_prune.out
index 30946f77b6..23f042d0ba 100644
--- a/src/test/regress/expected/partition_prune.out
+++ b/src/test/regress/expected/partition_prune.out
@@ -3638,3 +3638,47 @@ select * from listp where a = (select 2) and b <> 10;
(5 rows)
drop table listp;
+-- check that a directly accessed in a query is excluded with
+-- constraint_exclusion = on
+create table part_excl_test (a int) partition by list (a);
+create table part_excl_test1 partition of part_excl_test for values in (1);
+create table part_excl_test2 partition of part_excl_test for values in (2)
partition by list (a);
+create table part_excl_test22 partition of part_excl_test2 for values in (2);
+-- first off, turn off partition pruning, so that it doesn't interfere
+set enable_partition_pruning to off;
+-- constraint exclusion doesn't apply
+set constraint_exclusion to 'partition';
+explain (costs off) select * from part_excl_test1 where a = 2;
+ QUERY PLAN
+-----------------------------
+ Seq Scan on part_excl_test1
+ Filter: (a = 2)
+(2 rows)
+
+explain (costs off) select * from part_excl_test2 where a = 1;
+ QUERY PLAN
+------------------------------------
+ Append
+ -> Seq Scan on part_excl_test22
+ Filter: (a = 1)
+(3 rows)
+
+-- constraint exclusion applies
+set constraint_exclusion to 'on';
+explain (costs off) select * from part_excl_test1 where a = 2;
+ QUERY PLAN
+--------------------------
+ Result
+ One-Time Filter: false
+(2 rows)
+
+explain (costs off) select * from part_excl_test2 where a = 1;
+ QUERY PLAN
+--------------------------
+ Result
+ One-Time Filter: false
+(2 rows)
+
+reset constraint_exclusion;
+reset enable_partition_pruning;
+drop table part_excl_test;
diff --git a/src/test/regress/sql/partition_prune.sql
b/src/test/regress/sql/partition_prune.sql
index dc327caffd..164950ddab 100644
--- a/src/test/regress/sql/partition_prune.sql
+++ b/src/test/regress/sql/partition_prune.sql
@@ -986,3 +986,23 @@ explain (analyze, costs off, summary off, timing off)
select * from listp where a = (select 2) and b <> 10;
drop table listp;
+
+-- check that a directly accessed in a query is excluded with
+-- constraint_exclusion = on
+create table part_excl_test (a int) partition by list (a);
+create table part_excl_test1 partition of part_excl_test for values in (1);
+create table part_excl_test2 partition of part_excl_test for values in (2)
partition by list (a);
+create table part_excl_test22 partition of part_excl_test2 for values in (2);
+-- first off, turn off partition pruning, so that it doesn't interfere
+set enable_partition_pruning to off;
+-- constraint exclusion doesn't apply
+set constraint_exclusion to 'partition';
+explain (costs off) select * from part_excl_test1 where a = 2;
+explain (costs off) select * from part_excl_test2 where a = 1;
+-- constraint exclusion applies
+set constraint_exclusion to 'on';
+explain (costs off) select * from part_excl_test1 where a = 2;
+explain (costs off) select * from part_excl_test2 where a = 1;
+reset constraint_exclusion;
+reset enable_partition_pruning;
+drop table part_excl_test;
--
2.11.0
