From: Amit Langote <Langote_Amit_f8@lab.ntt.co.jp>
To: Imai Yoshikazu <yoshikazu_i443@live.jp>
To: jesper.pedersen@redhat.com <jesper.pedersen@redhat.com>
To: Imai, Yoshikazu <imai.yoshikazu@jp.fujitsu.com>
To: 'Amit Langote' <amitlangote09@gmail.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: David Rowley <david.rowley@2ndquadrant.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, 22 Mar 2019 16:39:30 +0900
Message-ID: <c8dcf0fd-4759-5e1c-ea67-e2d3f98e81bd@lab.ntt.co.jp> (raw)
In-Reply-To: <d04c7027-2df8-bbd5-9a8f-9806e3ab3270@lab.ntt.co.jp>
References: <CAKJS1f9XAePd2+8F4=wtHKdDdckgKcSoXb-ZbU8Wbt86mhLxKA@mail.gmail.com>
<d768d90d-628e-6cdc-513d-f50a0ce02cea@redhat.com>
<CA+HiwqFTqUVuiyK7dDQDCrNF1NiPDOAnmQum2jcw1yj5Z+kC0Q@mail.gmail.com>
<0F97FA9ABBDBE54F91744A9B37151A5129DD55@g01jpexmbkw24>
<256b9c87-b687-6f51-6274-19ae5743aef6@lab.ntt.co.jp>
<0F97FA9ABBDBE54F91744A9B37151A5129E604@g01jpexmbkw24>
<9feacaf6-ddb3-96dd-5b98-df5e927b1439@lab.ntt.co.jp>
<0F97FA9ABBDBE54F91744A9B37151A512B3279@g01jpexmbkw24>
<0c90ce90-b010-4791-41c0-a0df34f99ffa@lab.ntt.co.jp>
<0F97FA9ABBDBE54F91744A9B37151A512B3528@g01jpexmbkw24>
<d964510e-2eec-ce42-8ad8-884caf718e03@lab.ntt.co.jp>
<0F97FA9ABBDBE54F91744A9B37151A512B359C@g01jpexmbkw24>
<72094fc9-3232-0fc7-b7cd-42e1c73d5aee@lab.ntt.co.jp>
<0F97FA9ABBDBE54F91744A9B37151A512B45CD@g01jpexmbkw24>
<53a80692-c68d-5e11-1014-6441b6e8ca5a@redhat.com>
<BYAPR20MB22790FAFA860B03602A9A7B4BC420@BYAPR20MB2279.namprd20.prod.outlook.com>
<d04c7027-2df8-bbd5-9a8f-9806e3ab3270@lab.ntt.co.jp>
Hi,
On 2019/03/22 11:17, Amit Langote wrote:
> However, looking into make_partitionedrel_pruneinfo(), it seems that it's
> unconditionally allocating 3 arrays that all have nparts elements:
>
> subplan_map = (int *) palloc0(nparts * sizeof(int));
> subpart_map = (int *) palloc0(nparts * sizeof(int));
> relid_map = (Oid *) palloc0(nparts * sizeof(int));
>
> So, that part has got to cost more as the partition count grows.
>
> This is the code for runtime pruning, which is not exercised in our tests,
> so it might seem odd that we're spending any time here at all. I've been
> told that we have to perform at least *some* work here if only to conclude
> that runtime pruning is not needed and it seems that above allocations
> occur before making that conclusion. Maybe, that's salvageable by
> rearranging this code a bit. David may be in a better position to help
> with that.
I took a stab at this. I think rearranging the code in
make_partitionedrel_pruneinfo() slightly will take care of this.
The problem is that make_partitionedrel_pruneinfo() does some work which
involves allocating arrays containing nparts elements and then looping
over the part_rels array to fill values in those arrays that will be used
by run-time pruning. It does all that even *before* it has checked that
run-time pruning will be needed at all, which if it is not, it will simply
discard the result of the aforementioned work.
Patch 0008 of 0009 rearranges the code such that we check whether we will
need run-time pruning at all before doing any of the work that's necessary
for run-time to work.
Thanks,
Amit
From b7af3ae17c7e9b585af87331cc56a190ff6ad3d2 Mon Sep 17 00:00:00 2001
From: Amit <amitlangote09@gmail.com>
Date: Sat, 2 Mar 2019 14:13:13 +0900
Subject: [PATCH v34 1/9] Build "other rels" of appendrel baserels in a
separate step
Currently they're built in a stanza in build_simple_rel() which
builds the child rels in the same invocation of build_simple_rel
that's used to build the parent relation. Since query hasn't
been processed to distribute restriction clauses to individual
baserels at this point, we cannot perform partition pruning before
adding the children.
Newly added add_other_rels_to_query() runs *after* query_planner has
distributed restriction clauses to base relations. This will allow
us to use the clauses applied a given partitioned baserel to perform
partition pruning, and add other rels for only the unpruned
partitions. Later patches will do that.
---
src/backend/optimizer/path/allpaths.c | 2 +-
src/backend/optimizer/plan/initsplan.c | 60 +++++++++++++++--
src/backend/optimizer/plan/planmain.c | 15 +++--
src/backend/optimizer/util/relnode.c | 114 +++++++++++++++++++++------------
src/include/optimizer/pathnode.h | 2 +
src/include/optimizer/planmain.h | 1 +
6 files changed, 143 insertions(+), 51 deletions(-)
diff --git a/src/backend/optimizer/path/allpaths.c b/src/backend/optimizer/path/allpaths.cindex b2c5c833f7..3b70ef17fa 100644--- a/src/backend/optimizer/path/allpaths.c+++ b/src/backend/optimizer/path/allpaths.c@@ -1029,7 +1029,7 @@ set_append_rel_size(PlannerInfo *root, RelOptInfo *rel,
/*
* The child rel's RelOptInfo was already created during
- * add_base_rels_to_query.+ * add_other_rels_to_query.
*/
childrel = find_base_rel(root, childRTindex);
Assert(childrel->reloptkind == RELOPT_OTHER_MEMBER_REL);
diff --git a/src/backend/optimizer/plan/initsplan.c b/src/backend/optimizer/plan/initsplan.cindex 2afc3f1dfe..077d3203ba 100644--- a/src/backend/optimizer/plan/initsplan.c+++ b/src/backend/optimizer/plan/initsplan.c@@ -20,6 +20,7 @@
#include "nodes/nodeFuncs.h"
#include "optimizer/clauses.h"
#include "optimizer/cost.h"
+#include "optimizer/inherit.h"
#include "optimizer/joininfo.h"
#include "optimizer/optimizer.h"
#include "optimizer/pathnode.h"
@@ -30,6 +31,7 @@
#include "optimizer/prep.h"
#include "optimizer/restrictinfo.h"
#include "parser/analyze.h"
+#include "parser/parsetree.h"
#include "rewrite/rewriteManip.h"
#include "utils/lsyscache.h"
@@ -97,10 +99,11 @@ static void check_hashjoinable(RestrictInfo *restrictinfo);
* jtnode. Internally, the function recurses through the jointree.
*
* At the end of this process, there should be one baserel RelOptInfo for
- * every non-join RTE that is used in the query. Therefore, this routine- * is the only place that should call build_simple_rel with reloptkind- * RELOPT_BASEREL. (Note: build_simple_rel recurses internally to build- * "other rel" RelOptInfos for the members of any appendrels we find here.)+ * every non-join RTE that is specified in the query. Therefore, this+ * routine is the only place that should call build_simple_rel with+ * reloptkind RELOPT_BASEREL. (Note: "other rel" RelOptInfos for the+ * members of any appendrels we find here are built later when query_planner+ * calls add_other_rels_to_query().)
*/
void
add_base_rels_to_query(PlannerInfo *root, Node *jtnode)
@@ -133,6 +136,55 @@ add_base_rels_to_query(PlannerInfo *root, Node *jtnode)
(int) nodeTag(jtnode));
}
+/*+ * add_other_rels_to_query+ *+ * Scan the query's jointree and for each base rels that is an appendrel,+ * create otherrel RelOptInfos of its children+ *+ * At the end of this process, there should be RelOptInfos for all relations+ * that will be scanned by the query.+ */+void+add_other_rels_to_query(PlannerInfo *root, Node *jtnode)+{+ if (jtnode == NULL)+ return;+ if (IsA(jtnode, RangeTblRef))+ {+ int varno = ((RangeTblRef *) jtnode)->rtindex;+ RangeTblEntry *rte = rt_fetch(varno, root->parse->rtable);++ /*+ * Only the parent subquery of a flattened UNION ALL and an inherited+ * table can have children.+ */+ if (rte->rtekind != RTE_SUBQUERY && rte->rtekind != RTE_RELATION)+ return;++ if (rte->inh)+ (void) add_appendrel_other_rels(root, rte, varno);+ }+ else if (IsA(jtnode, FromExpr))+ {+ FromExpr *f = (FromExpr *) jtnode;+ ListCell *l;++ foreach(l, f->fromlist)+ add_other_rels_to_query(root, lfirst(l));+ }+ else if (IsA(jtnode, JoinExpr))+ {+ JoinExpr *j = (JoinExpr *) jtnode;++ add_other_rels_to_query(root, j->larg);+ add_other_rels_to_query(root, j->rarg);+ }+ else+ elog(ERROR, "unrecognized node type: %d",+ (int) nodeTag(jtnode));+}+
/*****************************************************************************
*
diff --git a/src/backend/optimizer/plan/planmain.c b/src/backend/optimizer/plan/planmain.cindex 3cedd01c98..03c81772a3 100644--- a/src/backend/optimizer/plan/planmain.c+++ b/src/backend/optimizer/plan/planmain.c@@ -159,10 +159,8 @@ query_planner(PlannerInfo *root, List *tlist,
setup_append_rel_array(root);
/*
- * Construct RelOptInfo nodes for all base relations in query, and- * indirectly for all appendrel member relations ("other rels"). This- * will give us a RelOptInfo for every "simple" (non-join) rel involved in- * the query.+ * Construct RelOptInfo nodes for all base relations directly mentioned+ * in query, but not any appendrel member relations ("other rels") yet.
*
* Note: the reason we find the rels by searching the jointree and
* appendrel list, rather than just scanning the rangetable, is that the
@@ -204,6 +202,15 @@ query_planner(PlannerInfo *root, List *tlist,
generate_base_implied_equalities(root);
/*
+ * Now that we have restrict clauses figured out and assigned to proper+ * base rels, we can proceed to add otherrels, that is, UNION ALL child+ * tables, inheritance child tables. Having restrict clauses ready helps+ * to exclude any children that wouldn't be necessary to scan, based on+ * constraint exclusion and partition pruning.+ */+ add_other_rels_to_query(root, (Node *) root->parse->jointree);++ /*
* We have completed merging equivalence sets, so it's now possible to
* generate pathkeys in canonical form; so compute query_pathkeys and
* other pathkeys fields in PlannerInfo.
diff --git a/src/backend/optimizer/util/relnode.c b/src/backend/optimizer/util/relnode.cindex 4130514952..8a59819469 100644--- a/src/backend/optimizer/util/relnode.c+++ b/src/backend/optimizer/util/relnode.c@@ -16,19 +16,26 @@
#include <limits.h>
+#include "access/table.h"
#include "miscadmin.h"
+#include "nodes/makefuncs.h"
#include "optimizer/appendinfo.h"
#include "optimizer/clauses.h"
#include "optimizer/cost.h"
+#include "optimizer/inherit.h"+#include "optimizer/optimizer.h"
#include "optimizer/pathnode.h"
#include "optimizer/paths.h"
#include "optimizer/placeholder.h"
#include "optimizer/plancat.h"
+#include "optimizer/planmain.h"
#include "optimizer/prep.h"
#include "optimizer/restrictinfo.h"
#include "optimizer/tlist.h"
#include "partitioning/partbounds.h"
+#include "partitioning/partdesc.h"
#include "utils/hsearch.h"
+#include "utils/rel.h"
typedef struct JoinHashEntry
@@ -273,53 +280,76 @@ build_simple_rel(PlannerInfo *root, int relid, RelOptInfo *parent)
root->qual_security_level = Max(root->qual_security_level,
list_length(rte->securityQuals));
+ return rel;+}++/*+ * add_appendrel_other_rels+ * This adds the "other rel" RelOptInfos a given "appendrel" baserel+ *+ * Parent relation in this case is the parent subquery in the flattened UNION+ * ALL case or an inheritance parent table.+ */+void+add_appendrel_other_rels(PlannerInfo *root, RangeTblEntry *rte, Index rti)+{+ ListCell *l;+ RelOptInfo *rel;+ int i;++ rel = find_base_rel(root, rti);+
/*
- * If this rel is an appendrel parent, recurse to build "other rel"- * RelOptInfos for its children. They are "other rels" because they are- * not in the main join tree, but we will need RelOptInfos to plan access- * to them.+ * For partitioned tables, we need to store the child RelOptInfos in the+ * rel->part_rels array too.
*/
- if (rte->inh)+ if (rel->part_scheme)
{
- ListCell *l;- int nparts = rel->nparts;- int cnt_parts = 0;-- if (nparts > 0)- rel->part_rels = (RelOptInfo **)- palloc(sizeof(RelOptInfo *) * nparts);-- foreach(l, root->append_rel_list)- {- AppendRelInfo *appinfo = (AppendRelInfo *) lfirst(l);- RelOptInfo *childrel;-- /* append_rel_list contains all append rels; ignore others */- if (appinfo->parent_relid != relid)- continue;-- childrel = build_simple_rel(root, appinfo->child_relid,- rel);-- /* Nothing more to do for an unpartitioned table. */- if (!rel->part_scheme)- continue;-- /*- * The order of partition OIDs in append_rel_list is the same as- * the order in the PartitionDesc, so the order of part_rels will- * also match the PartitionDesc. See expand_partitioned_rtentry.- */- Assert(cnt_parts < nparts);- rel->part_rels[cnt_parts] = childrel;- cnt_parts++;- }-- /* We should have seen all the child partitions. */- Assert(cnt_parts == nparts);+ Assert(rel->nparts > 0);+ rel->part_rels = (RelOptInfo **)+ palloc0(sizeof(RelOptInfo *) * rel->nparts);
}
- return rel;+ i = 0;+ foreach(l, root->append_rel_list)+ {+ AppendRelInfo *appinfo = lfirst(l);+ Index childRTindex = appinfo->child_relid;+ RangeTblEntry *childrte;+ RelOptInfo *childrel;++ if (appinfo->parent_relid != rti)+ continue;++ Assert(childRTindex < root->simple_rel_array_size);+ childrte = root->simple_rte_array[childRTindex];+ Assert(childrte != NULL);+ childrel = build_simple_rel(root, childRTindex, rel);++ /*+ * For partitioned parents, we also need to add childrel to its+ * part_rels array. The order in which child tables appear in+ * append_rel_list is same as the order in which they appear in the+ * parent's PartitionDesc, so assigning partitions like this works.+ */+ if (rel->part_scheme != NULL)+ {+ Assert(i < rel->nparts);+ rel->part_rels[i] = childrel;+ }++ i++;++ /* Child may itself be an inherited relation. */+ if (childrte->inh)+ add_appendrel_other_rels(root, childrte, childRTindex);+ }++ /*+ * For a partitioned table with non-zero number of partitions, we must+ * have assigned all elements of its part_rels array.+ */+ Assert(rel->nparts == 0 || i == rel->nparts);
}
/*
diff --git a/src/include/optimizer/pathnode.h b/src/include/optimizer/pathnode.hindex 574bb85b50..1a07963a7d 100644--- a/src/include/optimizer/pathnode.h+++ b/src/include/optimizer/pathnode.h@@ -279,6 +279,8 @@ extern void setup_simple_rel_arrays(PlannerInfo *root);
extern void setup_append_rel_array(PlannerInfo *root);
extern RelOptInfo *build_simple_rel(PlannerInfo *root, int relid,
RelOptInfo *parent);
+extern void add_appendrel_other_rels(PlannerInfo *root, RangeTblEntry *rte,+ Index rti);
extern RelOptInfo *find_base_rel(PlannerInfo *root, int relid);
extern RelOptInfo *find_join_rel(PlannerInfo *root, Relids relids);
extern RelOptInfo *build_join_rel(PlannerInfo *root,
diff --git a/src/include/optimizer/planmain.h b/src/include/optimizer/planmain.hindex 3bbdb5e2f7..035caac500 100644--- a/src/include/optimizer/planmain.h+++ b/src/include/optimizer/planmain.h@@ -65,6 +65,7 @@ extern int from_collapse_limit;
extern int join_collapse_limit;
extern void add_base_rels_to_query(PlannerInfo *root, Node *jtnode);
+extern void add_other_rels_to_query(PlannerInfo *root, Node *jtnode);
extern void build_base_rel_tlists(PlannerInfo *root, List *final_tlist);
extern void add_vars_to_targetlist(PlannerInfo *root, List *vars,
Relids where_needed, bool create_new_ph);
--
2.11.0
From f850f0f62e9d363f02f32a93d6b489db1e6df837 Mon Sep 17 00:00:00 2001
From: amit <amitlangote09@gmail.com>
Date: Tue, 12 Mar 2019 10:47:37 +0900
Subject: [PATCH v34 2/9] Add rowmark "junk" targetlist entries in
query_planner
We'd like to postpone adding inheritance children to query until
query_planner has finished distributing quals to individual
baserels, which means also delaying adding child row marks. As
things stand today, which "junk" Vars to add to the targetlist for
a given parent row mark is not clear until all the child row marks
have been seen. So, we must delay adding "junk" Vars until the
children are added.
Since this changes the order in which expressions are added to the
individual baserels' targetlists, so does the order of expressions
in the Paths to scan them and Paths built on top of them. That is
why there are regression test expected output changes.
---
contrib/postgres_fdw/expected/postgres_fdw.out | 76 ++++++-------
src/backend/optimizer/plan/planagg.c | 2 +-
src/backend/optimizer/plan/planmain.c | 28 ++++-
src/backend/optimizer/plan/planner.c | 21 ++--
src/backend/optimizer/prep/preptlist.c | 152 ++++++++++++++-----------
src/include/optimizer/planmain.h | 2 +-
src/include/optimizer/prep.h | 2 +
src/test/regress/expected/with.out | 8 +-
8 files changed, 168 insertions(+), 123 deletions(-)
diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.outindex 42108bd3d4..c912e95fe1 100644--- a/contrib/postgres_fdw/expected/postgres_fdw.out+++ b/contrib/postgres_fdw/expected/postgres_fdw.out@@ -5577,25 +5577,25 @@ UPDATE ft2 SET c3 = 'baz'
Output: ft2.c1, ft2.c2, ft2.c3, ft2.c4, ft2.c5, ft2.c6, ft2.c7, ft2.c8, ft4.c1, ft4.c2, ft4.c3, ft5.c1, ft5.c2, ft5.c3
Remote SQL: UPDATE "S 1"."T 1" SET c3 = $2 WHERE ctid = $1 RETURNING "C 1", c2, c3, c4, c5, c6, c7, c8
-> Nested Loop
- Output: ft2.c1, ft2.c2, NULL::integer, 'baz'::text, ft2.c4, ft2.c5, ft2.c6, ft2.c7, ft2.c8, ft2.ctid, ft4.*, ft5.*, ft4.c1, ft4.c2, ft4.c3, ft5.c1, ft5.c2, ft5.c3+ Output: ft2.c1, ft2.c2, NULL::integer, 'baz'::text, ft2.c4, ft2.c5, ft2.c6, ft2.c7, ft2.c8, ft2.ctid, ft4.c1, ft4.c2, ft4.c3, ft5.c1, ft5.c2, ft5.c3, ft4.*, ft5.*
Join Filter: (ft2.c2 === ft4.c1)
-> Foreign Scan on public.ft2
Output: ft2.c1, ft2.c2, ft2.c4, ft2.c5, ft2.c6, ft2.c7, ft2.c8, ft2.ctid
Remote SQL: SELECT "C 1", c2, c4, c5, c6, c7, c8, ctid FROM "S 1"."T 1" WHERE (("C 1" > 2000)) FOR UPDATE
-> Foreign Scan
- Output: ft4.*, ft4.c1, ft4.c2, ft4.c3, ft5.*, ft5.c1, ft5.c2, ft5.c3+ Output: ft4.c1, ft4.c2, ft4.c3, ft4.*, ft5.c1, ft5.c2, ft5.c3, ft5.*
Relations: (public.ft4) INNER JOIN (public.ft5)
- Remote SQL: SELECT CASE WHEN (r2.*)::text IS NOT NULL THEN ROW(r2.c1, r2.c2, r2.c3) END, r2.c1, r2.c2, r2.c3, CASE WHEN (r3.*)::text IS NOT NULL THEN ROW(r3.c1, r3.c2, r3.c3) END, r3.c1, r3.c2, r3.c3 FROM ("S 1"."T 3" r2 INNER JOIN "S 1"."T 4" r3 ON (((r2.c1 = r3.c1))))+ Remote SQL: SELECT r2.c1, r2.c2, r2.c3, CASE WHEN (r2.*)::text IS NOT NULL THEN ROW(r2.c1, r2.c2, r2.c3) END, r3.c1, r3.c2, r3.c3, CASE WHEN (r3.*)::text IS NOT NULL THEN ROW(r3.c1, r3.c2, r3.c3) END FROM ("S 1"."T 3" r2 INNER JOIN "S 1"."T 4" r3 ON (((r2.c1 = r3.c1))))
-> Hash Join
- Output: ft4.*, ft4.c1, ft4.c2, ft4.c3, ft5.*, ft5.c1, ft5.c2, ft5.c3+ Output: ft4.c1, ft4.c2, ft4.c3, ft4.*, ft5.c1, ft5.c2, ft5.c3, ft5.*
Hash Cond: (ft4.c1 = ft5.c1)
-> Foreign Scan on public.ft4
- Output: ft4.*, ft4.c1, ft4.c2, ft4.c3+ Output: ft4.c1, ft4.c2, ft4.c3, ft4.*
Remote SQL: SELECT c1, c2, c3 FROM "S 1"."T 3"
-> Hash
- Output: ft5.*, ft5.c1, ft5.c2, ft5.c3+ Output: ft5.c1, ft5.c2, ft5.c3, ft5.*
-> Foreign Scan on public.ft5
- Output: ft5.*, ft5.c1, ft5.c2, ft5.c3+ Output: ft5.c1, ft5.c2, ft5.c3, ft5.*
Remote SQL: SELECT c1, c2, c3 FROM "S 1"."T 4"
(24 rows)
@@ -5626,16 +5626,16 @@ DELETE FROM ft2
-> Nested Loop
Output: ft2.ctid, ft4.*, ft5.*, ft4.c1, ft5.c1
-> Nested Loop
- Output: ft2.ctid, ft4.*, ft4.c1+ Output: ft2.ctid, ft4.c1, ft4.*
Join Filter: (ft2.c2 = ft4.c1)
-> Foreign Scan on public.ft2
Output: ft2.ctid, ft2.c2
Remote SQL: SELECT c2, ctid FROM "S 1"."T 1" WHERE (("C 1" > 2000)) FOR UPDATE
-> Foreign Scan on public.ft4
- Output: ft4.*, ft4.c1+ Output: ft4.c1, ft4.*
Remote SQL: SELECT c1, c2, c3 FROM "S 1"."T 3"
-> Foreign Scan on public.ft5
- Output: ft5.*, ft5.c1+ Output: ft5.c1, ft5.*
Remote SQL: SELECT c1, c2, c3 FROM "S 1"."T 4"
(22 rows)
@@ -7128,15 +7128,15 @@ select * from bar where f1 in (select f1 from foo) for update;
Output: bar2.f1, bar2.f2, bar2.ctid, bar2.*, bar2.tableoid
Remote SQL: SELECT f1, f2, f3, ctid FROM public.loct2 FOR UPDATE
-> Hash
- Output: foo.ctid, foo.*, foo.tableoid, foo.f1+ Output: foo.f1, foo.ctid, foo.*, foo.tableoid
-> HashAggregate
- Output: foo.ctid, foo.*, foo.tableoid, foo.f1+ Output: foo.f1, foo.ctid, foo.*, foo.tableoid
Group Key: foo.f1
-> Append
-> Seq Scan on public.foo
- Output: foo.ctid, foo.*, foo.tableoid, foo.f1+ Output: foo.f1, foo.ctid, foo.*, foo.tableoid
-> Foreign Scan on public.foo2
- Output: foo2.ctid, foo2.*, foo2.tableoid, foo2.f1+ Output: foo2.f1, foo2.ctid, foo2.*, foo2.tableoid
Remote SQL: SELECT f1, f2, f3, ctid FROM public.loct1
(23 rows)
@@ -7166,15 +7166,15 @@ select * from bar where f1 in (select f1 from foo) for share;
Output: bar2.f1, bar2.f2, bar2.ctid, bar2.*, bar2.tableoid
Remote SQL: SELECT f1, f2, f3, ctid FROM public.loct2 FOR SHARE
-> Hash
- Output: foo.ctid, foo.*, foo.tableoid, foo.f1+ Output: foo.f1, foo.ctid, foo.*, foo.tableoid
-> HashAggregate
- Output: foo.ctid, foo.*, foo.tableoid, foo.f1+ Output: foo.f1, foo.ctid, foo.*, foo.tableoid
Group Key: foo.f1
-> Append
-> Seq Scan on public.foo
- Output: foo.ctid, foo.*, foo.tableoid, foo.f1+ Output: foo.f1, foo.ctid, foo.*, foo.tableoid
-> Foreign Scan on public.foo2
- Output: foo2.ctid, foo2.*, foo2.tableoid, foo2.f1+ Output: foo2.f1, foo2.ctid, foo2.*, foo2.tableoid
Remote SQL: SELECT f1, f2, f3, ctid FROM public.loct1
(23 rows)
@@ -7203,15 +7203,15 @@ update bar set f2 = f2 + 100 where f1 in (select f1 from foo);
-> Seq Scan on public.bar
Output: bar.f1, bar.f2, bar.ctid
-> Hash
- Output: foo.ctid, foo.*, foo.tableoid, foo.f1+ Output: foo.f1, foo.ctid, foo.*, foo.tableoid
-> HashAggregate
- Output: foo.ctid, foo.*, foo.tableoid, foo.f1+ Output: foo.f1, foo.ctid, foo.*, foo.tableoid
Group Key: foo.f1
-> Append
-> Seq Scan on public.foo
- Output: foo.ctid, foo.*, foo.tableoid, foo.f1+ Output: foo.f1, foo.ctid, foo.*, foo.tableoid
-> Foreign Scan on public.foo2
- Output: foo2.ctid, foo2.*, foo2.tableoid, foo2.f1+ Output: foo2.f1, foo2.ctid, foo2.*, foo2.tableoid
Remote SQL: SELECT f1, f2, f3, ctid FROM public.loct1
-> Hash Join
Output: bar2.f1, (bar2.f2 + 100), bar2.f3, bar2.ctid, foo.ctid, foo.*, foo.tableoid
@@ -7221,15 +7221,15 @@ update bar set f2 = f2 + 100 where f1 in (select f1 from foo);
Output: bar2.f1, bar2.f2, bar2.f3, bar2.ctid
Remote SQL: SELECT f1, f2, f3, ctid FROM public.loct2 FOR UPDATE
-> Hash
- Output: foo.ctid, foo.*, foo.tableoid, foo.f1+ Output: foo.f1, foo.ctid, foo.*, foo.tableoid
-> HashAggregate
- Output: foo.ctid, foo.*, foo.tableoid, foo.f1+ Output: foo.f1, foo.ctid, foo.*, foo.tableoid
Group Key: foo.f1
-> Append
-> Seq Scan on public.foo
- Output: foo.ctid, foo.*, foo.tableoid, foo.f1+ Output: foo.f1, foo.ctid, foo.*, foo.tableoid
-> Foreign Scan on public.foo2
- Output: foo2.ctid, foo2.*, foo2.tableoid, foo2.f1+ Output: foo2.f1, foo2.ctid, foo2.*, foo2.tableoid
Remote SQL: SELECT f1, f2, f3, ctid FROM public.loct1
(39 rows)
@@ -7262,14 +7262,14 @@ where bar.f1 = ss.f1;
Hash Cond: (foo.f1 = bar.f1)
-> Append
-> Seq Scan on public.foo
- Output: ROW(foo.f1), foo.f1+ Output: foo.f1, ROW(foo.f1)
-> Foreign Scan on public.foo2
- Output: ROW(foo2.f1), foo2.f1+ Output: foo2.f1, ROW(foo2.f1)
Remote SQL: SELECT f1 FROM public.loct1
-> Seq Scan on public.foo foo_1
- Output: ROW((foo_1.f1 + 3)), (foo_1.f1 + 3)+ Output: (foo_1.f1 + 3), ROW((foo_1.f1 + 3))
-> Foreign Scan on public.foo2 foo2_1
- Output: ROW((foo2_1.f1 + 3)), (foo2_1.f1 + 3)+ Output: (foo2_1.f1 + 3), ROW((foo2_1.f1 + 3))
Remote SQL: SELECT f1 FROM public.loct1
-> Hash
Output: bar.f1, bar.f2, bar.ctid
@@ -7285,18 +7285,18 @@ where bar.f1 = ss.f1;
Output: bar2.f1, bar2.f2, bar2.f3, bar2.ctid
Remote SQL: SELECT f1, f2, f3, ctid FROM public.loct2 FOR UPDATE
-> Sort
- Output: (ROW(foo.f1)), foo.f1+ Output: foo.f1, (ROW(foo.f1))
Sort Key: foo.f1
-> Append
-> Seq Scan on public.foo
- Output: ROW(foo.f1), foo.f1+ Output: foo.f1, ROW(foo.f1)
-> Foreign Scan on public.foo2
- Output: ROW(foo2.f1), foo2.f1+ Output: foo2.f1, ROW(foo2.f1)
Remote SQL: SELECT f1 FROM public.loct1
-> Seq Scan on public.foo foo_1
- Output: ROW((foo_1.f1 + 3)), (foo_1.f1 + 3)+ Output: (foo_1.f1 + 3), ROW((foo_1.f1 + 3))
-> Foreign Scan on public.foo2 foo2_1
- Output: ROW((foo2_1.f1 + 3)), (foo2_1.f1 + 3)+ Output: (foo2_1.f1 + 3), ROW((foo2_1.f1 + 3))
Remote SQL: SELECT f1 FROM public.loct1
(45 rows)
@@ -7559,12 +7559,12 @@ update parent set b = parent.b || remt2.b from remt2 where parent.a = remt2.a re
Update on public.parent
Foreign Update on public.remt1
-> Nested Loop
- Output: parent.a, (parent.b || remt2.b), parent.ctid, remt2.*, remt2.a, remt2.b+ Output: parent.a, (parent.b || remt2.b), parent.ctid, remt2.a, remt2.b, remt2.*
Join Filter: (parent.a = remt2.a)
-> Seq Scan on public.parent
Output: parent.a, parent.b, parent.ctid
-> Foreign Scan on public.remt2
- Output: remt2.b, remt2.*, remt2.a+ Output: remt2.b, remt2.a, remt2.*
Remote SQL: SELECT a, b FROM public.loct2
-> Foreign Update
Remote SQL: UPDATE public.loct1 r4 SET b = (r4.b || r2.b) FROM public.loct2 r2 WHERE ((r4.a = r2.a)) RETURNING r4.a, r4.b, r2.a, r2.b
@@ -7591,7 +7591,7 @@ delete from parent using remt2 where parent.a = remt2.a returning parent;
-> Seq Scan on public.parent
Output: parent.ctid, parent.a
-> Foreign Scan on public.remt2
- Output: remt2.*, remt2.a+ Output: remt2.a, remt2.*
Remote SQL: SELECT a, b FROM public.loct2
-> Foreign Delete
Remote SQL: DELETE FROM public.loct1 r4 USING public.loct2 r2 WHERE ((r4.a = r2.a)) RETURNING r4.a, r4.b
diff --git a/src/backend/optimizer/plan/planagg.c b/src/backend/optimizer/plan/planagg.cindex 86617099df..0e819a6e92 100644--- a/src/backend/optimizer/plan/planagg.c+++ b/src/backend/optimizer/plan/planagg.c@@ -442,7 +442,7 @@ build_minmax_path(PlannerInfo *root, MinMaxAggInfo *mminfo,
subroot->tuple_fraction = 1.0;
subroot->limit_tuples = 1.0;
- final_rel = query_planner(subroot, tlist, minmax_qp_callback, NULL);+ final_rel = query_planner(subroot, &tlist, minmax_qp_callback, NULL);
/*
* Since we didn't go through subquery_planner() to handle the subquery,
diff --git a/src/backend/optimizer/plan/planmain.c b/src/backend/optimizer/plan/planmain.cindex 03c81772a3..42c2130096 100644--- a/src/backend/optimizer/plan/planmain.c+++ b/src/backend/optimizer/plan/planmain.c@@ -29,6 +29,7 @@
#include "optimizer/paths.h"
#include "optimizer/placeholder.h"
#include "optimizer/planmain.h"
+#include "optimizer/prep.h"
/*
@@ -42,7 +43,8 @@
* (grouping_planner) can choose among the surviving paths for the rel.
*
* root describes the query to plan
- * tlist is the target list the query should produce+ * *tlist is the target list the query should produce (passed in as an output+ * variable, because more entries may be added to it)
* (this is NOT necessarily root->parse->targetList!)
* qp_callback is a function to compute query_pathkeys once it's safe to do so
* qp_extra is optional extra data to pass to qp_callback
@@ -54,7 +56,7 @@
* (We cannot construct canonical pathkeys until that's done.)
*/
RelOptInfo *
-query_planner(PlannerInfo *root, List *tlist,+query_planner(PlannerInfo *root, List **tlist,
query_pathkeys_callback qp_callback, void *qp_extra)
{
Query *parse = root->parse;
@@ -179,7 +181,7 @@ query_planner(PlannerInfo *root, List *tlist,
* restrictions. Finally, we form a target joinlist for make_one_rel() to
* work from.
*/
- build_base_rel_tlists(root, tlist);+ build_base_rel_tlists(root, *tlist);
find_placeholders_in_jointree(root);
@@ -211,6 +213,26 @@ query_planner(PlannerInfo *root, List *tlist,
add_other_rels_to_query(root, (Node *) root->parse->jointree);
/*
+ * root->rowMarks should contain all row marks at this point, including+ * child row marks if any, so add "junk" Vars to the targetlist based on+ * the "parent" row marks.+ */+ if (root->rowMarks)+ {+ List *junk_vars;++ /* More entries will added to *tlist. */+ add_rowmark_junk_vars_to_targetlist(root, tlist, &junk_vars);+ Assert(junk_vars != NIL);++ /*+ * There can't be any PlaceHolderVars in junk_vars, so pass false for+ * 'create_new_ph'.+ */+ add_vars_to_targetlist(root, junk_vars, bms_make_singleton(0), false);+ }++ /*
* We have completed merging equivalence sets, so it's now possible to
* generate pathkeys in canonical form; so compute query_pathkeys and
* other pathkeys fields in PlannerInfo.
diff --git a/src/backend/optimizer/plan/planner.c b/src/backend/optimizer/plan/planner.cindex e408e77d6f..abac84bcaa 100644--- a/src/backend/optimizer/plan/planner.c+++ b/src/backend/optimizer/plan/planner.c@@ -25,6 +25,7 @@
#include "access/table.h"
#include "access/xact.h"
#include "catalog/pg_constraint.h"
+#include "catalog/pg_inherits.h"
#include "catalog/pg_proc.h"
#include "catalog/pg_type.h"
#include "executor/executor.h"
@@ -1835,15 +1836,6 @@ grouping_planner(PlannerInfo *root, bool inheritance_update,
tlist = preprocess_targetlist(root);
/*
- * We are now done hacking up the query's targetlist. Most of the- * remaining planning work will be done with the PathTarget- * representation of tlists, but save aside the full representation so- * that we can transfer its decoration (resnames etc) to the topmost- * tlist of the finished Plan.- */- root->processed_tlist = tlist;-- /*
* Collect statistics about aggregates for estimating costs, and mark
* all the aggregates with resolved aggtranstypes. We must do this
* before slicing and dicing the tlist into various pathtargets, else
@@ -1921,10 +1913,19 @@ grouping_planner(PlannerInfo *root, bool inheritance_update,
* We also generate (in standard_qp_callback) pathkey representations
* of the query's sort clause, distinct clause, etc.
*/
- current_rel = query_planner(root, tlist,+ current_rel = query_planner(root, &tlist,
standard_qp_callback, &qp_extra);
/*
+ * We are now done hacking up the query's targetlist. Most of the+ * remaining planning work will be done with the PathTarget+ * representation of tlists, but save aside the full representation so+ * that we can transfer its decoration (resnames etc) to the topmost+ * tlist of the finished Plan.+ */+ root->processed_tlist = tlist;++ /*
* Convert the query's result tlist into PathTarget format.
*
* Note: it's desirable to not do this till after query_planner(),
diff --git a/src/backend/optimizer/prep/preptlist.c b/src/backend/optimizer/prep/preptlist.cindex 5392d1a561..1165382e82 100644--- a/src/backend/optimizer/prep/preptlist.c+++ b/src/backend/optimizer/prep/preptlist.c@@ -76,7 +76,6 @@ preprocess_targetlist(PlannerInfo *root)
RangeTblEntry *target_rte = NULL;
Relation target_relation = NULL;
List *tlist;
- ListCell *lc;
/*
* If there is a result relation, open it so we can look for missing
@@ -119,71 +118,6 @@ preprocess_targetlist(PlannerInfo *root)
result_relation, target_relation);
/*
- * Add necessary junk columns for rowmarked rels. These values are needed- * for locking of rels selected FOR UPDATE/SHARE, and to do EvalPlanQual- * rechecking. See comments for PlanRowMark in plannodes.h.- */- foreach(lc, root->rowMarks)- {- PlanRowMark *rc = (PlanRowMark *) lfirst(lc);- Var *var;- char resname[32];- TargetEntry *tle;-- /* child rels use the same junk attrs as their parents */- if (rc->rti != rc->prti)- continue;-- if (rc->allMarkTypes & ~(1 << ROW_MARK_COPY))- {- /* Need to fetch TID */- var = makeVar(rc->rti,- SelfItemPointerAttributeNumber,- TIDOID,- -1,- InvalidOid,- 0);- snprintf(resname, sizeof(resname), "ctid%u", rc->rowmarkId);- tle = makeTargetEntry((Expr *) var,- list_length(tlist) + 1,- pstrdup(resname),- true);- tlist = lappend(tlist, tle);- }- if (rc->allMarkTypes & (1 << ROW_MARK_COPY))- {- /* Need the whole row as a junk var */- var = makeWholeRowVar(rt_fetch(rc->rti, range_table),- rc->rti,- 0,- false);- snprintf(resname, sizeof(resname), "wholerow%u", rc->rowmarkId);- tle = makeTargetEntry((Expr *) var,- list_length(tlist) + 1,- pstrdup(resname),- true);- tlist = lappend(tlist, tle);- }-- /* If parent of inheritance tree, always fetch the tableoid too. */- if (rc->isParent)- {- var = makeVar(rc->rti,- TableOidAttributeNumber,- OIDOID,- -1,- InvalidOid,- 0);- snprintf(resname, sizeof(resname), "tableoid%u", rc->rowmarkId);- tle = makeTargetEntry((Expr *) var,- list_length(tlist) + 1,- pstrdup(resname),- true);- tlist = lappend(tlist, tle);- }- }-- /*
* If the query has a RETURNING list, add resjunk entries for any Vars
* used in RETURNING that belong to other relations. We need to do this
* to make these Vars available for the RETURNING calculation. Vars that
@@ -434,3 +368,89 @@ get_plan_rowmark(List *rowmarks, Index rtindex)
}
return NULL;
}
++/*+ * add_rowmark_junk_vars_to_targetlist+ * Add "junk" targetlist entries needed for applying row marks contained+ * in root->rowMarks+ *+ * Vars that are added to the targetlist are also returned in *junk_vars for+ * the caller's perusal.+ */+void+add_rowmark_junk_vars_to_targetlist(PlannerInfo *root,+ List **tlist, List **junk_vars)+{+ List *range_table = root->parse->rtable;+ ListCell *lc;++ *junk_vars = NIL;++ /*+ * Add necessary junk columns for rowmarked rels. These values are needed+ * for locking of rels selected FOR UPDATE/SHARE, and to do EvalPlanQual+ * rechecking. See comments for PlanRowMark in plannodes.h.+ */+ foreach(lc, root->rowMarks)+ {+ PlanRowMark *rc = (PlanRowMark *) lfirst(lc);+ Var *var;+ char resname[32];+ TargetEntry *tle;++ /* child rels use the same junk attrs as their parents */+ if (rc->rti != rc->prti)+ continue;++ if (rc->allMarkTypes & ~(1 << ROW_MARK_COPY))+ {+ /* Need to fetch TID */+ var = makeVar(rc->rti,+ SelfItemPointerAttributeNumber,+ TIDOID,+ -1,+ InvalidOid,+ 0);+ snprintf(resname, sizeof(resname), "ctid%u", rc->rowmarkId);+ tle = makeTargetEntry((Expr *) var,+ list_length(*tlist) + 1,+ pstrdup(resname),+ true);+ *tlist = lappend(*tlist, tle);+ *junk_vars = lappend(*junk_vars, var);+ }+ if (rc->allMarkTypes & (1 << ROW_MARK_COPY))+ {+ /* Need the whole row as a junk var */+ var = makeWholeRowVar(rt_fetch(rc->rti, range_table),+ rc->rti,+ 0,+ false);+ snprintf(resname, sizeof(resname), "wholerow%u", rc->rowmarkId);+ tle = makeTargetEntry((Expr *) var,+ list_length(*tlist) + 1,+ pstrdup(resname),+ true);+ *tlist = lappend(*tlist, tle);+ *junk_vars = lappend(*junk_vars, var);+ }++ /* For inheritance cases, always fetch the tableoid too. */+ if (rc->isParent)+ {+ var = makeVar(rc->rti,+ TableOidAttributeNumber,+ OIDOID,+ -1,+ InvalidOid,+ 0);+ snprintf(resname, sizeof(resname), "tableoid%u", rc->rowmarkId);+ tle = makeTargetEntry((Expr *) var,+ list_length(*tlist) + 1,+ pstrdup(resname),+ true);+ *tlist = lappend(*tlist, tle);+ *junk_vars = lappend(*junk_vars, var);+ }+ }+}diff --git a/src/include/optimizer/planmain.h b/src/include/optimizer/planmain.hindex 035caac500..a21783192c 100644--- a/src/include/optimizer/planmain.h+++ b/src/include/optimizer/planmain.h@@ -27,7 +27,7 @@ typedef void (*query_pathkeys_callback) (PlannerInfo *root, void *extra);
/*
* prototypes for plan/planmain.c
*/
-extern RelOptInfo *query_planner(PlannerInfo *root, List *tlist,+extern RelOptInfo *query_planner(PlannerInfo *root, List **tlist,
query_pathkeys_callback qp_callback, void *qp_extra);
/*
diff --git a/src/include/optimizer/prep.h b/src/include/optimizer/prep.hindex a9b2c9026c..cf7b3a9d06 100644--- a/src/include/optimizer/prep.h+++ b/src/include/optimizer/prep.h@@ -37,6 +37,8 @@ extern Relids get_relids_for_join(Query *query, int joinrelid);
extern List *preprocess_targetlist(PlannerInfo *root);
extern PlanRowMark *get_plan_rowmark(List *rowmarks, Index rtindex);
+extern void add_rowmark_junk_vars_to_targetlist(PlannerInfo *root,+ List **tlist, List **junk_vars);
/*
* prototypes for prepunion.c
diff --git a/src/test/regress/expected/with.out b/src/test/regress/expected/with.outindex 2a2085556b..4f0a13dd0d 100644--- a/src/test/regress/expected/with.out+++ b/src/test/regress/expected/with.out@@ -2199,28 +2199,28 @@ DELETE FROM a USING wcte WHERE aa = q2;
-> Seq Scan on public.a
Output: a.ctid, a.aa
-> CTE Scan on wcte
- Output: wcte.*, wcte.q2+ Output: wcte.q2, wcte.*
-> Nested Loop
Output: b.ctid, wcte.*
Join Filter: (b.aa = wcte.q2)
-> Seq Scan on public.b
Output: b.ctid, b.aa
-> CTE Scan on wcte
- Output: wcte.*, wcte.q2+ Output: wcte.q2, wcte.*
-> Nested Loop
Output: c.ctid, wcte.*
Join Filter: (c.aa = wcte.q2)
-> Seq Scan on public.c
Output: c.ctid, c.aa
-> CTE Scan on wcte
- Output: wcte.*, wcte.q2+ Output: wcte.q2, wcte.*
-> Nested Loop
Output: d.ctid, wcte.*
Join Filter: (d.aa = wcte.q2)
-> Seq Scan on public.d
Output: d.ctid, d.aa
-> CTE Scan on wcte
- Output: wcte.*, wcte.q2+ Output: wcte.q2, wcte.*
(38 rows)
-- error cases
--
2.11.0
From 3a93941e9a28fd95b840e0a9c37edbee6e72fa0c Mon Sep 17 00:00:00 2001
From: amit <amitlangote09@gmail.com>
Date: Mon, 4 Mar 2019 15:10:29 +0900
Subject: [PATCH v34 3/9] Delay adding inheritance child tables until
query_planner
Inheritance child tables are now added by add_other_rels_to_query,
which is called by query_planner. This replaces
expand_inherited_tables called by subquery_planner as the method
of adding child tables. At the point when add_other_rels_to_query
is called, restriction clauses have already been assigned to
individual base relations, so it will now be possible to perform
partition pruning so that we know exact child tables to add in that
case. This commit however doesn't change when pruning is performed.
inheritance_planner now adds the child target tables on its own,
because subquery_planner doesn't. Also, since it calls query_planner
once for every child relation, source inheritance child tables are
added multiple times. This both slows down queries when there are
inherited tables in UPDATE/DELETE's FROM clause (source inheritance)
and changes the EXPLAIN output a bit because source inheritance
child tables' aliases come out different for every targer child
subplan. Another patch will fix both these issues.
---
contrib/postgres_fdw/expected/postgres_fdw.out | 46 ++++----
src/backend/optimizer/plan/planner.c | 58 +++++++---
src/backend/optimizer/util/inherit.c | 152 ++++++++++++++++---------
src/backend/optimizer/util/plancat.c | 9 +-
src/backend/optimizer/util/relnode.c | 83 +++++++++-----
src/include/optimizer/inherit.h | 4 +-
src/include/optimizer/pathnode.h | 1 +
src/test/regress/expected/partition_prune.out | 12 +-
src/test/regress/expected/rowsecurity.out | 16 +--
9 files changed, 246 insertions(+), 135 deletions(-)
diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.outindex c912e95fe1..bbc9ead2e9 100644--- a/contrib/postgres_fdw/expected/postgres_fdw.out+++ b/contrib/postgres_fdw/expected/postgres_fdw.out@@ -7190,8 +7190,8 @@ select * from bar where f1 in (select f1 from foo) for share;
-- Check UPDATE with inherited target and an inherited source table
explain (verbose, costs off)
update bar set f2 = f2 + 100 where f1 in (select f1 from foo);
- QUERY PLAN ----------------------------------------------------------------------------------------------+ QUERY PLAN +---------------------------------------------------------------------------------------------------
Update on public.bar
Update on public.bar
Foreign Update on public.bar2
@@ -7214,22 +7214,22 @@ update bar set f2 = f2 + 100 where f1 in (select f1 from foo);
Output: foo2.f1, foo2.ctid, foo2.*, foo2.tableoid
Remote SQL: SELECT f1, f2, f3, ctid FROM public.loct1
-> Hash Join
- Output: bar2.f1, (bar2.f2 + 100), bar2.f3, bar2.ctid, foo.ctid, foo.*, foo.tableoid+ Output: bar2.f1, (bar2.f2 + 100), bar2.f3, bar2.ctid, foo_1.ctid, foo_1.*, foo_1.tableoid
Inner Unique: true
- Hash Cond: (bar2.f1 = foo.f1)+ Hash Cond: (bar2.f1 = foo_1.f1)
-> Foreign Scan on public.bar2
Output: bar2.f1, bar2.f2, bar2.f3, bar2.ctid
Remote SQL: SELECT f1, f2, f3, ctid FROM public.loct2 FOR UPDATE
-> Hash
- Output: foo.f1, foo.ctid, foo.*, foo.tableoid+ Output: foo_1.f1, foo_1.ctid, foo_1.*, foo_1.tableoid
-> HashAggregate
- Output: foo.f1, foo.ctid, foo.*, foo.tableoid- Group Key: foo.f1+ Output: foo_1.f1, foo_1.ctid, foo_1.*, foo_1.tableoid+ Group Key: foo_1.f1
-> Append
- -> Seq Scan on public.foo- Output: foo.f1, foo.ctid, foo.*, foo.tableoid- -> Foreign Scan on public.foo2- Output: foo2.f1, foo2.ctid, foo2.*, foo2.tableoid+ -> Seq Scan on public.foo foo_1+ Output: foo_1.f1, foo_1.ctid, foo_1.*, foo_1.tableoid+ -> Foreign Scan on public.foo2 foo2_1+ Output: foo2_1.f1, foo2_1.ctid, foo2_1.*, foo2_1.tableoid
Remote SQL: SELECT f1, f2, f3, ctid FROM public.loct1
(39 rows)
@@ -7276,8 +7276,8 @@ where bar.f1 = ss.f1;
-> Seq Scan on public.bar
Output: bar.f1, bar.f2, bar.ctid
-> Merge Join
- Output: bar2.f1, (bar2.f2 + 100), bar2.f3, bar2.ctid, (ROW(foo.f1))- Merge Cond: (bar2.f1 = foo.f1)+ Output: bar2.f1, (bar2.f2 + 100), bar2.f3, bar2.ctid, (ROW(foo_2.f1))+ Merge Cond: (bar2.f1 = foo_2.f1)
-> Sort
Output: bar2.f1, bar2.f2, bar2.f3, bar2.ctid
Sort Key: bar2.f1
@@ -7285,18 +7285,18 @@ where bar.f1 = ss.f1;
Output: bar2.f1, bar2.f2, bar2.f3, bar2.ctid
Remote SQL: SELECT f1, f2, f3, ctid FROM public.loct2 FOR UPDATE
-> Sort
- Output: foo.f1, (ROW(foo.f1))- Sort Key: foo.f1+ Output: foo_2.f1, (ROW(foo_2.f1))+ Sort Key: foo_2.f1
-> Append
- -> Seq Scan on public.foo- Output: foo.f1, ROW(foo.f1)- -> Foreign Scan on public.foo2- Output: foo2.f1, ROW(foo2.f1)+ -> Seq Scan on public.foo foo_2+ Output: foo_2.f1, ROW(foo_2.f1)+ -> Foreign Scan on public.foo2 foo2_2+ Output: foo2_2.f1, ROW(foo2_2.f1)
Remote SQL: SELECT f1 FROM public.loct1
- -> Seq Scan on public.foo foo_1- Output: (foo_1.f1 + 3), ROW((foo_1.f1 + 3))- -> Foreign Scan on public.foo2 foo2_1- Output: (foo2_1.f1 + 3), ROW((foo2_1.f1 + 3))+ -> Seq Scan on public.foo foo_3+ Output: (foo_3.f1 + 3), ROW((foo_3.f1 + 3))+ -> Foreign Scan on public.foo2 foo2_3+ Output: (foo2_3.f1 + 3), ROW((foo2_3.f1 + 3))
Remote SQL: SELECT f1 FROM public.loct1
(45 rows)
diff --git a/src/backend/optimizer/plan/planner.c b/src/backend/optimizer/plan/planner.cindex abac84bcaa..02ca1a283a 100644--- a/src/backend/optimizer/plan/planner.c+++ b/src/backend/optimizer/plan/planner.c@@ -689,6 +689,8 @@ subquery_planner(PlannerGlobal *glob, Query *parse,
* remove_useless_result_rtes(). Also check for outer joins --- if none,
* we can skip reduce_outer_joins(). And check for LATERAL RTEs, too.
* This must be done after we have done pull_up_subqueries(), of course.
+ * For RTE_RELATION rangetable entries whose inh flag is set, adjust the+ * value of the flag by checking whether has_subclass() returns true.
*/
root->hasJoinRTEs = false;
root->hasLateralRTEs = false;
@@ -708,29 +710,30 @@ subquery_planner(PlannerGlobal *glob, Query *parse,
{
hasResultRTEs = true;
}
+ else if (rte->rtekind == RTE_RELATION && rte->inh)+ {+ rte->inh = has_subclass(rte->relid);++ /*+ * While at it, initialize the PartitionDesc infrastructure for+ * this query, if not done yet.+ */+ if (root->glob->partition_directory == NULL)+ root->glob->partition_directory =+ CreatePartitionDirectory(CurrentMemoryContext);+ }+
if (rte->lateral)
root->hasLateralRTEs = true;
}
/*
* Preprocess RowMark information. We need to do this after subquery
- * pullup (so that all non-inherited RTEs are present) and before- * inheritance expansion (so that the info is available for- * expand_inherited_tables to examine and modify).+ * pullup (so that all non-inherited RTEs are present).
*/
preprocess_rowmarks(root);
/*
- * Expand any rangetable entries that are inheritance sets into "append- * relations". This can add entries to the rangetable, but they must be- * plain RTE_RELATION entries, so it's OK (and marginally more efficient)- * to do it after checking for joins and other special RTEs. We must do- * this after pulling up subqueries, else we'd fail to handle inherited- * tables in subqueries.- */- expand_inherited_tables(root);-- /*
* Set hasHavingQual to remember if HAVING clause is present. Needed
* because preprocess_expression will reduce a constant-true condition to
* an empty qual list ... but "HAVING TRUE" is not a semantic no-op.
@@ -1206,10 +1209,35 @@ inheritance_planner(PlannerInfo *root)
Query *parent_parse;
Bitmapset *parent_relids = bms_make_singleton(top_parentRTindex);
PlannerInfo **parent_roots = NULL;
+ List *orig_append_rel_list = list_copy(root->append_rel_list);
Assert(parse->commandType != CMD_INSERT);
/*
+ * Add child target relations. Note that this only adds the children of+ * the query's target relation and no other. Especially, the children of+ * any source relations are added by query_planner() during the planning+ * of each child query.+ */+ parent_rte = rt_fetch(top_parentRTindex, root->parse->rtable);++ expand_inherited_rtentry(root, parent_rte, top_parentRTindex);++ /*+ * If parent no longer has any children, then treat this as an update of+ * a single table.+ */+ if (!parent_rte->inh)+ {+ /*+ * We'll be retrieving all tuples to modify, so passing 0.0 for+ * tuple_fraction.+ */+ grouping_planner(root, false, 0.0);+ return;+ }++ /*
* We generate a modified instance of the original Query for each target
* relation, plan that, and put all the plans into a list that will be
* controlled by a single ModifyTable node. All the instances share the
@@ -1270,7 +1298,6 @@ inheritance_planner(PlannerInfo *root)
* not appear anywhere else in the plan, so the confusion explained below
* for non-partitioning inheritance cases is not possible.
*/
- parent_rte = rt_fetch(top_parentRTindex, root->parse->rtable);
if (parent_rte->relkind == RELKIND_PARTITIONED_TABLE)
{
nominalRelation = top_parentRTindex;
@@ -1321,6 +1348,9 @@ inheritance_planner(PlannerInfo *root)
subroot = makeNode(PlannerInfo);
memcpy(subroot, parent_root, sizeof(PlannerInfo));
+ /* grouping_planner doesn't need to see the target children. */+ subroot->append_rel_list = list_copy(orig_append_rel_list);+
/*
* Generate modified query with this rel as target. We first apply
* adjust_appendrel_attrs, which copies the Query and changes
diff --git a/src/backend/optimizer/util/inherit.c b/src/backend/optimizer/util/inherit.cindex 1fa154e0cb..24111c46e9 100644--- a/src/backend/optimizer/util/inherit.c+++ b/src/backend/optimizer/util/inherit.c@@ -21,14 +21,14 @@
#include "miscadmin.h"
#include "optimizer/appendinfo.h"
#include "optimizer/inherit.h"
+#include "optimizer/pathnode.h"+#include "optimizer/planmain.h"
#include "optimizer/planner.h"
#include "optimizer/prep.h"
#include "partitioning/partdesc.h"
#include "utils/rel.h"
-static void expand_inherited_rtentry(PlannerInfo *root, RangeTblEntry *rte,- Index rti);
static void expand_partitioned_rtentry(PlannerInfo *root,
RangeTblEntry *parentrte,
Index parentRTindex, Relation parentrel,
@@ -45,36 +45,6 @@ static Bitmapset *translate_col_privs(const Bitmapset *parent_privs,
/*
- * expand_inherited_tables- * Expand each rangetable entry that represents an inheritance set- * into an "append relation". At the conclusion of this process,- * the "inh" flag is set in all and only those RTEs that are append- * relation parents.- */-void-expand_inherited_tables(PlannerInfo *root)-{- Index nrtes;- Index rti;- ListCell *rl;-- /*- * expand_inherited_rtentry may add RTEs to parse->rtable. The function is- * expected to recursively handle any RTEs that it creates with inh=true.- * So just scan as far as the original end of the rtable list.- */- nrtes = list_length(root->parse->rtable);- rl = list_head(root->parse->rtable);- for (rti = 1; rti <= nrtes; rti++)- {- RangeTblEntry *rte = (RangeTblEntry *) lfirst(rl);-- expand_inherited_rtentry(root, rte, rti);- rl = lnext(rl);- }-}--/*
* expand_inherited_rtentry
* Check whether a rangetable entry represents an inheritance set.
* If so, add entries for all the child tables to the query's
@@ -94,7 +64,7 @@ expand_inherited_tables(PlannerInfo *root)
* Since a partitioned table is not scanned, it might have only one associated
* AppendRelInfo.
*/
-static void+void
expand_inherited_rtentry(PlannerInfo *root, RangeTblEntry *rte, Index rti)
{
Oid parentOID;
@@ -147,10 +117,6 @@ expand_inherited_rtentry(PlannerInfo *root, RangeTblEntry *rte, Index rti)
{
Assert(rte->relkind == RELKIND_PARTITIONED_TABLE);
- if (root->glob->partition_directory == NULL)- root->glob->partition_directory =- CreatePartitionDirectory(CurrentMemoryContext);-
/*
* If this table has partitions, recursively expand and lock them.
* While at it, also extract the partition key columns of all the
@@ -164,6 +130,8 @@ expand_inherited_rtentry(PlannerInfo *root, RangeTblEntry *rte, Index rti)
List *appinfos = NIL;
RangeTblEntry *childrte;
Index childRTindex;
+ bool is_source_inh_expansion;+ RelOptInfo *rel = NULL;
/* Scan for all members of inheritance set, acquire needed locks */
inhOIDs = find_all_inheritors(parentOID, lockmode, NULL);
@@ -182,6 +150,20 @@ expand_inherited_rtentry(PlannerInfo *root, RangeTblEntry *rte, Index rti)
}
/*
+ * If parent is a source relation of the query, we'd need to add the+ * child RelOptInfos as well, in addition to RangeTblEntry's and+ * AppendRelInfo's. We can tell it's source relation by noting that+ * simple_rel_array has been set up by query_planner.+ */+ is_source_inh_expansion = (root->simple_rel_array_size > 0);+ if (is_source_inh_expansion)+ {+ rel = find_base_rel(root, rti);+ /* Expand various arrays in PlannerInfo to hold child object. */+ expand_planner_arrays(root, list_length(inhOIDs));+ }++ /*
* This table has no partitions. Expand any plain inheritance
* children in the order the OIDs were returned by
* find_all_inheritors.
@@ -214,6 +196,10 @@ expand_inherited_rtentry(PlannerInfo *root, RangeTblEntry *rte, Index rti)
&appinfos, &childrte,
&childRTindex);
+ /* Create the otherrel RelOptInfo too. */+ if (is_source_inh_expansion)+ (void) build_simple_rel(root, childRTindex, rel);+
/* Close child relations, but keep locks */
if (childOID != parentOID)
table_close(newrelation, NoLock);
@@ -251,12 +237,14 @@ expand_partitioned_rtentry(PlannerInfo *root, RangeTblEntry *parentrte,
RangeTblEntry *childrte;
Index childRTindex;
PartitionDesc partdesc;
+ RelOptInfo *rel = NULL;+ bool is_source_inh_expansion;++ check_stack_depth();
partdesc = PartitionDirectoryLookup(root->glob->partition_directory,
parentrel);
- check_stack_depth();-
/* A partitioned table should always have a partition descriptor. */
Assert(partdesc);
@@ -273,6 +261,35 @@ expand_partitioned_rtentry(PlannerInfo *root, RangeTblEntry *parentrte,
root->partColsUpdated =
has_partition_attrs(parentrel, parentrte->updatedCols, NULL);
+ /*+ * If parent is a source relation of the query, we'd need to add the child+ * RelOptInfos as well, in addition to RangeTblEntry's and+ * AppendRelInfo's. We can tell it's source relation by noting that+ * simple_rel_array has been set up by query_planner.+ */+ is_source_inh_expansion = (root->simple_rel_array_size > 0);+ if (is_source_inh_expansion)+ {+ rel = find_base_rel(root, parentRTindex);++ /*+ * Expand simple_rel_array and friends to hold child objects.+ *+ * We'll need nparts + 1 new slots, because we also consider parent+ * as a child relation. FIXME: no longer add the parent as child+ */+ expand_planner_arrays(root, partdesc->nparts + 1);++ /*+ * We also store partition RelOptInfo pointers in the parent+ * relation.+ */+ Assert(rel->part_rels == NULL);+ if (partdesc->nparts > 0)+ rel->part_rels = (RelOptInfo **) palloc0(rel->nparts *+ sizeof(RelOptInfo *));+ }+
/* First expand the partitioned table itself. */
expand_single_inheritance_child(root, parentrte, parentRTindex, parentrel,
top_parentrc, parentrel,
@@ -308,8 +325,19 @@ expand_partitioned_rtentry(PlannerInfo *root, RangeTblEntry *parentrte,
parentrel, top_parentrc, childrel,
appinfos, &childrte, &childRTindex);
- /* If this child is itself partitioned, recurse */- if (childrel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE)+ /* Create the otherrel RelOptInfo too. */+ if (is_source_inh_expansion)+ {+ Assert(rel->part_rels != NULL && rel->part_rels[i] == NULL);+ rel->part_rels[i] = build_simple_rel(root, childRTindex, rel);+ }++ /*+ * If this child is itself partitioned, recurse. Back off if the inh+ * flag was reset by expand_single_inheritance_child().+ */+ if (childrel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE &&+ childrte->inh)
expand_partitioned_rtentry(root, childrte, childRTindex,
childrel, top_parentrc, lockmode,
appinfos);
@@ -352,7 +380,14 @@ expand_single_inheritance_child(PlannerInfo *root, RangeTblEntry *parentrte,
Oid childOID = RelationGetRelid(childrel);
RangeTblEntry *childrte;
Index childRTindex;
- AppendRelInfo *appinfo;+ AppendRelInfo *appinfo = NULL;+ PartitionDesc child_partdesc = NULL;+ bool is_source_inh_expansion;++ if (childrel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE)+ child_partdesc =+ PartitionDirectoryLookup(root->glob->partition_directory,+ childrel);
/*
* Build an RTE for the child, and attach to query's rangetable list. We
@@ -370,9 +405,11 @@ expand_single_inheritance_child(PlannerInfo *root, RangeTblEntry *parentrte,
*childrte_p = childrte;
childrte->relid = childOID;
childrte->relkind = childrel->rd_rel->relkind;
- /* A partitioned child will need to be expanded further. */- if (childOID != parentOID &&- childrte->relkind == RELKIND_PARTITIONED_TABLE)+ /*+ * A partitioned child will need to be expanded further, but only if it+ * actually has partitions.+ */+ if (childOID != parentOID && child_partdesc && child_partdesc->nparts > 0)
childrte->inh = true;
else
childrte->inh = false;
@@ -383,13 +420,11 @@ expand_single_inheritance_child(PlannerInfo *root, RangeTblEntry *parentrte,
*childRTindex_p = childRTindex;
/*
- * We need an AppendRelInfo if paths will be built for the child RTE. If- * childrte->inh is true, then we'll always need to generate append paths- * for it. If childrte->inh is false, we must scan it if it's not a- * partitioned table; but if it is a partitioned table, then it never has- * any data of its own and need not be scanned.+ * Don't need an AppendRelInfo for duplicate RTEs we create for+ * partitioned table parent rels.
*/
- if (childrte->relkind != RELKIND_PARTITIONED_TABLE || childrte->inh)+ if (childrte->relid != parentOID ||+ childrte->relkind != RELKIND_PARTITIONED_TABLE)
{
appinfo = make_append_rel_info(parentrel, childrel,
parentRTindex, childRTindex);
@@ -416,6 +451,21 @@ expand_single_inheritance_child(PlannerInfo *root, RangeTblEntry *parentrte,
}
/*
+ * If we are expanding source inheritance, store the RTE and appinfo in the+ * respective PlannerInfo arrays, which the caller must already have+ * allocated space for.+ */+ is_source_inh_expansion = (root->simple_rel_array_size > 0);+ if (is_source_inh_expansion > 0)+ {+ Assert(childRTindex < root->simple_rel_array_size);+ Assert(root->simple_rte_array[childRTindex] == NULL);+ root->simple_rte_array[childRTindex] = childrte;+ Assert(root->append_rel_array[childRTindex] == NULL);+ root->append_rel_array[childRTindex] = appinfo;+ }++ /*
* Build a PlanRowMark if parent is marked FOR UPDATE/SHARE.
*/
if (top_parentrc)
diff --git a/src/backend/optimizer/util/plancat.c b/src/backend/optimizer/util/plancat.cindex 30f4dc151b..2b22dff690 100644--- a/src/backend/optimizer/util/plancat.c+++ b/src/backend/optimizer/util/plancat.c@@ -2091,7 +2091,14 @@ set_relation_partition_info(PlannerInfo *root, RelOptInfo *rel,
partkey = RelationGetPartitionKey(relation);
rel->part_scheme = find_partition_scheme(root, relation);
Assert(partdesc != NULL && rel->part_scheme != NULL);
- rel->boundinfo = partition_bounds_copy(partdesc->boundinfo, partkey);+ /*+ * XXX we should be able to Assert(partdesc->nparts > 0), but we've only+ * looked at inh flag so far, whose value is based on relhassubclass as+ * checked by subquery_planner(). relhassubclass may be stale as it's+ * only lazily updated as far as dropped partitions are concerned.+ */+ if (partdesc->nparts > 0)+ rel->boundinfo = partition_bounds_copy(partdesc->boundinfo, partkey);
rel->nparts = partdesc->nparts;
set_baserel_partition_key_exprs(relation, rel);
rel->partition_qual = RelationGetPartitionQual(relation);
diff --git a/src/backend/optimizer/util/relnode.c b/src/backend/optimizer/util/relnode.cindex 8a59819469..9dcc3167e0 100644--- a/src/backend/optimizer/util/relnode.c+++ b/src/backend/optimizer/util/relnode.c@@ -139,6 +139,50 @@ setup_append_rel_array(PlannerInfo *root)
}
/*
+ * expand_planner_arrays+ * Expand the PlannerInfo arrays by add_size members and initialize the+ * the newly added bytes with zero+ */+void+expand_planner_arrays(PlannerInfo *root, int add_size)+{+ int new_size;++ Assert(add_size > 0);++ new_size = root->simple_rel_array_size + add_size;++ /* Expand various arrays and 0-initialize added bytes. */+ root->simple_rte_array = (RangeTblEntry **)+ repalloc(root->simple_rte_array,+ sizeof(RangeTblEntry *) * new_size);+ MemSet(root->simple_rte_array + root->simple_rel_array_size,+ 0, sizeof(RangeTblEntry *) * add_size);+ root->simple_rel_array = (RelOptInfo **)+ repalloc(root->simple_rel_array,+ sizeof(RelOptInfo *) * new_size);+ MemSet(root->simple_rel_array + root->simple_rel_array_size,+ 0, sizeof(RelOptInfo *) * add_size);++ if (root->append_rel_array)+ {+ root->append_rel_array = (AppendRelInfo **)+ repalloc(root->append_rel_array,+ sizeof(AppendRelInfo *) * new_size);+ MemSet(root->append_rel_array + root->simple_rel_array_size,+ 0, sizeof(AppendRelInfo *) * add_size);+ }+ else+ {+ root->append_rel_array = (AppendRelInfo **)+ palloc0(sizeof(AppendRelInfo *) *+ new_size);+ }++ root->simple_rel_array_size = new_size;+}++/*
* build_simple_rel
* Construct a new RelOptInfo for a base relation or 'other' relation.
*/
@@ -295,22 +339,21 @@ add_appendrel_other_rels(PlannerInfo *root, RangeTblEntry *rte, Index rti)
{
ListCell *l;
RelOptInfo *rel;
- int i;-- rel = find_base_rel(root, rti);
/*
- * For partitioned tables, we need to store the child RelOptInfos in the- * rel->part_rels array too.+ * Add inheritance children to the query. For child tables that are+ * themselves partitioned, their children will be added recursively.
*/
- if (rel->part_scheme)+ if (rte->rtekind == RTE_RELATION)
{
- Assert(rel->nparts > 0);- rel->part_rels = (RelOptInfo **)- palloc0(sizeof(RelOptInfo *) * rel->nparts);+ expand_inherited_rtentry(root, rte, rti);+ return;
}
- i = 0;+ /* Add other rels of flattened UNION ALL children. */+ Assert(rte->rtekind == RTE_SUBQUERY);+ rel = find_base_rel(root, rti);+
foreach(l, root->append_rel_list)
{
AppendRelInfo *appinfo = lfirst(l);
@@ -326,30 +369,10 @@ add_appendrel_other_rels(PlannerInfo *root, RangeTblEntry *rte, Index rti)
Assert(childrte != NULL);
childrel = build_simple_rel(root, childRTindex, rel);
- /*- * For partitioned parents, we also need to add childrel to its- * part_rels array. The order in which child tables appear in- * append_rel_list is same as the order in which they appear in the- * parent's PartitionDesc, so assigning partitions like this works.- */- if (rel->part_scheme != NULL)- {- Assert(i < rel->nparts);- rel->part_rels[i] = childrel;- }-- i++;-
/* Child may itself be an inherited relation. */
if (childrte->inh)
add_appendrel_other_rels(root, childrte, childRTindex);
}
-- /*- * For a partitioned table with non-zero number of partitions, we must- * have assigned all elements of its part_rels array.- */- Assert(rel->nparts == 0 || i == rel->nparts);
}
/*
diff --git a/src/include/optimizer/inherit.h b/src/include/optimizer/inherit.hindex d2418f15cf..427c1d0abe 100644--- a/src/include/optimizer/inherit.h+++ b/src/include/optimizer/inherit.h@@ -16,7 +16,7 @@
#include "nodes/pathnodes.h"
--extern void expand_inherited_tables(PlannerInfo *root);+extern void expand_inherited_rtentry(PlannerInfo *root, RangeTblEntry *rte,+ Index rti);
#endif /* INHERIT_H */
diff --git a/src/include/optimizer/pathnode.h b/src/include/optimizer/pathnode.hindex 1a07963a7d..60361507d2 100644--- a/src/include/optimizer/pathnode.h+++ b/src/include/optimizer/pathnode.h@@ -277,6 +277,7 @@ extern Path *reparameterize_path_by_child(PlannerInfo *root, Path *path,
*/
extern void setup_simple_rel_arrays(PlannerInfo *root);
extern void setup_append_rel_array(PlannerInfo *root);
+extern void expand_planner_arrays(PlannerInfo *root, int add_size);
extern RelOptInfo *build_simple_rel(PlannerInfo *root, int relid,
RelOptInfo *parent);
extern void add_appendrel_other_rels(PlannerInfo *root, RangeTblEntry *rte,
diff --git a/src/test/regress/expected/partition_prune.out b/src/test/regress/expected/partition_prune.outindex 30946f77b6..71942394ba 100644--- a/src/test/regress/expected/partition_prune.out+++ b/src/test/regress/expected/partition_prune.out@@ -2559,16 +2559,16 @@ update ab_a1 set b = 3 from ab where ab.a = 1 and ab.a = ab_a1.a;
Index Cond: (a = 1)
-> Nested Loop (actual rows=1 loops=1)
-> Append (actual rows=1 loops=1)
- -> Bitmap Heap Scan on ab_a1_b1 ab_a1_b1_1 (actual rows=0 loops=1)+ -> Bitmap Heap Scan on ab_a1_b1 ab_a1_b1_2 (actual rows=0 loops=1)
Recheck Cond: (a = 1)
-> Bitmap Index Scan on ab_a1_b1_a_idx (actual rows=0 loops=1)
Index Cond: (a = 1)
- -> Bitmap Heap Scan on ab_a1_b2 ab_a1_b2_1 (actual rows=1 loops=1)+ -> Bitmap Heap Scan on ab_a1_b2 ab_a1_b2_2 (actual rows=1 loops=1)
Recheck Cond: (a = 1)
Heap Blocks: exact=1
-> Bitmap Index Scan on ab_a1_b2_a_idx (actual rows=1 loops=1)
Index Cond: (a = 1)
- -> Bitmap Heap Scan on ab_a1_b3 ab_a1_b3_1 (actual rows=0 loops=1)+ -> Bitmap Heap Scan on ab_a1_b3 ab_a1_b3_2 (actual rows=0 loops=1)
Recheck Cond: (a = 1)
-> Bitmap Index Scan on ab_a1_b3_a_idx (actual rows=1 loops=1)
Index Cond: (a = 1)
@@ -2580,16 +2580,16 @@ update ab_a1 set b = 3 from ab where ab.a = 1 and ab.a = ab_a1.a;
Index Cond: (a = 1)
-> Nested Loop (actual rows=0 loops=1)
-> Append (actual rows=1 loops=1)
- -> Bitmap Heap Scan on ab_a1_b1 ab_a1_b1_1 (actual rows=0 loops=1)+ -> Bitmap Heap Scan on ab_a1_b1 ab_a1_b1_3 (actual rows=0 loops=1)
Recheck Cond: (a = 1)
-> Bitmap Index Scan on ab_a1_b1_a_idx (actual rows=0 loops=1)
Index Cond: (a = 1)
- -> Bitmap Heap Scan on ab_a1_b2 ab_a1_b2_1 (actual rows=1 loops=1)+ -> Bitmap Heap Scan on ab_a1_b2 ab_a1_b2_3 (actual rows=1 loops=1)
Recheck Cond: (a = 1)
Heap Blocks: exact=1
-> Bitmap Index Scan on ab_a1_b2_a_idx (actual rows=1 loops=1)
Index Cond: (a = 1)
- -> Bitmap Heap Scan on ab_a1_b3 ab_a1_b3_1 (actual rows=0 loops=1)+ -> Bitmap Heap Scan on ab_a1_b3 ab_a1_b3_3 (actual rows=0 loops=1)
Recheck Cond: (a = 1)
-> Bitmap Index Scan on ab_a1_b3_a_idx (actual rows=1 loops=1)
Index Cond: (a = 1)
diff --git a/src/test/regress/expected/rowsecurity.out b/src/test/regress/expected/rowsecurity.outindex bad5199d9e..37ef1deab6 100644--- a/src/test/regress/expected/rowsecurity.out+++ b/src/test/regress/expected/rowsecurity.out@@ -1815,26 +1815,26 @@ AND f_leak(t1_1.b) AND f_leak(t1_2.b) RETURNING *, t1_1, t1_2;
-> Seq Scan on t3 t1_2_2
Filter: ((a = 4) AND ((a % 2) = 0) AND f_leak(b))
-> Nested Loop
- Join Filter: (t1_1_1.b = t1_2.b)+ Join Filter: (t1_1_1.b = t1_2_3.b)
-> Seq Scan on t2 t1_1_1
Filter: ((a = 4) AND ((a % 2) = 0) AND f_leak(b))
-> Append
- -> Seq Scan on t1 t1_2+ -> Seq Scan on t1 t1_2_3
Filter: ((a = 4) AND ((a % 2) = 0) AND f_leak(b))
- -> Seq Scan on t2 t1_2_1+ -> Seq Scan on t2 t1_2_4
Filter: ((a = 4) AND ((a % 2) = 0) AND f_leak(b))
- -> Seq Scan on t3 t1_2_2+ -> Seq Scan on t3 t1_2_5
Filter: ((a = 4) AND ((a % 2) = 0) AND f_leak(b))
-> Nested Loop
- Join Filter: (t1_1_2.b = t1_2.b)+ Join Filter: (t1_1_2.b = t1_2_6.b)
-> Seq Scan on t3 t1_1_2
Filter: ((a = 4) AND ((a % 2) = 0) AND f_leak(b))
-> Append
- -> Seq Scan on t1 t1_2+ -> Seq Scan on t1 t1_2_6
Filter: ((a = 4) AND ((a % 2) = 0) AND f_leak(b))
- -> Seq Scan on t2 t1_2_1+ -> Seq Scan on t2 t1_2_7
Filter: ((a = 4) AND ((a % 2) = 0) AND f_leak(b))
- -> Seq Scan on t3 t1_2_2+ -> Seq Scan on t3 t1_2_8
Filter: ((a = 4) AND ((a % 2) = 0) AND f_leak(b))
(37 rows)
--
2.11.0
From 0c7eff3c9e0f6ba392c51a6c1bbbbe2b352aea88 Mon Sep 17 00:00:00 2001
From: amit <amitlangote09@gmail.com>
Date: Mon, 4 Mar 2019 15:52:11 +0900
Subject: [PATCH v34 4/9] Adjust inheritance_planner to reuse source child
tables
Set of source inheritance child tables won't change across repeated
planning of the query for different target child relations. So,
note down the RangeTblEntrys, AppendRelInfos, and PlanRowMarks of
the source inheritance child tables after the planning for the first
child table is finished. When planning for the subsequent child
target tables, put the saved objects of source inheritance child
tables into the child PlannerInfos and set contains_inherit_children
to true, so that query_planner won't add them again.
This restores the regression test outputs to their original state,
because like before, source inheritance child tables are not added
multiple times.
---
contrib/postgres_fdw/expected/postgres_fdw.out | 46 +++----
src/backend/nodes/outfuncs.c | 1 +
src/backend/optimizer/plan/planner.c | 176 +++++++++++++++----------
src/backend/optimizer/util/relnode.c | 47 ++++++-
src/include/nodes/pathnodes.h | 7 +
src/test/regress/expected/partition_prune.out | 66 +++++++++-
src/test/regress/expected/rowsecurity.out | 16 +--
src/test/regress/sql/partition_prune.sql | 7 +
8 files changed, 258 insertions(+), 108 deletions(-)
diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.outindex bbc9ead2e9..c912e95fe1 100644--- a/contrib/postgres_fdw/expected/postgres_fdw.out+++ b/contrib/postgres_fdw/expected/postgres_fdw.out@@ -7190,8 +7190,8 @@ select * from bar where f1 in (select f1 from foo) for share;
-- Check UPDATE with inherited target and an inherited source table
explain (verbose, costs off)
update bar set f2 = f2 + 100 where f1 in (select f1 from foo);
- QUERY PLAN ----------------------------------------------------------------------------------------------------+ QUERY PLAN +---------------------------------------------------------------------------------------------
Update on public.bar
Update on public.bar
Foreign Update on public.bar2
@@ -7214,22 +7214,22 @@ update bar set f2 = f2 + 100 where f1 in (select f1 from foo);
Output: foo2.f1, foo2.ctid, foo2.*, foo2.tableoid
Remote SQL: SELECT f1, f2, f3, ctid FROM public.loct1
-> Hash Join
- Output: bar2.f1, (bar2.f2 + 100), bar2.f3, bar2.ctid, foo_1.ctid, foo_1.*, foo_1.tableoid+ Output: bar2.f1, (bar2.f2 + 100), bar2.f3, bar2.ctid, foo.ctid, foo.*, foo.tableoid
Inner Unique: true
- Hash Cond: (bar2.f1 = foo_1.f1)+ Hash Cond: (bar2.f1 = foo.f1)
-> Foreign Scan on public.bar2
Output: bar2.f1, bar2.f2, bar2.f3, bar2.ctid
Remote SQL: SELECT f1, f2, f3, ctid FROM public.loct2 FOR UPDATE
-> Hash
- Output: foo_1.f1, foo_1.ctid, foo_1.*, foo_1.tableoid+ Output: foo.f1, foo.ctid, foo.*, foo.tableoid
-> HashAggregate
- Output: foo_1.f1, foo_1.ctid, foo_1.*, foo_1.tableoid- Group Key: foo_1.f1+ Output: foo.f1, foo.ctid, foo.*, foo.tableoid+ Group Key: foo.f1
-> Append
- -> Seq Scan on public.foo foo_1- Output: foo_1.f1, foo_1.ctid, foo_1.*, foo_1.tableoid- -> Foreign Scan on public.foo2 foo2_1- Output: foo2_1.f1, foo2_1.ctid, foo2_1.*, foo2_1.tableoid+ -> Seq Scan on public.foo+ Output: foo.f1, foo.ctid, foo.*, foo.tableoid+ -> Foreign Scan on public.foo2+ Output: foo2.f1, foo2.ctid, foo2.*, foo2.tableoid
Remote SQL: SELECT f1, f2, f3, ctid FROM public.loct1
(39 rows)
@@ -7276,8 +7276,8 @@ where bar.f1 = ss.f1;
-> Seq Scan on public.bar
Output: bar.f1, bar.f2, bar.ctid
-> Merge Join
- Output: bar2.f1, (bar2.f2 + 100), bar2.f3, bar2.ctid, (ROW(foo_2.f1))- Merge Cond: (bar2.f1 = foo_2.f1)+ Output: bar2.f1, (bar2.f2 + 100), bar2.f3, bar2.ctid, (ROW(foo.f1))+ Merge Cond: (bar2.f1 = foo.f1)
-> Sort
Output: bar2.f1, bar2.f2, bar2.f3, bar2.ctid
Sort Key: bar2.f1
@@ -7285,18 +7285,18 @@ where bar.f1 = ss.f1;
Output: bar2.f1, bar2.f2, bar2.f3, bar2.ctid
Remote SQL: SELECT f1, f2, f3, ctid FROM public.loct2 FOR UPDATE
-> Sort
- Output: foo_2.f1, (ROW(foo_2.f1))- Sort Key: foo_2.f1+ Output: foo.f1, (ROW(foo.f1))+ Sort Key: foo.f1
-> Append
- -> Seq Scan on public.foo foo_2- Output: foo_2.f1, ROW(foo_2.f1)- -> Foreign Scan on public.foo2 foo2_2- Output: foo2_2.f1, ROW(foo2_2.f1)+ -> Seq Scan on public.foo+ Output: foo.f1, ROW(foo.f1)+ -> Foreign Scan on public.foo2+ Output: foo2.f1, ROW(foo2.f1)
Remote SQL: SELECT f1 FROM public.loct1
- -> Seq Scan on public.foo foo_3- Output: (foo_3.f1 + 3), ROW((foo_3.f1 + 3))- -> Foreign Scan on public.foo2 foo2_3- Output: (foo2_3.f1 + 3), ROW((foo2_3.f1 + 3))+ -> Seq Scan on public.foo foo_1+ Output: (foo_1.f1 + 3), ROW((foo_1.f1 + 3))+ -> Foreign Scan on public.foo2 foo2_1+ Output: (foo2_1.f1 + 3), ROW((foo2_1.f1 + 3))
Remote SQL: SELECT f1 FROM public.loct1
(45 rows)
diff --git a/src/backend/nodes/outfuncs.c b/src/backend/nodes/outfuncs.cindex 69179a07c3..cd4741d36b 100644--- a/src/backend/nodes/outfuncs.c+++ b/src/backend/nodes/outfuncs.c@@ -2220,6 +2220,7 @@ _outPlannerInfo(StringInfo str, const PlannerInfo *node)
WRITE_BITMAPSET_FIELD(curOuterRels);
WRITE_NODE_FIELD(curOuterParams);
WRITE_BOOL_FIELD(partColsUpdated);
+ WRITE_BOOL_FIELD(contains_inherit_children);
}
static void
diff --git a/src/backend/optimizer/plan/planner.c b/src/backend/optimizer/plan/planner.cindex 02ca1a283a..95f69acac5 100644--- a/src/backend/optimizer/plan/planner.c+++ b/src/backend/optimizer/plan/planner.c@@ -637,6 +637,7 @@ subquery_planner(PlannerGlobal *glob, Query *parse,
root->wt_param_id = -1;
root->non_recursive_path = NULL;
root->partColsUpdated = false;
+ root->contains_inherit_children = false;
/*
* If there is a WITH list, process each WITH query and either convert it
@@ -1188,7 +1189,6 @@ inheritance_planner(PlannerInfo *root)
Query *parse = root->parse;
int top_parentRTindex = parse->resultRelation;
Bitmapset *subqueryRTindexes;
- Bitmapset *modifiableARIindexes;
int nominalRelation = -1;
Index rootRelation = 0;
List *final_rtable = NIL;
@@ -1210,6 +1210,10 @@ inheritance_planner(PlannerInfo *root)
Bitmapset *parent_relids = bms_make_singleton(top_parentRTindex);
PlannerInfo **parent_roots = NULL;
List *orig_append_rel_list = list_copy(root->append_rel_list);
+ List *source_appinfos = NIL;+ List *source_child_rowmarks = NIL;+ List *source_child_rtes = NIL;+ bool is_first_child;
Assert(parse->commandType != CMD_INSERT);
@@ -1217,7 +1221,8 @@ inheritance_planner(PlannerInfo *root)
* Add child target relations. Note that this only adds the children of
* the query's target relation and no other. Especially, the children of
* any source relations are added by query_planner() during the planning
- * of each child query.+ * of the 1st child query and reused for the planning of subsequent child+ * queries.
*/
parent_rte = rt_fetch(top_parentRTindex, root->parse->rtable);
@@ -1266,32 +1271,6 @@ inheritance_planner(PlannerInfo *root)
}
/*
- * Next, we want to identify which AppendRelInfo items contain references- * to any of the aforesaid subquery RTEs. These items will need to be- * copied and modified to adjust their subquery references; whereas the- * other ones need not be touched. It's worth being tense over this- * because we can usually avoid processing most of the AppendRelInfo- * items, thereby saving O(N^2) space and time when the target is a large- * inheritance tree. We can identify AppendRelInfo items by their- * child_relid, since that should be unique within the list.- */- modifiableARIindexes = NULL;- if (subqueryRTindexes != NULL)- {- foreach(lc, root->append_rel_list)- {- AppendRelInfo *appinfo = lfirst_node(AppendRelInfo, lc);-- if (bms_is_member(appinfo->parent_relid, subqueryRTindexes) ||- bms_is_member(appinfo->child_relid, subqueryRTindexes) ||- bms_overlap(pull_varnos((Node *) appinfo->translated_vars),- subqueryRTindexes))- modifiableARIindexes = bms_add_member(modifiableARIindexes,- appinfo->child_relid);- }- }-- /*
* If the parent RTE is a partitioned table, we should use that as the
* nominal target relation, because the RTEs added for partitioned tables
* (including the root parent) as child members of the inheritance set do
@@ -1320,6 +1299,7 @@ inheritance_planner(PlannerInfo *root)
/*
* And now we can get on with generating a plan for each child table.
*/
+ is_first_child = true;
foreach(lc, root->append_rel_list)
{
AppendRelInfo *appinfo = lfirst_node(AppendRelInfo, lc);
@@ -1348,9 +1328,6 @@ inheritance_planner(PlannerInfo *root)
subroot = makeNode(PlannerInfo);
memcpy(subroot, parent_root, sizeof(PlannerInfo));
- /* grouping_planner doesn't need to see the target children. */- subroot->append_rel_list = list_copy(orig_append_rel_list);-
/*
* Generate modified query with this rel as target. We first apply
* adjust_appendrel_attrs, which copies the Query and changes
@@ -1433,30 +1410,45 @@ inheritance_planner(PlannerInfo *root)
subroot->rowMarks = copyObject(parent_root->rowMarks);
/*
- * The append_rel_list likewise might contain references to subquery- * RTEs (if any subqueries were flattenable UNION ALLs). So prepare- * to apply ChangeVarNodes to that, too. As explained above, we only- * want to copy items that actually contain such references; the rest- * can just get linked into the subroot's append_rel_list.- *- * If we know there are no such references, we can just use the outer- * append_rel_list unmodified.+ * Prepare to apply ChangeVarNodes to orig_append_rel_list by copying+ * the AppendRelInfos contained in it. Note that orig_append_rel_list+ * contains only the AppendRelInfos pertaining to flattened UNION ALL+ * subqueries, so we'll be applying ChangeVarNodes to all of them.+ * We don't need to look at other members of+ * parent_root->append_rel_list, which are those corresponding to+ * child target relations, because they won't contain any subquery+ * references.
*/
- if (modifiableARIindexes != NULL)+ subroot->append_rel_list = copyObject(orig_append_rel_list);++ /*+ * If this isn't the first child query, then we can use the child+ * objects for source child relations created during the planning of+ * 1st child query. IOW, this planning run doesn't need to create the+ * child objects again, indicated by setting contains_inherit_children+ * of the child PlannerInfo. We use list_copy() on source_child_rtes,+ * because we may add more entries to subroot->parse->rtable below.+ *+ * We don't need to add source_appinfos into subroot yet, because we+ * won't need to modify any of its entries. Only orig_append_rel_list+ * will contain references to the subquery RTEs that we've already+ * made copies of. This also saves time below in the+ * ChangeVarNodes((Node *) subroot->append_rel_list, ...) statement.+ */+ if (!is_first_child)
{
- ListCell *lc2;+ subroot->rowMarks =+ list_concat(subroot->rowMarks,+ list_copy(source_child_rowmarks));+ subroot->parse->rtable =+ list_concat(subroot->parse->rtable,+ list_copy(source_child_rtes));- subroot->append_rel_list = NIL;- foreach(lc2, parent_root->append_rel_list)- {- AppendRelInfo *appinfo2 = lfirst_node(AppendRelInfo, lc2);-- if (bms_is_member(appinfo2->child_relid, modifiableARIindexes))- appinfo2 = copyObject(appinfo2);-- subroot->append_rel_list = lappend(subroot->append_rel_list,- appinfo2);- }+ /*+ * We have added child RTEs and row marks, so the next+ * query_planner doesn't need to add them again.+ */+ subroot->contains_inherit_children = true;
}
/*
@@ -1499,20 +1491,8 @@ inheritance_planner(PlannerInfo *root)
newrti = list_length(subroot->parse->rtable) + 1;
ChangeVarNodes((Node *) subroot->parse, rti, newrti, 0);
ChangeVarNodes((Node *) subroot->rowMarks, rti, newrti, 0);
- /* Skip processing unchanging parts of append_rel_list */- if (modifiableARIindexes != NULL)- {- ListCell *lc2;-- foreach(lc2, subroot->append_rel_list)- {- AppendRelInfo *appinfo2 = lfirst_node(AppendRelInfo, lc2);-- if (bms_is_member(appinfo2->child_relid,- modifiableARIindexes))- ChangeVarNodes((Node *) appinfo2, rti, newrti, 0);- }- }+ ChangeVarNodes((Node *) subroot->append_rel_list, rti,+ newrti, 0);
rte = copyObject(rte);
ChangeVarNodes((Node *) rte->securityQuals, rti, newrti, 0);
subroot->parse->rtable = lappend(subroot->parse->rtable,
@@ -1522,6 +1502,14 @@ inheritance_planner(PlannerInfo *root)
}
}
+ /*+ * Concatenate source AppendRelInfos, so the source child objects that+ * we hooked into subroot above are discoverable.+ */+ if (!is_first_child)+ subroot->append_rel_list = list_concat(subroot->append_rel_list,+ source_appinfos);+
/* There shouldn't be any OJ info to translate, as yet */
Assert(subroot->join_info_list == NIL);
/* and we haven't created PlaceHolderInfos, either */
@@ -1540,6 +1528,62 @@ inheritance_planner(PlannerInfo *root)
subpath = sub_final_rel->cheapest_total_path;
/*
+ * If we just finished planning the first child query, record the+ * child objects of source inheritance sets that were generated+ * during planning, if any.+ */+ if (is_first_child && subroot->append_rel_list)+ {+ int n_skip_appinfos = list_length(orig_append_rel_list);+ int n_skip_rowmarks = list_length(parent_root->rowMarks);+ int n_skip_rtes = list_length(parent_parse->rtable);+ ListCell *lc2;++ Assert(source_appinfos == NIL);+ source_appinfos = list_copy_tail(subroot->append_rel_list,+ n_skip_appinfos);+ Assert(source_child_rowmarks == NIL);+ source_child_rowmarks = list_copy_tail(subroot->rowMarks,+ n_skip_rowmarks);+ Assert(source_child_rtes == NIL);+ source_child_rtes = list_copy_tail(subroot->parse->rtable,+ n_skip_rtes);++ /*+ * Parent PlanRowMarks (actually their copies in the child+ * subroot) are modified when adding the child PlanRowMarks.+ * Copy those changes back into the parent_root's copies of those+ * parent PlanRowMarks. Doing that is necessary, because we won't+ * hit the code that adds child PlanRowMarks again.+ *+ * The original parent row marks are the beginning of+ * subroot->rowMarks; skip the rest.+ */+ foreach(lc2, parent_root->rowMarks)+ {+ PlanRowMark *oldrc = lfirst_node(PlanRowMark, lc2);+ ListCell *lc3;++ foreach(lc3, subroot->rowMarks)+ {+ PlanRowMark *newrc = lfirst_node(PlanRowMark, lc3);++ if (oldrc->rti == newrc->rti)+ {+ /* modified in expand_single_inheritance_child() */+ oldrc->allMarkTypes = newrc->allMarkTypes;+ /* modified in expand_inherited_rtentry() */+ oldrc->isParent = newrc->isParent;+ }+ else+ break;+ }+ }++ is_first_child = false;+ }++ /*
* If this child rel was excluded by constraint exclusion, exclude it
* from the result plan.
*/
diff --git a/src/backend/optimizer/util/relnode.c b/src/backend/optimizer/util/relnode.cindex 9dcc3167e0..0bf024b535 100644--- a/src/backend/optimizer/util/relnode.c+++ b/src/backend/optimizer/util/relnode.c@@ -339,21 +339,38 @@ add_appendrel_other_rels(PlannerInfo *root, RangeTblEntry *rte, Index rti)
{
ListCell *l;
RelOptInfo *rel;
+ int i;
/*
- * Add inheritance children to the query. For child tables that are- * themselves partitioned, their children will be added recursively.+ * Add inheritance children to the query if not already done. For child+ * tables that are themselves partitioned, their children will be added+ * recursively.+ *+ * If root already contains the children, we just need to build their+ * "other rel" RelOptInfos, which we do below.
*/
- if (rte->rtekind == RTE_RELATION)+ if (rte->rtekind == RTE_RELATION && !root->contains_inherit_children)
{
expand_inherited_rtentry(root, rte, rti);
return;
}
- /* Add other rels of flattened UNION ALL children. */- Assert(rte->rtekind == RTE_SUBQUERY);+ /* Add other rels. */+ Assert(rte->rtekind == RTE_SUBQUERY || rte->rtekind == RTE_RELATION);
rel = find_base_rel(root, rti);
+ /*+ * For partitioned tables, we need to store the child RelOptInfos in the+ * rel->part_rels array too.+ */+ if (rel->part_scheme)+ {+ Assert(rel->nparts > 0);+ rel->part_rels = (RelOptInfo **)+ palloc0(sizeof(RelOptInfo *) * rel->nparts);+ }++ i = 0;
foreach(l, root->append_rel_list)
{
AppendRelInfo *appinfo = lfirst(l);
@@ -369,10 +386,30 @@ add_appendrel_other_rels(PlannerInfo *root, RangeTblEntry *rte, Index rti)
Assert(childrte != NULL);
childrel = build_simple_rel(root, childRTindex, rel);
+ /*+ * For partitioned parents, we also need to add childrel to its+ * part_rels array. The order in which child tables appear in+ * append_rel_list is same as the order in which they appear in the+ * parent's PartitionDesc, so assigning partitions like this works.+ */+ if (rel->part_scheme != NULL)+ {+ Assert(i < rel->nparts);+ rel->part_rels[i] = childrel;+ }++ i++;+
/* Child may itself be an inherited relation. */
if (childrte->inh)
add_appendrel_other_rels(root, childrte, childRTindex);
}
++ /*+ * For a partitioned table with non-zero number of partitions, we must+ * have assigned all elements of its part_rels array.+ */+ Assert(rel->nparts == 0 || i == rel->nparts);
}
/*
diff --git a/src/include/nodes/pathnodes.h b/src/include/nodes/pathnodes.hindex 253e0b7e48..31811f9adb 100644--- a/src/include/nodes/pathnodes.h+++ b/src/include/nodes/pathnodes.h@@ -350,6 +350,13 @@ struct PlannerInfo
/* Does this query modify any partition key columns? */
bool partColsUpdated;
++ /*+ * Does this PlannerInfo and its Query object contain *all* inheritance+ * children? If true, the RTEs, the AppendRelInfos, and the PlanRowMarks+ * of all the children are assumed to be present.+ */+ bool contains_inherit_children;
};
diff --git a/src/test/regress/expected/partition_prune.out b/src/test/regress/expected/partition_prune.outindex 71942394ba..f1115c561e 100644--- a/src/test/regress/expected/partition_prune.out+++ b/src/test/regress/expected/partition_prune.out@@ -2559,16 +2559,16 @@ update ab_a1 set b = 3 from ab where ab.a = 1 and ab.a = ab_a1.a;
Index Cond: (a = 1)
-> Nested Loop (actual rows=1 loops=1)
-> Append (actual rows=1 loops=1)
- -> Bitmap Heap Scan on ab_a1_b1 ab_a1_b1_2 (actual rows=0 loops=1)+ -> Bitmap Heap Scan on ab_a1_b1 ab_a1_b1_1 (actual rows=0 loops=1)
Recheck Cond: (a = 1)
-> Bitmap Index Scan on ab_a1_b1_a_idx (actual rows=0 loops=1)
Index Cond: (a = 1)
- -> Bitmap Heap Scan on ab_a1_b2 ab_a1_b2_2 (actual rows=1 loops=1)+ -> Bitmap Heap Scan on ab_a1_b2 ab_a1_b2_1 (actual rows=1 loops=1)
Recheck Cond: (a = 1)
Heap Blocks: exact=1
-> Bitmap Index Scan on ab_a1_b2_a_idx (actual rows=1 loops=1)
Index Cond: (a = 1)
- -> Bitmap Heap Scan on ab_a1_b3 ab_a1_b3_2 (actual rows=0 loops=1)+ -> Bitmap Heap Scan on ab_a1_b3 ab_a1_b3_1 (actual rows=0 loops=1)
Recheck Cond: (a = 1)
-> Bitmap Index Scan on ab_a1_b3_a_idx (actual rows=1 loops=1)
Index Cond: (a = 1)
@@ -2580,16 +2580,16 @@ update ab_a1 set b = 3 from ab where ab.a = 1 and ab.a = ab_a1.a;
Index Cond: (a = 1)
-> Nested Loop (actual rows=0 loops=1)
-> Append (actual rows=1 loops=1)
- -> Bitmap Heap Scan on ab_a1_b1 ab_a1_b1_3 (actual rows=0 loops=1)+ -> Bitmap Heap Scan on ab_a1_b1 ab_a1_b1_1 (actual rows=0 loops=1)
Recheck Cond: (a = 1)
-> Bitmap Index Scan on ab_a1_b1_a_idx (actual rows=0 loops=1)
Index Cond: (a = 1)
- -> Bitmap Heap Scan on ab_a1_b2 ab_a1_b2_3 (actual rows=1 loops=1)+ -> Bitmap Heap Scan on ab_a1_b2 ab_a1_b2_1 (actual rows=1 loops=1)
Recheck Cond: (a = 1)
Heap Blocks: exact=1
-> Bitmap Index Scan on ab_a1_b2_a_idx (actual rows=1 loops=1)
Index Cond: (a = 1)
- -> Bitmap Heap Scan on ab_a1_b3 ab_a1_b3_3 (actual rows=0 loops=1)+ -> Bitmap Heap Scan on ab_a1_b3 ab_a1_b3_1 (actual rows=0 loops=1)
Recheck Cond: (a = 1)
-> Bitmap Index Scan on ab_a1_b3_a_idx (actual rows=1 loops=1)
Index Cond: (a = 1)
@@ -2606,6 +2606,60 @@ table ab;
1 | 3
(1 row)
+-- Test UPDATE where source relation has run-time pruning enabled+truncate ab;+insert into ab values (1, 1), (1, 2), (1, 3), (2, 1);+explain (analyze, costs off, summary off, timing off)+update ab_a1 set b = 3 from ab_a2 where ab_a2.b = (select 1);+ QUERY PLAN +----------------------------------------------------------------------+ Update on ab_a1 (actual rows=0 loops=1)+ Update on ab_a1_b1+ Update on ab_a1_b2+ Update on ab_a1_b3+ InitPlan 1 (returns $0)+ -> Result (actual rows=1 loops=1)+ -> Nested Loop (actual rows=1 loops=1)+ -> Seq Scan on ab_a1_b1 (actual rows=1 loops=1)+ -> Materialize (actual rows=1 loops=1)+ -> Append (actual rows=1 loops=1)+ -> Seq Scan on ab_a2_b1 (actual rows=1 loops=1)+ Filter: (b = $0)+ -> Seq Scan on ab_a2_b2 (never executed)+ Filter: (b = $0)+ -> Seq Scan on ab_a2_b3 (never executed)+ Filter: (b = $0)+ -> Nested Loop (actual rows=1 loops=1)+ -> Seq Scan on ab_a1_b2 (actual rows=1 loops=1)+ -> Materialize (actual rows=1 loops=1)+ -> Append (actual rows=1 loops=1)+ -> Seq Scan on ab_a2_b1 (actual rows=1 loops=1)+ Filter: (b = $0)+ -> Seq Scan on ab_a2_b2 (never executed)+ Filter: (b = $0)+ -> Seq Scan on ab_a2_b3 (never executed)+ Filter: (b = $0)+ -> Nested Loop (actual rows=1 loops=1)+ -> Seq Scan on ab_a1_b3 (actual rows=1 loops=1)+ -> Materialize (actual rows=1 loops=1)+ -> Append (actual rows=1 loops=1)+ -> Seq Scan on ab_a2_b1 (actual rows=1 loops=1)+ Filter: (b = $0)+ -> Seq Scan on ab_a2_b2 (never executed)+ Filter: (b = $0)+ -> Seq Scan on ab_a2_b3 (never executed)+ Filter: (b = $0)+(36 rows)++select tableoid::regclass, * from ab;+ tableoid | a | b +----------+---+---+ ab_a1_b3 | 1 | 3+ ab_a1_b3 | 1 | 3+ ab_a1_b3 | 1 | 3+ ab_a2_b1 | 2 | 1+(4 rows)+
drop table ab, lprt_a;
-- Join
create table tbl1(col1 int);
diff --git a/src/test/regress/expected/rowsecurity.out b/src/test/regress/expected/rowsecurity.outindex 37ef1deab6..bad5199d9e 100644--- a/src/test/regress/expected/rowsecurity.out+++ b/src/test/regress/expected/rowsecurity.out@@ -1815,26 +1815,26 @@ AND f_leak(t1_1.b) AND f_leak(t1_2.b) RETURNING *, t1_1, t1_2;
-> Seq Scan on t3 t1_2_2
Filter: ((a = 4) AND ((a % 2) = 0) AND f_leak(b))
-> Nested Loop
- Join Filter: (t1_1_1.b = t1_2_3.b)+ Join Filter: (t1_1_1.b = t1_2.b)
-> Seq Scan on t2 t1_1_1
Filter: ((a = 4) AND ((a % 2) = 0) AND f_leak(b))
-> Append
- -> Seq Scan on t1 t1_2_3+ -> Seq Scan on t1 t1_2
Filter: ((a = 4) AND ((a % 2) = 0) AND f_leak(b))
- -> Seq Scan on t2 t1_2_4+ -> Seq Scan on t2 t1_2_1
Filter: ((a = 4) AND ((a % 2) = 0) AND f_leak(b))
- -> Seq Scan on t3 t1_2_5+ -> Seq Scan on t3 t1_2_2
Filter: ((a = 4) AND ((a % 2) = 0) AND f_leak(b))
-> Nested Loop
- Join Filter: (t1_1_2.b = t1_2_6.b)+ Join Filter: (t1_1_2.b = t1_2.b)
-> Seq Scan on t3 t1_1_2
Filter: ((a = 4) AND ((a % 2) = 0) AND f_leak(b))
-> Append
- -> Seq Scan on t1 t1_2_6+ -> Seq Scan on t1 t1_2
Filter: ((a = 4) AND ((a % 2) = 0) AND f_leak(b))
- -> Seq Scan on t2 t1_2_7+ -> Seq Scan on t2 t1_2_1
Filter: ((a = 4) AND ((a % 2) = 0) AND f_leak(b))
- -> Seq Scan on t3 t1_2_8+ -> Seq Scan on t3 t1_2_2
Filter: ((a = 4) AND ((a % 2) = 0) AND f_leak(b))
(37 rows)
diff --git a/src/test/regress/sql/partition_prune.sql b/src/test/regress/sql/partition_prune.sqlindex dc327caffd..e15afc2dfd 100644--- a/src/test/regress/sql/partition_prune.sql+++ b/src/test/regress/sql/partition_prune.sql@@ -588,6 +588,13 @@ explain (analyze, costs off, summary off, timing off)
update ab_a1 set b = 3 from ab where ab.a = 1 and ab.a = ab_a1.a;
table ab;
+-- Test UPDATE where source relation has run-time pruning enabled+truncate ab;+insert into ab values (1, 1), (1, 2), (1, 3), (2, 1);+explain (analyze, costs off, summary off, timing off)+update ab_a1 set b = 3 from ab_a2 where ab_a2.b = (select 1);+select tableoid::regclass, * from ab;+
drop table ab, lprt_a;
-- Join
--
2.11.0
From 9be5b886ea04fc33c181ef991e443a6e792b6678 Mon Sep 17 00:00:00 2001
From: amit <amitlangote09@gmail.com>
Date: Fri, 8 Mar 2019 17:50:20 +0900
Subject: [PATCH v34 5/9] Further tweak inheritance_planner to avoid useless
work
When running adjust_appendrel_attrs() on the query, there's no need
for its range table to contain child target RTEs, because they don't
need to be translated. Trimming those off the range table makes
range_table_mutator() finish much quicker, because now it doesn't
have to crawl through potentially many RTEs essentially just copying
them. Note that copying the child target RTEs is unnecessary as
they won't be modified across different planning cycles. The (sub-)
list they are contained still must copied with list_copy(), because
more entries may be added to individual child query's range table.
Furthermore, in a few places where the code iterates over the range
table to first locate and later modify subquery RTEs, it's OK to
ignore the child target RTEs, because there won't be any subquery
RTEs among them and also they won't contain any references to
subquery RTEs that are found.
---
src/backend/optimizer/plan/planner.c | 34 ++++++++++++++++++++++++++++------
1 file changed, 28 insertions(+), 6 deletions(-)
diff --git a/src/backend/optimizer/plan/planner.c b/src/backend/optimizer/plan/planner.cindex 95f69acac5..f50c4321f3 100644--- a/src/backend/optimizer/plan/planner.c+++ b/src/backend/optimizer/plan/planner.c@@ -1209,6 +1209,7 @@ inheritance_planner(PlannerInfo *root)
Query *parent_parse;
Bitmapset *parent_relids = bms_make_singleton(top_parentRTindex);
PlannerInfo **parent_roots = NULL;
+ List *orig_rtable = list_copy(root->parse->rtable);
List *orig_append_rel_list = list_copy(root->append_rel_list);
List *source_appinfos = NIL;
List *source_child_rowmarks = NIL;
@@ -1258,10 +1259,12 @@ inheritance_planner(PlannerInfo *root)
* management of the rowMarks list.
*
* To begin with, generate a bitmapset of the relids of the subquery RTEs.
+ * We use orig_rtable, not parse->rtable, because we wouldn't need to+ * consider any newly added RTEs as they all must be RTE_RELATION entries.
*/
subqueryRTindexes = NULL;
rti = 1;
- foreach(lc, parse->rtable)+ foreach(lc, orig_rtable)
{
RangeTblEntry *rte = lfirst_node(RangeTblEntry, lc);
@@ -1307,6 +1310,7 @@ inheritance_planner(PlannerInfo *root)
RangeTblEntry *child_rte;
RelOptInfo *sub_final_rel;
Path *subpath;
+ List *parent_rtable;
/* append_rel_list contains all append rels; ignore others */
if (!bms_is_member(appinfo->parent_relid, parent_relids))
@@ -1333,11 +1337,30 @@ inheritance_planner(PlannerInfo *root)
* adjust_appendrel_attrs, which copies the Query and changes
* references to the parent RTE to refer to the current child RTE,
* then fool around with subquery RTEs.
+ *+ * In order to avoid range_table_mutator() uselessly spending time on+ * the child target RTEs that were added to query at the beginning of+ * this function, we swap the query's range table with the copy of the+ * range table before they were added (orig_table).
*/
+ parent_rtable = parent_parse->rtable;+ parent_parse->rtable = orig_rtable;
subroot->parse = (Query *)
adjust_appendrel_attrs(parent_root,
(Node *) parent_parse,
1, &appinfo);
+ /*+ * We do however need to add those child target RTEs to the range+ * table so that query_planner can find this child RTE. Other target+ * RTEs will not be accessed during this planning cycle, but we can't+ * just skip them.+ */+ subroot->parse->rtable =+ list_concat(subroot->parse->rtable,+ list_copy_tail(parent_rtable,+ list_length(orig_rtable)));+ /* Put it back for the next child's planning. */+ parent_parse->rtable = parent_rtable;
/*
* If there are securityQuals attached to the parent, move them to the
@@ -1464,17 +1487,16 @@ inheritance_planner(PlannerInfo *root)
/*
* If this isn't the first child Query, generate duplicates of all
* subquery RTEs, and adjust Var numbering to reference the
- * duplicates. To simplify the loop logic, we scan the original rtable- * not the copy just made by adjust_appendrel_attrs; that should be OK- * since subquery RTEs couldn't contain any references to the target- * rel.+ * duplicates. Note that we scan the original rtable before any+ * child target relations were added, which is OK, because no other+ * RTEs would contain references to subquery rels being modified.
*/
if (final_rtable != NIL && subqueryRTindexes != NULL)
{
ListCell *lr;
rti = 1;
- foreach(lr, parent_parse->rtable)+ foreach(lr, orig_rtable)
{
RangeTblEntry *rte = lfirst_node(RangeTblEntry, lr);
--
2.11.0
From 86e66433ac29320c00a6dd020e2a2b6f98c988f1 Mon Sep 17 00:00:00 2001
From: amit <amitlangote09@gmail.com>
Date: Mon, 4 Mar 2019 11:43:38 +0900
Subject: [PATCH v34 6/9] Perform pruning in expand_partitioned_rtentry
This allows to avoid opening/locking partitions that won't be
scanned at all.
expand_partitioned_rtentry recursively processes sub-partitioned
tables, which in turn performs partition pruning for them. To do
so, the translated quals must be added exactly when the child
RelOptInfo is built for the sub-partitioned tables, instead of
delaying that to set_append_rel_size() as is done today. So,
build_simple_rel() itself performs apply_child_basequals() now.
Code for partitioning optimizations that access partition RelOptInfos
from part_rels array of the parent's RelOptInfo must now be made
aware that some entries might be NULL due partition pruning. In the
case of partitionwise join, even a pruned partition on one side of
the join may need to be joined to the non-pruned counterpart on the
other side, if the pruned partition falls on the nullable side of an
outer join. A dummy RelOptInfo containing a dummy path is built in
that case and put into its parent's part_rels array.
---
src/backend/executor/execPartition.c | 17 ++-
src/backend/optimizer/path/allpaths.c | 176 +------------------------------
src/backend/optimizer/path/joinrels.c | 92 ++++++++++++++++-
src/backend/optimizer/plan/planner.c | 8 ++
src/backend/optimizer/util/inherit.c | 21 +++-
src/backend/optimizer/util/relnode.c | 189 +++++++++++++++++++++++++++++++---
src/backend/partitioning/partprune.c | 45 ++++----
7 files changed, 336 insertions(+), 212 deletions(-)
diff --git a/src/backend/executor/execPartition.c b/src/backend/executor/execPartition.cindex 37e96a6013..5b7c386144 100644--- a/src/backend/executor/execPartition.c+++ b/src/backend/executor/execPartition.c@@ -1653,9 +1653,20 @@ ExecCreatePartitionPruneState(PlanState *planstate,
memcpy(pprune->subplan_map, pinfo->subplan_map,
sizeof(int) * pinfo->nparts);
- /* Double-check that list of relations has not changed. */- Assert(memcmp(partdesc->oids, pinfo->relid_map,- pinfo->nparts * sizeof(Oid)) == 0);+#ifdef USE_ASSERT_CHECKING+ /*+ * Double-check that list of unpruned relations has not+ * changed.+ */+ {+ int k;++ /* Pruned partitions are not added to relid_map. */+ for (k = 0; k < pinfo->nparts; k++)+ Assert(partdesc->oids[k] == pinfo->relid_map[k] ||+ pinfo->subplan_map[k] == -1);+ }+#endif
}
else
{
diff --git a/src/backend/optimizer/path/allpaths.c b/src/backend/optimizer/path/allpaths.cindex 3b70ef17fa..31c8f16cae 100644--- a/src/backend/optimizer/path/allpaths.c+++ b/src/backend/optimizer/path/allpaths.c@@ -139,9 +139,6 @@ static void subquery_push_qual(Query *subquery,
static void recurse_push_qual(Node *setOp, Query *topquery,
RangeTblEntry *rte, Index rti, Node *qual);
static void remove_unused_subquery_outputs(Query *subquery, RelOptInfo *rel);
-static bool apply_child_basequals(PlannerInfo *root, RelOptInfo *rel,- RelOptInfo *childrel,- RangeTblEntry *childRTE, AppendRelInfo *appinfo);
/*
@@ -946,8 +943,6 @@ set_append_rel_size(PlannerInfo *root, RelOptInfo *rel,
double *parent_attrsizes;
int nattrs;
ListCell *l;
- Relids live_children = NULL;- bool did_pruning = false;
/* Guard against stack overflow due to overly deep inheritance tree. */
check_stack_depth();
@@ -966,21 +961,6 @@ set_append_rel_size(PlannerInfo *root, RelOptInfo *rel,
rel->partitioned_child_rels = list_make1_int(rti);
/*
- * If the partitioned relation has any baserestrictinfo quals then we- * attempt to use these quals to prune away partitions that cannot- * possibly contain any tuples matching these quals. In this case we'll- * store the relids of all partitions which could possibly contain a- * matching tuple, and skip anything else in the loop below.- */- if (enable_partition_pruning &&- rte->relkind == RELKIND_PARTITIONED_TABLE &&- rel->baserestrictinfo != NIL)- {- live_children = prune_append_rel_partitions(rel);- did_pruning = true;- }-- /*
* If this is a partitioned baserel, set the consider_partitionwise_join
* flag; currently, we only consider partitionwise joins with the baserel
* if its targetlist doesn't contain a whole-row Var.
@@ -1034,30 +1014,11 @@ set_append_rel_size(PlannerInfo *root, RelOptInfo *rel,
childrel = find_base_rel(root, childRTindex);
Assert(childrel->reloptkind == RELOPT_OTHER_MEMBER_REL);
- if (did_pruning && !bms_is_member(appinfo->child_relid, live_children))- {- /* This partition was pruned; skip it. */- set_dummy_rel_pathlist(childrel);+ /* build_simple_rel may have already proven the child to be dummy. */+ if (IS_DUMMY_REL(childrel))
continue;
- }-- /*- * We have to copy the parent's targetlist and quals to the child,- * with appropriate substitution of variables. If any constant false- * or NULL clauses turn up, we can disregard the child right away.- * If not, we can apply constraint exclusion with just the- * baserestrictinfo quals.- */- if (!apply_child_basequals(root, rel, childrel, childRTE, appinfo))- {- /*- * Some restriction clause reduced to constant FALSE or NULL after- * substitution, so this child need not be scanned.- */- set_dummy_rel_pathlist(childrel);- continue;- }+ /* Apply constraint exclusion. */
if (relation_excluded_by_constraints(root, childrel, childRTE))
{
/*
@@ -1069,7 +1030,8 @@ set_append_rel_size(PlannerInfo *root, RelOptInfo *rel,
}
/*
- * CE failed, so finish copying/modifying targetlist and join quals.+ * Constraint exclusion failed, so finish copying/modifying targetlist+ * and join quals.
*
* NB: the resulting childrel->reltarget->exprs may contain arbitrary
* expressions, which otherwise would not occur in a rel's targetlist.
@@ -3562,134 +3524,6 @@ generate_partitionwise_join_paths(PlannerInfo *root, RelOptInfo *rel)
list_free(live_children);
}
-/*- * apply_child_basequals- * Populate childrel's quals based on rel's quals, translating them using- * appinfo.- *- * If any of the resulting clauses evaluate to false or NULL, we return false- * and don't apply any quals. Caller can mark the relation as a dummy rel in- * this case, since it needn't be scanned.- *- * If any resulting clauses evaluate to true, they're unnecessary and we don't- * apply then.- */-static bool-apply_child_basequals(PlannerInfo *root, RelOptInfo *rel,- RelOptInfo *childrel, RangeTblEntry *childRTE,- AppendRelInfo *appinfo)-{- List *childquals;- Index cq_min_security;- ListCell *lc;-- /*- * The child rel's targetlist might contain non-Var expressions, which- * means that substitution into the quals could produce opportunities for- * const-simplification, and perhaps even pseudoconstant quals. Therefore,- * transform each RestrictInfo separately to see if it reduces to a- * constant or pseudoconstant. (We must process them separately to keep- * track of the security level of each qual.)- */- childquals = NIL;- cq_min_security = UINT_MAX;- foreach(lc, rel->baserestrictinfo)- {- RestrictInfo *rinfo = (RestrictInfo *) lfirst(lc);- Node *childqual;- ListCell *lc2;-- Assert(IsA(rinfo, RestrictInfo));- childqual = adjust_appendrel_attrs(root,- (Node *) rinfo->clause,- 1, &appinfo);- childqual = eval_const_expressions(root, childqual);- /* check for flat-out constant */- if (childqual && IsA(childqual, Const))- {- if (((Const *) childqual)->constisnull ||- !DatumGetBool(((Const *) childqual)->constvalue))- {- /* Restriction reduces to constant FALSE or NULL */- return false;- }- /* Restriction reduces to constant TRUE, so drop it */- continue;- }- /* might have gotten an AND clause, if so flatten it */- foreach(lc2, make_ands_implicit((Expr *) childqual))- {- Node *onecq = (Node *) lfirst(lc2);- bool pseudoconstant;-- /* check for pseudoconstant (no Vars or volatile functions) */- pseudoconstant =- !contain_vars_of_level(onecq, 0) &&- !contain_volatile_functions(onecq);- if (pseudoconstant)- {- /* tell createplan.c to check for gating quals */- root->hasPseudoConstantQuals = true;- }- /* reconstitute RestrictInfo with appropriate properties */- childquals = lappend(childquals,- make_restrictinfo((Expr *) onecq,- rinfo->is_pushed_down,- rinfo->outerjoin_delayed,- pseudoconstant,- rinfo->security_level,- NULL, NULL, NULL));- /* track minimum security level among child quals */- cq_min_security = Min(cq_min_security, rinfo->security_level);- }- }-- /*- * In addition to the quals inherited from the parent, we might have- * securityQuals associated with this particular child node. (Currently- * this can only happen in appendrels originating from UNION ALL;- * inheritance child tables don't have their own securityQuals, see- * expand_inherited_rtentry().) Pull any such securityQuals up into the- * baserestrictinfo for the child. This is similar to- * process_security_barrier_quals() for the parent rel, except that we- * can't make any general deductions from such quals, since they don't- * hold for the whole appendrel.- */- if (childRTE->securityQuals)- {- Index security_level = 0;-- foreach(lc, childRTE->securityQuals)- {- List *qualset = (List *) lfirst(lc);- ListCell *lc2;-- foreach(lc2, qualset)- {- Expr *qual = (Expr *) lfirst(lc2);-- /* not likely that we'd see constants here, so no check */- childquals = lappend(childquals,- make_restrictinfo(qual,- true, false, false,- security_level,- NULL, NULL, NULL));- cq_min_security = Min(cq_min_security, security_level);- }- security_level++;- }- Assert(security_level <= root->qual_security_level);- }-- /*- * OK, we've got all the baserestrictinfo quals for this child.- */- childrel->baserestrictinfo = childquals;- childrel->baserestrict_min_security = cq_min_security;-- return true;-}-
/*****************************************************************************
* DEBUG SUPPORT
*****************************************************************************/
diff --git a/src/backend/optimizer/path/joinrels.c b/src/backend/optimizer/path/joinrels.cindex 9604a54b77..83c24a45b8 100644--- a/src/backend/optimizer/path/joinrels.c+++ b/src/backend/optimizer/path/joinrels.c@@ -14,6 +14,7 @@
*/
#include "postgres.h"
+#include "access/table.h"
#include "miscadmin.h"
#include "nodes/nodeFuncs.h"
#include "optimizer/appendinfo.h"
@@ -21,6 +22,8 @@
#include "optimizer/joininfo.h"
#include "optimizer/pathnode.h"
#include "optimizer/paths.h"
+#include "optimizer/tlist.h"+#include "parser/parsetree.h"
#include "partitioning/partbounds.h"
#include "utils/lsyscache.h"
#include "utils/memutils.h"
@@ -51,6 +54,9 @@ static SpecialJoinInfo *build_child_join_sjinfo(PlannerInfo *root,
Relids left_relids, Relids right_relids);
static int match_expr_to_partition_keys(Expr *expr, RelOptInfo *rel,
bool strict_op);
+static RelOptInfo *build_dummy_partition_rel(PlannerInfo *root,+ RelOptInfo *parent, Relation parentrel,+ int partidx);
/*
@@ -1346,6 +1352,8 @@ try_partitionwise_join(PlannerInfo *root, RelOptInfo *rel1, RelOptInfo *rel2,
RelOptInfo *joinrel, SpecialJoinInfo *parent_sjinfo,
List *parent_restrictlist)
{
+ Relation baserel1 = NULL,+ baserel2 = NULL;
bool rel1_is_simple = IS_SIMPLE_REL(rel1);
bool rel2_is_simple = IS_SIMPLE_REL(rel2);
int nparts;
@@ -1396,6 +1404,18 @@ try_partitionwise_join(PlannerInfo *root, RelOptInfo *rel1, RelOptInfo *rel2,
nparts = joinrel->nparts;
+ if (rel1_is_simple)+ {+ RangeTblEntry *rte = planner_rt_fetch(rel1->relid, root);++ baserel1 = table_open(rte->relid, NoLock);+ }+ if (rel2_is_simple)+ {+ RangeTblEntry *rte = planner_rt_fetch(rel2->relid, root);++ baserel2 = table_open(rte->relid, NoLock);+ }
/*
* Create child-join relations for this partitioned join, if those don't
* exist. Add paths to child-joins for a pair of child relations
@@ -1412,6 +1432,13 @@ try_partitionwise_join(PlannerInfo *root, RelOptInfo *rel1, RelOptInfo *rel2,
AppendRelInfo **appinfos;
int nappinfos;
+ if (rel1_is_simple && child_rel1 == NULL)+ child_rel1 = build_dummy_partition_rel(root, rel1, baserel1,+ cnt_parts);+ if (rel2_is_simple && child_rel2 == NULL)+ child_rel2 = build_dummy_partition_rel(root, rel2, baserel2,+ cnt_parts);+
/*
* If a child table has consider_partitionwise_join=false, it means
* that it's a dummy relation for which we skipped setting up tlist
@@ -1472,6 +1499,11 @@ try_partitionwise_join(PlannerInfo *root, RelOptInfo *rel1, RelOptInfo *rel2,
child_joinrel, child_sjinfo,
child_restrictlist);
}
++ if (baserel1)+ table_close(baserel1, NoLock);+ if (baserel2)+ table_close(baserel2, NoLock);
}
/*
@@ -1490,8 +1522,14 @@ update_child_rel_info(PlannerInfo *root,
(Node *) rel->reltarget->exprs,
1, &appinfo);
- /* Make child entries in the EquivalenceClass as well */- if (rel->has_eclass_joins || has_useful_pathkeys(root, rel))+ /*+ * Make child entries in the EquivalenceClass as well. If the childrel+ * appears to be a dummy one (one built by build_dummy_partition_rel()),+ * no need to make any new entries, because anything that would need those+ * can instead use the parent's (rel).+ */+ if (childrel->relid != rel->relid &&+ (rel->has_eclass_joins || has_useful_pathkeys(root, rel)))
add_child_rel_equivalences(root, appinfo, rel, childrel);
childrel->has_eclass_joins = rel->has_eclass_joins;
}
@@ -1702,3 +1740,53 @@ match_expr_to_partition_keys(Expr *expr, RelOptInfo *rel, bool strict_op)
return -1;
}
++/*+ * build_dummy_partition_rel+ * Build a RelOptInfo and AppendRelInfo for a pruned partition+ *+ * This does not result in opening the relation or a range table entry being+ * created. Also, the RelOptInfo thus created is not stored anywhere else+ * beside the parent's part_rels array.+ *+ * The only reason this exists is because partition-wise join, in some cases,+ * needs a RelOptInfo to represent an empty relation that's on the nullable+ * side of an outer join, so that a Path representing the outer join can be+ * created.+ */+static RelOptInfo *+build_dummy_partition_rel(PlannerInfo *root, RelOptInfo *parent,+ Relation parentrel, int partidx)+{+ RelOptInfo *rel;++ Assert(parent->part_rels[partidx] == NULL);++ /* Create minimally valid-looking RelOptInfo with parent's relid. */+ rel = makeNode(RelOptInfo);+ rel->reloptkind = RELOPT_OTHER_MEMBER_REL;+ rel->relid = parent->relid;+ rel->relids = bms_copy(parent->relids);+ if (parent->top_parent_relids)+ rel->top_parent_relids = parent->top_parent_relids;+ else+ rel->top_parent_relids = bms_copy(parent->relids);+ rel->reltarget = copy_pathtarget(parent->reltarget);+ parent->part_rels[partidx] = rel;+ mark_dummy_rel(rel);++ /*+ * Now we'll need a (no-op) AppendRelInfo for parent, because we're+ * setting the dummy partition's relid to be same as the parent's.+ */+ if (root->append_rel_array[parent->relid] == NULL)+ {+ AppendRelInfo *appinfo = make_append_rel_info(parentrel, parentrel,+ parent->relid,+ parent->relid);++ root->append_rel_array[parent->relid] = appinfo;+ }++ return rel;+}diff --git a/src/backend/optimizer/plan/planner.c b/src/backend/optimizer/plan/planner.cindex f50c4321f3..6a0246b47f 100644--- a/src/backend/optimizer/plan/planner.c+++ b/src/backend/optimizer/plan/planner.c@@ -7107,6 +7107,10 @@ apply_scanjoin_target_to_paths(PlannerInfo *root,
List *child_scanjoin_targets = NIL;
ListCell *lc;
+ /* Skip processing pruned partitions. */+ if (child_rel == NULL)+ continue;+
/* Translate scan/join targets for this child. */
appinfos = find_appinfos_by_relids(root, child_rel->relids,
&nappinfos);
@@ -7207,6 +7211,10 @@ create_partitionwise_grouping_paths(PlannerInfo *root,
RelOptInfo *child_grouped_rel;
RelOptInfo *child_partially_grouped_rel;
+ /* Skip processing pruned partitions. */+ if (child_input_rel == NULL)+ continue;+
/* Input child rel must have a path */
Assert(child_input_rel->pathlist != NIL);
diff --git a/src/backend/optimizer/util/inherit.c b/src/backend/optimizer/util/inherit.cindex 24111c46e9..5d7005de96 100644--- a/src/backend/optimizer/util/inherit.c+++ b/src/backend/optimizer/util/inherit.c@@ -26,6 +26,7 @@
#include "optimizer/planner.h"
#include "optimizer/prep.h"
#include "partitioning/partdesc.h"
+#include "partitioning/partprune.h"
#include "utils/rel.h"
@@ -233,6 +234,7 @@ expand_partitioned_rtentry(PlannerInfo *root, RangeTblEntry *parentrte,
PlanRowMark *top_parentrc, LOCKMODE lockmode,
List **appinfos)
{
+ Bitmapset *live_parts = 0;
int i;
RangeTblEntry *childrte;
Index childRTindex;
@@ -273,22 +275,34 @@ expand_partitioned_rtentry(PlannerInfo *root, RangeTblEntry *parentrte,
rel = find_base_rel(root, parentRTindex);
/*
+ * Perform partition pruning using restriction clauses assigned to+ * parent relation. live_parts will contain PartitionDesc indexes+ * of partitions that survive pruning. Below, we will initialize+ * child objects for the surviving partitions.+ */+ if (partdesc->nparts > 0)+ live_parts = prune_append_rel_partitions(rel);++ /*
* Expand simple_rel_array and friends to hold child objects.
*
* We'll need nparts + 1 new slots, because we also consider parent
* as a child relation. FIXME: no longer add the parent as child
*/
- expand_planner_arrays(root, partdesc->nparts + 1);+ expand_planner_arrays(root, bms_num_members(live_parts) + 1);
/*
* We also store partition RelOptInfo pointers in the parent
- * relation.+ * relation. Since we're palloc0'ing, slots corresponding to+ * pruned partitions will contain NULL.
*/
Assert(rel->part_rels == NULL);
if (partdesc->nparts > 0)
rel->part_rels = (RelOptInfo **) palloc0(rel->nparts *
sizeof(RelOptInfo *));
}
+ else+ live_parts = bms_add_range(NULL, 0, partdesc->nparts - 1);
/* First expand the partitioned table itself. */
expand_single_inheritance_child(root, parentrte, parentRTindex, parentrel,
@@ -305,7 +319,8 @@ expand_partitioned_rtentry(PlannerInfo *root, RangeTblEntry *parentrte,
return;
}
- for (i = 0; i < partdesc->nparts; i++)+ i = -1;+ while ((i = bms_next_member(live_parts, i)) >= 0)
{
Oid childOID = partdesc->oids[i];
Relation childrel;
diff --git a/src/backend/optimizer/util/relnode.c b/src/backend/optimizer/util/relnode.cindex 0bf024b535..98ec918ebc 100644--- a/src/backend/optimizer/util/relnode.c+++ b/src/backend/optimizer/util/relnode.c@@ -70,6 +70,9 @@ static void build_child_join_reltarget(PlannerInfo *root,
RelOptInfo *childrel,
int nappinfos,
AppendRelInfo **appinfos);
+static bool apply_child_basequals(PlannerInfo *root, RelOptInfo *rel,+ RelOptInfo *childrel,+ RangeTblEntry *childRTE, AppendRelInfo *appinfo);
/*
@@ -324,10 +327,162 @@ build_simple_rel(PlannerInfo *root, int relid, RelOptInfo *parent)
root->qual_security_level = Max(root->qual_security_level,
list_length(rte->securityQuals));
+ /*+ * Copy the parent's quals to the child, with appropriate substitution of+ * variables. If any constant false or NULL clauses turn up, we can+ * disregard the child as dummy right away.+ *+ * We must copy the quals now, because if the child rel happens to be a+ * partitioned table, expand_partitioned_rtentry (our caller) can+ * immediate perform partition prunning using the translated quals.+ */+ if (parent)+ {+ AppendRelInfo *appinfo = root->append_rel_array[relid];++ Assert(appinfo != NULL);+ if (!apply_child_basequals(root, parent, rel, rte, appinfo))+ {+ /*+ * Some restriction clause reduced to constant FALSE or NULL after+ * substitution, so this child need not be scanned.+ */+ mark_dummy_rel(rel);+ }+ }+
return rel;
}
/*
+ * apply_child_basequals+ * Populate childrel's quals based on rel's quals, translating them using+ * appinfo.+ *+ * If any of the resulting clauses evaluate to false or NULL, we return false+ * and don't apply any quals. Caller can mark the relation as a dummy rel in+ * this case, since it doesn't need to be scanned.+ *+ * If any resulting clauses evaluate to true, they're unnecessary and we don't+ * apply then.+ */+static bool+apply_child_basequals(PlannerInfo *root, RelOptInfo *rel,+ RelOptInfo *childrel, RangeTblEntry *childRTE,+ AppendRelInfo *appinfo)+{+ List *childquals;+ Index cq_min_security;+ ListCell *lc;++ /*+ * The child rel's targetlist might contain non-Var expressions, which+ * means that substitution into the quals could produce opportunities for+ * const-simplification, and perhaps even pseudoconstant quals. Therefore,+ * transform each RestrictInfo separately to see if it reduces to a+ * constant or pseudoconstant. (We must process them separately to keep+ * track of the security level of each qual.)+ */+ childquals = NIL;+ cq_min_security = UINT_MAX;+ foreach(lc, rel->baserestrictinfo)+ {+ RestrictInfo *rinfo = (RestrictInfo *) lfirst(lc);+ Node *childqual;+ ListCell *lc2;++ Assert(IsA(rinfo, RestrictInfo));+ childqual = adjust_appendrel_attrs(root,+ (Node *) rinfo->clause,+ 1, &appinfo);+ childqual = eval_const_expressions(root, childqual);+ /* check for flat-out constant */+ if (childqual && IsA(childqual, Const))+ {+ if (((Const *) childqual)->constisnull ||+ !DatumGetBool(((Const *) childqual)->constvalue))+ {+ /* Restriction reduces to constant FALSE or NULL */+ return false;+ }+ /* Restriction reduces to constant TRUE, so drop it */+ continue;+ }+ /* might have gotten an AND clause, if so flatten it */+ foreach(lc2, make_ands_implicit((Expr *) childqual))+ {+ Node *onecq = (Node *) lfirst(lc2);+ bool pseudoconstant;++ /* check for pseudoconstant (no Vars or volatile functions) */+ pseudoconstant =+ !contain_vars_of_level(onecq, 0) &&+ !contain_volatile_functions(onecq);+ if (pseudoconstant)+ {+ /* tell createplan.c to check for gating quals */+ root->hasPseudoConstantQuals = true;+ }+ /* reconstitute RestrictInfo with appropriate properties */+ childquals = lappend(childquals,+ make_restrictinfo((Expr *) onecq,+ rinfo->is_pushed_down,+ rinfo->outerjoin_delayed,+ pseudoconstant,+ rinfo->security_level,+ NULL, NULL, NULL));+ /* track minimum security level among child quals */+ cq_min_security = Min(cq_min_security, rinfo->security_level);+ }+ }++ /*+ * In addition to the quals inherited from the parent, we might have+ * securityQuals associated with this particular child node. (Currently+ * this can only happen in appendrels originating from UNION ALL;+ * inheritance child tables don't have their own securityQuals, see+ * expand_inherited_rtentry().) Pull any such securityQuals up into the+ * baserestrictinfo for the child. This is similar to+ * process_security_barrier_quals() for the parent rel, except that we+ * can't make any general deductions from such quals, since they don't+ * hold for the whole appendrel.+ */+ if (childRTE->securityQuals)+ {+ Index security_level = 0;++ foreach(lc, childRTE->securityQuals)+ {+ List *qualset = (List *) lfirst(lc);+ ListCell *lc2;++ foreach(lc2, qualset)+ {+ Expr *qual = (Expr *) lfirst(lc2);++ /* not likely that we'd see constants here, so no check */+ childquals = lappend(childquals,+ make_restrictinfo(qual,+ true, false, false,+ security_level,+ NULL, NULL, NULL));+ cq_min_security = Min(cq_min_security, security_level);+ }+ security_level++;+ }+ Assert(security_level <= root->qual_security_level);+ }++ /*+ * OK, we've got all the baserestrictinfo quals for this child.+ */+ childrel->baserestrictinfo = childquals;+ childrel->baserestrict_min_security = cq_min_security;++ return true;+}++/*
* add_appendrel_other_rels
* This adds the "other rel" RelOptInfos a given "appendrel" baserel
*
@@ -339,6 +494,8 @@ add_appendrel_other_rels(PlannerInfo *root, RangeTblEntry *rte, Index rti)
{
ListCell *l;
RelOptInfo *rel;
+ Relation relation = NULL;+ PartitionDesc partdesc = NULL;
int i;
/*
@@ -361,16 +518,20 @@ add_appendrel_other_rels(PlannerInfo *root, RangeTblEntry *rte, Index rti)
/*
* For partitioned tables, we need to store the child RelOptInfos in the
- * rel->part_rels array too.+ * rel->part_rels array too. Some elements of this array might remain set+ * to NULL if the corresponding partitions were pruned and hence not in+ * append_rel_list.
*/
if (rel->part_scheme)
{
Assert(rel->nparts > 0);
rel->part_rels = (RelOptInfo **)
palloc0(sizeof(RelOptInfo *) * rel->nparts);
+ relation = table_open(rte->relid, NoLock);+ partdesc = PartitionDirectoryLookup(root->glob->partition_directory,+ relation);
}
- i = 0;
foreach(l, root->append_rel_list)
{
AppendRelInfo *appinfo = lfirst(l);
@@ -390,26 +551,30 @@ add_appendrel_other_rels(PlannerInfo *root, RangeTblEntry *rte, Index rti)
* For partitioned parents, we also need to add childrel to its
* part_rels array. The order in which child tables appear in
* append_rel_list is same as the order in which they appear in the
- * parent's PartitionDesc, so assigning partitions like this works.+ * parent's PartitionDesc. But considering that some partitions may+ * have been pruned, we need to look up the correct position to+ * assign this childrel by scanning the PartitionDesc.
*/
if (rel->part_scheme != NULL)
{
- Assert(i < rel->nparts);- rel->part_rels[i] = childrel;+ Assert(partdesc != NULL);+ for (i = 0; i < partdesc->nparts; i++)+ {+ if (childrte->relid == partdesc->oids[i])+ {+ rel->part_rels[i] = childrel;+ break;+ }+ }
}
- i++;-
/* Child may itself be an inherited relation. */
if (childrte->inh)
add_appendrel_other_rels(root, childrte, childRTindex);
}
- /*- * For a partitioned table with non-zero number of partitions, we must- * have assigned all elements of its part_rels array.- */- Assert(rel->nparts == 0 || i == rel->nparts);+ if (relation)+ table_close(relation, NoLock);
}
/*
diff --git a/src/backend/partitioning/partprune.c b/src/backend/partitioning/partprune.cindex b5c0889935..9ebded67a9 100644--- a/src/backend/partitioning/partprune.c+++ b/src/backend/partitioning/partprune.c@@ -45,6 +45,7 @@
#include "nodes/makefuncs.h"
#include "nodes/nodeFuncs.h"
#include "optimizer/appendinfo.h"
+#include "optimizer/cost.h"
#include "optimizer/optimizer.h"
#include "optimizer/pathnode.h"
#include "parser/parsetree.h"
@@ -435,18 +436,24 @@ make_partitionedrel_pruneinfo(PlannerInfo *root, RelOptInfo *parentrel,
* is, not pruned already).
*/
subplan_map = (int *) palloc(nparts * sizeof(int));
+ memset(subplan_map, -1, nparts * sizeof(int));
subpart_map = (int *) palloc(nparts * sizeof(int));
- relid_map = (Oid *) palloc(nparts * sizeof(int));+ memset(subpart_map, -1, nparts * sizeof(int));+ relid_map = (Oid *) palloc0(nparts * sizeof(int));
present_parts = NULL;
for (i = 0; i < nparts; i++)
{
RelOptInfo *partrel = subpart->part_rels[i];
- int subplanidx = relid_subplan_map[partrel->relid] - 1;- int subpartidx = relid_subpart_map[partrel->relid] - 1;+ int subplanidx;+ int subpartidx;- subplan_map[i] = subplanidx;- subpart_map[i] = subpartidx;+ /* Skip processing pruned partitions. */+ if (partrel == NULL)+ continue;++ subplan_map[i] = subplanidx = relid_subplan_map[partrel->relid] - 1;+ subpart_map[i] = subpartidx = relid_subpart_map[partrel->relid] - 1;
relid_map[i] = planner_rt_fetch(partrel->relid, root)->relid;
if (subplanidx >= 0)
{
@@ -542,23 +549,20 @@ gen_partprune_steps(RelOptInfo *rel, List *clauses, bool *contradictory)
/*
* prune_append_rel_partitions
- * Returns RT indexes of the minimum set of child partitions which must- * be scanned to satisfy rel's baserestrictinfo quals.+ * Returns indexes into rel->part_rels of the minimum set of child+ * partitions which must be scanned to satisfy rel's baserestrictinfo+ * quals.
*
* Callers must ensure that 'rel' is a partitioned table.
*/
-Relids+Bitmapset *
prune_append_rel_partitions(RelOptInfo *rel)
{
- Relids result;
List *clauses = rel->baserestrictinfo;
List *pruning_steps;
bool contradictory;
PartitionPruneContext context;
- Bitmapset *partindexes;- int i;- Assert(clauses != NIL);
Assert(rel->part_scheme != NULL);
/* If there are no partitions, return the empty set */
@@ -566,6 +570,13 @@ prune_append_rel_partitions(RelOptInfo *rel)
return NULL;
/*
+ * If pruning is disabled or if there are no clauses to prune with,+ * return all partitions.+ */+ if (!enable_partition_pruning || clauses == NIL)+ return bms_add_range(NULL, 0, rel->nparts - 1);++ /*
* Process clauses. If the clauses are found to be contradictory, we can
* return the empty set.
*/
@@ -592,15 +603,7 @@ prune_append_rel_partitions(RelOptInfo *rel)
context.evalexecparams = false;
/* Actual pruning happens here. */
- partindexes = get_matching_partitions(&context, pruning_steps);-- /* Add selected partitions' RT indexes to result. */- i = -1;- result = NULL;- while ((i = bms_next_member(partindexes, i)) >= 0)- result = bms_add_member(result, rel->part_rels[i]->relid);-- return result;+ return get_matching_partitions(&context, pruning_steps);
}
/*
--
2.11.0
From 2c6b94f53d166ef2f9c7d2514afe4efb8574a951 Mon Sep 17 00:00:00 2001
From: amit <amitlangote09@gmail.com>
Date: Mon, 4 Mar 2019 11:57:04 +0900
Subject: [PATCH v34 7/9] Teach planner to only process unpruned partitions
This adds a bitmapset field live_parts to RelOptInfo and stores
the partition indexes of non-dummy partitions in it, meaning it
contains indexes of only those partitions that have a non-NULL
RelOptInfo present in its parent's RelOptInfo's part_rels array.
This speeds up processing partitioned table's partitions compared
to going through the whole part_rels array in a number of places.
Partitionwise join still has to visit each member of part_rels,
because pruned children may fall on the nullable side of an outer
join and an empty Result path must be present for building the
outer join paths.
---
src/backend/optimizer/path/joinrels.c | 3 +++
src/backend/optimizer/plan/planner.c | 18 +++++++++---------
src/backend/optimizer/util/inherit.c | 6 ++++++
src/backend/optimizer/util/relnode.c | 4 ++++
src/backend/partitioning/partprune.c | 19 ++++++-------------
src/include/nodes/pathnodes.h | 4 ++++
6 files changed, 32 insertions(+), 22 deletions(-)
diff --git a/src/backend/optimizer/path/joinrels.c b/src/backend/optimizer/path/joinrels.cindex 83c24a45b8..38fda5832b 100644--- a/src/backend/optimizer/path/joinrels.c+++ b/src/backend/optimizer/path/joinrels.c@@ -1498,6 +1498,9 @@ try_partitionwise_join(PlannerInfo *root, RelOptInfo *rel1, RelOptInfo *rel2,
populate_joinrel_with_paths(root, child_rel1, child_rel2,
child_joinrel, child_sjinfo,
child_restrictlist);
+ if (!IS_DUMMY_REL(child_joinrel))+ joinrel->live_parts = bms_add_member(joinrel->live_parts,+ cnt_parts);
}
if (baserel1)
diff --git a/src/backend/optimizer/plan/planner.c b/src/backend/optimizer/plan/planner.cindex 6a0246b47f..fa1b6de9d2 100644--- a/src/backend/optimizer/plan/planner.c+++ b/src/backend/optimizer/plan/planner.c@@ -7099,7 +7099,9 @@ apply_scanjoin_target_to_paths(PlannerInfo *root,
int partition_idx;
/* Adjust each partition. */
- for (partition_idx = 0; partition_idx < rel->nparts; partition_idx++)+ partition_idx = -1;+ while ((partition_idx = bms_next_member(rel->live_parts,+ partition_idx)) >= 0)
{
RelOptInfo *child_rel = rel->part_rels[partition_idx];
AppendRelInfo **appinfos;
@@ -7107,9 +7109,7 @@ apply_scanjoin_target_to_paths(PlannerInfo *root,
List *child_scanjoin_targets = NIL;
ListCell *lc;
- /* Skip processing pruned partitions. */- if (child_rel == NULL)- continue;+ Assert(child_rel != NULL);
/* Translate scan/join targets for this child. */
appinfos = find_appinfos_by_relids(root, child_rel->relids,
@@ -7189,7 +7189,6 @@ create_partitionwise_grouping_paths(PlannerInfo *root,
PartitionwiseAggregateType patype,
GroupPathExtraData *extra)
{
- int nparts = input_rel->nparts;
int cnt_parts;
List *grouped_live_children = NIL;
List *partially_grouped_live_children = NIL;
@@ -7201,7 +7200,9 @@ create_partitionwise_grouping_paths(PlannerInfo *root,
partially_grouped_rel != NULL);
/* Add paths for partitionwise aggregation/grouping. */
- for (cnt_parts = 0; cnt_parts < nparts; cnt_parts++)+ cnt_parts = -1;+ while ((cnt_parts = bms_next_member(input_rel->live_parts,+ cnt_parts)) >= 0)
{
RelOptInfo *child_input_rel = input_rel->part_rels[cnt_parts];
PathTarget *child_target = copy_pathtarget(target);
@@ -7211,9 +7212,8 @@ create_partitionwise_grouping_paths(PlannerInfo *root,
RelOptInfo *child_grouped_rel;
RelOptInfo *child_partially_grouped_rel;
- /* Skip processing pruned partitions. */- if (child_input_rel == NULL)- continue;+ /* A live partition must have a RelOptInfo. */+ Assert(child_input_rel != NULL);
/* Input child rel must have a path */
Assert(child_input_rel->pathlist != NIL);
diff --git a/src/backend/optimizer/util/inherit.c b/src/backend/optimizer/util/inherit.cindex 5d7005de96..228fd3b339 100644--- a/src/backend/optimizer/util/inherit.c+++ b/src/backend/optimizer/util/inherit.c@@ -284,6 +284,12 @@ expand_partitioned_rtentry(PlannerInfo *root, RangeTblEntry *parentrte,
live_parts = prune_append_rel_partitions(rel);
/*
+ * Later steps that loop over part_rels should use these indexes+ * to access unpruned partitions.+ */+ rel->live_parts = live_parts;++ /*
* Expand simple_rel_array and friends to hold child objects.
*
* We'll need nparts + 1 new slots, because we also consider parent
diff --git a/src/backend/optimizer/util/relnode.c b/src/backend/optimizer/util/relnode.cindex 98ec918ebc..6787c33afa 100644--- a/src/backend/optimizer/util/relnode.c+++ b/src/backend/optimizer/util/relnode.c@@ -563,6 +563,7 @@ add_appendrel_other_rels(PlannerInfo *root, RangeTblEntry *rte, Index rti)
if (childrte->relid == partdesc->oids[i])
{
rel->part_rels[i] = childrel;
+ rel->live_parts = bms_add_member(rel->live_parts, i);
break;
}
}
@@ -1989,6 +1990,9 @@ build_joinrel_partition_info(RelOptInfo *joinrel, RelOptInfo *outer_rel,
joinrel->partexprs[cnt] = partexpr;
joinrel->nullable_partexprs[cnt] = nullable_partexpr;
}
++ /* Partitions will be added by try_partitionwise_join. */+ joinrel->live_parts = NULL;
}
/*
diff --git a/src/backend/partitioning/partprune.c b/src/backend/partitioning/partprune.cindex 9ebded67a9..e766555a9f 100644--- a/src/backend/partitioning/partprune.c+++ b/src/backend/partitioning/partprune.c@@ -440,30 +440,23 @@ make_partitionedrel_pruneinfo(PlannerInfo *root, RelOptInfo *parentrel,
subpart_map = (int *) palloc(nparts * sizeof(int));
memset(subpart_map, -1, nparts * sizeof(int));
relid_map = (Oid *) palloc0(nparts * sizeof(int));
- present_parts = NULL;+ present_parts = bms_copy(subpart->live_parts);- for (i = 0; i < nparts; i++)+ i = -1;+ while ((i = bms_next_member(present_parts, i)) >= 0)
{
RelOptInfo *partrel = subpart->part_rels[i];
int subplanidx;
int subpartidx;
- /* Skip processing pruned partitions. */- if (partrel == NULL)- continue;-+ Assert(partrel != NULL);
subplan_map[i] = subplanidx = relid_subplan_map[partrel->relid] - 1;
subpart_map[i] = subpartidx = relid_subpart_map[partrel->relid] - 1;
relid_map[i] = planner_rt_fetch(partrel->relid, root)->relid;
- if (subplanidx >= 0)- {- present_parts = bms_add_member(present_parts, i);- /* Record finding this subplan */+ /* Record finding this subplan */+ if (subplanidx >= 0)
subplansfound = bms_add_member(subplansfound, subplanidx);
- }- else if (subpartidx >= 0)- present_parts = bms_add_member(present_parts, i);
}
pinfo = makeNode(PartitionedRelPruneInfo);
diff --git a/src/include/nodes/pathnodes.h b/src/include/nodes/pathnodes.hindex 31811f9adb..3298bd78ca 100644--- a/src/include/nodes/pathnodes.h+++ b/src/include/nodes/pathnodes.h@@ -714,6 +714,10 @@ typedef struct RelOptInfo
List *partition_qual; /* partition constraint */
struct RelOptInfo **part_rels; /* Array of RelOptInfos of partitions,
* stored in the same order of bounds */
+ Bitmapset *live_parts; /* Indexes into part_rels of the non-NULL+ * RelOptInfos of unpruned partitions; exists+ * to avoid having to iterate over the entire+ * part_rels array to filter NULL entries. */
List **partexprs; /* Non-nullable partition key expressions. */
List **nullable_partexprs; /* Nullable partition key expressions. */
List *partitioned_child_rels; /* List of RT indexes. */
--
2.11.0
From 7828f62949c4d57c176059dda910ccf1f7deaf96 Mon Sep 17 00:00:00 2001
From: amit <amitlangote09@gmail.com>
Date: Fri, 22 Mar 2019 15:09:45 +0900
Subject: [PATCH v34 8/9] Rearrange the code in make_partitionedrel_pruneinfo
for efficiency
The current code will loop over all partitions to create maps that
will be put into the PartitionedRelPruneInfo of a given partitioned
table, *before* checking if run-time pruning will be needed at all.
This commit rearranges the code such that maps are created *after*
we've determined that run-time will be needed.
---
src/backend/partitioning/partprune.c | 84 ++++++++++++++++++++----------------
1 file changed, 47 insertions(+), 37 deletions(-)
diff --git a/src/backend/partitioning/partprune.c b/src/backend/partitioning/partprune.cindex e766555a9f..6004ae37f3 100644--- a/src/backend/partitioning/partprune.c+++ b/src/backend/partitioning/partprune.c@@ -333,39 +333,24 @@ make_partitionedrel_pruneinfo(PlannerInfo *root, RelOptInfo *parentrel,
*/
relid_subpart_map = palloc0(sizeof(int) * root->simple_rel_array_size);
- /*- * relid_subpart_map maps relid of a non-leaf partition to the index in- * 'partitioned_rels' of that rel (which will also be the index in the- * returned PartitionedRelPruneInfo list of the info for that partition).- */- i = 1;- foreach(lc, partitioned_rels)- {- Index rti = lfirst_int(lc);-- Assert(rti < root->simple_rel_array_size);- /* No duplicates please */- Assert(relid_subpart_map[rti] == 0);-- relid_subpart_map[rti] = i++;- }-
/* We now build a PartitionedRelPruneInfo for each partitioned rel */
+ i = 1;
foreach(lc, partitioned_rels)
{
Index rti = lfirst_int(lc);
RelOptInfo *subpart = find_base_rel(root, rti);
PartitionedRelPruneInfo *pinfo;
- Bitmapset *present_parts;- int nparts = subpart->nparts;
int partnatts = subpart->part_scheme->partnatts;
- int *subplan_map;- int *subpart_map;- Oid *relid_map;
List *partprunequal;
List *pruning_steps;
bool contradictory;
+ /* Map the table's RT index to its position in partitioned_rels. */+ Assert(rti < root->simple_rel_array_size);+ /* No duplicates please */+ Assert(relid_subpart_map[rti] == 0);+ relid_subpart_map[rti] = i++;+
/*
* The first item in the list is the target partitioned relation.
*/
@@ -429,6 +414,45 @@ make_partitionedrel_pruneinfo(PlannerInfo *root, RelOptInfo *parentrel,
return NIL;
}
+ /* Other information will be set in the following loop. */+ pinfo = makeNode(PartitionedRelPruneInfo);+ pinfo->rtindex = rti;+ pinfo->pruning_steps = pruning_steps;++ /*+ * Determine which pruning types should be enabled at this level.+ * This also records paramids relevant to pruning steps in 'pinfo'.+ */+ doruntimeprune |= analyze_partkey_exprs(pinfo, pruning_steps,+ partnatts);++ pinfolist = lappend(pinfolist, pinfo);+ }++ if (!doruntimeprune)+ {+ /* No run-time pruning required. */+ return NIL;+ }++ /*+ * Run-time pruning will be required, so initialize other information.+ * That includes two maps -- one needed to convert partition indexes+ * of leaf partitions to the indexes of their subplans in the subplan+ * list, another needed to convert partition indexes of sub-partitioned+ * partitions to the indexes of their PartitionedRelPruneInfo in the+ * PartitionedRelPruneInfo list.+ */+ foreach(lc, pinfolist)+ {+ PartitionedRelPruneInfo *pinfo = lfirst(lc);+ RelOptInfo *subpart = find_base_rel(root, pinfo->rtindex);+ Bitmapset *present_parts;+ int nparts = subpart->nparts;+ int *subplan_map;+ int *subpart_map;+ Oid *relid_map;+
/*
* Construct the subplan and subpart maps for this partitioning level.
* Here we convert to zero-based indexes, with -1 for empty entries.
@@ -459,30 +483,16 @@ make_partitionedrel_pruneinfo(PlannerInfo *root, RelOptInfo *parentrel,
subplansfound = bms_add_member(subplansfound, subplanidx);
}
- pinfo = makeNode(PartitionedRelPruneInfo);- pinfo->rtindex = rti;- pinfo->pruning_steps = pruning_steps;+ /* Record the maps and other information. */
pinfo->present_parts = present_parts;
pinfo->nparts = nparts;
pinfo->subplan_map = subplan_map;
pinfo->subpart_map = subpart_map;
pinfo->relid_map = relid_map;
-- /* Determine which pruning types should be enabled at this level */- doruntimeprune |= analyze_partkey_exprs(pinfo, pruning_steps,- partnatts);-- pinfolist = lappend(pinfolist, pinfo);
}
pfree(relid_subpart_map);
- if (!doruntimeprune)- {- /* No run-time pruning required. */- return NIL;- }-
*matchedsubplans = subplansfound;
return pinfolist;
--
2.11.0
From 9c9d2f973152277ca334f945cb27b7a065191260 Mon Sep 17 00:00:00 2001
From: amit <amitlangote09@gmail.com>
Date: Mon, 4 Mar 2019 16:03:49 +0900
Subject: [PATCH v34 9/9] Don't copy PartitionBoundInfo in
set_relation_partition_info
As long as we hold a lock on the table, it shouldn't change, so
seems pointless to copy, which can get really expensive as the
number of partitions grows beyond thousands.
---
src/backend/optimizer/util/plancat.c | 10 +++++++---
1 file changed, 7 insertions(+), 3 deletions(-)
diff --git a/src/backend/optimizer/util/plancat.c b/src/backend/optimizer/util/plancat.cindex 2b22dff690..b8f01269be 100644--- a/src/backend/optimizer/util/plancat.c+++ b/src/backend/optimizer/util/plancat.c@@ -2082,13 +2082,11 @@ set_relation_partition_info(PlannerInfo *root, RelOptInfo *rel,
Relation relation)
{
PartitionDesc partdesc;
- PartitionKey partkey;
Assert(relation->rd_rel->relkind == RELKIND_PARTITIONED_TABLE);
partdesc = PartitionDirectoryLookup(root->glob->partition_directory,
relation);
- partkey = RelationGetPartitionKey(relation);
rel->part_scheme = find_partition_scheme(root, relation);
Assert(partdesc != NULL && rel->part_scheme != NULL);
/*
@@ -2098,7 +2096,13 @@ set_relation_partition_info(PlannerInfo *root, RelOptInfo *rel,
* only lazily updated as far as dropped partitions are concerned.
*/
if (partdesc->nparts > 0)
- rel->boundinfo = partition_bounds_copy(partdesc->boundinfo, partkey);+ {+ /*+ * Holding onto the relcache pointer should be OK, as it won't change+ * under us as long as we're holding the lock on the relation.+ */+ rel->boundinfo = partdesc->boundinfo;+ }
rel->nparts = partdesc->nparts;
set_baserel_partition_key_exprs(relation, rel);
rel->partition_qual = RelationGetPartitionQual(relation);
--
2.11.0
Attachments:
[text/plain] v34-0001-Build-other-rels-of-appendrel-baserels-in-a-sepa.patch (11.8K, ../c8dcf0fd-4759-5e1c-ea67-e2d3f98e81bd@lab.ntt.co.jp/2-v34-0001-Build-other-rels-of-appendrel-baserels-in-a-sepa.patch)
download | inline diff:
From b7af3ae17c7e9b585af87331cc56a190ff6ad3d2 Mon Sep 17 00:00:00 2001
From: Amit <amitlangote09@gmail.com>
Date: Sat, 2 Mar 2019 14:13:13 +0900
Subject: [PATCH v34 1/9] Build "other rels" of appendrel baserels in a
separate step
Currently they're built in a stanza in build_simple_rel() which
builds the child rels in the same invocation of build_simple_rel
that's used to build the parent relation. Since query hasn't
been processed to distribute restriction clauses to individual
baserels at this point, we cannot perform partition pruning before
adding the children.
Newly added add_other_rels_to_query() runs *after* query_planner has
distributed restriction clauses to base relations. This will allow
us to use the clauses applied a given partitioned baserel to perform
partition pruning, and add other rels for only the unpruned
partitions. Later patches will do that.
---
src/backend/optimizer/path/allpaths.c | 2 +-
src/backend/optimizer/plan/initsplan.c | 60 +++++++++++++++--
src/backend/optimizer/plan/planmain.c | 15 +++--
src/backend/optimizer/util/relnode.c | 114 +++++++++++++++++++++------------
src/include/optimizer/pathnode.h | 2 +
src/include/optimizer/planmain.h | 1 +
6 files changed, 143 insertions(+), 51 deletions(-)
diff --git a/src/backend/optimizer/path/allpaths.c b/src/backend/optimizer/path/allpaths.cindex b2c5c833f7..3b70ef17fa 100644--- a/src/backend/optimizer/path/allpaths.c+++ b/src/backend/optimizer/path/allpaths.c@@ -1029,7 +1029,7 @@ set_append_rel_size(PlannerInfo *root, RelOptInfo *rel,
/*
* The child rel's RelOptInfo was already created during
- * add_base_rels_to_query.+ * add_other_rels_to_query.
*/
childrel = find_base_rel(root, childRTindex);
Assert(childrel->reloptkind == RELOPT_OTHER_MEMBER_REL);
diff --git a/src/backend/optimizer/plan/initsplan.c b/src/backend/optimizer/plan/initsplan.cindex 2afc3f1dfe..077d3203ba 100644--- a/src/backend/optimizer/plan/initsplan.c+++ b/src/backend/optimizer/plan/initsplan.c@@ -20,6 +20,7 @@
#include "nodes/nodeFuncs.h"
#include "optimizer/clauses.h"
#include "optimizer/cost.h"
+#include "optimizer/inherit.h"
#include "optimizer/joininfo.h"
#include "optimizer/optimizer.h"
#include "optimizer/pathnode.h"
@@ -30,6 +31,7 @@
#include "optimizer/prep.h"
#include "optimizer/restrictinfo.h"
#include "parser/analyze.h"
+#include "parser/parsetree.h"
#include "rewrite/rewriteManip.h"
#include "utils/lsyscache.h"
@@ -97,10 +99,11 @@ static void check_hashjoinable(RestrictInfo *restrictinfo);
* jtnode. Internally, the function recurses through the jointree.
*
* At the end of this process, there should be one baserel RelOptInfo for
- * every non-join RTE that is used in the query. Therefore, this routine- * is the only place that should call build_simple_rel with reloptkind- * RELOPT_BASEREL. (Note: build_simple_rel recurses internally to build- * "other rel" RelOptInfos for the members of any appendrels we find here.)+ * every non-join RTE that is specified in the query. Therefore, this+ * routine is the only place that should call build_simple_rel with+ * reloptkind RELOPT_BASEREL. (Note: "other rel" RelOptInfos for the+ * members of any appendrels we find here are built later when query_planner+ * calls add_other_rels_to_query().)
*/
void
add_base_rels_to_query(PlannerInfo *root, Node *jtnode)
@@ -133,6 +136,55 @@ add_base_rels_to_query(PlannerInfo *root, Node *jtnode)
(int) nodeTag(jtnode));
}
+/*+ * add_other_rels_to_query+ *+ * Scan the query's jointree and for each base rels that is an appendrel,+ * create otherrel RelOptInfos of its children+ *+ * At the end of this process, there should be RelOptInfos for all relations+ * that will be scanned by the query.+ */+void+add_other_rels_to_query(PlannerInfo *root, Node *jtnode)+{+ if (jtnode == NULL)+ return;+ if (IsA(jtnode, RangeTblRef))+ {+ int varno = ((RangeTblRef *) jtnode)->rtindex;+ RangeTblEntry *rte = rt_fetch(varno, root->parse->rtable);++ /*+ * Only the parent subquery of a flattened UNION ALL and an inherited+ * table can have children.+ */+ if (rte->rtekind != RTE_SUBQUERY && rte->rtekind != RTE_RELATION)+ return;++ if (rte->inh)+ (void) add_appendrel_other_rels(root, rte, varno);+ }+ else if (IsA(jtnode, FromExpr))+ {+ FromExpr *f = (FromExpr *) jtnode;+ ListCell *l;++ foreach(l, f->fromlist)+ add_other_rels_to_query(root, lfirst(l));+ }+ else if (IsA(jtnode, JoinExpr))+ {+ JoinExpr *j = (JoinExpr *) jtnode;++ add_other_rels_to_query(root, j->larg);+ add_other_rels_to_query(root, j->rarg);+ }+ else+ elog(ERROR, "unrecognized node type: %d",+ (int) nodeTag(jtnode));+}+
/*****************************************************************************
*
diff --git a/src/backend/optimizer/plan/planmain.c b/src/backend/optimizer/plan/planmain.cindex 3cedd01c98..03c81772a3 100644--- a/src/backend/optimizer/plan/planmain.c+++ b/src/backend/optimizer/plan/planmain.c@@ -159,10 +159,8 @@ query_planner(PlannerInfo *root, List *tlist,
setup_append_rel_array(root);
/*
- * Construct RelOptInfo nodes for all base relations in query, and- * indirectly for all appendrel member relations ("other rels"). This- * will give us a RelOptInfo for every "simple" (non-join) rel involved in- * the query.+ * Construct RelOptInfo nodes for all base relations directly mentioned+ * in query, but not any appendrel member relations ("other rels") yet.
*
* Note: the reason we find the rels by searching the jointree and
* appendrel list, rather than just scanning the rangetable, is that the
@@ -204,6 +202,15 @@ query_planner(PlannerInfo *root, List *tlist,
generate_base_implied_equalities(root);
/*
+ * Now that we have restrict clauses figured out and assigned to proper+ * base rels, we can proceed to add otherrels, that is, UNION ALL child+ * tables, inheritance child tables. Having restrict clauses ready helps+ * to exclude any children that wouldn't be necessary to scan, based on+ * constraint exclusion and partition pruning.+ */+ add_other_rels_to_query(root, (Node *) root->parse->jointree);++ /*
* We have completed merging equivalence sets, so it's now possible to
* generate pathkeys in canonical form; so compute query_pathkeys and
* other pathkeys fields in PlannerInfo.
diff --git a/src/backend/optimizer/util/relnode.c b/src/backend/optimizer/util/relnode.cindex 4130514952..8a59819469 100644--- a/src/backend/optimizer/util/relnode.c+++ b/src/backend/optimizer/util/relnode.c@@ -16,19 +16,26 @@
#include <limits.h>
+#include "access/table.h"
#include "miscadmin.h"
+#include "nodes/makefuncs.h"
#include "optimizer/appendinfo.h"
#include "optimizer/clauses.h"
#include "optimizer/cost.h"
+#include "optimizer/inherit.h"+#include "optimizer/optimizer.h"
#include "optimizer/pathnode.h"
#include "optimizer/paths.h"
#include "optimizer/placeholder.h"
#include "optimizer/plancat.h"
+#include "optimizer/planmain.h"
#include "optimizer/prep.h"
#include "optimizer/restrictinfo.h"
#include "optimizer/tlist.h"
#include "partitioning/partbounds.h"
+#include "partitioning/partdesc.h"
#include "utils/hsearch.h"
+#include "utils/rel.h"
typedef struct JoinHashEntry
@@ -273,53 +280,76 @@ build_simple_rel(PlannerInfo *root, int relid, RelOptInfo *parent)
root->qual_security_level = Max(root->qual_security_level,
list_length(rte->securityQuals));
+ return rel;+}++/*+ * add_appendrel_other_rels+ * This adds the "other rel" RelOptInfos a given "appendrel" baserel+ *+ * Parent relation in this case is the parent subquery in the flattened UNION+ * ALL case or an inheritance parent table.+ */+void+add_appendrel_other_rels(PlannerInfo *root, RangeTblEntry *rte, Index rti)+{+ ListCell *l;+ RelOptInfo *rel;+ int i;++ rel = find_base_rel(root, rti);+
/*
- * If this rel is an appendrel parent, recurse to build "other rel"- * RelOptInfos for its children. They are "other rels" because they are- * not in the main join tree, but we will need RelOptInfos to plan access- * to them.+ * For partitioned tables, we need to store the child RelOptInfos in the+ * rel->part_rels array too.
*/
- if (rte->inh)+ if (rel->part_scheme)
{
- ListCell *l;- int nparts = rel->nparts;- int cnt_parts = 0;-- if (nparts > 0)- rel->part_rels = (RelOptInfo **)- palloc(sizeof(RelOptInfo *) * nparts);-- foreach(l, root->append_rel_list)- {- AppendRelInfo *appinfo = (AppendRelInfo *) lfirst(l);- RelOptInfo *childrel;-- /* append_rel_list contains all append rels; ignore others */- if (appinfo->parent_relid != relid)- continue;-- childrel = build_simple_rel(root, appinfo->child_relid,- rel);-- /* Nothing more to do for an unpartitioned table. */- if (!rel->part_scheme)- continue;-- /*- * The order of partition OIDs in append_rel_list is the same as- * the order in the PartitionDesc, so the order of part_rels will- * also match the PartitionDesc. See expand_partitioned_rtentry.- */- Assert(cnt_parts < nparts);- rel->part_rels[cnt_parts] = childrel;- cnt_parts++;- }-- /* We should have seen all the child partitions. */- Assert(cnt_parts == nparts);+ Assert(rel->nparts > 0);+ rel->part_rels = (RelOptInfo **)+ palloc0(sizeof(RelOptInfo *) * rel->nparts);
}
- return rel;+ i = 0;+ foreach(l, root->append_rel_list)+ {+ AppendRelInfo *appinfo = lfirst(l);+ Index childRTindex = appinfo->child_relid;+ RangeTblEntry *childrte;+ RelOptInfo *childrel;++ if (appinfo->parent_relid != rti)+ continue;++ Assert(childRTindex < root->simple_rel_array_size);+ childrte = root->simple_rte_array[childRTindex];+ Assert(childrte != NULL);+ childrel = build_simple_rel(root, childRTindex, rel);++ /*+ * For partitioned parents, we also need to add childrel to its+ * part_rels array. The order in which child tables appear in+ * append_rel_list is same as the order in which they appear in the+ * parent's PartitionDesc, so assigning partitions like this works.+ */+ if (rel->part_scheme != NULL)+ {+ Assert(i < rel->nparts);+ rel->part_rels[i] = childrel;+ }++ i++;++ /* Child may itself be an inherited relation. */+ if (childrte->inh)+ add_appendrel_other_rels(root, childrte, childRTindex);+ }++ /*+ * For a partitioned table with non-zero number of partitions, we must+ * have assigned all elements of its part_rels array.+ */+ Assert(rel->nparts == 0 || i == rel->nparts);
}
/*
diff --git a/src/include/optimizer/pathnode.h b/src/include/optimizer/pathnode.hindex 574bb85b50..1a07963a7d 100644--- a/src/include/optimizer/pathnode.h+++ b/src/include/optimizer/pathnode.h@@ -279,6 +279,8 @@ extern void setup_simple_rel_arrays(PlannerInfo *root);
extern void setup_append_rel_array(PlannerInfo *root);
extern RelOptInfo *build_simple_rel(PlannerInfo *root, int relid,
RelOptInfo *parent);
+extern void add_appendrel_other_rels(PlannerInfo *root, RangeTblEntry *rte,+ Index rti);
extern RelOptInfo *find_base_rel(PlannerInfo *root, int relid);
extern RelOptInfo *find_join_rel(PlannerInfo *root, Relids relids);
extern RelOptInfo *build_join_rel(PlannerInfo *root,
diff --git a/src/include/optimizer/planmain.h b/src/include/optimizer/planmain.hindex 3bbdb5e2f7..035caac500 100644--- a/src/include/optimizer/planmain.h+++ b/src/include/optimizer/planmain.h@@ -65,6 +65,7 @@ extern int from_collapse_limit;
extern int join_collapse_limit;
extern void add_base_rels_to_query(PlannerInfo *root, Node *jtnode);
+extern void add_other_rels_to_query(PlannerInfo *root, Node *jtnode);
extern void build_base_rel_tlists(PlannerInfo *root, List *final_tlist);
extern void add_vars_to_targetlist(PlannerInfo *root, List *vars,
Relids where_needed, bool create_new_ph);
--
2.11.0
[text/plain] v34-0002-Add-rowmark-junk-targetlist-entries-in-query_pla.patch (25.6K, ../c8dcf0fd-4759-5e1c-ea67-e2d3f98e81bd@lab.ntt.co.jp/3-v34-0002-Add-rowmark-junk-targetlist-entries-in-query_pla.patch)
download | inline diff:
From f850f0f62e9d363f02f32a93d6b489db1e6df837 Mon Sep 17 00:00:00 2001
From: amit <amitlangote09@gmail.com>
Date: Tue, 12 Mar 2019 10:47:37 +0900
Subject: [PATCH v34 2/9] Add rowmark "junk" targetlist entries in
query_planner
We'd like to postpone adding inheritance children to query until
query_planner has finished distributing quals to individual
baserels, which means also delaying adding child row marks. As
things stand today, which "junk" Vars to add to the targetlist for
a given parent row mark is not clear until all the child row marks
have been seen. So, we must delay adding "junk" Vars until the
children are added.
Since this changes the order in which expressions are added to the
individual baserels' targetlists, so does the order of expressions
in the Paths to scan them and Paths built on top of them. That is
why there are regression test expected output changes.
---
contrib/postgres_fdw/expected/postgres_fdw.out | 76 ++++++-------
src/backend/optimizer/plan/planagg.c | 2 +-
src/backend/optimizer/plan/planmain.c | 28 ++++-
src/backend/optimizer/plan/planner.c | 21 ++--
src/backend/optimizer/prep/preptlist.c | 152 ++++++++++++++-----------
src/include/optimizer/planmain.h | 2 +-
src/include/optimizer/prep.h | 2 +
src/test/regress/expected/with.out | 8 +-
8 files changed, 168 insertions(+), 123 deletions(-)
diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.outindex 42108bd3d4..c912e95fe1 100644--- a/contrib/postgres_fdw/expected/postgres_fdw.out+++ b/contrib/postgres_fdw/expected/postgres_fdw.out@@ -5577,25 +5577,25 @@ UPDATE ft2 SET c3 = 'baz'
Output: ft2.c1, ft2.c2, ft2.c3, ft2.c4, ft2.c5, ft2.c6, ft2.c7, ft2.c8, ft4.c1, ft4.c2, ft4.c3, ft5.c1, ft5.c2, ft5.c3
Remote SQL: UPDATE "S 1"."T 1" SET c3 = $2 WHERE ctid = $1 RETURNING "C 1", c2, c3, c4, c5, c6, c7, c8
-> Nested Loop
- Output: ft2.c1, ft2.c2, NULL::integer, 'baz'::text, ft2.c4, ft2.c5, ft2.c6, ft2.c7, ft2.c8, ft2.ctid, ft4.*, ft5.*, ft4.c1, ft4.c2, ft4.c3, ft5.c1, ft5.c2, ft5.c3+ Output: ft2.c1, ft2.c2, NULL::integer, 'baz'::text, ft2.c4, ft2.c5, ft2.c6, ft2.c7, ft2.c8, ft2.ctid, ft4.c1, ft4.c2, ft4.c3, ft5.c1, ft5.c2, ft5.c3, ft4.*, ft5.*
Join Filter: (ft2.c2 === ft4.c1)
-> Foreign Scan on public.ft2
Output: ft2.c1, ft2.c2, ft2.c4, ft2.c5, ft2.c6, ft2.c7, ft2.c8, ft2.ctid
Remote SQL: SELECT "C 1", c2, c4, c5, c6, c7, c8, ctid FROM "S 1"."T 1" WHERE (("C 1" > 2000)) FOR UPDATE
-> Foreign Scan
- Output: ft4.*, ft4.c1, ft4.c2, ft4.c3, ft5.*, ft5.c1, ft5.c2, ft5.c3+ Output: ft4.c1, ft4.c2, ft4.c3, ft4.*, ft5.c1, ft5.c2, ft5.c3, ft5.*
Relations: (public.ft4) INNER JOIN (public.ft5)
- Remote SQL: SELECT CASE WHEN (r2.*)::text IS NOT NULL THEN ROW(r2.c1, r2.c2, r2.c3) END, r2.c1, r2.c2, r2.c3, CASE WHEN (r3.*)::text IS NOT NULL THEN ROW(r3.c1, r3.c2, r3.c3) END, r3.c1, r3.c2, r3.c3 FROM ("S 1"."T 3" r2 INNER JOIN "S 1"."T 4" r3 ON (((r2.c1 = r3.c1))))+ Remote SQL: SELECT r2.c1, r2.c2, r2.c3, CASE WHEN (r2.*)::text IS NOT NULL THEN ROW(r2.c1, r2.c2, r2.c3) END, r3.c1, r3.c2, r3.c3, CASE WHEN (r3.*)::text IS NOT NULL THEN ROW(r3.c1, r3.c2, r3.c3) END FROM ("S 1"."T 3" r2 INNER JOIN "S 1"."T 4" r3 ON (((r2.c1 = r3.c1))))
-> Hash Join
- Output: ft4.*, ft4.c1, ft4.c2, ft4.c3, ft5.*, ft5.c1, ft5.c2, ft5.c3+ Output: ft4.c1, ft4.c2, ft4.c3, ft4.*, ft5.c1, ft5.c2, ft5.c3, ft5.*
Hash Cond: (ft4.c1 = ft5.c1)
-> Foreign Scan on public.ft4
- Output: ft4.*, ft4.c1, ft4.c2, ft4.c3+ Output: ft4.c1, ft4.c2, ft4.c3, ft4.*
Remote SQL: SELECT c1, c2, c3 FROM "S 1"."T 3"
-> Hash
- Output: ft5.*, ft5.c1, ft5.c2, ft5.c3+ Output: ft5.c1, ft5.c2, ft5.c3, ft5.*
-> Foreign Scan on public.ft5
- Output: ft5.*, ft5.c1, ft5.c2, ft5.c3+ Output: ft5.c1, ft5.c2, ft5.c3, ft5.*
Remote SQL: SELECT c1, c2, c3 FROM "S 1"."T 4"
(24 rows)
@@ -5626,16 +5626,16 @@ DELETE FROM ft2
-> Nested Loop
Output: ft2.ctid, ft4.*, ft5.*, ft4.c1, ft5.c1
-> Nested Loop
- Output: ft2.ctid, ft4.*, ft4.c1+ Output: ft2.ctid, ft4.c1, ft4.*
Join Filter: (ft2.c2 = ft4.c1)
-> Foreign Scan on public.ft2
Output: ft2.ctid, ft2.c2
Remote SQL: SELECT c2, ctid FROM "S 1"."T 1" WHERE (("C 1" > 2000)) FOR UPDATE
-> Foreign Scan on public.ft4
- Output: ft4.*, ft4.c1+ Output: ft4.c1, ft4.*
Remote SQL: SELECT c1, c2, c3 FROM "S 1"."T 3"
-> Foreign Scan on public.ft5
- Output: ft5.*, ft5.c1+ Output: ft5.c1, ft5.*
Remote SQL: SELECT c1, c2, c3 FROM "S 1"."T 4"
(22 rows)
@@ -7128,15 +7128,15 @@ select * from bar where f1 in (select f1 from foo) for update;
Output: bar2.f1, bar2.f2, bar2.ctid, bar2.*, bar2.tableoid
Remote SQL: SELECT f1, f2, f3, ctid FROM public.loct2 FOR UPDATE
-> Hash
- Output: foo.ctid, foo.*, foo.tableoid, foo.f1+ Output: foo.f1, foo.ctid, foo.*, foo.tableoid
-> HashAggregate
- Output: foo.ctid, foo.*, foo.tableoid, foo.f1+ Output: foo.f1, foo.ctid, foo.*, foo.tableoid
Group Key: foo.f1
-> Append
-> Seq Scan on public.foo
- Output: foo.ctid, foo.*, foo.tableoid, foo.f1+ Output: foo.f1, foo.ctid, foo.*, foo.tableoid
-> Foreign Scan on public.foo2
- Output: foo2.ctid, foo2.*, foo2.tableoid, foo2.f1+ Output: foo2.f1, foo2.ctid, foo2.*, foo2.tableoid
Remote SQL: SELECT f1, f2, f3, ctid FROM public.loct1
(23 rows)
@@ -7166,15 +7166,15 @@ select * from bar where f1 in (select f1 from foo) for share;
Output: bar2.f1, bar2.f2, bar2.ctid, bar2.*, bar2.tableoid
Remote SQL: SELECT f1, f2, f3, ctid FROM public.loct2 FOR SHARE
-> Hash
- Output: foo.ctid, foo.*, foo.tableoid, foo.f1+ Output: foo.f1, foo.ctid, foo.*, foo.tableoid
-> HashAggregate
- Output: foo.ctid, foo.*, foo.tableoid, foo.f1+ Output: foo.f1, foo.ctid, foo.*, foo.tableoid
Group Key: foo.f1
-> Append
-> Seq Scan on public.foo
- Output: foo.ctid, foo.*, foo.tableoid, foo.f1+ Output: foo.f1, foo.ctid, foo.*, foo.tableoid
-> Foreign Scan on public.foo2
- Output: foo2.ctid, foo2.*, foo2.tableoid, foo2.f1+ Output: foo2.f1, foo2.ctid, foo2.*, foo2.tableoid
Remote SQL: SELECT f1, f2, f3, ctid FROM public.loct1
(23 rows)
@@ -7203,15 +7203,15 @@ update bar set f2 = f2 + 100 where f1 in (select f1 from foo);
-> Seq Scan on public.bar
Output: bar.f1, bar.f2, bar.ctid
-> Hash
- Output: foo.ctid, foo.*, foo.tableoid, foo.f1+ Output: foo.f1, foo.ctid, foo.*, foo.tableoid
-> HashAggregate
- Output: foo.ctid, foo.*, foo.tableoid, foo.f1+ Output: foo.f1, foo.ctid, foo.*, foo.tableoid
Group Key: foo.f1
-> Append
-> Seq Scan on public.foo
- Output: foo.ctid, foo.*, foo.tableoid, foo.f1+ Output: foo.f1, foo.ctid, foo.*, foo.tableoid
-> Foreign Scan on public.foo2
- Output: foo2.ctid, foo2.*, foo2.tableoid, foo2.f1+ Output: foo2.f1, foo2.ctid, foo2.*, foo2.tableoid
Remote SQL: SELECT f1, f2, f3, ctid FROM public.loct1
-> Hash Join
Output: bar2.f1, (bar2.f2 + 100), bar2.f3, bar2.ctid, foo.ctid, foo.*, foo.tableoid
@@ -7221,15 +7221,15 @@ update bar set f2 = f2 + 100 where f1 in (select f1 from foo);
Output: bar2.f1, bar2.f2, bar2.f3, bar2.ctid
Remote SQL: SELECT f1, f2, f3, ctid FROM public.loct2 FOR UPDATE
-> Hash
- Output: foo.ctid, foo.*, foo.tableoid, foo.f1+ Output: foo.f1, foo.ctid, foo.*, foo.tableoid
-> HashAggregate
- Output: foo.ctid, foo.*, foo.tableoid, foo.f1+ Output: foo.f1, foo.ctid, foo.*, foo.tableoid
Group Key: foo.f1
-> Append
-> Seq Scan on public.foo
- Output: foo.ctid, foo.*, foo.tableoid, foo.f1+ Output: foo.f1, foo.ctid, foo.*, foo.tableoid
-> Foreign Scan on public.foo2
- Output: foo2.ctid, foo2.*, foo2.tableoid, foo2.f1+ Output: foo2.f1, foo2.ctid, foo2.*, foo2.tableoid
Remote SQL: SELECT f1, f2, f3, ctid FROM public.loct1
(39 rows)
@@ -7262,14 +7262,14 @@ where bar.f1 = ss.f1;
Hash Cond: (foo.f1 = bar.f1)
-> Append
-> Seq Scan on public.foo
- Output: ROW(foo.f1), foo.f1+ Output: foo.f1, ROW(foo.f1)
-> Foreign Scan on public.foo2
- Output: ROW(foo2.f1), foo2.f1+ Output: foo2.f1, ROW(foo2.f1)
Remote SQL: SELECT f1 FROM public.loct1
-> Seq Scan on public.foo foo_1
- Output: ROW((foo_1.f1 + 3)), (foo_1.f1 + 3)+ Output: (foo_1.f1 + 3), ROW((foo_1.f1 + 3))
-> Foreign Scan on public.foo2 foo2_1
- Output: ROW((foo2_1.f1 + 3)), (foo2_1.f1 + 3)+ Output: (foo2_1.f1 + 3), ROW((foo2_1.f1 + 3))
Remote SQL: SELECT f1 FROM public.loct1
-> Hash
Output: bar.f1, bar.f2, bar.ctid
@@ -7285,18 +7285,18 @@ where bar.f1 = ss.f1;
Output: bar2.f1, bar2.f2, bar2.f3, bar2.ctid
Remote SQL: SELECT f1, f2, f3, ctid FROM public.loct2 FOR UPDATE
-> Sort
- Output: (ROW(foo.f1)), foo.f1+ Output: foo.f1, (ROW(foo.f1))
Sort Key: foo.f1
-> Append
-> Seq Scan on public.foo
- Output: ROW(foo.f1), foo.f1+ Output: foo.f1, ROW(foo.f1)
-> Foreign Scan on public.foo2
- Output: ROW(foo2.f1), foo2.f1+ Output: foo2.f1, ROW(foo2.f1)
Remote SQL: SELECT f1 FROM public.loct1
-> Seq Scan on public.foo foo_1
- Output: ROW((foo_1.f1 + 3)), (foo_1.f1 + 3)+ Output: (foo_1.f1 + 3), ROW((foo_1.f1 + 3))
-> Foreign Scan on public.foo2 foo2_1
- Output: ROW((foo2_1.f1 + 3)), (foo2_1.f1 + 3)+ Output: (foo2_1.f1 + 3), ROW((foo2_1.f1 + 3))
Remote SQL: SELECT f1 FROM public.loct1
(45 rows)
@@ -7559,12 +7559,12 @@ update parent set b = parent.b || remt2.b from remt2 where parent.a = remt2.a re
Update on public.parent
Foreign Update on public.remt1
-> Nested Loop
- Output: parent.a, (parent.b || remt2.b), parent.ctid, remt2.*, remt2.a, remt2.b+ Output: parent.a, (parent.b || remt2.b), parent.ctid, remt2.a, remt2.b, remt2.*
Join Filter: (parent.a = remt2.a)
-> Seq Scan on public.parent
Output: parent.a, parent.b, parent.ctid
-> Foreign Scan on public.remt2
- Output: remt2.b, remt2.*, remt2.a+ Output: remt2.b, remt2.a, remt2.*
Remote SQL: SELECT a, b FROM public.loct2
-> Foreign Update
Remote SQL: UPDATE public.loct1 r4 SET b = (r4.b || r2.b) FROM public.loct2 r2 WHERE ((r4.a = r2.a)) RETURNING r4.a, r4.b, r2.a, r2.b
@@ -7591,7 +7591,7 @@ delete from parent using remt2 where parent.a = remt2.a returning parent;
-> Seq Scan on public.parent
Output: parent.ctid, parent.a
-> Foreign Scan on public.remt2
- Output: remt2.*, remt2.a+ Output: remt2.a, remt2.*
Remote SQL: SELECT a, b FROM public.loct2
-> Foreign Delete
Remote SQL: DELETE FROM public.loct1 r4 USING public.loct2 r2 WHERE ((r4.a = r2.a)) RETURNING r4.a, r4.b
diff --git a/src/backend/optimizer/plan/planagg.c b/src/backend/optimizer/plan/planagg.cindex 86617099df..0e819a6e92 100644--- a/src/backend/optimizer/plan/planagg.c+++ b/src/backend/optimizer/plan/planagg.c@@ -442,7 +442,7 @@ build_minmax_path(PlannerInfo *root, MinMaxAggInfo *mminfo,
subroot->tuple_fraction = 1.0;
subroot->limit_tuples = 1.0;
- final_rel = query_planner(subroot, tlist, minmax_qp_callback, NULL);+ final_rel = query_planner(subroot, &tlist, minmax_qp_callback, NULL);
/*
* Since we didn't go through subquery_planner() to handle the subquery,
diff --git a/src/backend/optimizer/plan/planmain.c b/src/backend/optimizer/plan/planmain.cindex 03c81772a3..42c2130096 100644--- a/src/backend/optimizer/plan/planmain.c+++ b/src/backend/optimizer/plan/planmain.c@@ -29,6 +29,7 @@
#include "optimizer/paths.h"
#include "optimizer/placeholder.h"
#include "optimizer/planmain.h"
+#include "optimizer/prep.h"
/*
@@ -42,7 +43,8 @@
* (grouping_planner) can choose among the surviving paths for the rel.
*
* root describes the query to plan
- * tlist is the target list the query should produce+ * *tlist is the target list the query should produce (passed in as an output+ * variable, because more entries may be added to it)
* (this is NOT necessarily root->parse->targetList!)
* qp_callback is a function to compute query_pathkeys once it's safe to do so
* qp_extra is optional extra data to pass to qp_callback
@@ -54,7 +56,7 @@
* (We cannot construct canonical pathkeys until that's done.)
*/
RelOptInfo *
-query_planner(PlannerInfo *root, List *tlist,+query_planner(PlannerInfo *root, List **tlist,
query_pathkeys_callback qp_callback, void *qp_extra)
{
Query *parse = root->parse;
@@ -179,7 +181,7 @@ query_planner(PlannerInfo *root, List *tlist,
* restrictions. Finally, we form a target joinlist for make_one_rel() to
* work from.
*/
- build_base_rel_tlists(root, tlist);+ build_base_rel_tlists(root, *tlist);
find_placeholders_in_jointree(root);
@@ -211,6 +213,26 @@ query_planner(PlannerInfo *root, List *tlist,
add_other_rels_to_query(root, (Node *) root->parse->jointree);
/*
+ * root->rowMarks should contain all row marks at this point, including+ * child row marks if any, so add "junk" Vars to the targetlist based on+ * the "parent" row marks.+ */+ if (root->rowMarks)+ {+ List *junk_vars;++ /* More entries will added to *tlist. */+ add_rowmark_junk_vars_to_targetlist(root, tlist, &junk_vars);+ Assert(junk_vars != NIL);++ /*+ * There can't be any PlaceHolderVars in junk_vars, so pass false for+ * 'create_new_ph'.+ */+ add_vars_to_targetlist(root, junk_vars, bms_make_singleton(0), false);+ }++ /*
* We have completed merging equivalence sets, so it's now possible to
* generate pathkeys in canonical form; so compute query_pathkeys and
* other pathkeys fields in PlannerInfo.
diff --git a/src/backend/optimizer/plan/planner.c b/src/backend/optimizer/plan/planner.cindex e408e77d6f..abac84bcaa 100644--- a/src/backend/optimizer/plan/planner.c+++ b/src/backend/optimizer/plan/planner.c@@ -25,6 +25,7 @@
#include "access/table.h"
#include "access/xact.h"
#include "catalog/pg_constraint.h"
+#include "catalog/pg_inherits.h"
#include "catalog/pg_proc.h"
#include "catalog/pg_type.h"
#include "executor/executor.h"
@@ -1835,15 +1836,6 @@ grouping_planner(PlannerInfo *root, bool inheritance_update,
tlist = preprocess_targetlist(root);
/*
- * We are now done hacking up the query's targetlist. Most of the- * remaining planning work will be done with the PathTarget- * representation of tlists, but save aside the full representation so- * that we can transfer its decoration (resnames etc) to the topmost- * tlist of the finished Plan.- */- root->processed_tlist = tlist;-- /*
* Collect statistics about aggregates for estimating costs, and mark
* all the aggregates with resolved aggtranstypes. We must do this
* before slicing and dicing the tlist into various pathtargets, else
@@ -1921,10 +1913,19 @@ grouping_planner(PlannerInfo *root, bool inheritance_update,
* We also generate (in standard_qp_callback) pathkey representations
* of the query's sort clause, distinct clause, etc.
*/
- current_rel = query_planner(root, tlist,+ current_rel = query_planner(root, &tlist,
standard_qp_callback, &qp_extra);
/*
+ * We are now done hacking up the query's targetlist. Most of the+ * remaining planning work will be done with the PathTarget+ * representation of tlists, but save aside the full representation so+ * that we can transfer its decoration (resnames etc) to the topmost+ * tlist of the finished Plan.+ */+ root->processed_tlist = tlist;++ /*
* Convert the query's result tlist into PathTarget format.
*
* Note: it's desirable to not do this till after query_planner(),
diff --git a/src/backend/optimizer/prep/preptlist.c b/src/backend/optimizer/prep/preptlist.cindex 5392d1a561..1165382e82 100644--- a/src/backend/optimizer/prep/preptlist.c+++ b/src/backend/optimizer/prep/preptlist.c@@ -76,7 +76,6 @@ preprocess_targetlist(PlannerInfo *root)
RangeTblEntry *target_rte = NULL;
Relation target_relation = NULL;
List *tlist;
- ListCell *lc;
/*
* If there is a result relation, open it so we can look for missing
@@ -119,71 +118,6 @@ preprocess_targetlist(PlannerInfo *root)
result_relation, target_relation);
/*
- * Add necessary junk columns for rowmarked rels. These values are needed- * for locking of rels selected FOR UPDATE/SHARE, and to do EvalPlanQual- * rechecking. See comments for PlanRowMark in plannodes.h.- */- foreach(lc, root->rowMarks)- {- PlanRowMark *rc = (PlanRowMark *) lfirst(lc);- Var *var;- char resname[32];- TargetEntry *tle;-- /* child rels use the same junk attrs as their parents */- if (rc->rti != rc->prti)- continue;-- if (rc->allMarkTypes & ~(1 << ROW_MARK_COPY))- {- /* Need to fetch TID */- var = makeVar(rc->rti,- SelfItemPointerAttributeNumber,- TIDOID,- -1,- InvalidOid,- 0);- snprintf(resname, sizeof(resname), "ctid%u", rc->rowmarkId);- tle = makeTargetEntry((Expr *) var,- list_length(tlist) + 1,- pstrdup(resname),- true);- tlist = lappend(tlist, tle);- }- if (rc->allMarkTypes & (1 << ROW_MARK_COPY))- {- /* Need the whole row as a junk var */- var = makeWholeRowVar(rt_fetch(rc->rti, range_table),- rc->rti,- 0,- false);- snprintf(resname, sizeof(resname), "wholerow%u", rc->rowmarkId);- tle = makeTargetEntry((Expr *) var,- list_length(tlist) + 1,- pstrdup(resname),- true);- tlist = lappend(tlist, tle);- }-- /* If parent of inheritance tree, always fetch the tableoid too. */- if (rc->isParent)- {- var = makeVar(rc->rti,- TableOidAttributeNumber,- OIDOID,- -1,- InvalidOid,- 0);- snprintf(resname, sizeof(resname), "tableoid%u", rc->rowmarkId);- tle = makeTargetEntry((Expr *) var,- list_length(tlist) + 1,- pstrdup(resname),- true);- tlist = lappend(tlist, tle);- }- }-- /*
* If the query has a RETURNING list, add resjunk entries for any Vars
* used in RETURNING that belong to other relations. We need to do this
* to make these Vars available for the RETURNING calculation. Vars that
@@ -434,3 +368,89 @@ get_plan_rowmark(List *rowmarks, Index rtindex)
}
return NULL;
}
++/*+ * add_rowmark_junk_vars_to_targetlist+ * Add "junk" targetlist entries needed for applying row marks contained+ * in root->rowMarks+ *+ * Vars that are added to the targetlist are also returned in *junk_vars for+ * the caller's perusal.+ */+void+add_rowmark_junk_vars_to_targetlist(PlannerInfo *root,+ List **tlist, List **junk_vars)+{+ List *range_table = root->parse->rtable;+ ListCell *lc;++ *junk_vars = NIL;++ /*+ * Add necessary junk columns for rowmarked rels. These values are needed+ * for locking of rels selected FOR UPDATE/SHARE, and to do EvalPlanQual+ * rechecking. See comments for PlanRowMark in plannodes.h.+ */+ foreach(lc, root->rowMarks)+ {+ PlanRowMark *rc = (PlanRowMark *) lfirst(lc);+ Var *var;+ char resname[32];+ TargetEntry *tle;++ /* child rels use the same junk attrs as their parents */+ if (rc->rti != rc->prti)+ continue;++ if (rc->allMarkTypes & ~(1 << ROW_MARK_COPY))+ {+ /* Need to fetch TID */+ var = makeVar(rc->rti,+ SelfItemPointerAttributeNumber,+ TIDOID,+ -1,+ InvalidOid,+ 0);+ snprintf(resname, sizeof(resname), "ctid%u", rc->rowmarkId);+ tle = makeTargetEntry((Expr *) var,+ list_length(*tlist) + 1,+ pstrdup(resname),+ true);+ *tlist = lappend(*tlist, tle);+ *junk_vars = lappend(*junk_vars, var);+ }+ if (rc->allMarkTypes & (1 << ROW_MARK_COPY))+ {+ /* Need the whole row as a junk var */+ var = makeWholeRowVar(rt_fetch(rc->rti, range_table),+ rc->rti,+ 0,+ false);+ snprintf(resname, sizeof(resname), "wholerow%u", rc->rowmarkId);+ tle = makeTargetEntry((Expr *) var,+ list_length(*tlist) + 1,+ pstrdup(resname),+ true);+ *tlist = lappend(*tlist, tle);+ *junk_vars = lappend(*junk_vars, var);+ }++ /* For inheritance cases, always fetch the tableoid too. */+ if (rc->isParent)+ {+ var = makeVar(rc->rti,+ TableOidAttributeNumber,+ OIDOID,+ -1,+ InvalidOid,+ 0);+ snprintf(resname, sizeof(resname), "tableoid%u", rc->rowmarkId);+ tle = makeTargetEntry((Expr *) var,+ list_length(*tlist) + 1,+ pstrdup(resname),+ true);+ *tlist = lappend(*tlist, tle);+ *junk_vars = lappend(*junk_vars, var);+ }+ }+}diff --git a/src/include/optimizer/planmain.h b/src/include/optimizer/planmain.hindex 035caac500..a21783192c 100644--- a/src/include/optimizer/planmain.h+++ b/src/include/optimizer/planmain.h@@ -27,7 +27,7 @@ typedef void (*query_pathkeys_callback) (PlannerInfo *root, void *extra);
/*
* prototypes for plan/planmain.c
*/
-extern RelOptInfo *query_planner(PlannerInfo *root, List *tlist,+extern RelOptInfo *query_planner(PlannerInfo *root, List **tlist,
query_pathkeys_callback qp_callback, void *qp_extra);
/*
diff --git a/src/include/optimizer/prep.h b/src/include/optimizer/prep.hindex a9b2c9026c..cf7b3a9d06 100644--- a/src/include/optimizer/prep.h+++ b/src/include/optimizer/prep.h@@ -37,6 +37,8 @@ extern Relids get_relids_for_join(Query *query, int joinrelid);
extern List *preprocess_targetlist(PlannerInfo *root);
extern PlanRowMark *get_plan_rowmark(List *rowmarks, Index rtindex);
+extern void add_rowmark_junk_vars_to_targetlist(PlannerInfo *root,+ List **tlist, List **junk_vars);
/*
* prototypes for prepunion.c
diff --git a/src/test/regress/expected/with.out b/src/test/regress/expected/with.outindex 2a2085556b..4f0a13dd0d 100644--- a/src/test/regress/expected/with.out+++ b/src/test/regress/expected/with.out@@ -2199,28 +2199,28 @@ DELETE FROM a USING wcte WHERE aa = q2;
-> Seq Scan on public.a
Output: a.ctid, a.aa
-> CTE Scan on wcte
- Output: wcte.*, wcte.q2+ Output: wcte.q2, wcte.*
-> Nested Loop
Output: b.ctid, wcte.*
Join Filter: (b.aa = wcte.q2)
-> Seq Scan on public.b
Output: b.ctid, b.aa
-> CTE Scan on wcte
- Output: wcte.*, wcte.q2+ Output: wcte.q2, wcte.*
-> Nested Loop
Output: c.ctid, wcte.*
Join Filter: (c.aa = wcte.q2)
-> Seq Scan on public.c
Output: c.ctid, c.aa
-> CTE Scan on wcte
- Output: wcte.*, wcte.q2+ Output: wcte.q2, wcte.*
-> Nested Loop
Output: d.ctid, wcte.*
Join Filter: (d.aa = wcte.q2)
-> Seq Scan on public.d
Output: d.ctid, d.aa
-> CTE Scan on wcte
- Output: wcte.*, wcte.q2+ Output: wcte.q2, wcte.*
(38 rows)
-- error cases
--
2.11.0
[text/plain] v34-0003-Delay-adding-inheritance-child-tables-until-quer.patch (30.4K, ../c8dcf0fd-4759-5e1c-ea67-e2d3f98e81bd@lab.ntt.co.jp/4-v34-0003-Delay-adding-inheritance-child-tables-until-quer.patch)
download | inline diff:
From 3a93941e9a28fd95b840e0a9c37edbee6e72fa0c Mon Sep 17 00:00:00 2001
From: amit <amitlangote09@gmail.com>
Date: Mon, 4 Mar 2019 15:10:29 +0900
Subject: [PATCH v34 3/9] Delay adding inheritance child tables until
query_planner
Inheritance child tables are now added by add_other_rels_to_query,
which is called by query_planner. This replaces
expand_inherited_tables called by subquery_planner as the method
of adding child tables. At the point when add_other_rels_to_query
is called, restriction clauses have already been assigned to
individual base relations, so it will now be possible to perform
partition pruning so that we know exact child tables to add in that
case. This commit however doesn't change when pruning is performed.
inheritance_planner now adds the child target tables on its own,
because subquery_planner doesn't. Also, since it calls query_planner
once for every child relation, source inheritance child tables are
added multiple times. This both slows down queries when there are
inherited tables in UPDATE/DELETE's FROM clause (source inheritance)
and changes the EXPLAIN output a bit because source inheritance
child tables' aliases come out different for every targer child
subplan. Another patch will fix both these issues.
---
contrib/postgres_fdw/expected/postgres_fdw.out | 46 ++++----
src/backend/optimizer/plan/planner.c | 58 +++++++---
src/backend/optimizer/util/inherit.c | 152 ++++++++++++++++---------
src/backend/optimizer/util/plancat.c | 9 +-
src/backend/optimizer/util/relnode.c | 83 +++++++++-----
src/include/optimizer/inherit.h | 4 +-
src/include/optimizer/pathnode.h | 1 +
src/test/regress/expected/partition_prune.out | 12 +-
src/test/regress/expected/rowsecurity.out | 16 +--
9 files changed, 246 insertions(+), 135 deletions(-)
diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.outindex c912e95fe1..bbc9ead2e9 100644--- a/contrib/postgres_fdw/expected/postgres_fdw.out+++ b/contrib/postgres_fdw/expected/postgres_fdw.out@@ -7190,8 +7190,8 @@ select * from bar where f1 in (select f1 from foo) for share;
-- Check UPDATE with inherited target and an inherited source table
explain (verbose, costs off)
update bar set f2 = f2 + 100 where f1 in (select f1 from foo);
- QUERY PLAN ----------------------------------------------------------------------------------------------+ QUERY PLAN +---------------------------------------------------------------------------------------------------
Update on public.bar
Update on public.bar
Foreign Update on public.bar2
@@ -7214,22 +7214,22 @@ update bar set f2 = f2 + 100 where f1 in (select f1 from foo);
Output: foo2.f1, foo2.ctid, foo2.*, foo2.tableoid
Remote SQL: SELECT f1, f2, f3, ctid FROM public.loct1
-> Hash Join
- Output: bar2.f1, (bar2.f2 + 100), bar2.f3, bar2.ctid, foo.ctid, foo.*, foo.tableoid+ Output: bar2.f1, (bar2.f2 + 100), bar2.f3, bar2.ctid, foo_1.ctid, foo_1.*, foo_1.tableoid
Inner Unique: true
- Hash Cond: (bar2.f1 = foo.f1)+ Hash Cond: (bar2.f1 = foo_1.f1)
-> Foreign Scan on public.bar2
Output: bar2.f1, bar2.f2, bar2.f3, bar2.ctid
Remote SQL: SELECT f1, f2, f3, ctid FROM public.loct2 FOR UPDATE
-> Hash
- Output: foo.f1, foo.ctid, foo.*, foo.tableoid+ Output: foo_1.f1, foo_1.ctid, foo_1.*, foo_1.tableoid
-> HashAggregate
- Output: foo.f1, foo.ctid, foo.*, foo.tableoid- Group Key: foo.f1+ Output: foo_1.f1, foo_1.ctid, foo_1.*, foo_1.tableoid+ Group Key: foo_1.f1
-> Append
- -> Seq Scan on public.foo- Output: foo.f1, foo.ctid, foo.*, foo.tableoid- -> Foreign Scan on public.foo2- Output: foo2.f1, foo2.ctid, foo2.*, foo2.tableoid+ -> Seq Scan on public.foo foo_1+ Output: foo_1.f1, foo_1.ctid, foo_1.*, foo_1.tableoid+ -> Foreign Scan on public.foo2 foo2_1+ Output: foo2_1.f1, foo2_1.ctid, foo2_1.*, foo2_1.tableoid
Remote SQL: SELECT f1, f2, f3, ctid FROM public.loct1
(39 rows)
@@ -7276,8 +7276,8 @@ where bar.f1 = ss.f1;
-> Seq Scan on public.bar
Output: bar.f1, bar.f2, bar.ctid
-> Merge Join
- Output: bar2.f1, (bar2.f2 + 100), bar2.f3, bar2.ctid, (ROW(foo.f1))- Merge Cond: (bar2.f1 = foo.f1)+ Output: bar2.f1, (bar2.f2 + 100), bar2.f3, bar2.ctid, (ROW(foo_2.f1))+ Merge Cond: (bar2.f1 = foo_2.f1)
-> Sort
Output: bar2.f1, bar2.f2, bar2.f3, bar2.ctid
Sort Key: bar2.f1
@@ -7285,18 +7285,18 @@ where bar.f1 = ss.f1;
Output: bar2.f1, bar2.f2, bar2.f3, bar2.ctid
Remote SQL: SELECT f1, f2, f3, ctid FROM public.loct2 FOR UPDATE
-> Sort
- Output: foo.f1, (ROW(foo.f1))- Sort Key: foo.f1+ Output: foo_2.f1, (ROW(foo_2.f1))+ Sort Key: foo_2.f1
-> Append
- -> Seq Scan on public.foo- Output: foo.f1, ROW(foo.f1)- -> Foreign Scan on public.foo2- Output: foo2.f1, ROW(foo2.f1)+ -> Seq Scan on public.foo foo_2+ Output: foo_2.f1, ROW(foo_2.f1)+ -> Foreign Scan on public.foo2 foo2_2+ Output: foo2_2.f1, ROW(foo2_2.f1)
Remote SQL: SELECT f1 FROM public.loct1
- -> Seq Scan on public.foo foo_1- Output: (foo_1.f1 + 3), ROW((foo_1.f1 + 3))- -> Foreign Scan on public.foo2 foo2_1- Output: (foo2_1.f1 + 3), ROW((foo2_1.f1 + 3))+ -> Seq Scan on public.foo foo_3+ Output: (foo_3.f1 + 3), ROW((foo_3.f1 + 3))+ -> Foreign Scan on public.foo2 foo2_3+ Output: (foo2_3.f1 + 3), ROW((foo2_3.f1 + 3))
Remote SQL: SELECT f1 FROM public.loct1
(45 rows)
diff --git a/src/backend/optimizer/plan/planner.c b/src/backend/optimizer/plan/planner.cindex abac84bcaa..02ca1a283a 100644--- a/src/backend/optimizer/plan/planner.c+++ b/src/backend/optimizer/plan/planner.c@@ -689,6 +689,8 @@ subquery_planner(PlannerGlobal *glob, Query *parse,
* remove_useless_result_rtes(). Also check for outer joins --- if none,
* we can skip reduce_outer_joins(). And check for LATERAL RTEs, too.
* This must be done after we have done pull_up_subqueries(), of course.
+ * For RTE_RELATION rangetable entries whose inh flag is set, adjust the+ * value of the flag by checking whether has_subclass() returns true.
*/
root->hasJoinRTEs = false;
root->hasLateralRTEs = false;
@@ -708,29 +710,30 @@ subquery_planner(PlannerGlobal *glob, Query *parse,
{
hasResultRTEs = true;
}
+ else if (rte->rtekind == RTE_RELATION && rte->inh)+ {+ rte->inh = has_subclass(rte->relid);++ /*+ * While at it, initialize the PartitionDesc infrastructure for+ * this query, if not done yet.+ */+ if (root->glob->partition_directory == NULL)+ root->glob->partition_directory =+ CreatePartitionDirectory(CurrentMemoryContext);+ }+
if (rte->lateral)
root->hasLateralRTEs = true;
}
/*
* Preprocess RowMark information. We need to do this after subquery
- * pullup (so that all non-inherited RTEs are present) and before- * inheritance expansion (so that the info is available for- * expand_inherited_tables to examine and modify).+ * pullup (so that all non-inherited RTEs are present).
*/
preprocess_rowmarks(root);
/*
- * Expand any rangetable entries that are inheritance sets into "append- * relations". This can add entries to the rangetable, but they must be- * plain RTE_RELATION entries, so it's OK (and marginally more efficient)- * to do it after checking for joins and other special RTEs. We must do- * this after pulling up subqueries, else we'd fail to handle inherited- * tables in subqueries.- */- expand_inherited_tables(root);-- /*
* Set hasHavingQual to remember if HAVING clause is present. Needed
* because preprocess_expression will reduce a constant-true condition to
* an empty qual list ... but "HAVING TRUE" is not a semantic no-op.
@@ -1206,10 +1209,35 @@ inheritance_planner(PlannerInfo *root)
Query *parent_parse;
Bitmapset *parent_relids = bms_make_singleton(top_parentRTindex);
PlannerInfo **parent_roots = NULL;
+ List *orig_append_rel_list = list_copy(root->append_rel_list);
Assert(parse->commandType != CMD_INSERT);
/*
+ * Add child target relations. Note that this only adds the children of+ * the query's target relation and no other. Especially, the children of+ * any source relations are added by query_planner() during the planning+ * of each child query.+ */+ parent_rte = rt_fetch(top_parentRTindex, root->parse->rtable);++ expand_inherited_rtentry(root, parent_rte, top_parentRTindex);++ /*+ * If parent no longer has any children, then treat this as an update of+ * a single table.+ */+ if (!parent_rte->inh)+ {+ /*+ * We'll be retrieving all tuples to modify, so passing 0.0 for+ * tuple_fraction.+ */+ grouping_planner(root, false, 0.0);+ return;+ }++ /*
* We generate a modified instance of the original Query for each target
* relation, plan that, and put all the plans into a list that will be
* controlled by a single ModifyTable node. All the instances share the
@@ -1270,7 +1298,6 @@ inheritance_planner(PlannerInfo *root)
* not appear anywhere else in the plan, so the confusion explained below
* for non-partitioning inheritance cases is not possible.
*/
- parent_rte = rt_fetch(top_parentRTindex, root->parse->rtable);
if (parent_rte->relkind == RELKIND_PARTITIONED_TABLE)
{
nominalRelation = top_parentRTindex;
@@ -1321,6 +1348,9 @@ inheritance_planner(PlannerInfo *root)
subroot = makeNode(PlannerInfo);
memcpy(subroot, parent_root, sizeof(PlannerInfo));
+ /* grouping_planner doesn't need to see the target children. */+ subroot->append_rel_list = list_copy(orig_append_rel_list);+
/*
* Generate modified query with this rel as target. We first apply
* adjust_appendrel_attrs, which copies the Query and changes
diff --git a/src/backend/optimizer/util/inherit.c b/src/backend/optimizer/util/inherit.cindex 1fa154e0cb..24111c46e9 100644--- a/src/backend/optimizer/util/inherit.c+++ b/src/backend/optimizer/util/inherit.c@@ -21,14 +21,14 @@
#include "miscadmin.h"
#include "optimizer/appendinfo.h"
#include "optimizer/inherit.h"
+#include "optimizer/pathnode.h"+#include "optimizer/planmain.h"
#include "optimizer/planner.h"
#include "optimizer/prep.h"
#include "partitioning/partdesc.h"
#include "utils/rel.h"
-static void expand_inherited_rtentry(PlannerInfo *root, RangeTblEntry *rte,- Index rti);
static void expand_partitioned_rtentry(PlannerInfo *root,
RangeTblEntry *parentrte,
Index parentRTindex, Relation parentrel,
@@ -45,36 +45,6 @@ static Bitmapset *translate_col_privs(const Bitmapset *parent_privs,
/*
- * expand_inherited_tables- * Expand each rangetable entry that represents an inheritance set- * into an "append relation". At the conclusion of this process,- * the "inh" flag is set in all and only those RTEs that are append- * relation parents.- */-void-expand_inherited_tables(PlannerInfo *root)-{- Index nrtes;- Index rti;- ListCell *rl;-- /*- * expand_inherited_rtentry may add RTEs to parse->rtable. The function is- * expected to recursively handle any RTEs that it creates with inh=true.- * So just scan as far as the original end of the rtable list.- */- nrtes = list_length(root->parse->rtable);- rl = list_head(root->parse->rtable);- for (rti = 1; rti <= nrtes; rti++)- {- RangeTblEntry *rte = (RangeTblEntry *) lfirst(rl);-- expand_inherited_rtentry(root, rte, rti);- rl = lnext(rl);- }-}--/*
* expand_inherited_rtentry
* Check whether a rangetable entry represents an inheritance set.
* If so, add entries for all the child tables to the query's
@@ -94,7 +64,7 @@ expand_inherited_tables(PlannerInfo *root)
* Since a partitioned table is not scanned, it might have only one associated
* AppendRelInfo.
*/
-static void+void
expand_inherited_rtentry(PlannerInfo *root, RangeTblEntry *rte, Index rti)
{
Oid parentOID;
@@ -147,10 +117,6 @@ expand_inherited_rtentry(PlannerInfo *root, RangeTblEntry *rte, Index rti)
{
Assert(rte->relkind == RELKIND_PARTITIONED_TABLE);
- if (root->glob->partition_directory == NULL)- root->glob->partition_directory =- CreatePartitionDirectory(CurrentMemoryContext);-
/*
* If this table has partitions, recursively expand and lock them.
* While at it, also extract the partition key columns of all the
@@ -164,6 +130,8 @@ expand_inherited_rtentry(PlannerInfo *root, RangeTblEntry *rte, Index rti)
List *appinfos = NIL;
RangeTblEntry *childrte;
Index childRTindex;
+ bool is_source_inh_expansion;+ RelOptInfo *rel = NULL;
/* Scan for all members of inheritance set, acquire needed locks */
inhOIDs = find_all_inheritors(parentOID, lockmode, NULL);
@@ -182,6 +150,20 @@ expand_inherited_rtentry(PlannerInfo *root, RangeTblEntry *rte, Index rti)
}
/*
+ * If parent is a source relation of the query, we'd need to add the+ * child RelOptInfos as well, in addition to RangeTblEntry's and+ * AppendRelInfo's. We can tell it's source relation by noting that+ * simple_rel_array has been set up by query_planner.+ */+ is_source_inh_expansion = (root->simple_rel_array_size > 0);+ if (is_source_inh_expansion)+ {+ rel = find_base_rel(root, rti);+ /* Expand various arrays in PlannerInfo to hold child object. */+ expand_planner_arrays(root, list_length(inhOIDs));+ }++ /*
* This table has no partitions. Expand any plain inheritance
* children in the order the OIDs were returned by
* find_all_inheritors.
@@ -214,6 +196,10 @@ expand_inherited_rtentry(PlannerInfo *root, RangeTblEntry *rte, Index rti)
&appinfos, &childrte,
&childRTindex);
+ /* Create the otherrel RelOptInfo too. */+ if (is_source_inh_expansion)+ (void) build_simple_rel(root, childRTindex, rel);+
/* Close child relations, but keep locks */
if (childOID != parentOID)
table_close(newrelation, NoLock);
@@ -251,12 +237,14 @@ expand_partitioned_rtentry(PlannerInfo *root, RangeTblEntry *parentrte,
RangeTblEntry *childrte;
Index childRTindex;
PartitionDesc partdesc;
+ RelOptInfo *rel = NULL;+ bool is_source_inh_expansion;++ check_stack_depth();
partdesc = PartitionDirectoryLookup(root->glob->partition_directory,
parentrel);
- check_stack_depth();-
/* A partitioned table should always have a partition descriptor. */
Assert(partdesc);
@@ -273,6 +261,35 @@ expand_partitioned_rtentry(PlannerInfo *root, RangeTblEntry *parentrte,
root->partColsUpdated =
has_partition_attrs(parentrel, parentrte->updatedCols, NULL);
+ /*+ * If parent is a source relation of the query, we'd need to add the child+ * RelOptInfos as well, in addition to RangeTblEntry's and+ * AppendRelInfo's. We can tell it's source relation by noting that+ * simple_rel_array has been set up by query_planner.+ */+ is_source_inh_expansion = (root->simple_rel_array_size > 0);+ if (is_source_inh_expansion)+ {+ rel = find_base_rel(root, parentRTindex);++ /*+ * Expand simple_rel_array and friends to hold child objects.+ *+ * We'll need nparts + 1 new slots, because we also consider parent+ * as a child relation. FIXME: no longer add the parent as child+ */+ expand_planner_arrays(root, partdesc->nparts + 1);++ /*+ * We also store partition RelOptInfo pointers in the parent+ * relation.+ */+ Assert(rel->part_rels == NULL);+ if (partdesc->nparts > 0)+ rel->part_rels = (RelOptInfo **) palloc0(rel->nparts *+ sizeof(RelOptInfo *));+ }+
/* First expand the partitioned table itself. */
expand_single_inheritance_child(root, parentrte, parentRTindex, parentrel,
top_parentrc, parentrel,
@@ -308,8 +325,19 @@ expand_partitioned_rtentry(PlannerInfo *root, RangeTblEntry *parentrte,
parentrel, top_parentrc, childrel,
appinfos, &childrte, &childRTindex);
- /* If this child is itself partitioned, recurse */- if (childrel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE)+ /* Create the otherrel RelOptInfo too. */+ if (is_source_inh_expansion)+ {+ Assert(rel->part_rels != NULL && rel->part_rels[i] == NULL);+ rel->part_rels[i] = build_simple_rel(root, childRTindex, rel);+ }++ /*+ * If this child is itself partitioned, recurse. Back off if the inh+ * flag was reset by expand_single_inheritance_child().+ */+ if (childrel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE &&+ childrte->inh)
expand_partitioned_rtentry(root, childrte, childRTindex,
childrel, top_parentrc, lockmode,
appinfos);
@@ -352,7 +380,14 @@ expand_single_inheritance_child(PlannerInfo *root, RangeTblEntry *parentrte,
Oid childOID = RelationGetRelid(childrel);
RangeTblEntry *childrte;
Index childRTindex;
- AppendRelInfo *appinfo;+ AppendRelInfo *appinfo = NULL;+ PartitionDesc child_partdesc = NULL;+ bool is_source_inh_expansion;++ if (childrel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE)+ child_partdesc =+ PartitionDirectoryLookup(root->glob->partition_directory,+ childrel);
/*
* Build an RTE for the child, and attach to query's rangetable list. We
@@ -370,9 +405,11 @@ expand_single_inheritance_child(PlannerInfo *root, RangeTblEntry *parentrte,
*childrte_p = childrte;
childrte->relid = childOID;
childrte->relkind = childrel->rd_rel->relkind;
- /* A partitioned child will need to be expanded further. */- if (childOID != parentOID &&- childrte->relkind == RELKIND_PARTITIONED_TABLE)+ /*+ * A partitioned child will need to be expanded further, but only if it+ * actually has partitions.+ */+ if (childOID != parentOID && child_partdesc && child_partdesc->nparts > 0)
childrte->inh = true;
else
childrte->inh = false;
@@ -383,13 +420,11 @@ expand_single_inheritance_child(PlannerInfo *root, RangeTblEntry *parentrte,
*childRTindex_p = childRTindex;
/*
- * We need an AppendRelInfo if paths will be built for the child RTE. If- * childrte->inh is true, then we'll always need to generate append paths- * for it. If childrte->inh is false, we must scan it if it's not a- * partitioned table; but if it is a partitioned table, then it never has- * any data of its own and need not be scanned.+ * Don't need an AppendRelInfo for duplicate RTEs we create for+ * partitioned table parent rels.
*/
- if (childrte->relkind != RELKIND_PARTITIONED_TABLE || childrte->inh)+ if (childrte->relid != parentOID ||+ childrte->relkind != RELKIND_PARTITIONED_TABLE)
{
appinfo = make_append_rel_info(parentrel, childrel,
parentRTindex, childRTindex);
@@ -416,6 +451,21 @@ expand_single_inheritance_child(PlannerInfo *root, RangeTblEntry *parentrte,
}
/*
+ * If we are expanding source inheritance, store the RTE and appinfo in the+ * respective PlannerInfo arrays, which the caller must already have+ * allocated space for.+ */+ is_source_inh_expansion = (root->simple_rel_array_size > 0);+ if (is_source_inh_expansion > 0)+ {+ Assert(childRTindex < root->simple_rel_array_size);+ Assert(root->simple_rte_array[childRTindex] == NULL);+ root->simple_rte_array[childRTindex] = childrte;+ Assert(root->append_rel_array[childRTindex] == NULL);+ root->append_rel_array[childRTindex] = appinfo;+ }++ /*
* Build a PlanRowMark if parent is marked FOR UPDATE/SHARE.
*/
if (top_parentrc)
diff --git a/src/backend/optimizer/util/plancat.c b/src/backend/optimizer/util/plancat.cindex 30f4dc151b..2b22dff690 100644--- a/src/backend/optimizer/util/plancat.c+++ b/src/backend/optimizer/util/plancat.c@@ -2091,7 +2091,14 @@ set_relation_partition_info(PlannerInfo *root, RelOptInfo *rel,
partkey = RelationGetPartitionKey(relation);
rel->part_scheme = find_partition_scheme(root, relation);
Assert(partdesc != NULL && rel->part_scheme != NULL);
- rel->boundinfo = partition_bounds_copy(partdesc->boundinfo, partkey);+ /*+ * XXX we should be able to Assert(partdesc->nparts > 0), but we've only+ * looked at inh flag so far, whose value is based on relhassubclass as+ * checked by subquery_planner(). relhassubclass may be stale as it's+ * only lazily updated as far as dropped partitions are concerned.+ */+ if (partdesc->nparts > 0)+ rel->boundinfo = partition_bounds_copy(partdesc->boundinfo, partkey);
rel->nparts = partdesc->nparts;
set_baserel_partition_key_exprs(relation, rel);
rel->partition_qual = RelationGetPartitionQual(relation);
diff --git a/src/backend/optimizer/util/relnode.c b/src/backend/optimizer/util/relnode.cindex 8a59819469..9dcc3167e0 100644--- a/src/backend/optimizer/util/relnode.c+++ b/src/backend/optimizer/util/relnode.c@@ -139,6 +139,50 @@ setup_append_rel_array(PlannerInfo *root)
}
/*
+ * expand_planner_arrays+ * Expand the PlannerInfo arrays by add_size members and initialize the+ * the newly added bytes with zero+ */+void+expand_planner_arrays(PlannerInfo *root, int add_size)+{+ int new_size;++ Assert(add_size > 0);++ new_size = root->simple_rel_array_size + add_size;++ /* Expand various arrays and 0-initialize added bytes. */+ root->simple_rte_array = (RangeTblEntry **)+ repalloc(root->simple_rte_array,+ sizeof(RangeTblEntry *) * new_size);+ MemSet(root->simple_rte_array + root->simple_rel_array_size,+ 0, sizeof(RangeTblEntry *) * add_size);+ root->simple_rel_array = (RelOptInfo **)+ repalloc(root->simple_rel_array,+ sizeof(RelOptInfo *) * new_size);+ MemSet(root->simple_rel_array + root->simple_rel_array_size,+ 0, sizeof(RelOptInfo *) * add_size);++ if (root->append_rel_array)+ {+ root->append_rel_array = (AppendRelInfo **)+ repalloc(root->append_rel_array,+ sizeof(AppendRelInfo *) * new_size);+ MemSet(root->append_rel_array + root->simple_rel_array_size,+ 0, sizeof(AppendRelInfo *) * add_size);+ }+ else+ {+ root->append_rel_array = (AppendRelInfo **)+ palloc0(sizeof(AppendRelInfo *) *+ new_size);+ }++ root->simple_rel_array_size = new_size;+}++/*
* build_simple_rel
* Construct a new RelOptInfo for a base relation or 'other' relation.
*/
@@ -295,22 +339,21 @@ add_appendrel_other_rels(PlannerInfo *root, RangeTblEntry *rte, Index rti)
{
ListCell *l;
RelOptInfo *rel;
- int i;-- rel = find_base_rel(root, rti);
/*
- * For partitioned tables, we need to store the child RelOptInfos in the- * rel->part_rels array too.+ * Add inheritance children to the query. For child tables that are+ * themselves partitioned, their children will be added recursively.
*/
- if (rel->part_scheme)+ if (rte->rtekind == RTE_RELATION)
{
- Assert(rel->nparts > 0);- rel->part_rels = (RelOptInfo **)- palloc0(sizeof(RelOptInfo *) * rel->nparts);+ expand_inherited_rtentry(root, rte, rti);+ return;
}
- i = 0;+ /* Add other rels of flattened UNION ALL children. */+ Assert(rte->rtekind == RTE_SUBQUERY);+ rel = find_base_rel(root, rti);+
foreach(l, root->append_rel_list)
{
AppendRelInfo *appinfo = lfirst(l);
@@ -326,30 +369,10 @@ add_appendrel_other_rels(PlannerInfo *root, RangeTblEntry *rte, Index rti)
Assert(childrte != NULL);
childrel = build_simple_rel(root, childRTindex, rel);
- /*- * For partitioned parents, we also need to add childrel to its- * part_rels array. The order in which child tables appear in- * append_rel_list is same as the order in which they appear in the- * parent's PartitionDesc, so assigning partitions like this works.- */- if (rel->part_scheme != NULL)- {- Assert(i < rel->nparts);- rel->part_rels[i] = childrel;- }-- i++;-
/* Child may itself be an inherited relation. */
if (childrte->inh)
add_appendrel_other_rels(root, childrte, childRTindex);
}
-- /*- * For a partitioned table with non-zero number of partitions, we must- * have assigned all elements of its part_rels array.- */- Assert(rel->nparts == 0 || i == rel->nparts);
}
/*
diff --git a/src/include/optimizer/inherit.h b/src/include/optimizer/inherit.hindex d2418f15cf..427c1d0abe 100644--- a/src/include/optimizer/inherit.h+++ b/src/include/optimizer/inherit.h@@ -16,7 +16,7 @@
#include "nodes/pathnodes.h"
--extern void expand_inherited_tables(PlannerInfo *root);+extern void expand_inherited_rtentry(PlannerInfo *root, RangeTblEntry *rte,+ Index rti);
#endif /* INHERIT_H */
diff --git a/src/include/optimizer/pathnode.h b/src/include/optimizer/pathnode.hindex 1a07963a7d..60361507d2 100644--- a/src/include/optimizer/pathnode.h+++ b/src/include/optimizer/pathnode.h@@ -277,6 +277,7 @@ extern Path *reparameterize_path_by_child(PlannerInfo *root, Path *path,
*/
extern void setup_simple_rel_arrays(PlannerInfo *root);
extern void setup_append_rel_array(PlannerInfo *root);
+extern void expand_planner_arrays(PlannerInfo *root, int add_size);
extern RelOptInfo *build_simple_rel(PlannerInfo *root, int relid,
RelOptInfo *parent);
extern void add_appendrel_other_rels(PlannerInfo *root, RangeTblEntry *rte,
diff --git a/src/test/regress/expected/partition_prune.out b/src/test/regress/expected/partition_prune.outindex 30946f77b6..71942394ba 100644--- a/src/test/regress/expected/partition_prune.out+++ b/src/test/regress/expected/partition_prune.out@@ -2559,16 +2559,16 @@ update ab_a1 set b = 3 from ab where ab.a = 1 and ab.a = ab_a1.a;
Index Cond: (a = 1)
-> Nested Loop (actual rows=1 loops=1)
-> Append (actual rows=1 loops=1)
- -> Bitmap Heap Scan on ab_a1_b1 ab_a1_b1_1 (actual rows=0 loops=1)+ -> Bitmap Heap Scan on ab_a1_b1 ab_a1_b1_2 (actual rows=0 loops=1)
Recheck Cond: (a = 1)
-> Bitmap Index Scan on ab_a1_b1_a_idx (actual rows=0 loops=1)
Index Cond: (a = 1)
- -> Bitmap Heap Scan on ab_a1_b2 ab_a1_b2_1 (actual rows=1 loops=1)+ -> Bitmap Heap Scan on ab_a1_b2 ab_a1_b2_2 (actual rows=1 loops=1)
Recheck Cond: (a = 1)
Heap Blocks: exact=1
-> Bitmap Index Scan on ab_a1_b2_a_idx (actual rows=1 loops=1)
Index Cond: (a = 1)
- -> Bitmap Heap Scan on ab_a1_b3 ab_a1_b3_1 (actual rows=0 loops=1)+ -> Bitmap Heap Scan on ab_a1_b3 ab_a1_b3_2 (actual rows=0 loops=1)
Recheck Cond: (a = 1)
-> Bitmap Index Scan on ab_a1_b3_a_idx (actual rows=1 loops=1)
Index Cond: (a = 1)
@@ -2580,16 +2580,16 @@ update ab_a1 set b = 3 from ab where ab.a = 1 and ab.a = ab_a1.a;
Index Cond: (a = 1)
-> Nested Loop (actual rows=0 loops=1)
-> Append (actual rows=1 loops=1)
- -> Bitmap Heap Scan on ab_a1_b1 ab_a1_b1_1 (actual rows=0 loops=1)+ -> Bitmap Heap Scan on ab_a1_b1 ab_a1_b1_3 (actual rows=0 loops=1)
Recheck Cond: (a = 1)
-> Bitmap Index Scan on ab_a1_b1_a_idx (actual rows=0 loops=1)
Index Cond: (a = 1)
- -> Bitmap Heap Scan on ab_a1_b2 ab_a1_b2_1 (actual rows=1 loops=1)+ -> Bitmap Heap Scan on ab_a1_b2 ab_a1_b2_3 (actual rows=1 loops=1)
Recheck Cond: (a = 1)
Heap Blocks: exact=1
-> Bitmap Index Scan on ab_a1_b2_a_idx (actual rows=1 loops=1)
Index Cond: (a = 1)
- -> Bitmap Heap Scan on ab_a1_b3 ab_a1_b3_1 (actual rows=0 loops=1)+ -> Bitmap Heap Scan on ab_a1_b3 ab_a1_b3_3 (actual rows=0 loops=1)
Recheck Cond: (a = 1)
-> Bitmap Index Scan on ab_a1_b3_a_idx (actual rows=1 loops=1)
Index Cond: (a = 1)
diff --git a/src/test/regress/expected/rowsecurity.out b/src/test/regress/expected/rowsecurity.outindex bad5199d9e..37ef1deab6 100644--- a/src/test/regress/expected/rowsecurity.out+++ b/src/test/regress/expected/rowsecurity.out@@ -1815,26 +1815,26 @@ AND f_leak(t1_1.b) AND f_leak(t1_2.b) RETURNING *, t1_1, t1_2;
-> Seq Scan on t3 t1_2_2
Filter: ((a = 4) AND ((a % 2) = 0) AND f_leak(b))
-> Nested Loop
- Join Filter: (t1_1_1.b = t1_2.b)+ Join Filter: (t1_1_1.b = t1_2_3.b)
-> Seq Scan on t2 t1_1_1
Filter: ((a = 4) AND ((a % 2) = 0) AND f_leak(b))
-> Append
- -> Seq Scan on t1 t1_2+ -> Seq Scan on t1 t1_2_3
Filter: ((a = 4) AND ((a % 2) = 0) AND f_leak(b))
- -> Seq Scan on t2 t1_2_1+ -> Seq Scan on t2 t1_2_4
Filter: ((a = 4) AND ((a % 2) = 0) AND f_leak(b))
- -> Seq Scan on t3 t1_2_2+ -> Seq Scan on t3 t1_2_5
Filter: ((a = 4) AND ((a % 2) = 0) AND f_leak(b))
-> Nested Loop
- Join Filter: (t1_1_2.b = t1_2.b)+ Join Filter: (t1_1_2.b = t1_2_6.b)
-> Seq Scan on t3 t1_1_2
Filter: ((a = 4) AND ((a % 2) = 0) AND f_leak(b))
-> Append
- -> Seq Scan on t1 t1_2+ -> Seq Scan on t1 t1_2_6
Filter: ((a = 4) AND ((a % 2) = 0) AND f_leak(b))
- -> Seq Scan on t2 t1_2_1+ -> Seq Scan on t2 t1_2_7
Filter: ((a = 4) AND ((a % 2) = 0) AND f_leak(b))
- -> Seq Scan on t3 t1_2_2+ -> Seq Scan on t3 t1_2_8
Filter: ((a = 4) AND ((a % 2) = 0) AND f_leak(b))
(37 rows)
--
2.11.0
[text/plain] v34-0004-Adjust-inheritance_planner-to-reuse-source-child.patch (27.2K, ../c8dcf0fd-4759-5e1c-ea67-e2d3f98e81bd@lab.ntt.co.jp/5-v34-0004-Adjust-inheritance_planner-to-reuse-source-child.patch)
download | inline diff:
From 0c7eff3c9e0f6ba392c51a6c1bbbbe2b352aea88 Mon Sep 17 00:00:00 2001
From: amit <amitlangote09@gmail.com>
Date: Mon, 4 Mar 2019 15:52:11 +0900
Subject: [PATCH v34 4/9] Adjust inheritance_planner to reuse source child
tables
Set of source inheritance child tables won't change across repeated
planning of the query for different target child relations. So,
note down the RangeTblEntrys, AppendRelInfos, and PlanRowMarks of
the source inheritance child tables after the planning for the first
child table is finished. When planning for the subsequent child
target tables, put the saved objects of source inheritance child
tables into the child PlannerInfos and set contains_inherit_children
to true, so that query_planner won't add them again.
This restores the regression test outputs to their original state,
because like before, source inheritance child tables are not added
multiple times.
---
contrib/postgres_fdw/expected/postgres_fdw.out | 46 +++----
src/backend/nodes/outfuncs.c | 1 +
src/backend/optimizer/plan/planner.c | 176 +++++++++++++++----------
src/backend/optimizer/util/relnode.c | 47 ++++++-
src/include/nodes/pathnodes.h | 7 +
src/test/regress/expected/partition_prune.out | 66 +++++++++-
src/test/regress/expected/rowsecurity.out | 16 +--
src/test/regress/sql/partition_prune.sql | 7 +
8 files changed, 258 insertions(+), 108 deletions(-)
diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.outindex bbc9ead2e9..c912e95fe1 100644--- a/contrib/postgres_fdw/expected/postgres_fdw.out+++ b/contrib/postgres_fdw/expected/postgres_fdw.out@@ -7190,8 +7190,8 @@ select * from bar where f1 in (select f1 from foo) for share;
-- Check UPDATE with inherited target and an inherited source table
explain (verbose, costs off)
update bar set f2 = f2 + 100 where f1 in (select f1 from foo);
- QUERY PLAN ----------------------------------------------------------------------------------------------------+ QUERY PLAN +---------------------------------------------------------------------------------------------
Update on public.bar
Update on public.bar
Foreign Update on public.bar2
@@ -7214,22 +7214,22 @@ update bar set f2 = f2 + 100 where f1 in (select f1 from foo);
Output: foo2.f1, foo2.ctid, foo2.*, foo2.tableoid
Remote SQL: SELECT f1, f2, f3, ctid FROM public.loct1
-> Hash Join
- Output: bar2.f1, (bar2.f2 + 100), bar2.f3, bar2.ctid, foo_1.ctid, foo_1.*, foo_1.tableoid+ Output: bar2.f1, (bar2.f2 + 100), bar2.f3, bar2.ctid, foo.ctid, foo.*, foo.tableoid
Inner Unique: true
- Hash Cond: (bar2.f1 = foo_1.f1)+ Hash Cond: (bar2.f1 = foo.f1)
-> Foreign Scan on public.bar2
Output: bar2.f1, bar2.f2, bar2.f3, bar2.ctid
Remote SQL: SELECT f1, f2, f3, ctid FROM public.loct2 FOR UPDATE
-> Hash
- Output: foo_1.f1, foo_1.ctid, foo_1.*, foo_1.tableoid+ Output: foo.f1, foo.ctid, foo.*, foo.tableoid
-> HashAggregate
- Output: foo_1.f1, foo_1.ctid, foo_1.*, foo_1.tableoid- Group Key: foo_1.f1+ Output: foo.f1, foo.ctid, foo.*, foo.tableoid+ Group Key: foo.f1
-> Append
- -> Seq Scan on public.foo foo_1- Output: foo_1.f1, foo_1.ctid, foo_1.*, foo_1.tableoid- -> Foreign Scan on public.foo2 foo2_1- Output: foo2_1.f1, foo2_1.ctid, foo2_1.*, foo2_1.tableoid+ -> Seq Scan on public.foo+ Output: foo.f1, foo.ctid, foo.*, foo.tableoid+ -> Foreign Scan on public.foo2+ Output: foo2.f1, foo2.ctid, foo2.*, foo2.tableoid
Remote SQL: SELECT f1, f2, f3, ctid FROM public.loct1
(39 rows)
@@ -7276,8 +7276,8 @@ where bar.f1 = ss.f1;
-> Seq Scan on public.bar
Output: bar.f1, bar.f2, bar.ctid
-> Merge Join
- Output: bar2.f1, (bar2.f2 + 100), bar2.f3, bar2.ctid, (ROW(foo_2.f1))- Merge Cond: (bar2.f1 = foo_2.f1)+ Output: bar2.f1, (bar2.f2 + 100), bar2.f3, bar2.ctid, (ROW(foo.f1))+ Merge Cond: (bar2.f1 = foo.f1)
-> Sort
Output: bar2.f1, bar2.f2, bar2.f3, bar2.ctid
Sort Key: bar2.f1
@@ -7285,18 +7285,18 @@ where bar.f1 = ss.f1;
Output: bar2.f1, bar2.f2, bar2.f3, bar2.ctid
Remote SQL: SELECT f1, f2, f3, ctid FROM public.loct2 FOR UPDATE
-> Sort
- Output: foo_2.f1, (ROW(foo_2.f1))- Sort Key: foo_2.f1+ Output: foo.f1, (ROW(foo.f1))+ Sort Key: foo.f1
-> Append
- -> Seq Scan on public.foo foo_2- Output: foo_2.f1, ROW(foo_2.f1)- -> Foreign Scan on public.foo2 foo2_2- Output: foo2_2.f1, ROW(foo2_2.f1)+ -> Seq Scan on public.foo+ Output: foo.f1, ROW(foo.f1)+ -> Foreign Scan on public.foo2+ Output: foo2.f1, ROW(foo2.f1)
Remote SQL: SELECT f1 FROM public.loct1
- -> Seq Scan on public.foo foo_3- Output: (foo_3.f1 + 3), ROW((foo_3.f1 + 3))- -> Foreign Scan on public.foo2 foo2_3- Output: (foo2_3.f1 + 3), ROW((foo2_3.f1 + 3))+ -> Seq Scan on public.foo foo_1+ Output: (foo_1.f1 + 3), ROW((foo_1.f1 + 3))+ -> Foreign Scan on public.foo2 foo2_1+ Output: (foo2_1.f1 + 3), ROW((foo2_1.f1 + 3))
Remote SQL: SELECT f1 FROM public.loct1
(45 rows)
diff --git a/src/backend/nodes/outfuncs.c b/src/backend/nodes/outfuncs.cindex 69179a07c3..cd4741d36b 100644--- a/src/backend/nodes/outfuncs.c+++ b/src/backend/nodes/outfuncs.c@@ -2220,6 +2220,7 @@ _outPlannerInfo(StringInfo str, const PlannerInfo *node)
WRITE_BITMAPSET_FIELD(curOuterRels);
WRITE_NODE_FIELD(curOuterParams);
WRITE_BOOL_FIELD(partColsUpdated);
+ WRITE_BOOL_FIELD(contains_inherit_children);
}
static void
diff --git a/src/backend/optimizer/plan/planner.c b/src/backend/optimizer/plan/planner.cindex 02ca1a283a..95f69acac5 100644--- a/src/backend/optimizer/plan/planner.c+++ b/src/backend/optimizer/plan/planner.c@@ -637,6 +637,7 @@ subquery_planner(PlannerGlobal *glob, Query *parse,
root->wt_param_id = -1;
root->non_recursive_path = NULL;
root->partColsUpdated = false;
+ root->contains_inherit_children = false;
/*
* If there is a WITH list, process each WITH query and either convert it
@@ -1188,7 +1189,6 @@ inheritance_planner(PlannerInfo *root)
Query *parse = root->parse;
int top_parentRTindex = parse->resultRelation;
Bitmapset *subqueryRTindexes;
- Bitmapset *modifiableARIindexes;
int nominalRelation = -1;
Index rootRelation = 0;
List *final_rtable = NIL;
@@ -1210,6 +1210,10 @@ inheritance_planner(PlannerInfo *root)
Bitmapset *parent_relids = bms_make_singleton(top_parentRTindex);
PlannerInfo **parent_roots = NULL;
List *orig_append_rel_list = list_copy(root->append_rel_list);
+ List *source_appinfos = NIL;+ List *source_child_rowmarks = NIL;+ List *source_child_rtes = NIL;+ bool is_first_child;
Assert(parse->commandType != CMD_INSERT);
@@ -1217,7 +1221,8 @@ inheritance_planner(PlannerInfo *root)
* Add child target relations. Note that this only adds the children of
* the query's target relation and no other. Especially, the children of
* any source relations are added by query_planner() during the planning
- * of each child query.+ * of the 1st child query and reused for the planning of subsequent child+ * queries.
*/
parent_rte = rt_fetch(top_parentRTindex, root->parse->rtable);
@@ -1266,32 +1271,6 @@ inheritance_planner(PlannerInfo *root)
}
/*
- * Next, we want to identify which AppendRelInfo items contain references- * to any of the aforesaid subquery RTEs. These items will need to be- * copied and modified to adjust their subquery references; whereas the- * other ones need not be touched. It's worth being tense over this- * because we can usually avoid processing most of the AppendRelInfo- * items, thereby saving O(N^2) space and time when the target is a large- * inheritance tree. We can identify AppendRelInfo items by their- * child_relid, since that should be unique within the list.- */- modifiableARIindexes = NULL;- if (subqueryRTindexes != NULL)- {- foreach(lc, root->append_rel_list)- {- AppendRelInfo *appinfo = lfirst_node(AppendRelInfo, lc);-- if (bms_is_member(appinfo->parent_relid, subqueryRTindexes) ||- bms_is_member(appinfo->child_relid, subqueryRTindexes) ||- bms_overlap(pull_varnos((Node *) appinfo->translated_vars),- subqueryRTindexes))- modifiableARIindexes = bms_add_member(modifiableARIindexes,- appinfo->child_relid);- }- }-- /*
* If the parent RTE is a partitioned table, we should use that as the
* nominal target relation, because the RTEs added for partitioned tables
* (including the root parent) as child members of the inheritance set do
@@ -1320,6 +1299,7 @@ inheritance_planner(PlannerInfo *root)
/*
* And now we can get on with generating a plan for each child table.
*/
+ is_first_child = true;
foreach(lc, root->append_rel_list)
{
AppendRelInfo *appinfo = lfirst_node(AppendRelInfo, lc);
@@ -1348,9 +1328,6 @@ inheritance_planner(PlannerInfo *root)
subroot = makeNode(PlannerInfo);
memcpy(subroot, parent_root, sizeof(PlannerInfo));
- /* grouping_planner doesn't need to see the target children. */- subroot->append_rel_list = list_copy(orig_append_rel_list);-
/*
* Generate modified query with this rel as target. We first apply
* adjust_appendrel_attrs, which copies the Query and changes
@@ -1433,30 +1410,45 @@ inheritance_planner(PlannerInfo *root)
subroot->rowMarks = copyObject(parent_root->rowMarks);
/*
- * The append_rel_list likewise might contain references to subquery- * RTEs (if any subqueries were flattenable UNION ALLs). So prepare- * to apply ChangeVarNodes to that, too. As explained above, we only- * want to copy items that actually contain such references; the rest- * can just get linked into the subroot's append_rel_list.- *- * If we know there are no such references, we can just use the outer- * append_rel_list unmodified.+ * Prepare to apply ChangeVarNodes to orig_append_rel_list by copying+ * the AppendRelInfos contained in it. Note that orig_append_rel_list+ * contains only the AppendRelInfos pertaining to flattened UNION ALL+ * subqueries, so we'll be applying ChangeVarNodes to all of them.+ * We don't need to look at other members of+ * parent_root->append_rel_list, which are those corresponding to+ * child target relations, because they won't contain any subquery+ * references.
*/
- if (modifiableARIindexes != NULL)+ subroot->append_rel_list = copyObject(orig_append_rel_list);++ /*+ * If this isn't the first child query, then we can use the child+ * objects for source child relations created during the planning of+ * 1st child query. IOW, this planning run doesn't need to create the+ * child objects again, indicated by setting contains_inherit_children+ * of the child PlannerInfo. We use list_copy() on source_child_rtes,+ * because we may add more entries to subroot->parse->rtable below.+ *+ * We don't need to add source_appinfos into subroot yet, because we+ * won't need to modify any of its entries. Only orig_append_rel_list+ * will contain references to the subquery RTEs that we've already+ * made copies of. This also saves time below in the+ * ChangeVarNodes((Node *) subroot->append_rel_list, ...) statement.+ */+ if (!is_first_child)
{
- ListCell *lc2;+ subroot->rowMarks =+ list_concat(subroot->rowMarks,+ list_copy(source_child_rowmarks));+ subroot->parse->rtable =+ list_concat(subroot->parse->rtable,+ list_copy(source_child_rtes));- subroot->append_rel_list = NIL;- foreach(lc2, parent_root->append_rel_list)- {- AppendRelInfo *appinfo2 = lfirst_node(AppendRelInfo, lc2);-- if (bms_is_member(appinfo2->child_relid, modifiableARIindexes))- appinfo2 = copyObject(appinfo2);-- subroot->append_rel_list = lappend(subroot->append_rel_list,- appinfo2);- }+ /*+ * We have added child RTEs and row marks, so the next+ * query_planner doesn't need to add them again.+ */+ subroot->contains_inherit_children = true;
}
/*
@@ -1499,20 +1491,8 @@ inheritance_planner(PlannerInfo *root)
newrti = list_length(subroot->parse->rtable) + 1;
ChangeVarNodes((Node *) subroot->parse, rti, newrti, 0);
ChangeVarNodes((Node *) subroot->rowMarks, rti, newrti, 0);
- /* Skip processing unchanging parts of append_rel_list */- if (modifiableARIindexes != NULL)- {- ListCell *lc2;-- foreach(lc2, subroot->append_rel_list)- {- AppendRelInfo *appinfo2 = lfirst_node(AppendRelInfo, lc2);-- if (bms_is_member(appinfo2->child_relid,- modifiableARIindexes))- ChangeVarNodes((Node *) appinfo2, rti, newrti, 0);- }- }+ ChangeVarNodes((Node *) subroot->append_rel_list, rti,+ newrti, 0);
rte = copyObject(rte);
ChangeVarNodes((Node *) rte->securityQuals, rti, newrti, 0);
subroot->parse->rtable = lappend(subroot->parse->rtable,
@@ -1522,6 +1502,14 @@ inheritance_planner(PlannerInfo *root)
}
}
+ /*+ * Concatenate source AppendRelInfos, so the source child objects that+ * we hooked into subroot above are discoverable.+ */+ if (!is_first_child)+ subroot->append_rel_list = list_concat(subroot->append_rel_list,+ source_appinfos);+
/* There shouldn't be any OJ info to translate, as yet */
Assert(subroot->join_info_list == NIL);
/* and we haven't created PlaceHolderInfos, either */
@@ -1540,6 +1528,62 @@ inheritance_planner(PlannerInfo *root)
subpath = sub_final_rel->cheapest_total_path;
/*
+ * If we just finished planning the first child query, record the+ * child objects of source inheritance sets that were generated+ * during planning, if any.+ */+ if (is_first_child && subroot->append_rel_list)+ {+ int n_skip_appinfos = list_length(orig_append_rel_list);+ int n_skip_rowmarks = list_length(parent_root->rowMarks);+ int n_skip_rtes = list_length(parent_parse->rtable);+ ListCell *lc2;++ Assert(source_appinfos == NIL);+ source_appinfos = list_copy_tail(subroot->append_rel_list,+ n_skip_appinfos);+ Assert(source_child_rowmarks == NIL);+ source_child_rowmarks = list_copy_tail(subroot->rowMarks,+ n_skip_rowmarks);+ Assert(source_child_rtes == NIL);+ source_child_rtes = list_copy_tail(subroot->parse->rtable,+ n_skip_rtes);++ /*+ * Parent PlanRowMarks (actually their copies in the child+ * subroot) are modified when adding the child PlanRowMarks.+ * Copy those changes back into the parent_root's copies of those+ * parent PlanRowMarks. Doing that is necessary, because we won't+ * hit the code that adds child PlanRowMarks again.+ *+ * The original parent row marks are the beginning of+ * subroot->rowMarks; skip the rest.+ */+ foreach(lc2, parent_root->rowMarks)+ {+ PlanRowMark *oldrc = lfirst_node(PlanRowMark, lc2);+ ListCell *lc3;++ foreach(lc3, subroot->rowMarks)+ {+ PlanRowMark *newrc = lfirst_node(PlanRowMark, lc3);++ if (oldrc->rti == newrc->rti)+ {+ /* modified in expand_single_inheritance_child() */+ oldrc->allMarkTypes = newrc->allMarkTypes;+ /* modified in expand_inherited_rtentry() */+ oldrc->isParent = newrc->isParent;+ }+ else+ break;+ }+ }++ is_first_child = false;+ }++ /*
* If this child rel was excluded by constraint exclusion, exclude it
* from the result plan.
*/
diff --git a/src/backend/optimizer/util/relnode.c b/src/backend/optimizer/util/relnode.cindex 9dcc3167e0..0bf024b535 100644--- a/src/backend/optimizer/util/relnode.c+++ b/src/backend/optimizer/util/relnode.c@@ -339,21 +339,38 @@ add_appendrel_other_rels(PlannerInfo *root, RangeTblEntry *rte, Index rti)
{
ListCell *l;
RelOptInfo *rel;
+ int i;
/*
- * Add inheritance children to the query. For child tables that are- * themselves partitioned, their children will be added recursively.+ * Add inheritance children to the query if not already done. For child+ * tables that are themselves partitioned, their children will be added+ * recursively.+ *+ * If root already contains the children, we just need to build their+ * "other rel" RelOptInfos, which we do below.
*/
- if (rte->rtekind == RTE_RELATION)+ if (rte->rtekind == RTE_RELATION && !root->contains_inherit_children)
{
expand_inherited_rtentry(root, rte, rti);
return;
}
- /* Add other rels of flattened UNION ALL children. */- Assert(rte->rtekind == RTE_SUBQUERY);+ /* Add other rels. */+ Assert(rte->rtekind == RTE_SUBQUERY || rte->rtekind == RTE_RELATION);
rel = find_base_rel(root, rti);
+ /*+ * For partitioned tables, we need to store the child RelOptInfos in the+ * rel->part_rels array too.+ */+ if (rel->part_scheme)+ {+ Assert(rel->nparts > 0);+ rel->part_rels = (RelOptInfo **)+ palloc0(sizeof(RelOptInfo *) * rel->nparts);+ }++ i = 0;
foreach(l, root->append_rel_list)
{
AppendRelInfo *appinfo = lfirst(l);
@@ -369,10 +386,30 @@ add_appendrel_other_rels(PlannerInfo *root, RangeTblEntry *rte, Index rti)
Assert(childrte != NULL);
childrel = build_simple_rel(root, childRTindex, rel);
+ /*+ * For partitioned parents, we also need to add childrel to its+ * part_rels array. The order in which child tables appear in+ * append_rel_list is same as the order in which they appear in the+ * parent's PartitionDesc, so assigning partitions like this works.+ */+ if (rel->part_scheme != NULL)+ {+ Assert(i < rel->nparts);+ rel->part_rels[i] = childrel;+ }++ i++;+
/* Child may itself be an inherited relation. */
if (childrte->inh)
add_appendrel_other_rels(root, childrte, childRTindex);
}
++ /*+ * For a partitioned table with non-zero number of partitions, we must+ * have assigned all elements of its part_rels array.+ */+ Assert(rel->nparts == 0 || i == rel->nparts);
}
/*
diff --git a/src/include/nodes/pathnodes.h b/src/include/nodes/pathnodes.hindex 253e0b7e48..31811f9adb 100644--- a/src/include/nodes/pathnodes.h+++ b/src/include/nodes/pathnodes.h@@ -350,6 +350,13 @@ struct PlannerInfo
/* Does this query modify any partition key columns? */
bool partColsUpdated;
++ /*+ * Does this PlannerInfo and its Query object contain *all* inheritance+ * children? If true, the RTEs, the AppendRelInfos, and the PlanRowMarks+ * of all the children are assumed to be present.+ */+ bool contains_inherit_children;
};
diff --git a/src/test/regress/expected/partition_prune.out b/src/test/regress/expected/partition_prune.outindex 71942394ba..f1115c561e 100644--- a/src/test/regress/expected/partition_prune.out+++ b/src/test/regress/expected/partition_prune.out@@ -2559,16 +2559,16 @@ update ab_a1 set b = 3 from ab where ab.a = 1 and ab.a = ab_a1.a;
Index Cond: (a = 1)
-> Nested Loop (actual rows=1 loops=1)
-> Append (actual rows=1 loops=1)
- -> Bitmap Heap Scan on ab_a1_b1 ab_a1_b1_2 (actual rows=0 loops=1)+ -> Bitmap Heap Scan on ab_a1_b1 ab_a1_b1_1 (actual rows=0 loops=1)
Recheck Cond: (a = 1)
-> Bitmap Index Scan on ab_a1_b1_a_idx (actual rows=0 loops=1)
Index Cond: (a = 1)
- -> Bitmap Heap Scan on ab_a1_b2 ab_a1_b2_2 (actual rows=1 loops=1)+ -> Bitmap Heap Scan on ab_a1_b2 ab_a1_b2_1 (actual rows=1 loops=1)
Recheck Cond: (a = 1)
Heap Blocks: exact=1
-> Bitmap Index Scan on ab_a1_b2_a_idx (actual rows=1 loops=1)
Index Cond: (a = 1)
- -> Bitmap Heap Scan on ab_a1_b3 ab_a1_b3_2 (actual rows=0 loops=1)+ -> Bitmap Heap Scan on ab_a1_b3 ab_a1_b3_1 (actual rows=0 loops=1)
Recheck Cond: (a = 1)
-> Bitmap Index Scan on ab_a1_b3_a_idx (actual rows=1 loops=1)
Index Cond: (a = 1)
@@ -2580,16 +2580,16 @@ update ab_a1 set b = 3 from ab where ab.a = 1 and ab.a = ab_a1.a;
Index Cond: (a = 1)
-> Nested Loop (actual rows=0 loops=1)
-> Append (actual rows=1 loops=1)
- -> Bitmap Heap Scan on ab_a1_b1 ab_a1_b1_3 (actual rows=0 loops=1)+ -> Bitmap Heap Scan on ab_a1_b1 ab_a1_b1_1 (actual rows=0 loops=1)
Recheck Cond: (a = 1)
-> Bitmap Index Scan on ab_a1_b1_a_idx (actual rows=0 loops=1)
Index Cond: (a = 1)
- -> Bitmap Heap Scan on ab_a1_b2 ab_a1_b2_3 (actual rows=1 loops=1)+ -> Bitmap Heap Scan on ab_a1_b2 ab_a1_b2_1 (actual rows=1 loops=1)
Recheck Cond: (a = 1)
Heap Blocks: exact=1
-> Bitmap Index Scan on ab_a1_b2_a_idx (actual rows=1 loops=1)
Index Cond: (a = 1)
- -> Bitmap Heap Scan on ab_a1_b3 ab_a1_b3_3 (actual rows=0 loops=1)+ -> Bitmap Heap Scan on ab_a1_b3 ab_a1_b3_1 (actual rows=0 loops=1)
Recheck Cond: (a = 1)
-> Bitmap Index Scan on ab_a1_b3_a_idx (actual rows=1 loops=1)
Index Cond: (a = 1)
@@ -2606,6 +2606,60 @@ table ab;
1 | 3
(1 row)
+-- Test UPDATE where source relation has run-time pruning enabled+truncate ab;+insert into ab values (1, 1), (1, 2), (1, 3), (2, 1);+explain (analyze, costs off, summary off, timing off)+update ab_a1 set b = 3 from ab_a2 where ab_a2.b = (select 1);+ QUERY PLAN +----------------------------------------------------------------------+ Update on ab_a1 (actual rows=0 loops=1)+ Update on ab_a1_b1+ Update on ab_a1_b2+ Update on ab_a1_b3+ InitPlan 1 (returns $0)+ -> Result (actual rows=1 loops=1)+ -> Nested Loop (actual rows=1 loops=1)+ -> Seq Scan on ab_a1_b1 (actual rows=1 loops=1)+ -> Materialize (actual rows=1 loops=1)+ -> Append (actual rows=1 loops=1)+ -> Seq Scan on ab_a2_b1 (actual rows=1 loops=1)+ Filter: (b = $0)+ -> Seq Scan on ab_a2_b2 (never executed)+ Filter: (b = $0)+ -> Seq Scan on ab_a2_b3 (never executed)+ Filter: (b = $0)+ -> Nested Loop (actual rows=1 loops=1)+ -> Seq Scan on ab_a1_b2 (actual rows=1 loops=1)+ -> Materialize (actual rows=1 loops=1)+ -> Append (actual rows=1 loops=1)+ -> Seq Scan on ab_a2_b1 (actual rows=1 loops=1)+ Filter: (b = $0)+ -> Seq Scan on ab_a2_b2 (never executed)+ Filter: (b = $0)+ -> Seq Scan on ab_a2_b3 (never executed)+ Filter: (b = $0)+ -> Nested Loop (actual rows=1 loops=1)+ -> Seq Scan on ab_a1_b3 (actual rows=1 loops=1)+ -> Materialize (actual rows=1 loops=1)+ -> Append (actual rows=1 loops=1)+ -> Seq Scan on ab_a2_b1 (actual rows=1 loops=1)+ Filter: (b = $0)+ -> Seq Scan on ab_a2_b2 (never executed)+ Filter: (b = $0)+ -> Seq Scan on ab_a2_b3 (never executed)+ Filter: (b = $0)+(36 rows)++select tableoid::regclass, * from ab;+ tableoid | a | b +----------+---+---+ ab_a1_b3 | 1 | 3+ ab_a1_b3 | 1 | 3+ ab_a1_b3 | 1 | 3+ ab_a2_b1 | 2 | 1+(4 rows)+
drop table ab, lprt_a;
-- Join
create table tbl1(col1 int);
diff --git a/src/test/regress/expected/rowsecurity.out b/src/test/regress/expected/rowsecurity.outindex 37ef1deab6..bad5199d9e 100644--- a/src/test/regress/expected/rowsecurity.out+++ b/src/test/regress/expected/rowsecurity.out@@ -1815,26 +1815,26 @@ AND f_leak(t1_1.b) AND f_leak(t1_2.b) RETURNING *, t1_1, t1_2;
-> Seq Scan on t3 t1_2_2
Filter: ((a = 4) AND ((a % 2) = 0) AND f_leak(b))
-> Nested Loop
- Join Filter: (t1_1_1.b = t1_2_3.b)+ Join Filter: (t1_1_1.b = t1_2.b)
-> Seq Scan on t2 t1_1_1
Filter: ((a = 4) AND ((a % 2) = 0) AND f_leak(b))
-> Append
- -> Seq Scan on t1 t1_2_3+ -> Seq Scan on t1 t1_2
Filter: ((a = 4) AND ((a % 2) = 0) AND f_leak(b))
- -> Seq Scan on t2 t1_2_4+ -> Seq Scan on t2 t1_2_1
Filter: ((a = 4) AND ((a % 2) = 0) AND f_leak(b))
- -> Seq Scan on t3 t1_2_5+ -> Seq Scan on t3 t1_2_2
Filter: ((a = 4) AND ((a % 2) = 0) AND f_leak(b))
-> Nested Loop
- Join Filter: (t1_1_2.b = t1_2_6.b)+ Join Filter: (t1_1_2.b = t1_2.b)
-> Seq Scan on t3 t1_1_2
Filter: ((a = 4) AND ((a % 2) = 0) AND f_leak(b))
-> Append
- -> Seq Scan on t1 t1_2_6+ -> Seq Scan on t1 t1_2
Filter: ((a = 4) AND ((a % 2) = 0) AND f_leak(b))
- -> Seq Scan on t2 t1_2_7+ -> Seq Scan on t2 t1_2_1
Filter: ((a = 4) AND ((a % 2) = 0) AND f_leak(b))
- -> Seq Scan on t3 t1_2_8+ -> Seq Scan on t3 t1_2_2
Filter: ((a = 4) AND ((a % 2) = 0) AND f_leak(b))
(37 rows)
diff --git a/src/test/regress/sql/partition_prune.sql b/src/test/regress/sql/partition_prune.sqlindex dc327caffd..e15afc2dfd 100644--- a/src/test/regress/sql/partition_prune.sql+++ b/src/test/regress/sql/partition_prune.sql@@ -588,6 +588,13 @@ explain (analyze, costs off, summary off, timing off)
update ab_a1 set b = 3 from ab where ab.a = 1 and ab.a = ab_a1.a;
table ab;
+-- Test UPDATE where source relation has run-time pruning enabled+truncate ab;+insert into ab values (1, 1), (1, 2), (1, 3), (2, 1);+explain (analyze, costs off, summary off, timing off)+update ab_a1 set b = 3 from ab_a2 where ab_a2.b = (select 1);+select tableoid::regclass, * from ab;+
drop table ab, lprt_a;
-- Join
--
2.11.0
[text/plain] v34-0005-Further-tweak-inheritance_planner-to-avoid-usele.patch (4.7K, ../c8dcf0fd-4759-5e1c-ea67-e2d3f98e81bd@lab.ntt.co.jp/6-v34-0005-Further-tweak-inheritance_planner-to-avoid-usele.patch)
download | inline diff:
From 9be5b886ea04fc33c181ef991e443a6e792b6678 Mon Sep 17 00:00:00 2001
From: amit <amitlangote09@gmail.com>
Date: Fri, 8 Mar 2019 17:50:20 +0900
Subject: [PATCH v34 5/9] Further tweak inheritance_planner to avoid useless
work
When running adjust_appendrel_attrs() on the query, there's no need
for its range table to contain child target RTEs, because they don't
need to be translated. Trimming those off the range table makes
range_table_mutator() finish much quicker, because now it doesn't
have to crawl through potentially many RTEs essentially just copying
them. Note that copying the child target RTEs is unnecessary as
they won't be modified across different planning cycles. The (sub-)
list they are contained still must copied with list_copy(), because
more entries may be added to individual child query's range table.
Furthermore, in a few places where the code iterates over the range
table to first locate and later modify subquery RTEs, it's OK to
ignore the child target RTEs, because there won't be any subquery
RTEs among them and also they won't contain any references to
subquery RTEs that are found.
---
src/backend/optimizer/plan/planner.c | 34 ++++++++++++++++++++++++++++------
1 file changed, 28 insertions(+), 6 deletions(-)
diff --git a/src/backend/optimizer/plan/planner.c b/src/backend/optimizer/plan/planner.cindex 95f69acac5..f50c4321f3 100644--- a/src/backend/optimizer/plan/planner.c+++ b/src/backend/optimizer/plan/planner.c@@ -1209,6 +1209,7 @@ inheritance_planner(PlannerInfo *root)
Query *parent_parse;
Bitmapset *parent_relids = bms_make_singleton(top_parentRTindex);
PlannerInfo **parent_roots = NULL;
+ List *orig_rtable = list_copy(root->parse->rtable);
List *orig_append_rel_list = list_copy(root->append_rel_list);
List *source_appinfos = NIL;
List *source_child_rowmarks = NIL;
@@ -1258,10 +1259,12 @@ inheritance_planner(PlannerInfo *root)
* management of the rowMarks list.
*
* To begin with, generate a bitmapset of the relids of the subquery RTEs.
+ * We use orig_rtable, not parse->rtable, because we wouldn't need to+ * consider any newly added RTEs as they all must be RTE_RELATION entries.
*/
subqueryRTindexes = NULL;
rti = 1;
- foreach(lc, parse->rtable)+ foreach(lc, orig_rtable)
{
RangeTblEntry *rte = lfirst_node(RangeTblEntry, lc);
@@ -1307,6 +1310,7 @@ inheritance_planner(PlannerInfo *root)
RangeTblEntry *child_rte;
RelOptInfo *sub_final_rel;
Path *subpath;
+ List *parent_rtable;
/* append_rel_list contains all append rels; ignore others */
if (!bms_is_member(appinfo->parent_relid, parent_relids))
@@ -1333,11 +1337,30 @@ inheritance_planner(PlannerInfo *root)
* adjust_appendrel_attrs, which copies the Query and changes
* references to the parent RTE to refer to the current child RTE,
* then fool around with subquery RTEs.
+ *+ * In order to avoid range_table_mutator() uselessly spending time on+ * the child target RTEs that were added to query at the beginning of+ * this function, we swap the query's range table with the copy of the+ * range table before they were added (orig_table).
*/
+ parent_rtable = parent_parse->rtable;+ parent_parse->rtable = orig_rtable;
subroot->parse = (Query *)
adjust_appendrel_attrs(parent_root,
(Node *) parent_parse,
1, &appinfo);
+ /*+ * We do however need to add those child target RTEs to the range+ * table so that query_planner can find this child RTE. Other target+ * RTEs will not be accessed during this planning cycle, but we can't+ * just skip them.+ */+ subroot->parse->rtable =+ list_concat(subroot->parse->rtable,+ list_copy_tail(parent_rtable,+ list_length(orig_rtable)));+ /* Put it back for the next child's planning. */+ parent_parse->rtable = parent_rtable;
/*
* If there are securityQuals attached to the parent, move them to the
@@ -1464,17 +1487,16 @@ inheritance_planner(PlannerInfo *root)
/*
* If this isn't the first child Query, generate duplicates of all
* subquery RTEs, and adjust Var numbering to reference the
- * duplicates. To simplify the loop logic, we scan the original rtable- * not the copy just made by adjust_appendrel_attrs; that should be OK- * since subquery RTEs couldn't contain any references to the target- * rel.+ * duplicates. Note that we scan the original rtable before any+ * child target relations were added, which is OK, because no other+ * RTEs would contain references to subquery rels being modified.
*/
if (final_rtable != NIL && subqueryRTindexes != NULL)
{
ListCell *lr;
rti = 1;
- foreach(lr, parent_parse->rtable)+ foreach(lr, orig_rtable)
{
RangeTblEntry *rte = lfirst_node(RangeTblEntry, lr);
--
2.11.0
[text/plain] v34-0006-Perform-pruning-in-expand_partitioned_rtentry.patch (30.0K, ../c8dcf0fd-4759-5e1c-ea67-e2d3f98e81bd@lab.ntt.co.jp/7-v34-0006-Perform-pruning-in-expand_partitioned_rtentry.patch)
download | inline diff:
From 86e66433ac29320c00a6dd020e2a2b6f98c988f1 Mon Sep 17 00:00:00 2001
From: amit <amitlangote09@gmail.com>
Date: Mon, 4 Mar 2019 11:43:38 +0900
Subject: [PATCH v34 6/9] Perform pruning in expand_partitioned_rtentry
This allows to avoid opening/locking partitions that won't be
scanned at all.
expand_partitioned_rtentry recursively processes sub-partitioned
tables, which in turn performs partition pruning for them. To do
so, the translated quals must be added exactly when the child
RelOptInfo is built for the sub-partitioned tables, instead of
delaying that to set_append_rel_size() as is done today. So,
build_simple_rel() itself performs apply_child_basequals() now.
Code for partitioning optimizations that access partition RelOptInfos
from part_rels array of the parent's RelOptInfo must now be made
aware that some entries might be NULL due partition pruning. In the
case of partitionwise join, even a pruned partition on one side of
the join may need to be joined to the non-pruned counterpart on the
other side, if the pruned partition falls on the nullable side of an
outer join. A dummy RelOptInfo containing a dummy path is built in
that case and put into its parent's part_rels array.
---
src/backend/executor/execPartition.c | 17 ++-
src/backend/optimizer/path/allpaths.c | 176 +------------------------------
src/backend/optimizer/path/joinrels.c | 92 ++++++++++++++++-
src/backend/optimizer/plan/planner.c | 8 ++
src/backend/optimizer/util/inherit.c | 21 +++-
src/backend/optimizer/util/relnode.c | 189 +++++++++++++++++++++++++++++++---
src/backend/partitioning/partprune.c | 45 ++++----
7 files changed, 336 insertions(+), 212 deletions(-)
diff --git a/src/backend/executor/execPartition.c b/src/backend/executor/execPartition.cindex 37e96a6013..5b7c386144 100644--- a/src/backend/executor/execPartition.c+++ b/src/backend/executor/execPartition.c@@ -1653,9 +1653,20 @@ ExecCreatePartitionPruneState(PlanState *planstate,
memcpy(pprune->subplan_map, pinfo->subplan_map,
sizeof(int) * pinfo->nparts);
- /* Double-check that list of relations has not changed. */- Assert(memcmp(partdesc->oids, pinfo->relid_map,- pinfo->nparts * sizeof(Oid)) == 0);+#ifdef USE_ASSERT_CHECKING+ /*+ * Double-check that list of unpruned relations has not+ * changed.+ */+ {+ int k;++ /* Pruned partitions are not added to relid_map. */+ for (k = 0; k < pinfo->nparts; k++)+ Assert(partdesc->oids[k] == pinfo->relid_map[k] ||+ pinfo->subplan_map[k] == -1);+ }+#endif
}
else
{
diff --git a/src/backend/optimizer/path/allpaths.c b/src/backend/optimizer/path/allpaths.cindex 3b70ef17fa..31c8f16cae 100644--- a/src/backend/optimizer/path/allpaths.c+++ b/src/backend/optimizer/path/allpaths.c@@ -139,9 +139,6 @@ static void subquery_push_qual(Query *subquery,
static void recurse_push_qual(Node *setOp, Query *topquery,
RangeTblEntry *rte, Index rti, Node *qual);
static void remove_unused_subquery_outputs(Query *subquery, RelOptInfo *rel);
-static bool apply_child_basequals(PlannerInfo *root, RelOptInfo *rel,- RelOptInfo *childrel,- RangeTblEntry *childRTE, AppendRelInfo *appinfo);
/*
@@ -946,8 +943,6 @@ set_append_rel_size(PlannerInfo *root, RelOptInfo *rel,
double *parent_attrsizes;
int nattrs;
ListCell *l;
- Relids live_children = NULL;- bool did_pruning = false;
/* Guard against stack overflow due to overly deep inheritance tree. */
check_stack_depth();
@@ -966,21 +961,6 @@ set_append_rel_size(PlannerInfo *root, RelOptInfo *rel,
rel->partitioned_child_rels = list_make1_int(rti);
/*
- * If the partitioned relation has any baserestrictinfo quals then we- * attempt to use these quals to prune away partitions that cannot- * possibly contain any tuples matching these quals. In this case we'll- * store the relids of all partitions which could possibly contain a- * matching tuple, and skip anything else in the loop below.- */- if (enable_partition_pruning &&- rte->relkind == RELKIND_PARTITIONED_TABLE &&- rel->baserestrictinfo != NIL)- {- live_children = prune_append_rel_partitions(rel);- did_pruning = true;- }-- /*
* If this is a partitioned baserel, set the consider_partitionwise_join
* flag; currently, we only consider partitionwise joins with the baserel
* if its targetlist doesn't contain a whole-row Var.
@@ -1034,30 +1014,11 @@ set_append_rel_size(PlannerInfo *root, RelOptInfo *rel,
childrel = find_base_rel(root, childRTindex);
Assert(childrel->reloptkind == RELOPT_OTHER_MEMBER_REL);
- if (did_pruning && !bms_is_member(appinfo->child_relid, live_children))- {- /* This partition was pruned; skip it. */- set_dummy_rel_pathlist(childrel);+ /* build_simple_rel may have already proven the child to be dummy. */+ if (IS_DUMMY_REL(childrel))
continue;
- }-- /*- * We have to copy the parent's targetlist and quals to the child,- * with appropriate substitution of variables. If any constant false- * or NULL clauses turn up, we can disregard the child right away.- * If not, we can apply constraint exclusion with just the- * baserestrictinfo quals.- */- if (!apply_child_basequals(root, rel, childrel, childRTE, appinfo))- {- /*- * Some restriction clause reduced to constant FALSE or NULL after- * substitution, so this child need not be scanned.- */- set_dummy_rel_pathlist(childrel);- continue;- }+ /* Apply constraint exclusion. */
if (relation_excluded_by_constraints(root, childrel, childRTE))
{
/*
@@ -1069,7 +1030,8 @@ set_append_rel_size(PlannerInfo *root, RelOptInfo *rel,
}
/*
- * CE failed, so finish copying/modifying targetlist and join quals.+ * Constraint exclusion failed, so finish copying/modifying targetlist+ * and join quals.
*
* NB: the resulting childrel->reltarget->exprs may contain arbitrary
* expressions, which otherwise would not occur in a rel's targetlist.
@@ -3562,134 +3524,6 @@ generate_partitionwise_join_paths(PlannerInfo *root, RelOptInfo *rel)
list_free(live_children);
}
-/*- * apply_child_basequals- * Populate childrel's quals based on rel's quals, translating them using- * appinfo.- *- * If any of the resulting clauses evaluate to false or NULL, we return false- * and don't apply any quals. Caller can mark the relation as a dummy rel in- * this case, since it needn't be scanned.- *- * If any resulting clauses evaluate to true, they're unnecessary and we don't- * apply then.- */-static bool-apply_child_basequals(PlannerInfo *root, RelOptInfo *rel,- RelOptInfo *childrel, RangeTblEntry *childRTE,- AppendRelInfo *appinfo)-{- List *childquals;- Index cq_min_security;- ListCell *lc;-- /*- * The child rel's targetlist might contain non-Var expressions, which- * means that substitution into the quals could produce opportunities for- * const-simplification, and perhaps even pseudoconstant quals. Therefore,- * transform each RestrictInfo separately to see if it reduces to a- * constant or pseudoconstant. (We must process them separately to keep- * track of the security level of each qual.)- */- childquals = NIL;- cq_min_security = UINT_MAX;- foreach(lc, rel->baserestrictinfo)- {- RestrictInfo *rinfo = (RestrictInfo *) lfirst(lc);- Node *childqual;- ListCell *lc2;-- Assert(IsA(rinfo, RestrictInfo));- childqual = adjust_appendrel_attrs(root,- (Node *) rinfo->clause,- 1, &appinfo);- childqual = eval_const_expressions(root, childqual);- /* check for flat-out constant */- if (childqual && IsA(childqual, Const))- {- if (((Const *) childqual)->constisnull ||- !DatumGetBool(((Const *) childqual)->constvalue))- {- /* Restriction reduces to constant FALSE or NULL */- return false;- }- /* Restriction reduces to constant TRUE, so drop it */- continue;- }- /* might have gotten an AND clause, if so flatten it */- foreach(lc2, make_ands_implicit((Expr *) childqual))- {- Node *onecq = (Node *) lfirst(lc2);- bool pseudoconstant;-- /* check for pseudoconstant (no Vars or volatile functions) */- pseudoconstant =- !contain_vars_of_level(onecq, 0) &&- !contain_volatile_functions(onecq);- if (pseudoconstant)- {- /* tell createplan.c to check for gating quals */- root->hasPseudoConstantQuals = true;- }- /* reconstitute RestrictInfo with appropriate properties */- childquals = lappend(childquals,- make_restrictinfo((Expr *) onecq,- rinfo->is_pushed_down,- rinfo->outerjoin_delayed,- pseudoconstant,- rinfo->security_level,- NULL, NULL, NULL));- /* track minimum security level among child quals */- cq_min_security = Min(cq_min_security, rinfo->security_level);- }- }-- /*- * In addition to the quals inherited from the parent, we might have- * securityQuals associated with this particular child node. (Currently- * this can only happen in appendrels originating from UNION ALL;- * inheritance child tables don't have their own securityQuals, see- * expand_inherited_rtentry().) Pull any such securityQuals up into the- * baserestrictinfo for the child. This is similar to- * process_security_barrier_quals() for the parent rel, except that we- * can't make any general deductions from such quals, since they don't- * hold for the whole appendrel.- */- if (childRTE->securityQuals)- {- Index security_level = 0;-- foreach(lc, childRTE->securityQuals)- {- List *qualset = (List *) lfirst(lc);- ListCell *lc2;-- foreach(lc2, qualset)- {- Expr *qual = (Expr *) lfirst(lc2);-- /* not likely that we'd see constants here, so no check */- childquals = lappend(childquals,- make_restrictinfo(qual,- true, false, false,- security_level,- NULL, NULL, NULL));- cq_min_security = Min(cq_min_security, security_level);- }- security_level++;- }- Assert(security_level <= root->qual_security_level);- }-- /*- * OK, we've got all the baserestrictinfo quals for this child.- */- childrel->baserestrictinfo = childquals;- childrel->baserestrict_min_security = cq_min_security;-- return true;-}-
/*****************************************************************************
* DEBUG SUPPORT
*****************************************************************************/
diff --git a/src/backend/optimizer/path/joinrels.c b/src/backend/optimizer/path/joinrels.cindex 9604a54b77..83c24a45b8 100644--- a/src/backend/optimizer/path/joinrels.c+++ b/src/backend/optimizer/path/joinrels.c@@ -14,6 +14,7 @@
*/
#include "postgres.h"
+#include "access/table.h"
#include "miscadmin.h"
#include "nodes/nodeFuncs.h"
#include "optimizer/appendinfo.h"
@@ -21,6 +22,8 @@
#include "optimizer/joininfo.h"
#include "optimizer/pathnode.h"
#include "optimizer/paths.h"
+#include "optimizer/tlist.h"+#include "parser/parsetree.h"
#include "partitioning/partbounds.h"
#include "utils/lsyscache.h"
#include "utils/memutils.h"
@@ -51,6 +54,9 @@ static SpecialJoinInfo *build_child_join_sjinfo(PlannerInfo *root,
Relids left_relids, Relids right_relids);
static int match_expr_to_partition_keys(Expr *expr, RelOptInfo *rel,
bool strict_op);
+static RelOptInfo *build_dummy_partition_rel(PlannerInfo *root,+ RelOptInfo *parent, Relation parentrel,+ int partidx);
/*
@@ -1346,6 +1352,8 @@ try_partitionwise_join(PlannerInfo *root, RelOptInfo *rel1, RelOptInfo *rel2,
RelOptInfo *joinrel, SpecialJoinInfo *parent_sjinfo,
List *parent_restrictlist)
{
+ Relation baserel1 = NULL,+ baserel2 = NULL;
bool rel1_is_simple = IS_SIMPLE_REL(rel1);
bool rel2_is_simple = IS_SIMPLE_REL(rel2);
int nparts;
@@ -1396,6 +1404,18 @@ try_partitionwise_join(PlannerInfo *root, RelOptInfo *rel1, RelOptInfo *rel2,
nparts = joinrel->nparts;
+ if (rel1_is_simple)+ {+ RangeTblEntry *rte = planner_rt_fetch(rel1->relid, root);++ baserel1 = table_open(rte->relid, NoLock);+ }+ if (rel2_is_simple)+ {+ RangeTblEntry *rte = planner_rt_fetch(rel2->relid, root);++ baserel2 = table_open(rte->relid, NoLock);+ }
/*
* Create child-join relations for this partitioned join, if those don't
* exist. Add paths to child-joins for a pair of child relations
@@ -1412,6 +1432,13 @@ try_partitionwise_join(PlannerInfo *root, RelOptInfo *rel1, RelOptInfo *rel2,
AppendRelInfo **appinfos;
int nappinfos;
+ if (rel1_is_simple && child_rel1 == NULL)+ child_rel1 = build_dummy_partition_rel(root, rel1, baserel1,+ cnt_parts);+ if (rel2_is_simple && child_rel2 == NULL)+ child_rel2 = build_dummy_partition_rel(root, rel2, baserel2,+ cnt_parts);+
/*
* If a child table has consider_partitionwise_join=false, it means
* that it's a dummy relation for which we skipped setting up tlist
@@ -1472,6 +1499,11 @@ try_partitionwise_join(PlannerInfo *root, RelOptInfo *rel1, RelOptInfo *rel2,
child_joinrel, child_sjinfo,
child_restrictlist);
}
++ if (baserel1)+ table_close(baserel1, NoLock);+ if (baserel2)+ table_close(baserel2, NoLock);
}
/*
@@ -1490,8 +1522,14 @@ update_child_rel_info(PlannerInfo *root,
(Node *) rel->reltarget->exprs,
1, &appinfo);
- /* Make child entries in the EquivalenceClass as well */- if (rel->has_eclass_joins || has_useful_pathkeys(root, rel))+ /*+ * Make child entries in the EquivalenceClass as well. If the childrel+ * appears to be a dummy one (one built by build_dummy_partition_rel()),+ * no need to make any new entries, because anything that would need those+ * can instead use the parent's (rel).+ */+ if (childrel->relid != rel->relid &&+ (rel->has_eclass_joins || has_useful_pathkeys(root, rel)))
add_child_rel_equivalences(root, appinfo, rel, childrel);
childrel->has_eclass_joins = rel->has_eclass_joins;
}
@@ -1702,3 +1740,53 @@ match_expr_to_partition_keys(Expr *expr, RelOptInfo *rel, bool strict_op)
return -1;
}
++/*+ * build_dummy_partition_rel+ * Build a RelOptInfo and AppendRelInfo for a pruned partition+ *+ * This does not result in opening the relation or a range table entry being+ * created. Also, the RelOptInfo thus created is not stored anywhere else+ * beside the parent's part_rels array.+ *+ * The only reason this exists is because partition-wise join, in some cases,+ * needs a RelOptInfo to represent an empty relation that's on the nullable+ * side of an outer join, so that a Path representing the outer join can be+ * created.+ */+static RelOptInfo *+build_dummy_partition_rel(PlannerInfo *root, RelOptInfo *parent,+ Relation parentrel, int partidx)+{+ RelOptInfo *rel;++ Assert(parent->part_rels[partidx] == NULL);++ /* Create minimally valid-looking RelOptInfo with parent's relid. */+ rel = makeNode(RelOptInfo);+ rel->reloptkind = RELOPT_OTHER_MEMBER_REL;+ rel->relid = parent->relid;+ rel->relids = bms_copy(parent->relids);+ if (parent->top_parent_relids)+ rel->top_parent_relids = parent->top_parent_relids;+ else+ rel->top_parent_relids = bms_copy(parent->relids);+ rel->reltarget = copy_pathtarget(parent->reltarget);+ parent->part_rels[partidx] = rel;+ mark_dummy_rel(rel);++ /*+ * Now we'll need a (no-op) AppendRelInfo for parent, because we're+ * setting the dummy partition's relid to be same as the parent's.+ */+ if (root->append_rel_array[parent->relid] == NULL)+ {+ AppendRelInfo *appinfo = make_append_rel_info(parentrel, parentrel,+ parent->relid,+ parent->relid);++ root->append_rel_array[parent->relid] = appinfo;+ }++ return rel;+}diff --git a/src/backend/optimizer/plan/planner.c b/src/backend/optimizer/plan/planner.cindex f50c4321f3..6a0246b47f 100644--- a/src/backend/optimizer/plan/planner.c+++ b/src/backend/optimizer/plan/planner.c@@ -7107,6 +7107,10 @@ apply_scanjoin_target_to_paths(PlannerInfo *root,
List *child_scanjoin_targets = NIL;
ListCell *lc;
+ /* Skip processing pruned partitions. */+ if (child_rel == NULL)+ continue;+
/* Translate scan/join targets for this child. */
appinfos = find_appinfos_by_relids(root, child_rel->relids,
&nappinfos);
@@ -7207,6 +7211,10 @@ create_partitionwise_grouping_paths(PlannerInfo *root,
RelOptInfo *child_grouped_rel;
RelOptInfo *child_partially_grouped_rel;
+ /* Skip processing pruned partitions. */+ if (child_input_rel == NULL)+ continue;+
/* Input child rel must have a path */
Assert(child_input_rel->pathlist != NIL);
diff --git a/src/backend/optimizer/util/inherit.c b/src/backend/optimizer/util/inherit.cindex 24111c46e9..5d7005de96 100644--- a/src/backend/optimizer/util/inherit.c+++ b/src/backend/optimizer/util/inherit.c@@ -26,6 +26,7 @@
#include "optimizer/planner.h"
#include "optimizer/prep.h"
#include "partitioning/partdesc.h"
+#include "partitioning/partprune.h"
#include "utils/rel.h"
@@ -233,6 +234,7 @@ expand_partitioned_rtentry(PlannerInfo *root, RangeTblEntry *parentrte,
PlanRowMark *top_parentrc, LOCKMODE lockmode,
List **appinfos)
{
+ Bitmapset *live_parts = 0;
int i;
RangeTblEntry *childrte;
Index childRTindex;
@@ -273,22 +275,34 @@ expand_partitioned_rtentry(PlannerInfo *root, RangeTblEntry *parentrte,
rel = find_base_rel(root, parentRTindex);
/*
+ * Perform partition pruning using restriction clauses assigned to+ * parent relation. live_parts will contain PartitionDesc indexes+ * of partitions that survive pruning. Below, we will initialize+ * child objects for the surviving partitions.+ */+ if (partdesc->nparts > 0)+ live_parts = prune_append_rel_partitions(rel);++ /*
* Expand simple_rel_array and friends to hold child objects.
*
* We'll need nparts + 1 new slots, because we also consider parent
* as a child relation. FIXME: no longer add the parent as child
*/
- expand_planner_arrays(root, partdesc->nparts + 1);+ expand_planner_arrays(root, bms_num_members(live_parts) + 1);
/*
* We also store partition RelOptInfo pointers in the parent
- * relation.+ * relation. Since we're palloc0'ing, slots corresponding to+ * pruned partitions will contain NULL.
*/
Assert(rel->part_rels == NULL);
if (partdesc->nparts > 0)
rel->part_rels = (RelOptInfo **) palloc0(rel->nparts *
sizeof(RelOptInfo *));
}
+ else+ live_parts = bms_add_range(NULL, 0, partdesc->nparts - 1);
/* First expand the partitioned table itself. */
expand_single_inheritance_child(root, parentrte, parentRTindex, parentrel,
@@ -305,7 +319,8 @@ expand_partitioned_rtentry(PlannerInfo *root, RangeTblEntry *parentrte,
return;
}
- for (i = 0; i < partdesc->nparts; i++)+ i = -1;+ while ((i = bms_next_member(live_parts, i)) >= 0)
{
Oid childOID = partdesc->oids[i];
Relation childrel;
diff --git a/src/backend/optimizer/util/relnode.c b/src/backend/optimizer/util/relnode.cindex 0bf024b535..98ec918ebc 100644--- a/src/backend/optimizer/util/relnode.c+++ b/src/backend/optimizer/util/relnode.c@@ -70,6 +70,9 @@ static void build_child_join_reltarget(PlannerInfo *root,
RelOptInfo *childrel,
int nappinfos,
AppendRelInfo **appinfos);
+static bool apply_child_basequals(PlannerInfo *root, RelOptInfo *rel,+ RelOptInfo *childrel,+ RangeTblEntry *childRTE, AppendRelInfo *appinfo);
/*
@@ -324,10 +327,162 @@ build_simple_rel(PlannerInfo *root, int relid, RelOptInfo *parent)
root->qual_security_level = Max(root->qual_security_level,
list_length(rte->securityQuals));
+ /*+ * Copy the parent's quals to the child, with appropriate substitution of+ * variables. If any constant false or NULL clauses turn up, we can+ * disregard the child as dummy right away.+ *+ * We must copy the quals now, because if the child rel happens to be a+ * partitioned table, expand_partitioned_rtentry (our caller) can+ * immediate perform partition prunning using the translated quals.+ */+ if (parent)+ {+ AppendRelInfo *appinfo = root->append_rel_array[relid];++ Assert(appinfo != NULL);+ if (!apply_child_basequals(root, parent, rel, rte, appinfo))+ {+ /*+ * Some restriction clause reduced to constant FALSE or NULL after+ * substitution, so this child need not be scanned.+ */+ mark_dummy_rel(rel);+ }+ }+
return rel;
}
/*
+ * apply_child_basequals+ * Populate childrel's quals based on rel's quals, translating them using+ * appinfo.+ *+ * If any of the resulting clauses evaluate to false or NULL, we return false+ * and don't apply any quals. Caller can mark the relation as a dummy rel in+ * this case, since it doesn't need to be scanned.+ *+ * If any resulting clauses evaluate to true, they're unnecessary and we don't+ * apply then.+ */+static bool+apply_child_basequals(PlannerInfo *root, RelOptInfo *rel,+ RelOptInfo *childrel, RangeTblEntry *childRTE,+ AppendRelInfo *appinfo)+{+ List *childquals;+ Index cq_min_security;+ ListCell *lc;++ /*+ * The child rel's targetlist might contain non-Var expressions, which+ * means that substitution into the quals could produce opportunities for+ * const-simplification, and perhaps even pseudoconstant quals. Therefore,+ * transform each RestrictInfo separately to see if it reduces to a+ * constant or pseudoconstant. (We must process them separately to keep+ * track of the security level of each qual.)+ */+ childquals = NIL;+ cq_min_security = UINT_MAX;+ foreach(lc, rel->baserestrictinfo)+ {+ RestrictInfo *rinfo = (RestrictInfo *) lfirst(lc);+ Node *childqual;+ ListCell *lc2;++ Assert(IsA(rinfo, RestrictInfo));+ childqual = adjust_appendrel_attrs(root,+ (Node *) rinfo->clause,+ 1, &appinfo);+ childqual = eval_const_expressions(root, childqual);+ /* check for flat-out constant */+ if (childqual && IsA(childqual, Const))+ {+ if (((Const *) childqual)->constisnull ||+ !DatumGetBool(((Const *) childqual)->constvalue))+ {+ /* Restriction reduces to constant FALSE or NULL */+ return false;+ }+ /* Restriction reduces to constant TRUE, so drop it */+ continue;+ }+ /* might have gotten an AND clause, if so flatten it */+ foreach(lc2, make_ands_implicit((Expr *) childqual))+ {+ Node *onecq = (Node *) lfirst(lc2);+ bool pseudoconstant;++ /* check for pseudoconstant (no Vars or volatile functions) */+ pseudoconstant =+ !contain_vars_of_level(onecq, 0) &&+ !contain_volatile_functions(onecq);+ if (pseudoconstant)+ {+ /* tell createplan.c to check for gating quals */+ root->hasPseudoConstantQuals = true;+ }+ /* reconstitute RestrictInfo with appropriate properties */+ childquals = lappend(childquals,+ make_restrictinfo((Expr *) onecq,+ rinfo->is_pushed_down,+ rinfo->outerjoin_delayed,+ pseudoconstant,+ rinfo->security_level,+ NULL, NULL, NULL));+ /* track minimum security level among child quals */+ cq_min_security = Min(cq_min_security, rinfo->security_level);+ }+ }++ /*+ * In addition to the quals inherited from the parent, we might have+ * securityQuals associated with this particular child node. (Currently+ * this can only happen in appendrels originating from UNION ALL;+ * inheritance child tables don't have their own securityQuals, see+ * expand_inherited_rtentry().) Pull any such securityQuals up into the+ * baserestrictinfo for the child. This is similar to+ * process_security_barrier_quals() for the parent rel, except that we+ * can't make any general deductions from such quals, since they don't+ * hold for the whole appendrel.+ */+ if (childRTE->securityQuals)+ {+ Index security_level = 0;++ foreach(lc, childRTE->securityQuals)+ {+ List *qualset = (List *) lfirst(lc);+ ListCell *lc2;++ foreach(lc2, qualset)+ {+ Expr *qual = (Expr *) lfirst(lc2);++ /* not likely that we'd see constants here, so no check */+ childquals = lappend(childquals,+ make_restrictinfo(qual,+ true, false, false,+ security_level,+ NULL, NULL, NULL));+ cq_min_security = Min(cq_min_security, security_level);+ }+ security_level++;+ }+ Assert(security_level <= root->qual_security_level);+ }++ /*+ * OK, we've got all the baserestrictinfo quals for this child.+ */+ childrel->baserestrictinfo = childquals;+ childrel->baserestrict_min_security = cq_min_security;++ return true;+}++/*
* add_appendrel_other_rels
* This adds the "other rel" RelOptInfos a given "appendrel" baserel
*
@@ -339,6 +494,8 @@ add_appendrel_other_rels(PlannerInfo *root, RangeTblEntry *rte, Index rti)
{
ListCell *l;
RelOptInfo *rel;
+ Relation relation = NULL;+ PartitionDesc partdesc = NULL;
int i;
/*
@@ -361,16 +518,20 @@ add_appendrel_other_rels(PlannerInfo *root, RangeTblEntry *rte, Index rti)
/*
* For partitioned tables, we need to store the child RelOptInfos in the
- * rel->part_rels array too.+ * rel->part_rels array too. Some elements of this array might remain set+ * to NULL if the corresponding partitions were pruned and hence not in+ * append_rel_list.
*/
if (rel->part_scheme)
{
Assert(rel->nparts > 0);
rel->part_rels = (RelOptInfo **)
palloc0(sizeof(RelOptInfo *) * rel->nparts);
+ relation = table_open(rte->relid, NoLock);+ partdesc = PartitionDirectoryLookup(root->glob->partition_directory,+ relation);
}
- i = 0;
foreach(l, root->append_rel_list)
{
AppendRelInfo *appinfo = lfirst(l);
@@ -390,26 +551,30 @@ add_appendrel_other_rels(PlannerInfo *root, RangeTblEntry *rte, Index rti)
* For partitioned parents, we also need to add childrel to its
* part_rels array. The order in which child tables appear in
* append_rel_list is same as the order in which they appear in the
- * parent's PartitionDesc, so assigning partitions like this works.+ * parent's PartitionDesc. But considering that some partitions may+ * have been pruned, we need to look up the correct position to+ * assign this childrel by scanning the PartitionDesc.
*/
if (rel->part_scheme != NULL)
{
- Assert(i < rel->nparts);- rel->part_rels[i] = childrel;+ Assert(partdesc != NULL);+ for (i = 0; i < partdesc->nparts; i++)+ {+ if (childrte->relid == partdesc->oids[i])+ {+ rel->part_rels[i] = childrel;+ break;+ }+ }
}
- i++;-
/* Child may itself be an inherited relation. */
if (childrte->inh)
add_appendrel_other_rels(root, childrte, childRTindex);
}
- /*- * For a partitioned table with non-zero number of partitions, we must- * have assigned all elements of its part_rels array.- */- Assert(rel->nparts == 0 || i == rel->nparts);+ if (relation)+ table_close(relation, NoLock);
}
/*
diff --git a/src/backend/partitioning/partprune.c b/src/backend/partitioning/partprune.cindex b5c0889935..9ebded67a9 100644--- a/src/backend/partitioning/partprune.c+++ b/src/backend/partitioning/partprune.c@@ -45,6 +45,7 @@
#include "nodes/makefuncs.h"
#include "nodes/nodeFuncs.h"
#include "optimizer/appendinfo.h"
+#include "optimizer/cost.h"
#include "optimizer/optimizer.h"
#include "optimizer/pathnode.h"
#include "parser/parsetree.h"
@@ -435,18 +436,24 @@ make_partitionedrel_pruneinfo(PlannerInfo *root, RelOptInfo *parentrel,
* is, not pruned already).
*/
subplan_map = (int *) palloc(nparts * sizeof(int));
+ memset(subplan_map, -1, nparts * sizeof(int));
subpart_map = (int *) palloc(nparts * sizeof(int));
- relid_map = (Oid *) palloc(nparts * sizeof(int));+ memset(subpart_map, -1, nparts * sizeof(int));+ relid_map = (Oid *) palloc0(nparts * sizeof(int));
present_parts = NULL;
for (i = 0; i < nparts; i++)
{
RelOptInfo *partrel = subpart->part_rels[i];
- int subplanidx = relid_subplan_map[partrel->relid] - 1;- int subpartidx = relid_subpart_map[partrel->relid] - 1;+ int subplanidx;+ int subpartidx;- subplan_map[i] = subplanidx;- subpart_map[i] = subpartidx;+ /* Skip processing pruned partitions. */+ if (partrel == NULL)+ continue;++ subplan_map[i] = subplanidx = relid_subplan_map[partrel->relid] - 1;+ subpart_map[i] = subpartidx = relid_subpart_map[partrel->relid] - 1;
relid_map[i] = planner_rt_fetch(partrel->relid, root)->relid;
if (subplanidx >= 0)
{
@@ -542,23 +549,20 @@ gen_partprune_steps(RelOptInfo *rel, List *clauses, bool *contradictory)
/*
* prune_append_rel_partitions
- * Returns RT indexes of the minimum set of child partitions which must- * be scanned to satisfy rel's baserestrictinfo quals.+ * Returns indexes into rel->part_rels of the minimum set of child+ * partitions which must be scanned to satisfy rel's baserestrictinfo+ * quals.
*
* Callers must ensure that 'rel' is a partitioned table.
*/
-Relids+Bitmapset *
prune_append_rel_partitions(RelOptInfo *rel)
{
- Relids result;
List *clauses = rel->baserestrictinfo;
List *pruning_steps;
bool contradictory;
PartitionPruneContext context;
- Bitmapset *partindexes;- int i;- Assert(clauses != NIL);
Assert(rel->part_scheme != NULL);
/* If there are no partitions, return the empty set */
@@ -566,6 +570,13 @@ prune_append_rel_partitions(RelOptInfo *rel)
return NULL;
/*
+ * If pruning is disabled or if there are no clauses to prune with,+ * return all partitions.+ */+ if (!enable_partition_pruning || clauses == NIL)+ return bms_add_range(NULL, 0, rel->nparts - 1);++ /*
* Process clauses. If the clauses are found to be contradictory, we can
* return the empty set.
*/
@@ -592,15 +603,7 @@ prune_append_rel_partitions(RelOptInfo *rel)
context.evalexecparams = false;
/* Actual pruning happens here. */
- partindexes = get_matching_partitions(&context, pruning_steps);-- /* Add selected partitions' RT indexes to result. */- i = -1;- result = NULL;- while ((i = bms_next_member(partindexes, i)) >= 0)- result = bms_add_member(result, rel->part_rels[i]->relid);-- return result;+ return get_matching_partitions(&context, pruning_steps);
}
/*
--
2.11.0
[text/plain] v34-0007-Teach-planner-to-only-process-unpruned-partition.patch (7.6K, ../c8dcf0fd-4759-5e1c-ea67-e2d3f98e81bd@lab.ntt.co.jp/8-v34-0007-Teach-planner-to-only-process-unpruned-partition.patch)
download | inline diff:
From 2c6b94f53d166ef2f9c7d2514afe4efb8574a951 Mon Sep 17 00:00:00 2001
From: amit <amitlangote09@gmail.com>
Date: Mon, 4 Mar 2019 11:57:04 +0900
Subject: [PATCH v34 7/9] Teach planner to only process unpruned partitions
This adds a bitmapset field live_parts to RelOptInfo and stores
the partition indexes of non-dummy partitions in it, meaning it
contains indexes of only those partitions that have a non-NULL
RelOptInfo present in its parent's RelOptInfo's part_rels array.
This speeds up processing partitioned table's partitions compared
to going through the whole part_rels array in a number of places.
Partitionwise join still has to visit each member of part_rels,
because pruned children may fall on the nullable side of an outer
join and an empty Result path must be present for building the
outer join paths.
---
src/backend/optimizer/path/joinrels.c | 3 +++
src/backend/optimizer/plan/planner.c | 18 +++++++++---------
src/backend/optimizer/util/inherit.c | 6 ++++++
src/backend/optimizer/util/relnode.c | 4 ++++
src/backend/partitioning/partprune.c | 19 ++++++-------------
src/include/nodes/pathnodes.h | 4 ++++
6 files changed, 32 insertions(+), 22 deletions(-)
diff --git a/src/backend/optimizer/path/joinrels.c b/src/backend/optimizer/path/joinrels.cindex 83c24a45b8..38fda5832b 100644--- a/src/backend/optimizer/path/joinrels.c+++ b/src/backend/optimizer/path/joinrels.c@@ -1498,6 +1498,9 @@ try_partitionwise_join(PlannerInfo *root, RelOptInfo *rel1, RelOptInfo *rel2,
populate_joinrel_with_paths(root, child_rel1, child_rel2,
child_joinrel, child_sjinfo,
child_restrictlist);
+ if (!IS_DUMMY_REL(child_joinrel))+ joinrel->live_parts = bms_add_member(joinrel->live_parts,+ cnt_parts);
}
if (baserel1)
diff --git a/src/backend/optimizer/plan/planner.c b/src/backend/optimizer/plan/planner.cindex 6a0246b47f..fa1b6de9d2 100644--- a/src/backend/optimizer/plan/planner.c+++ b/src/backend/optimizer/plan/planner.c@@ -7099,7 +7099,9 @@ apply_scanjoin_target_to_paths(PlannerInfo *root,
int partition_idx;
/* Adjust each partition. */
- for (partition_idx = 0; partition_idx < rel->nparts; partition_idx++)+ partition_idx = -1;+ while ((partition_idx = bms_next_member(rel->live_parts,+ partition_idx)) >= 0)
{
RelOptInfo *child_rel = rel->part_rels[partition_idx];
AppendRelInfo **appinfos;
@@ -7107,9 +7109,7 @@ apply_scanjoin_target_to_paths(PlannerInfo *root,
List *child_scanjoin_targets = NIL;
ListCell *lc;
- /* Skip processing pruned partitions. */- if (child_rel == NULL)- continue;+ Assert(child_rel != NULL);
/* Translate scan/join targets for this child. */
appinfos = find_appinfos_by_relids(root, child_rel->relids,
@@ -7189,7 +7189,6 @@ create_partitionwise_grouping_paths(PlannerInfo *root,
PartitionwiseAggregateType patype,
GroupPathExtraData *extra)
{
- int nparts = input_rel->nparts;
int cnt_parts;
List *grouped_live_children = NIL;
List *partially_grouped_live_children = NIL;
@@ -7201,7 +7200,9 @@ create_partitionwise_grouping_paths(PlannerInfo *root,
partially_grouped_rel != NULL);
/* Add paths for partitionwise aggregation/grouping. */
- for (cnt_parts = 0; cnt_parts < nparts; cnt_parts++)+ cnt_parts = -1;+ while ((cnt_parts = bms_next_member(input_rel->live_parts,+ cnt_parts)) >= 0)
{
RelOptInfo *child_input_rel = input_rel->part_rels[cnt_parts];
PathTarget *child_target = copy_pathtarget(target);
@@ -7211,9 +7212,8 @@ create_partitionwise_grouping_paths(PlannerInfo *root,
RelOptInfo *child_grouped_rel;
RelOptInfo *child_partially_grouped_rel;
- /* Skip processing pruned partitions. */- if (child_input_rel == NULL)- continue;+ /* A live partition must have a RelOptInfo. */+ Assert(child_input_rel != NULL);
/* Input child rel must have a path */
Assert(child_input_rel->pathlist != NIL);
diff --git a/src/backend/optimizer/util/inherit.c b/src/backend/optimizer/util/inherit.cindex 5d7005de96..228fd3b339 100644--- a/src/backend/optimizer/util/inherit.c+++ b/src/backend/optimizer/util/inherit.c@@ -284,6 +284,12 @@ expand_partitioned_rtentry(PlannerInfo *root, RangeTblEntry *parentrte,
live_parts = prune_append_rel_partitions(rel);
/*
+ * Later steps that loop over part_rels should use these indexes+ * to access unpruned partitions.+ */+ rel->live_parts = live_parts;++ /*
* Expand simple_rel_array and friends to hold child objects.
*
* We'll need nparts + 1 new slots, because we also consider parent
diff --git a/src/backend/optimizer/util/relnode.c b/src/backend/optimizer/util/relnode.cindex 98ec918ebc..6787c33afa 100644--- a/src/backend/optimizer/util/relnode.c+++ b/src/backend/optimizer/util/relnode.c@@ -563,6 +563,7 @@ add_appendrel_other_rels(PlannerInfo *root, RangeTblEntry *rte, Index rti)
if (childrte->relid == partdesc->oids[i])
{
rel->part_rels[i] = childrel;
+ rel->live_parts = bms_add_member(rel->live_parts, i);
break;
}
}
@@ -1989,6 +1990,9 @@ build_joinrel_partition_info(RelOptInfo *joinrel, RelOptInfo *outer_rel,
joinrel->partexprs[cnt] = partexpr;
joinrel->nullable_partexprs[cnt] = nullable_partexpr;
}
++ /* Partitions will be added by try_partitionwise_join. */+ joinrel->live_parts = NULL;
}
/*
diff --git a/src/backend/partitioning/partprune.c b/src/backend/partitioning/partprune.cindex 9ebded67a9..e766555a9f 100644--- a/src/backend/partitioning/partprune.c+++ b/src/backend/partitioning/partprune.c@@ -440,30 +440,23 @@ make_partitionedrel_pruneinfo(PlannerInfo *root, RelOptInfo *parentrel,
subpart_map = (int *) palloc(nparts * sizeof(int));
memset(subpart_map, -1, nparts * sizeof(int));
relid_map = (Oid *) palloc0(nparts * sizeof(int));
- present_parts = NULL;+ present_parts = bms_copy(subpart->live_parts);- for (i = 0; i < nparts; i++)+ i = -1;+ while ((i = bms_next_member(present_parts, i)) >= 0)
{
RelOptInfo *partrel = subpart->part_rels[i];
int subplanidx;
int subpartidx;
- /* Skip processing pruned partitions. */- if (partrel == NULL)- continue;-+ Assert(partrel != NULL);
subplan_map[i] = subplanidx = relid_subplan_map[partrel->relid] - 1;
subpart_map[i] = subpartidx = relid_subpart_map[partrel->relid] - 1;
relid_map[i] = planner_rt_fetch(partrel->relid, root)->relid;
- if (subplanidx >= 0)- {- present_parts = bms_add_member(present_parts, i);- /* Record finding this subplan */+ /* Record finding this subplan */+ if (subplanidx >= 0)
subplansfound = bms_add_member(subplansfound, subplanidx);
- }- else if (subpartidx >= 0)- present_parts = bms_add_member(present_parts, i);
}
pinfo = makeNode(PartitionedRelPruneInfo);
diff --git a/src/include/nodes/pathnodes.h b/src/include/nodes/pathnodes.hindex 31811f9adb..3298bd78ca 100644--- a/src/include/nodes/pathnodes.h+++ b/src/include/nodes/pathnodes.h@@ -714,6 +714,10 @@ typedef struct RelOptInfo
List *partition_qual; /* partition constraint */
struct RelOptInfo **part_rels; /* Array of RelOptInfos of partitions,
* stored in the same order of bounds */
+ Bitmapset *live_parts; /* Indexes into part_rels of the non-NULL+ * RelOptInfos of unpruned partitions; exists+ * to avoid having to iterate over the entire+ * part_rels array to filter NULL entries. */
List **partexprs; /* Non-nullable partition key expressions. */
List **nullable_partexprs; /* Nullable partition key expressions. */
List *partitioned_child_rels; /* List of RT indexes. */
--
2.11.0
[text/plain] v34-0008-Rearrange-the-code-in-make_partitionedrel_prunei.patch (4.7K, ../c8dcf0fd-4759-5e1c-ea67-e2d3f98e81bd@lab.ntt.co.jp/9-v34-0008-Rearrange-the-code-in-make_partitionedrel_prunei.patch)
download | inline diff:
From 7828f62949c4d57c176059dda910ccf1f7deaf96 Mon Sep 17 00:00:00 2001
From: amit <amitlangote09@gmail.com>
Date: Fri, 22 Mar 2019 15:09:45 +0900
Subject: [PATCH v34 8/9] Rearrange the code in make_partitionedrel_pruneinfo
for efficiency
The current code will loop over all partitions to create maps that
will be put into the PartitionedRelPruneInfo of a given partitioned
table, *before* checking if run-time pruning will be needed at all.
This commit rearranges the code such that maps are created *after*
we've determined that run-time will be needed.
---
src/backend/partitioning/partprune.c | 84 ++++++++++++++++++++----------------
1 file changed, 47 insertions(+), 37 deletions(-)
diff --git a/src/backend/partitioning/partprune.c b/src/backend/partitioning/partprune.cindex e766555a9f..6004ae37f3 100644--- a/src/backend/partitioning/partprune.c+++ b/src/backend/partitioning/partprune.c@@ -333,39 +333,24 @@ make_partitionedrel_pruneinfo(PlannerInfo *root, RelOptInfo *parentrel,
*/
relid_subpart_map = palloc0(sizeof(int) * root->simple_rel_array_size);
- /*- * relid_subpart_map maps relid of a non-leaf partition to the index in- * 'partitioned_rels' of that rel (which will also be the index in the- * returned PartitionedRelPruneInfo list of the info for that partition).- */- i = 1;- foreach(lc, partitioned_rels)- {- Index rti = lfirst_int(lc);-- Assert(rti < root->simple_rel_array_size);- /* No duplicates please */- Assert(relid_subpart_map[rti] == 0);-- relid_subpart_map[rti] = i++;- }-
/* We now build a PartitionedRelPruneInfo for each partitioned rel */
+ i = 1;
foreach(lc, partitioned_rels)
{
Index rti = lfirst_int(lc);
RelOptInfo *subpart = find_base_rel(root, rti);
PartitionedRelPruneInfo *pinfo;
- Bitmapset *present_parts;- int nparts = subpart->nparts;
int partnatts = subpart->part_scheme->partnatts;
- int *subplan_map;- int *subpart_map;- Oid *relid_map;
List *partprunequal;
List *pruning_steps;
bool contradictory;
+ /* Map the table's RT index to its position in partitioned_rels. */+ Assert(rti < root->simple_rel_array_size);+ /* No duplicates please */+ Assert(relid_subpart_map[rti] == 0);+ relid_subpart_map[rti] = i++;+
/*
* The first item in the list is the target partitioned relation.
*/
@@ -429,6 +414,45 @@ make_partitionedrel_pruneinfo(PlannerInfo *root, RelOptInfo *parentrel,
return NIL;
}
+ /* Other information will be set in the following loop. */+ pinfo = makeNode(PartitionedRelPruneInfo);+ pinfo->rtindex = rti;+ pinfo->pruning_steps = pruning_steps;++ /*+ * Determine which pruning types should be enabled at this level.+ * This also records paramids relevant to pruning steps in 'pinfo'.+ */+ doruntimeprune |= analyze_partkey_exprs(pinfo, pruning_steps,+ partnatts);++ pinfolist = lappend(pinfolist, pinfo);+ }++ if (!doruntimeprune)+ {+ /* No run-time pruning required. */+ return NIL;+ }++ /*+ * Run-time pruning will be required, so initialize other information.+ * That includes two maps -- one needed to convert partition indexes+ * of leaf partitions to the indexes of their subplans in the subplan+ * list, another needed to convert partition indexes of sub-partitioned+ * partitions to the indexes of their PartitionedRelPruneInfo in the+ * PartitionedRelPruneInfo list.+ */+ foreach(lc, pinfolist)+ {+ PartitionedRelPruneInfo *pinfo = lfirst(lc);+ RelOptInfo *subpart = find_base_rel(root, pinfo->rtindex);+ Bitmapset *present_parts;+ int nparts = subpart->nparts;+ int *subplan_map;+ int *subpart_map;+ Oid *relid_map;+
/*
* Construct the subplan and subpart maps for this partitioning level.
* Here we convert to zero-based indexes, with -1 for empty entries.
@@ -459,30 +483,16 @@ make_partitionedrel_pruneinfo(PlannerInfo *root, RelOptInfo *parentrel,
subplansfound = bms_add_member(subplansfound, subplanidx);
}
- pinfo = makeNode(PartitionedRelPruneInfo);- pinfo->rtindex = rti;- pinfo->pruning_steps = pruning_steps;+ /* Record the maps and other information. */
pinfo->present_parts = present_parts;
pinfo->nparts = nparts;
pinfo->subplan_map = subplan_map;
pinfo->subpart_map = subpart_map;
pinfo->relid_map = relid_map;
-- /* Determine which pruning types should be enabled at this level */- doruntimeprune |= analyze_partkey_exprs(pinfo, pruning_steps,- partnatts);-- pinfolist = lappend(pinfolist, pinfo);
}
pfree(relid_subpart_map);
- if (!doruntimeprune)- {- /* No run-time pruning required. */- return NIL;- }-
*matchedsubplans = subplansfound;
return pinfolist;
--
2.11.0
[text/plain] v34-0009-Don-t-copy-PartitionBoundInfo-in-set_relation_pa.patch (1.8K, ../c8dcf0fd-4759-5e1c-ea67-e2d3f98e81bd@lab.ntt.co.jp/10-v34-0009-Don-t-copy-PartitionBoundInfo-in-set_relation_pa.patch)
download | inline diff:
From 9c9d2f973152277ca334f945cb27b7a065191260 Mon Sep 17 00:00:00 2001
From: amit <amitlangote09@gmail.com>
Date: Mon, 4 Mar 2019 16:03:49 +0900
Subject: [PATCH v34 9/9] Don't copy PartitionBoundInfo in
set_relation_partition_info
As long as we hold a lock on the table, it shouldn't change, so
seems pointless to copy, which can get really expensive as the
number of partitions grows beyond thousands.
---
src/backend/optimizer/util/plancat.c | 10 +++++++---
1 file changed, 7 insertions(+), 3 deletions(-)
diff --git a/src/backend/optimizer/util/plancat.c b/src/backend/optimizer/util/plancat.cindex 2b22dff690..b8f01269be 100644--- a/src/backend/optimizer/util/plancat.c+++ b/src/backend/optimizer/util/plancat.c@@ -2082,13 +2082,11 @@ set_relation_partition_info(PlannerInfo *root, RelOptInfo *rel,
Relation relation)
{
PartitionDesc partdesc;
- PartitionKey partkey;
Assert(relation->rd_rel->relkind == RELKIND_PARTITIONED_TABLE);
partdesc = PartitionDirectoryLookup(root->glob->partition_directory,
relation);
- partkey = RelationGetPartitionKey(relation);
rel->part_scheme = find_partition_scheme(root, relation);
Assert(partdesc != NULL && rel->part_scheme != NULL);
/*
@@ -2098,7 +2096,13 @@ set_relation_partition_info(PlannerInfo *root, RelOptInfo *rel,
* only lazily updated as far as dropped partitions are concerned.
*/
if (partdesc->nparts > 0)
- rel->boundinfo = partition_bounds_copy(partdesc->boundinfo, partkey);+ {+ /*+ * Holding onto the relcache pointer should be OK, as it won't change+ * under us as long as we're holding the lock on the relation.+ */+ rel->boundinfo = partdesc->boundinfo;+ }
rel->nparts = partdesc->nparts;
set_baserel_partition_key_exprs(relation, rel);
rel->partition_qual = RelationGetPartitionQual(relation);
--
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, yoshikazu_i443@live.jp, jesper.pedersen@redhat.com, imai.yoshikazu@jp.fujitsu.com, amitlangote09@gmail.com, tgl@sss.pgh.pa.us, david.rowley@2ndquadrant.com, alvherre@2ndquadrant.com, robertmhaas@gmail.com, pryzby@telsasoft.com
Subject: Re: speeding up planning with partitions
In-Reply-To: <c8dcf0fd-4759-5e1c-ea67-e2d3f98e81bd@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