On 2019/03/30 0:29, Tom Lane wrote:
> Amit Langote <[email protected]> writes:
>> Finally, it's not in the patch, but how about visiting
>> get_relation_constraints() for revising this block of code:
>
> That seems like probably an independent patch --- do you want to write it?
Here is that patch.
It revises get_relation_constraints() such that the partition constraint
is loaded in only the intended cases. To summarize:
* PG 11 currently misses one such intended case (select * from partition)
causing a *bug* that constraint exclusion fails to exclude the partition
with constraint_exclusion = on
* HEAD loads the partition constraint even in some cases where 428b260f87
rendered doing that unnecessary
Thanks,
Amit
diff --git a/src/backend/optimizer/util/plancat.c
b/src/backend/optimizer/util/plancat.c
index 31a3784536..b7ae063585 100644
--- a/src/backend/optimizer/util/plancat.c
+++ b/src/backend/optimizer/util/plancat.c
@@ -1250,11 +1250,15 @@ 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.
+ * If the partition is accessed indirectly via its parent table,
partition
+ * pruning is performed with the parent table's partition bound, so
there
+ * is no need to include the partition constraint in that case.
However,
+ * if the partition is referenced directly in the query and we're not
+ * being called from inheritance_planner(), then no partition pruning
+ * would have occurred, so we'll include it in that case.
*/
- if (enable_partition_pruning && root->parse->commandType != CMD_SELECT)
+ if (rel->reloptkind == RELOPT_BASEREL &&
+ root->inhTargetKind == INHKIND_NONE)
{
List *pcqual = RelationGetPartitionQual(relation);
diff --git a/src/test/regress/expected/partition_prune.out
b/src/test/regress/expected/partition_prune.out
index 7806ba1d47..0bc0ed8042 100644
--- a/src/test/regress/expected/partition_prune.out
+++ b/src/test/regress/expected/partition_prune.out
@@ -3643,4 +3643,44 @@ select * from listp where a = (select 2) and b <> 10;
-> Result (never executed)
(4 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;
+-- constraint exclusion doesn't apply
+set constraint_exclusion to 'partition';
+explain (costs off) select * from listp1 where a = 2;
+ QUERY PLAN
+--------------------
+ Seq Scan on listp1
+ Filter: (a = 2)
+(2 rows)
+
+explain (costs off) select * from listp2 where a = 1;
+ QUERY PLAN
+-----------------------
+ Seq Scan on listp2_10
+ Filter: (a = 1)
+(2 rows)
+
+-- constraint exclusion applies
+set constraint_exclusion to 'on';
+explain (costs off) select * from listp1 where a = 2;
+ QUERY PLAN
+--------------------------
+ Result
+ One-Time Filter: false
+(2 rows)
+
+explain (costs off) select * from listp2 where a = 1;
+ QUERY PLAN
+--------------------------
+ Result
+ One-Time Filter: false
+(2 rows)
+
+reset constraint_exclusion;
+reset enable_partition_pruning;
drop table listp;
diff --git a/src/test/regress/sql/partition_prune.sql
b/src/test/regress/sql/partition_prune.sql
index 2e4d2b483d..cc3c497238 100644
--- a/src/test/regress/sql/partition_prune.sql
+++ b/src/test/regress/sql/partition_prune.sql
@@ -990,4 +990,22 @@ create table listp2_10 partition of listp2 for values in
(10);
explain (analyze, costs off, summary off, timing off)
select * from listp where a = (select 2) and b <> 10;
+--
+-- 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;
+
+-- constraint exclusion doesn't apply
+set constraint_exclusion to 'partition';
+explain (costs off) select * from listp1 where a = 2;
+explain (costs off) select * from listp2 where a = 1;
+-- constraint exclusion applies
+set constraint_exclusion to 'on';
+explain (costs off) select * from listp1 where a = 2;
+explain (costs off) select * from listp2 where a = 1;
+reset constraint_exclusion;
+reset enable_partition_pruning;
drop table listp;
diff --git a/src/backend/optimizer/util/plancat.c
b/src/backend/optimizer/util/plancat.c
index 8369e3ad62..8428fe37bb 100644
--- a/src/backend/optimizer/util/plancat.c
+++ b/src/backend/optimizer/util/plancat.c
@@ -1269,10 +1269,14 @@ 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.
+ * descriptor, so there's no need to include the partition constraint
for
+ * this case. However, if the partition is referenced directly in the
+ * query then no partition pruning will occur, so we'll include it in
that
+ * case.
*/
- if (enable_partition_pruning && root->parse->commandType != CMD_SELECT)
+ if ((root->parse->commandType != CMD_SELECT &&
enable_partition_pruning) ||
+ (root->parse->commandType == CMD_SELECT &&
+ rel->reloptkind == RELOPT_BASEREL))
{
List *pcqual = RelationGetPartitionQual(relation);
diff --git a/src/test/regress/expected/partition_prune.out
b/src/test/regress/expected/partition_prune.out
index 79e29e762b..02f8ceaa26 100644
--- a/src/test/regress/expected/partition_prune.out
+++ b/src/test/regress/expected/partition_prune.out
@@ -3417,4 +3417,45 @@ select * from listp where a = (select 2) and b <> 10;
Filter: ((b <> 10) AND (a = $0))
(5 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;
+-- constraint exclusion doesn't apply
+set constraint_exclusion to 'partition';
+explain (costs off) select * from listp1 where a = 2;
+ QUERY PLAN
+--------------------
+ Seq Scan on listp1
+ Filter: (a = 2)
+(2 rows)
+
+explain (costs off) select * from listp2 where a = 1;
+ QUERY PLAN
+-----------------------------
+ Append
+ -> Seq Scan on listp2_10
+ Filter: (a = 1)
+(3 rows)
+
+-- constraint exclusion applies
+set constraint_exclusion to 'on';
+explain (costs off) select * from listp1 where a = 2;
+ QUERY PLAN
+--------------------------
+ Result
+ One-Time Filter: false
+(2 rows)
+
+explain (costs off) select * from listp2 where a = 1;
+ QUERY PLAN
+--------------------------
+ Result
+ One-Time Filter: false
+(2 rows)
+
+reset constraint_exclusion;
+reset enable_partition_pruning;
drop table listp;
diff --git a/src/test/regress/sql/partition_prune.sql
b/src/test/regress/sql/partition_prune.sql
index 6aecf25f46..cf56898e59 100644
--- a/src/test/regress/sql/partition_prune.sql
+++ b/src/test/regress/sql/partition_prune.sql
@@ -899,4 +899,22 @@ create table listp2_10 partition of listp2 for values in
(10);
explain (analyze, costs off, summary off, timing off)
select * from listp where a = (select 2) and b <> 10;
+--
+-- 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;
+
+-- constraint exclusion doesn't apply
+set constraint_exclusion to 'partition';
+explain (costs off) select * from listp1 where a = 2;
+explain (costs off) select * from listp2 where a = 1;
+-- constraint exclusion applies
+set constraint_exclusion to 'on';
+explain (costs off) select * from listp1 where a = 2;
+explain (costs off) select * from listp2 where a = 1;
+reset constraint_exclusion;
+reset enable_partition_pruning;
drop table listp;
