On 2019/04/02 14:50, Amit Langote wrote:
> Attached patch is only for HEAD this time. I'll post one for PG 11 (if
> you'd like) once we reach consensus on the best thing to do here is.
While we're on the topic of the relation between constraint exclusion and
partition pruning, I'd like to (re-) propose this documentation update
patch. The partitioning chapter in ddl.sgml says update/delete of
partitioned tables uses constraint exclusion internally to emulate
partition pruning, which is no longer true as of 428b260f8.
The v2-0001 patch hasn't changed.
Thanks,
Amit
>From 336963d5f08a937c0890e794553dc23aced1fca1 Mon Sep 17 00:00:00 2001
From: amit <amitlangot...@gmail.com>
Date: Fri, 5 Apr 2019 15:41:11 +0900
Subject: [PATCH v3 2/2] Update docs that update/delete no longer use
constraint exclusion
---
doc/src/sgml/ddl.sgml | 18 ++----------------
1 file changed, 2 insertions(+), 16 deletions(-)
diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml
index 1fe27c5da9..33012939b8 100644
--- a/doc/src/sgml/ddl.sgml
+++ b/doc/src/sgml/ddl.sgml
@@ -4535,26 +4535,12 @@ EXPLAIN SELECT count(*) FROM measurement WHERE logdate
>= DATE '2008-01-01';
<xref linkend="guc-enable-partition-pruning"/> setting.
</para>
- <note>
- <para>
- Currently, pruning of partitions during the planning of an
- <command>UPDATE</command> or <command>DELETE</command> command is
- implemented using the constraint exclusion method (however, it is
- controlled by the <literal>enable_partition_pruning</literal> rather than
- <literal>constraint_exclusion</literal>) — see the following section
- for details and caveats that apply.
- </para>
-
<para>
Execution-time partition pruning currently only occurs for the
<literal>Append</literal> and <literal>MergeAppend</literal> node types.
It is not yet implemented for the <literal>ModifyTable</literal> node
- type.
- </para>
-
- <para>
- Both of these behaviors are likely to be changed in a future release
- of <productname>PostgreSQL</productname>.
+ type, but that is likely to be changed in a future release of
+ <productname>PostgreSQL</productname>.
</para>
</note>
</sect2>
--
2.11.0
>From 5549e6caae79259032e844812804529ffbf0d321 Mon Sep 17 00:00:00 2001
From: amit <amitlangot...@gmail.com>
Date: Tue, 2 Apr 2019 14:54:08 +0900
Subject: [PATCH v3 1/2] Fix partition constraint loading in planner
---
src/backend/optimizer/plan/planner.c | 5 +-
src/backend/optimizer/util/plancat.c | 106 +++++++++++++-------------
src/test/regress/expected/partition_prune.out | 48 ++++++++++++
src/test/regress/sql/partition_prune.sql | 19 +++++
4 files changed, 121 insertions(+), 57 deletions(-)
diff --git a/src/backend/optimizer/plan/planner.c
b/src/backend/optimizer/plan/planner.c
index e2cdc83613..1f6bd142b7 100644
--- a/src/backend/optimizer/plan/planner.c
+++ b/src/backend/optimizer/plan/planner.c
@@ -1513,8 +1513,9 @@ inheritance_planner(PlannerInfo *root)
parent_rte->securityQuals = NIL;
/*
- * Mark whether we're planning a query to a partitioned table
or an
- * inheritance parent.
+ * HACK: setting this to a value other than INHKIND_NONE
signals to
+ * relation_excluded_by_constraints() to process the result
relation as
+ * a partition; see that function for more details.
*/
subroot->inhTargetKind =
(rootRelation != 0) ? INHKIND_PARTITIONED :
INHKIND_INHERITED;
diff --git a/src/backend/optimizer/util/plancat.c
b/src/backend/optimizer/util/plancat.c
index 3301331304..28923f805e 100644
--- a/src/backend/optimizer/util/plancat.c
+++ b/src/backend/optimizer/util/plancat.c
@@ -66,7 +66,7 @@ static void get_relation_foreign_keys(PlannerInfo *root,
RelOptInfo *rel,
static bool infer_collation_opclass_match(InferenceElem *elem, Relation idxRel,
List *idxExprs);
static List *get_relation_constraints(PlannerInfo *root,
- Oid relationObjectId,
RelOptInfo *rel,
+ Relation relation, RelOptInfo
*rel,
bool include_notnull);
static List *build_index_tlist(PlannerInfo *root, IndexOptInfo *index,
Relation heapRelation);
@@ -1150,19 +1150,13 @@ get_relation_data_width(Oid relid, int32 *attr_widths)
*/
static List *
get_relation_constraints(PlannerInfo *root,
- Oid relationObjectId,
RelOptInfo *rel,
+ Relation relation, RelOptInfo
*rel,
bool include_notnull)
{
List *result = NIL;
Index varno = rel->relid;
- Relation relation;
TupleConstr *constr;
- /*
- * We assume the relation has already been safely locked.
- */
- relation = table_open(relationObjectId, NoLock);
-
constr = relation->rd_att->constr;
if (constr != NULL)
{
@@ -1242,38 +1236,6 @@ 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 (enable_partition_pruning && root->parse->commandType != CMD_SELECT)
- {
- List *pcqual = RelationGetPartitionQual(relation);
-
- if (pcqual)
- {
- /*
- * Run the partition quals through const-simplification
similar to
- * check constraints. We skip canonicalize_qual,
though, because
- * partition quals should be in canonical form already;
also,
- * since the qual is in implicit-AND format, we'd have
to
- * explicitly convert it to explicit-AND format and
back again.
- */
- pcqual = (List *) eval_const_expressions(root, (Node *)
pcqual);
-
- /* Fix Vars to have the desired varno */
- if (varno != 1)
- ChangeVarNodes((Node *) pcqual, 1, varno, 0);
-
- result = list_concat(result, pcqual);
- }
- }
-
- table_close(relation, NoLock);
-
return result;
}
@@ -1380,6 +1342,7 @@ relation_excluded_by_constraints(PlannerInfo *root,
List *constraint_pred;
List *safe_constraints;
ListCell *lc;
+ Relation relation;
/* As of now, constraint exclusion works only with simple relations. */
Assert(IS_SIMPLE_REL(rel));
@@ -1410,26 +1373,14 @@ relation_excluded_by_constraints(PlannerInfo *root,
switch (constraint_exclusion)
{
case CONSTRAINT_EXCLUSION_OFF:
-
- /*
- * Don't prune if feature turned off -- except if the
relation is
- * a partition. While partprune.c-style partition
pruning is not
- * yet in use for all cases (update/delete is not
handled), it
- * would be a UI horror to use different user-visible
controls
- * depending on such a volatile implementation detail.
Therefore,
- * for partitioned tables we use
enable_partition_pruning to
- * control this behavior.
- */
- if (root->inhTargetKind == INHKIND_PARTITIONED)
- break;
return false;
case CONSTRAINT_EXCLUSION_PARTITION:
/*
* When constraint_exclusion is set to 'partition' we
only handle
- * OTHER_MEMBER_RELs, or BASERELs in cases where the
result target
- * is an inheritance parent or a partitioned table.
+ * OTHER_MEMBER_RELs, or BASERELs in cases where the
relation is
+ * is an inherited target relation.
*/
if ((rel->reloptkind != RELOPT_OTHER_MEMBER_REL) &&
!(rel->reloptkind == RELOPT_BASEREL &&
@@ -1481,10 +1432,55 @@ relation_excluded_by_constraints(PlannerInfo *root,
return false;
/*
+ * We assume the relation has already been safely locked.
+ */
+ relation = table_open(rte->relid, NoLock);
+
+ /*
* OK to fetch the constraint expressions. Include "col IS NOT NULL"
* expressions for attnotnull columns, in case we can refute those.
*/
- constraint_pred = get_relation_constraints(root, rte->relid, rel, true);
+ constraint_pred = get_relation_constraints(root, relation, rel, true);
+
+ /*
+ * Append partition predicates, if any.
+ *
+ * If the partition is accessed indirectly via its parent table,
partition
+ * pruning is performed with the parent table's partition bound
descriptor,
+ * so there is no need to include the partition constraint in that case.
+ * We do need to include it if the partition is referenced directly in
the
+ * query, because no partition pruning would have occurred in that case,
+ * except in the case where the partition is a target relation. (See
+ * inheritance_planner().)
+ */
+ if (rel->reloptkind == RELOPT_BASEREL &&
+ !(root->inhTargetKind == INHKIND_PARTITIONED &&
+ rel->relid == root->parse->resultRelation))
+ {
+ List *pcqual = RelationGetPartitionQual(relation);
+
+ if (pcqual)
+ {
+ Index varno = rel->relid;
+
+ /*
+ * Run the partition quals through const-simplification
similar to
+ * check constraints. We skip canonicalize_qual,
though, because
+ * partition quals should be in canonical form already;
also,
+ * since the qual is in implicit-AND format, we'd have
to
+ * explicitly convert it to explicit-AND format and
back again.
+ */
+ pcqual = (List *) eval_const_expressions(root, (Node *)
pcqual);
+
+ /* Fix Vars to have the desired varno */
+ if (varno != 1)
+ ChangeVarNodes((Node *) pcqual, 1, varno, 0);
+
+ constraint_pred = list_concat(constraint_pred, pcqual);
+ }
+ }
+
+ table_close(relation, NoLock);
/*
* We do not currently enforce that CHECK constraints contain only
diff --git a/src/test/regress/expected/partition_prune.out
b/src/test/regress/expected/partition_prune.out
index 7806ba1d47..e957badf09 100644
--- a/src/test/regress/expected/partition_prune.out
+++ b/src/test/regress/expected/partition_prune.out
@@ -3643,4 +3643,52 @@ 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)
+
+explain (costs off) update listp set a = listp1.b from listp1 where listp.a =
listp1.a and listp1.a = 2;
+ QUERY PLAN
+--------------------------------
+ Update on listp
+ -> Result
+ One-Time Filter: false
+(3 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..ff3312bb31 100644
--- a/src/test/regress/sql/partition_prune.sql
+++ b/src/test/regress/sql/partition_prune.sql
@@ -990,4 +990,23 @@ 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;
+explain (costs off) update listp set a = listp1.b from listp1 where listp.a =
listp1.a and listp1.a = 2;
+reset constraint_exclusion;
+reset enable_partition_pruning;
drop table listp;
--
2.11.0
