Re: speeding up planning with partitions
| От | Tom Lane |
|---|---|
| Тема | Re: speeding up planning with partitions |
| Дата | |
| Msg-id | 7323.1556572349@sss.pgh.pa.us обсуждение исходный текст |
| Ответ на | Re: speeding up planning with partitions (David Rowley <david.rowley@2ndquadrant.com>) |
| Список | pgsql-hackers |
Amit Langote <amitlangote09@gmail.com> writes:
> Here is the patch. I've also included the patch to update the text in
> ddl.sgml regarding constraint exclusion and partition pruning.
I thought this was a bit messy. In particular, IMV the reason to
have a split between get_relation_constraints and its only caller
relation_excluded_by_constraints is to create a policy vs mechanism
separation: relation_excluded_by_constraints figures out what kinds
of constraints we need to look at, while get_relation_constraints does
the gruntwork of digging them out of the catalog data. Somebody had
already ignored this principle to the extent of putting this
very-much-policy test into get_relation_constraints:
if (enable_partition_pruning && root->parse->commandType != CMD_SELECT)
but the way to improve that is to add another flag parameter to convey
the policy choice, not to move the whole chunk of mechanism out to the
caller.
It also struck me while looking at the code that we were being
unnecessarily stupid about non-inheritable constraints: rather than
just throwing up our hands for traditional inheritance situations,
we can still apply constraint exclusion, as long as we consider only
constraints that aren't marked ccnoinherit. (attnotnull constraints
have to be considered as if they were ccnoinherit, for ordinary
tables but not partitioned ones.)
So, I propose the attached revised patch.
I'm not sure how much of this, if anything, we should back-patch to
v11. It definitely doesn't seem like we should back-patch the
improvement just explained. I tried diking out that change, as
in the v11 variant attached, and found that this still causes quite a
few other changes in v11's expected results, most of them not for the
better. So I'm thinking that we'd better conclude that v11's ship
has sailed. Its behavior is in some ways weird, but I am not sure
that anyone will appreciate our changing it on the fourth minor
release.
It's somewhat interesting that we get these other changes in v11
but not HEAD. I think the reason is that we reimplemented so much
of inheritance_planner in 428b260f8; that is, it seems the weird
decisions we find in relation_excluded_by_constraints are mostly
there to band-aid over the old weird behavior of inheritance_planner.
Anyway, my current thought is to apply this to HEAD and do nothing
in v11. I include the v11 patch just for amusement. (I did not
check v11's behavior outside the core regression tests; it might
possibly have additional test diffs in contrib.)
regards, tom lane
diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index 8ddab75..84341a3 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -5084,10 +5084,11 @@ ANY <replaceable class="parameter">num_sync</replaceable> ( <replaceable class="
The allowed values of <varname>constraint_exclusion</varname> are
<literal>on</literal> (examine constraints for all tables),
<literal>off</literal> (never examine constraints), and
- <literal>partition</literal> (examine constraints only for inheritance child
- tables and <literal>UNION ALL</literal> subqueries).
+ <literal>partition</literal> (examine constraints only for inheritance
+ child tables and <literal>UNION ALL</literal> subqueries).
<literal>partition</literal> is the default setting.
- It is often used with inheritance tables to improve performance.
+ It is often used with traditional inheritance trees to improve
+ performance.
</para>
<para>
@@ -5111,15 +5112,19 @@ SELECT * FROM parent WHERE key = 2400;
<para>
Currently, constraint exclusion is enabled by default
only for cases that are often used to implement table partitioning via
- inheritance tables. Turning it on for all tables imposes extra
+ inheritance trees. Turning it on for all tables imposes extra
planning overhead that is quite noticeable on simple queries, and most
often will yield no benefit for simple queries. If you have no
- inheritance partitioned tables you might prefer to turn it off entirely.
+ tables that are partitioned using traditional inheritance, you might
+ prefer to turn it off entirely. (Note that the equivalent feature for
+ partitioned tables is controlled by a separate parameter,
+ <xref linkend="guc-enable-partition-pruning"/>.)
</para>
<para>
Refer to <xref linkend="ddl-partitioning-constraint-exclusion"/> for
- more information on using constraint exclusion and partitioning.
+ more information on using constraint exclusion to implement
+ partitioning.
</para>
</listitem>
</varlistentry>
diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml
index cba2ea9..a0a7435 100644
--- a/doc/src/sgml/ddl.sgml
+++ b/doc/src/sgml/ddl.sgml
@@ -4535,24 +4535,11 @@ EXPLAIN SELECT count(*) FROM measurement WHERE logdate >= DATE '2008-01-01';
<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>
diff --git a/src/backend/optimizer/plan/planner.c b/src/backend/optimizer/plan/planner.c
index 0a6710c..eb6f5a3 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 treat the result relation as
+ * being an appendrel member.
*/
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 3301331..3215c29 100644
--- a/src/backend/optimizer/util/plancat.c
+++ b/src/backend/optimizer/util/plancat.c
@@ -67,7 +67,9 @@ static bool infer_collation_opclass_match(InferenceElem *elem, Relation idxRel,
List *idxExprs);
static List *get_relation_constraints(PlannerInfo *root,
Oid relationObjectId, RelOptInfo *rel,
- bool include_notnull);
+ bool include_noinherit,
+ bool include_notnull,
+ bool include_partition);
static List *build_index_tlist(PlannerInfo *root, IndexOptInfo *index,
Relation heapRelation);
static List *get_relation_statistics(RelOptInfo *rel, Relation relation);
@@ -1134,16 +1136,22 @@ get_relation_data_width(Oid relid, int32 *attr_widths)
/*
* get_relation_constraints
*
- * Retrieve the validated CHECK constraint expressions of the given relation.
+ * Retrieve the applicable constraint expressions of the given relation.
*
* Returns a List (possibly empty) of constraint expressions. Each one
* has been canonicalized, and its Vars are changed to have the varno
* indicated by rel->relid. This allows the expressions to be easily
* compared to expressions taken from WHERE.
*
+ * If include_noinherit is true, it's okay to include constraints that
+ * are marked NO INHERIT.
+ *
* If include_notnull is true, "col IS NOT NULL" expressions are generated
* and added to the result for each column that's marked attnotnull.
*
+ * If include_partition is true, and the relation is a partition,
+ * also include the partitioning constraints.
+ *
* Note: at present this is invoked at most once per relation per planner
* run, and in many cases it won't be invoked at all, so there seems no
* point in caching the data in RelOptInfo.
@@ -1151,7 +1159,9 @@ get_relation_data_width(Oid relid, int32 *attr_widths)
static List *
get_relation_constraints(PlannerInfo *root,
Oid relationObjectId, RelOptInfo *rel,
- bool include_notnull)
+ bool include_noinherit,
+ bool include_notnull,
+ bool include_partition)
{
List *result = NIL;
Index varno = rel->relid;
@@ -1175,10 +1185,13 @@ get_relation_constraints(PlannerInfo *root,
/*
* If this constraint hasn't been fully validated yet, we must
- * ignore it here.
+ * ignore it here. Also ignore if NO INHERIT and we weren't told
+ * that that's safe.
*/
if (!constr->check[i].ccvalid)
continue;
+ if (constr->check[i].ccnoinherit && !include_noinherit)
+ continue;
cexpr = stringToNode(constr->check[i].ccbin);
@@ -1243,13 +1256,9 @@ 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.
+ * Add partitioning constraints, if requested.
*/
- if (enable_partition_pruning && root->parse->commandType != CMD_SELECT)
+ if (include_partition && relation->rd_rel->relispartition)
{
List *pcqual = RelationGetPartitionQual(relation);
@@ -1366,7 +1375,7 @@ get_relation_statistics(RelOptInfo *rel, Relation relation)
*
* Detect whether the relation need not be scanned because it has either
* self-inconsistent restrictions, or restrictions inconsistent with the
- * relation's validated CHECK constraints.
+ * relation's applicable constraints.
*
* Note: this examines only rel->relid, rel->reloptkind, and
* rel->baserestrictinfo; therefore it can be called before filling in
@@ -1376,6 +1385,9 @@ bool
relation_excluded_by_constraints(PlannerInfo *root,
RelOptInfo *rel, RangeTblEntry *rte)
{
+ bool include_noinherit;
+ bool include_notnull;
+ bool include_partition = false;
List *safe_restrictions;
List *constraint_pred;
List *safe_constraints;
@@ -1385,6 +1397,13 @@ relation_excluded_by_constraints(PlannerInfo *root,
Assert(IS_SIMPLE_REL(rel));
/*
+ * If there are no base restriction clauses, we have no hope of proving
+ * anything below, so fall out quickly.
+ */
+ if (rel->baserestrictinfo == NIL)
+ return false;
+
+ /*
* Regardless of the setting of constraint_exclusion, detect
* constant-FALSE-or-NULL restriction clauses. Because const-folding will
* reduce "anything AND FALSE" to just "FALSE", any such case should
@@ -1410,35 +1429,41 @@ 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;
+ /* In 'off' mode, never make any further tests */
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.
+ * appendrel members. Normally, they are RELOPT_OTHER_MEMBER_REL
+ * relations, but we also consider inherited target relations as
+ * appendrel members for the purposes of constraint exclusion
+ * (since, indeed, they were appendrel members earlier in
+ * inheritance_planner).
+ *
+ * In both cases, partition pruning was already applied, so there
+ * is no need to consider the rel's partition constraints here.
*/
- if ((rel->reloptkind != RELOPT_OTHER_MEMBER_REL) &&
- !(rel->reloptkind == RELOPT_BASEREL &&
- root->inhTargetKind != INHKIND_NONE &&
- rel->relid == root->parse->resultRelation))
- return false;
- break;
+ if (rel->reloptkind == RELOPT_OTHER_MEMBER_REL ||
+ (rel->relid == root->parse->resultRelation &&
+ root->inhTargetKind != INHKIND_NONE))
+ break; /* appendrel member, so process it */
+ return false;
case CONSTRAINT_EXCLUSION_ON:
+
+ /*
+ * In 'on' mode, always apply constraint exclusion. If we are
+ * considering a baserel that is a partition (i.e., it was
+ * directly named rather than expanded from a parent table), then
+ * its partition constraints haven't been considered yet, so
+ * include them in the processing here.
+ */
+ if (rel->reloptkind == RELOPT_BASEREL &&
+ !(rel->relid == root->parse->resultRelation &&
+ root->inhTargetKind != INHKIND_NONE))
+ include_partition = true;
break; /* always try to exclude */
}
@@ -1467,24 +1492,33 @@ relation_excluded_by_constraints(PlannerInfo *root,
return true;
/*
- * Only plain relations have constraints. In a partitioning hierarchy,
- * but not with regular table inheritance, it's OK to assume that any
- * constraints that hold for the parent also hold for every child; for
- * instance, table inheritance allows the parent to have constraints
- * marked NO INHERIT, but table partitioning does not. We choose to check
- * whether the partitioning parents can be excluded here; doing so
- * consumes some cycles, but potentially saves us the work of excluding
- * each child individually.
+ * Only plain relations have constraints, so stop here for other rtekinds.
*/
- if (rte->rtekind != RTE_RELATION ||
- (rte->inh && rte->relkind != RELKIND_PARTITIONED_TABLE))
+ if (rte->rtekind != RTE_RELATION)
return false;
/*
- * OK to fetch the constraint expressions. Include "col IS NOT NULL"
- * expressions for attnotnull columns, in case we can refute those.
+ * If we are scanning just this table, we can use NO INHERIT constraints,
+ * but not if we're scanning its children too. (Note that partitioned
+ * tables should never have NO INHERIT constraints; but it's not necessary
+ * for us to assume that here.)
+ */
+ include_noinherit = !rte->inh;
+
+ /*
+ * Currently, attnotnull constraints must be treated as NO INHERIT unless
+ * this is a partitioned table. In future we might track their
+ * inheritance status more accurately, allowing this to be refined.
+ */
+ include_notnull = (!rte->inh || rte->relkind == RELKIND_PARTITIONED_TABLE);
+
+ /*
+ * Fetch the appropriate set of constraint expressions.
*/
- constraint_pred = get_relation_constraints(root, rte->relid, rel, true);
+ constraint_pred = get_relation_constraints(root, rte->relid, rel,
+ include_noinherit,
+ include_notnull,
+ include_partition);
/*
* 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 0789b31..bd64bed 100644
--- a/src/test/regress/expected/partition_prune.out
+++ b/src/test/regress/expected/partition_prune.out
@@ -3639,4 +3639,46 @@ 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;
+-- setting constraint_exclusion to 'partition' disables exclusion
+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) update listp1 set a = 1 where a = 2;
+ QUERY PLAN
+--------------------------
+ Update on listp1
+ -> Seq Scan on listp1
+ Filter: (a = 2)
+(3 rows)
+
+-- constraint exclusion enabled
+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) update listp1 set a = 1 where a = 2;
+ QUERY PLAN
+--------------------------------
+ Update on listp1
+ -> 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 c30e58e..246c627 100644
--- a/src/test/regress/sql/partition_prune.sql
+++ b/src/test/regress/sql/partition_prune.sql
@@ -990,4 +990,24 @@ 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;
+
+-- setting constraint_exclusion to 'partition' disables exclusion
+set constraint_exclusion to 'partition';
+explain (costs off) select * from listp1 where a = 2;
+explain (costs off) update listp1 set a = 1 where a = 2;
+-- constraint exclusion enabled
+set constraint_exclusion to 'on';
+explain (costs off) select * from listp1 where a = 2;
+explain (costs off) update listp1 set a = 1 where a = 2;
+
+reset constraint_exclusion;
+reset enable_partition_pruning;
+
drop table listp;
diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index d94d033..37e21e8 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -4410,10 +4410,11 @@ ANY <replaceable class="parameter">num_sync</replaceable> ( <replaceable class="
The allowed values of <varname>constraint_exclusion</varname> are
<literal>on</literal> (examine constraints for all tables),
<literal>off</literal> (never examine constraints), and
- <literal>partition</literal> (examine constraints only for inheritance child
- tables and <literal>UNION ALL</literal> subqueries).
+ <literal>partition</literal> (examine constraints only for inheritance
+ child tables and <literal>UNION ALL</literal> subqueries).
<literal>partition</literal> is the default setting.
- It is often used with inheritance tables to improve performance.
+ It is often used with traditional inheritance trees to improve
+ performance.
</para>
<para>
@@ -4437,15 +4438,19 @@ SELECT * FROM parent WHERE key = 2400;
<para>
Currently, constraint exclusion is enabled by default
only for cases that are often used to implement table partitioning via
- inheritance tables. Turning it on for all tables imposes extra
+ inheritance trees. Turning it on for all tables imposes extra
planning overhead that is quite noticeable on simple queries, and most
often will yield no benefit for simple queries. If you have no
- inheritance partitioned tables you might prefer to turn it off entirely.
+ tables that are partitioned using traditional inheritance, you might
+ prefer to turn it off entirely. (Note that the equivalent feature for
+ partitioned tables is controlled by a separate parameter,
+ <xref linkend="guc-enable-partition-pruning"/>.)
</para>
<para>
Refer to <xref linkend="ddl-partitioning-constraint-exclusion"/> for
- more information on using constraint exclusion and partitioning.
+ more information on using constraint exclusion to implement
+ partitioning.
</para>
</listitem>
</varlistentry>
diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml
index 59685d7..f53e3c6 100644
--- a/doc/src/sgml/ddl.sgml
+++ b/doc/src/sgml/ddl.sgml
@@ -3918,22 +3918,11 @@ EXPLAIN SELECT count(*) FROM measurement WHERE logdate >= DATE '2008-01-01';
<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>
- Also, execution-time partition pruning currently only occurs for the
- <literal>Append</literal> node type, not <literal>MergeAppend</literal>.
- </para>
-
- <para>
- Both of these behaviors are likely to be changed in a future release
- of <productname>PostgreSQL</productname>.
+ Execution-time partition pruning currently only occurs for the
+ <literal>Append</literal> node type, not
+ for <literal>MergeAppend</literal> or <literal>ModifyTable</literal>
+ nodes. That is likely to be changed in a future release of
+ <productname>PostgreSQL</productname>.
</para>
</note>
</sect2>
diff --git a/src/backend/optimizer/plan/planner.c b/src/backend/optimizer/plan/planner.c
index 94b962b..0f46914 100644
--- a/src/backend/optimizer/plan/planner.c
+++ b/src/backend/optimizer/plan/planner.c
@@ -1324,8 +1324,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 treat the result relation as
+ * being an appendrel member.
*/
subroot->inhTargetKind =
partitioned_relids ? INHKIND_PARTITIONED : INHKIND_INHERITED;
diff --git a/src/backend/optimizer/util/plancat.c b/src/backend/optimizer/util/plancat.c
index 8369e3a..2453953 100644
--- a/src/backend/optimizer/util/plancat.c
+++ b/src/backend/optimizer/util/plancat.c
@@ -66,7 +66,9 @@ static bool infer_collation_opclass_match(InferenceElem *elem, Relation idxRel,
static int32 get_rel_data_width(Relation rel, int32 *attr_widths);
static List *get_relation_constraints(PlannerInfo *root,
Oid relationObjectId, RelOptInfo *rel,
- bool include_notnull);
+ bool include_noinherit,
+ bool include_notnull,
+ bool include_partition);
static List *build_index_tlist(PlannerInfo *root, IndexOptInfo *index,
Relation heapRelation);
static List *get_relation_statistics(RelOptInfo *rel, Relation relation);
@@ -1157,16 +1159,22 @@ get_relation_data_width(Oid relid, int32 *attr_widths)
/*
* get_relation_constraints
*
- * Retrieve the validated CHECK constraint expressions of the given relation.
+ * Retrieve the applicable constraint expressions of the given relation.
*
* Returns a List (possibly empty) of constraint expressions. Each one
* has been canonicalized, and its Vars are changed to have the varno
* indicated by rel->relid. This allows the expressions to be easily
* compared to expressions taken from WHERE.
*
+ * If include_noinherit is true, it's okay to include constraints that
+ * are marked NO INHERIT.
+ *
* If include_notnull is true, "col IS NOT NULL" expressions are generated
* and added to the result for each column that's marked attnotnull.
*
+ * If include_partition is true, and the relation is a partition,
+ * also include the partitioning constraints.
+ *
* Note: at present this is invoked at most once per relation per planner
* run, and in many cases it won't be invoked at all, so there seems no
* point in caching the data in RelOptInfo.
@@ -1174,7 +1182,9 @@ get_relation_data_width(Oid relid, int32 *attr_widths)
static List *
get_relation_constraints(PlannerInfo *root,
Oid relationObjectId, RelOptInfo *rel,
- bool include_notnull)
+ bool include_noinherit,
+ bool include_notnull,
+ bool include_partition)
{
List *result = NIL;
Index varno = rel->relid;
@@ -1198,10 +1208,13 @@ get_relation_constraints(PlannerInfo *root,
/*
* If this constraint hasn't been fully validated yet, we must
- * ignore it here.
+ * ignore it here. Also ignore if NO INHERIT and we weren't told
+ * that that's safe.
*/
if (!constr->check[i].ccvalid)
continue;
+ if (constr->check[i].ccnoinherit && !include_noinherit)
+ continue;
cexpr = stringToNode(constr->check[i].ccbin);
@@ -1266,13 +1279,9 @@ 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.
+ * Add partitioning constraints, if requested.
*/
- if (enable_partition_pruning && root->parse->commandType != CMD_SELECT)
+ if (include_partition && relation->rd_rel->relispartition)
{
List *pcqual = RelationGetPartitionQual(relation);
@@ -1377,7 +1386,7 @@ get_relation_statistics(RelOptInfo *rel, Relation relation)
*
* Detect whether the relation need not be scanned because it has either
* self-inconsistent restrictions, or restrictions inconsistent with the
- * relation's validated CHECK constraints.
+ * relation's applicable constraints.
*
* Note: this examines only rel->relid, rel->reloptkind, and
* rel->baserestrictinfo; therefore it can be called before filling in
@@ -1387,6 +1396,9 @@ bool
relation_excluded_by_constraints(PlannerInfo *root,
RelOptInfo *rel, RangeTblEntry *rte)
{
+ bool include_noinherit;
+ bool include_notnull;
+ bool include_partition = false;
List *safe_restrictions;
List *constraint_pred;
List *safe_constraints;
@@ -1396,6 +1408,13 @@ relation_excluded_by_constraints(PlannerInfo *root,
Assert(IS_SIMPLE_REL(rel));
/*
+ * If there are no base restriction clauses, we have no hope of proving
+ * anything below, so fall out quickly.
+ */
+ if (rel->baserestrictinfo == NIL)
+ return false;
+
+ /*
* Regardless of the setting of constraint_exclusion, detect
* constant-FALSE-or-NULL restriction clauses. Because const-folding will
* reduce "anything AND FALSE" to just "FALSE", any such case should
@@ -1421,35 +1440,41 @@ 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;
+ /* In 'off' mode, never make any further tests */
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.
+ * appendrel members. Normally, they are RELOPT_OTHER_MEMBER_REL
+ * relations, but we also consider inherited target relations as
+ * appendrel members for the purposes of constraint exclusion
+ * (since, indeed, they were appendrel members earlier in
+ * inheritance_planner).
+ *
+ * In both cases, partition pruning was already applied, so there
+ * is no need to consider the rel's partition constraints here.
*/
- if ((rel->reloptkind != RELOPT_OTHER_MEMBER_REL) &&
- !(rel->reloptkind == RELOPT_BASEREL &&
- root->inhTargetKind != INHKIND_NONE &&
- rel->relid == root->parse->resultRelation))
- return false;
- break;
+ if (rel->reloptkind == RELOPT_OTHER_MEMBER_REL ||
+ (rel->relid == root->parse->resultRelation &&
+ root->inhTargetKind != INHKIND_NONE))
+ break; /* appendrel member, so process it */
+ return false;
case CONSTRAINT_EXCLUSION_ON:
+
+ /*
+ * In 'on' mode, always apply constraint exclusion. If we are
+ * considering a baserel that is a partition (i.e., it was
+ * directly named rather than expanded from a parent table), then
+ * its partition constraints haven't been considered yet, so
+ * include them in the processing here.
+ */
+ if (rel->reloptkind == RELOPT_BASEREL &&
+ !(rel->relid == root->parse->resultRelation &&
+ root->inhTargetKind != INHKIND_NONE))
+ include_partition = true;
break; /* always try to exclude */
}
@@ -1478,24 +1503,40 @@ relation_excluded_by_constraints(PlannerInfo *root,
return true;
/*
- * Only plain relations have constraints. In a partitioning hierarchy,
- * but not with regular table inheritance, it's OK to assume that any
- * constraints that hold for the parent also hold for every child; for
- * instance, table inheritance allows the parent to have constraints
- * marked NO INHERIT, but table partitioning does not. We choose to check
- * whether the partitioning parents can be excluded here; doing so
- * consumes some cycles, but potentially saves us the work of excluding
- * each child individually.
+ * Only plain relations have constraints, so stop here for other rtekinds.
*/
- if (rte->rtekind != RTE_RELATION ||
- (rte->inh && rte->relkind != RELKIND_PARTITIONED_TABLE))
+ if (rte->rtekind != RTE_RELATION)
return false;
/*
- * OK to fetch the constraint expressions. Include "col IS NOT NULL"
- * expressions for attnotnull columns, in case we can refute those.
+ * In a partitioning hierarchy, but not with regular table inheritance,
+ * it's OK to assume that any constraints that hold for the parent also
+ * hold for every child; for instance, table inheritance allows the parent
+ * to have constraints marked NO INHERIT, but table partitioning does not.
+ * We choose to check whether the partitioning parents can be excluded
+ * here; doing so consumes some cycles, but potentially saves us the work
+ * of excluding each child individually.
+ *
+ * This is unnecessarily stupid, but making it smarter seems out of scope
+ * for v11.
+ */
+ if (rte->inh && rte->relkind != RELKIND_PARTITIONED_TABLE)
+ return false;
+
+ /*
+ * Given the above restriction, we can always include NO INHERIT and NOT
+ * NULL constraints.
+ */
+ include_noinherit = true;
+ include_notnull = true;
+
+ /*
+ * Fetch the appropriate set of constraint expressions.
*/
- constraint_pred = get_relation_constraints(root, rte->relid, rel, true);
+ constraint_pred = get_relation_constraints(root, rte->relid, rel,
+ include_noinherit,
+ include_notnull,
+ include_partition);
/*
* We do not currently enforce that CHECK constraints contain only
diff --git a/src/test/regress/expected/partition_join.out b/src/test/regress/expected/partition_join.out
index 078b5fd..0e9373c 100644
--- a/src/test/regress/expected/partition_join.out
+++ b/src/test/regress/expected/partition_join.out
@@ -1681,6 +1681,8 @@ WHERE EXISTS (
---------------------------------------------------------------
Delete on prt1_l
Delete on prt1_l_p1
+ Delete on prt1_l_p2_p1
+ Delete on prt1_l_p2_p2
Delete on prt1_l_p3_p1
Delete on prt1_l_p3_p2
-> Nested Loop Semi Join
@@ -1692,7 +1694,7 @@ WHERE EXISTS (
-> Limit
-> Seq Scan on int8_tbl
-> Nested Loop Semi Join
- -> Seq Scan on prt1_l_p3_p1
+ -> Seq Scan on prt1_l_p2_p1
Filter: (c IS NULL)
-> Nested Loop
-> Seq Scan on int4_tbl
@@ -1700,14 +1702,30 @@ WHERE EXISTS (
-> Limit
-> Seq Scan on int8_tbl int8_tbl_1
-> Nested Loop Semi Join
- -> Seq Scan on prt1_l_p3_p2
+ -> Seq Scan on prt1_l_p2_p2
Filter: (c IS NULL)
-> Nested Loop
-> Seq Scan on int4_tbl
-> Subquery Scan on ss_2
-> Limit
-> Seq Scan on int8_tbl int8_tbl_2
-(28 rows)
+ -> Nested Loop Semi Join
+ -> Seq Scan on prt1_l_p3_p1
+ Filter: (c IS NULL)
+ -> Nested Loop
+ -> Seq Scan on int4_tbl
+ -> Subquery Scan on ss_3
+ -> Limit
+ -> Seq Scan on int8_tbl int8_tbl_3
+ -> Nested Loop Semi Join
+ -> Seq Scan on prt1_l_p3_p2
+ Filter: (c IS NULL)
+ -> Nested Loop
+ -> Seq Scan on int4_tbl
+ -> Subquery Scan on ss_4
+ -> Limit
+ -> Seq Scan on int8_tbl int8_tbl_4
+(46 rows)
--
-- negative testcases
diff --git a/src/test/regress/expected/partition_prune.out b/src/test/regress/expected/partition_prune.out
index 79e29e7..00f076b 100644
--- a/src/test/regress/expected/partition_prune.out
+++ b/src/test/regress/expected/partition_prune.out
@@ -3047,18 +3047,24 @@ explain (costs off) update pp_arrpart set a = a where a = '{1}';
----------------------------------------
Update on pp_arrpart
Update on pp_arrpart1
+ Update on pp_arrpart2
-> Seq Scan on pp_arrpart1
Filter: (a = '{1}'::integer[])
-(4 rows)
+ -> Seq Scan on pp_arrpart2
+ Filter: (a = '{1}'::integer[])
+(7 rows)
explain (costs off) delete from pp_arrpart where a = '{1}';
QUERY PLAN
----------------------------------------
Delete on pp_arrpart
Delete on pp_arrpart1
+ Delete on pp_arrpart2
-> Seq Scan on pp_arrpart1
Filter: (a = '{1}'::integer[])
-(4 rows)
+ -> Seq Scan on pp_arrpart2
+ Filter: (a = '{1}'::integer[])
+(7 rows)
drop table pp_arrpart;
-- array type hash partition key
@@ -3184,18 +3190,24 @@ explain (costs off) update pp_lp set value = 10 where a = 1;
--------------------------
Update on pp_lp
Update on pp_lp1
+ Update on pp_lp2
-> Seq Scan on pp_lp1
Filter: (a = 1)
-(4 rows)
+ -> Seq Scan on pp_lp2
+ Filter: (a = 1)
+(7 rows)
explain (costs off) delete from pp_lp where a = 1;
QUERY PLAN
--------------------------
Delete on pp_lp
Delete on pp_lp1
+ Delete on pp_lp2
-> Seq Scan on pp_lp1
Filter: (a = 1)
-(4 rows)
+ -> Seq Scan on pp_lp2
+ Filter: (a = 1)
+(7 rows)
set enable_partition_pruning = off;
set constraint_exclusion = 'partition'; -- this should not affect the result.
@@ -3417,4 +3429,46 @@ 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;
+-- setting constraint_exclusion to 'partition' disables exclusion
+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) update listp1 set a = 1 where a = 2;
+ QUERY PLAN
+--------------------------
+ Update on listp1
+ -> Seq Scan on listp1
+ Filter: (a = 2)
+(3 rows)
+
+-- constraint exclusion enabled
+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) update listp1 set a = 1 where a = 2;
+ QUERY PLAN
+--------------------------------
+ Update on listp1
+ -> 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 6aecf25..eafbec6 100644
--- a/src/test/regress/sql/partition_prune.sql
+++ b/src/test/regress/sql/partition_prune.sql
@@ -899,4 +899,24 @@ 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;
+
+-- setting constraint_exclusion to 'partition' disables exclusion
+set constraint_exclusion to 'partition';
+explain (costs off) select * from listp1 where a = 2;
+explain (costs off) update listp1 set a = 1 where a = 2;
+-- constraint exclusion enabled
+set constraint_exclusion to 'on';
+explain (costs off) select * from listp1 where a = 2;
+explain (costs off) update listp1 set a = 1 where a = 2;
+
+reset constraint_exclusion;
+reset enable_partition_pruning;
+
drop table listp;
В списке pgsql-hackers по дате отправления:
Предыдущее
От: Fabien COELHOДата:
Сообщение: Re: [PATCH v5] Show detailed table persistence in \dt+