From: Amit Langote <Langote_Amit_f8@lab.ntt.co.jp>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: David Rowley <david.rowley@2ndquadrant.com>
Cc: Imai Yoshikazu <yoshikazu_i443@live.jp>
Cc: jesper.pedersen@redhat.com <jesper.pedersen@redhat.com>
Cc: Imai, Yoshikazu <imai.yoshikazu@jp.fujitsu.com>
Cc: Amit Langote <amitlangote09@gmail.com>
Cc: Alvaro Herrera <alvherre@2ndquadrant.com>
Cc: Robert Haas <robertmhaas@gmail.com>
Cc: Justin Pryzby <pryzby@telsasoft.com>
Cc: Pg Hackers <pgsql-hackers@postgresql.org>
Subject: Re: speeding up planning with partitions
Date: Fri, 5 Apr 2019 15:50:25 +0900
Message-ID: <4f049572-9440-3c99-afa1-f7ca7f38fe80@lab.ntt.co.jp> (raw)
In-Reply-To: <50415da6-0258-d135-2ba4-197041b57c5b@lab.ntt.co.jp>
References: <CAKJS1f9XAePd2+8F4=wtHKdDdckgKcSoXb-ZbU8Wbt86mhLxKA@mail.gmail.com>
<20519.1553288822@sss.pgh.pa.us>
<15573.1553639191@sss.pgh.pa.us>
<47d2fd82-3f70-077b-abc6-863e3fa23503@lab.ntt.co.jp>
<379243a6-33d5-af38-43c9-37120a0893bd@lab.ntt.co.jp>
<a9f76f49-c747-2145-683b-767ff5343d7b@lab.ntt.co.jp>
<CAKJS1f9N6POxkCsq1s_Qzshz-Jmr7Jpq-eJW=ZM=U-SUCPxoMw@mail.gmail.com! >
<ab390249-6aab-195e-bced-e7a427c3462f@lab.ntt.co.jp>
<CAKJS1f-UeJmCLeWgMdcY7Y_Tf2-ZjGFwqHSY2VENYmx2tp2uRQ@mail.gmail.com>
<18712.1553698656@sss.pgh.pa.us>
<5! 9737210-61bd-e583-4393-cc0a5ab52549@lab.ntt.co.jp>
<19663.1553749401@sss.pgh.pa.us>
<2a531b7c-8ced-f868-2e! f2-5cf33b92ef0f@lab.ntt.co.jp>
<23856.1553812684@sss.pgh.pa.us>
<5c83dbca-12b5-1acf-0e85-58299e464a26@lab.ntt.co.jp>
<d807e9fd-2e4c-6910-ade0-1a7e23c706a1@lab.nt! t.co.jp>
<23831.1553873385@sss.pgh.pa.us>
<0b484! 202-4b89-0797-355f-5c23f786aa6a@lab.ntt.co.jp>
<30707.1554140085@sss.pgh.pa.us>
<50415da6-0258-d135-2ba4-197041b57c5b@lab.ntt.co.jp>
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
Attachments:download all (2 files, .tar.gz)Update-docs-that-update-delete-no-longer-use-cons.patch (text/plain, 1.6K) download
From 336963d5f08a937c0890e794553dc23aced1fca1 Mon Sep 17 00:00:00 2001
From: amit <amitlangote09@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.sgmlindex 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
v2-0001-Fix-partition-constraint-loading-in-planner.patch (text/plain, 9.8K) download
From 5549e6caae79259032e844812804529ffbf0d321 Mon Sep 17 00:00:00 2001
From: amit <amitlangote09@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.cindex 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.cindex 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.outindex 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.sqlindex 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: Langote_Amit_f8@lab.ntt.co.jp, tgl@sss.pgh.pa.us, david.rowley@2ndquadrant.com, yoshikazu_i443@live.jp, jesper.pedersen@redhat.com, imai.yoshikazu@jp.fujitsu.com, amitlangote09@gmail.com, alvherre@2ndquadrant.com, robertmhaas@gmail.com, pryzby@telsasoft.com
Subject: Re: speeding up planning with partitions
In-Reply-To: <4f049572-9440-3c99-afa1-f7ca7f38fe80@lab.ntt.co.jp>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox