Skip to content

Commit 428b260

Browse files
tglsfdctglsfdc
committed
Speed up planning when partitions can be pruned at plan time.
Previously, the planner created RangeTblEntry and RelOptInfo structs for every partition of a partitioned table, even though many of them might later be deemed uninteresting thanks to partition pruning logic. This incurred significant overhead when there are many partitions. Arrange to postpone creation of these data structures until after we've processed the query enough to identify restriction quals for the partitioned table, and then apply partition pruning before not after creation of each partition's data structures. In this way we need not open the partition relations at all for partitions that the planner has no real interest in. For queries that can be proven at plan time to access only a small number of partitions, this patch improves the practical maximum number of partitions from under 100 to perhaps a few thousand. Amit Langote, reviewed at various times by Dilip Kumar, Jesper Pedersen, Yoshikazu Imai, and David Rowley Discussion: https://postgr.es/m/9d7c5112-cb99-6a47-d3be-cf1ee6862a1d@lab.ntt.co.jp
1 parent ad3107b commit 428b260

File tree

16 files changed

+887
-621
lines changed

16 files changed

+887
-621
lines changed

‎contrib/postgres_fdw/expected/postgres_fdw.out

Lines changed: 21 additions & 21 deletions
Original file line numberDiff line numberDiff line change
@@ -7141,9 +7141,9 @@ select * from bar where f1 in (select f1 from foo) for update;
71417141
QUERY PLAN
71427142
----------------------------------------------------------------------------------------------
71437143
LockRows
7144-
Output: bar.f1, bar.f2, bar.ctid, bar.*, bar.tableoid, foo.ctid, foo.*, foo.tableoid
7144+
Output: bar.f1, bar.f2, bar.ctid, foo.ctid, bar.*, bar.tableoid, foo.*, foo.tableoid
71457145
-> Hash Join
7146-
Output: bar.f1, bar.f2, bar.ctid, bar.*, bar.tableoid, foo.ctid, foo.*, foo.tableoid
7146+
Output: bar.f1, bar.f2, bar.ctid, foo.ctid, bar.*, bar.tableoid, foo.*, foo.tableoid
71477147
Inner Unique: true
71487148
Hash Cond: (bar.f1 = foo.f1)
71497149
-> Append
@@ -7153,15 +7153,15 @@ select * from bar where f1 in (select f1 from foo) for update;
71537153
Output: bar2.f1, bar2.f2, bar2.ctid, bar2.*, bar2.tableoid
71547154
Remote SQL: SELECT f1, f2, f3, ctid FROM public.loct2 FOR UPDATE
71557155
-> Hash
7156-
Output: foo.ctid, foo.*, foo.tableoid, foo.f1
7156+
Output: foo.ctid, foo.f1, foo.*, foo.tableoid
71577157
-> HashAggregate
7158-
Output: foo.ctid, foo.*, foo.tableoid, foo.f1
7158+
Output: foo.ctid, foo.f1, foo.*, foo.tableoid
71597159
Group Key: foo.f1
71607160
-> Append
71617161
-> Seq Scan on public.foo
7162-
Output: foo.ctid, foo.*, foo.tableoid, foo.f1
7162+
Output: foo.ctid, foo.f1, foo.*, foo.tableoid
71637163
-> Foreign Scan on public.foo2
7164-
Output: foo2.ctid, foo2.*, foo2.tableoid, foo2.f1
7164+
Output: foo2.ctid, foo2.f1, foo2.*, foo2.tableoid
71657165
Remote SQL: SELECT f1, f2, f3, ctid FROM public.loct1
71667166
(23 rows)
71677167

@@ -7179,9 +7179,9 @@ select * from bar where f1 in (select f1 from foo) for share;
71797179
QUERY PLAN
71807180
----------------------------------------------------------------------------------------------
71817181
LockRows
7182-
Output: bar.f1, bar.f2, bar.ctid, bar.*, bar.tableoid, foo.ctid, foo.*, foo.tableoid
7182+
Output: bar.f1, bar.f2, bar.ctid, foo.ctid, bar.*, bar.tableoid, foo.*, foo.tableoid
71837183
-> Hash Join
7184-
Output: bar.f1, bar.f2, bar.ctid, bar.*, bar.tableoid, foo.ctid, foo.*, foo.tableoid
7184+
Output: bar.f1, bar.f2, bar.ctid, foo.ctid, bar.*, bar.tableoid, foo.*, foo.tableoid
71857185
Inner Unique: true
71867186
Hash Cond: (bar.f1 = foo.f1)
71877187
-> Append
@@ -7191,15 +7191,15 @@ select * from bar where f1 in (select f1 from foo) for share;
71917191
Output: bar2.f1, bar2.f2, bar2.ctid, bar2.*, bar2.tableoid
71927192
Remote SQL: SELECT f1, f2, f3, ctid FROM public.loct2 FOR SHARE
71937193
-> Hash
7194-
Output: foo.ctid, foo.*, foo.tableoid, foo.f1
7194+
Output: foo.ctid, foo.f1, foo.*, foo.tableoid
71957195
-> HashAggregate
7196-
Output: foo.ctid, foo.*, foo.tableoid, foo.f1
7196+
Output: foo.ctid, foo.f1, foo.*, foo.tableoid
71977197
Group Key: foo.f1
71987198
-> Append
71997199
-> Seq Scan on public.foo
7200-
Output: foo.ctid, foo.*, foo.tableoid, foo.f1
7200+
Output: foo.ctid, foo.f1, foo.*, foo.tableoid
72017201
-> Foreign Scan on public.foo2
7202-
Output: foo2.ctid, foo2.*, foo2.tableoid, foo2.f1
7202+
Output: foo2.ctid, foo2.f1, foo2.*, foo2.tableoid
72037203
Remote SQL: SELECT f1, f2, f3, ctid FROM public.loct1
72047204
(23 rows)
72057205

@@ -7228,15 +7228,15 @@ update bar set f2 = f2 + 100 where f1 in (select f1 from foo);
72287228
-> Seq Scan on public.bar
72297229
Output: bar.f1, bar.f2, bar.ctid
72307230
-> Hash
7231-
Output: foo.ctid, foo.*, foo.tableoid, foo.f1
7231+
Output: foo.ctid, foo.f1, foo.*, foo.tableoid
72327232
-> HashAggregate
7233-
Output: foo.ctid, foo.*, foo.tableoid, foo.f1
7233+
Output: foo.ctid, foo.f1, foo.*, foo.tableoid
72347234
Group Key: foo.f1
72357235
-> Append
72367236
-> Seq Scan on public.foo
7237-
Output: foo.ctid, foo.*, foo.tableoid, foo.f1
7237+
Output: foo.ctid, foo.f1, foo.*, foo.tableoid
72387238
-> Foreign Scan on public.foo2
7239-
Output: foo2.ctid, foo2.*, foo2.tableoid, foo2.f1
7239+
Output: foo2.ctid, foo2.f1, foo2.*, foo2.tableoid
72407240
Remote SQL: SELECT f1, f2, f3, ctid FROM public.loct1
72417241
-> Hash Join
72427242
Output: bar2.f1, (bar2.f2 + 100), bar2.f3, bar2.ctid, foo.ctid, foo.*, foo.tableoid
@@ -7246,15 +7246,15 @@ update bar set f2 = f2 + 100 where f1 in (select f1 from foo);
72467246
Output: bar2.f1, bar2.f2, bar2.f3, bar2.ctid
72477247
Remote SQL: SELECT f1, f2, f3, ctid FROM public.loct2 FOR UPDATE
72487248
-> Hash
7249-
Output: foo.ctid, foo.*, foo.tableoid, foo.f1
7249+
Output: foo.ctid, foo.f1, foo.*, foo.tableoid
72507250
-> HashAggregate
7251-
Output: foo.ctid, foo.*, foo.tableoid, foo.f1
7251+
Output: foo.ctid, foo.f1, foo.*, foo.tableoid
72527252
Group Key: foo.f1
72537253
-> Append
72547254
-> Seq Scan on public.foo
7255-
Output: foo.ctid, foo.*, foo.tableoid, foo.f1
7255+
Output: foo.ctid, foo.f1, foo.*, foo.tableoid
72567256
-> Foreign Scan on public.foo2
7257-
Output: foo2.ctid, foo2.*, foo2.tableoid, foo2.f1
7257+
Output: foo2.ctid, foo2.f1, foo2.*, foo2.tableoid
72587258
Remote SQL: SELECT f1, f2, f3, ctid FROM public.loct1
72597259
(39 rows)
72607260

@@ -8460,7 +8460,7 @@ SELECT t1.a,t2.b,t2.c FROM fprt1 t1 LEFT JOIN (SELECT * FROM fprt2 WHERE a < 10)
84608460
Foreign Scan
84618461
Output: t1.a, ftprt2_p1.b, ftprt2_p1.c
84628462
Relations: (public.ftprt1_p1 t1) LEFT JOIN (public.ftprt2_p1 fprt2)
8463-
Remote SQL: SELECT r5.a, r7.b, r7.c FROM (public.fprt1_p1 r5 LEFT JOIN public.fprt2_p1 r7 ON (((r5.a = r7.b)) AND ((r5.b = r7.a)) AND ((r7.a < 10)))) WHERE ((r5.a < 10)) ORDER BY r5.a ASC NULLS LAST, r7.b ASC NULLS LAST, r7.c ASC NULLS LAST
8463+
Remote SQL: SELECT r5.a, r6.b, r6.c FROM (public.fprt1_p1 r5 LEFT JOIN public.fprt2_p1 r6 ON (((r5.a = r6.b)) AND ((r5.b = r6.a)) AND ((r6.a < 10)))) WHERE ((r5.a < 10)) ORDER BY r5.a ASC NULLS LAST, r6.b ASC NULLS LAST, r6.c ASC NULLS LAST
84648464
(4 rows)
84658465

84668466
SELECT t1.a,t2.b,t2.c FROM fprt1 t1 LEFT JOIN (SELECT * FROM fprt2 WHERE a < 10) t2 ON (t1.a = t2.b and t1.b = t2.a) WHERE t1.a < 10 ORDER BY 1,2,3;

‎src/backend/executor/execPartition.c

Lines changed: 11 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -1654,9 +1654,17 @@ ExecCreatePartitionPruneState(PlanState *planstate,
16541654
memcpy(pprune->subplan_map, pinfo->subplan_map,
16551655
sizeof(int) * pinfo->nparts);
16561656

1657-
/* Double-check that list of relations has not changed. */
1658-
Assert(memcmp(partdesc->oids, pinfo->relid_map,
1659-
pinfo->nparts * sizeof(Oid)) == 0);
1657+
/*
1658+
* Double-check that the list of unpruned relations has not
1659+
* changed. (Pruned partitions are not in relid_map[].)
1660+
*/
1661+
#ifdef USE_ASSERT_CHECKING
1662+
for (int k = 0; k < pinfo->nparts; k++)
1663+
{
1664+
Assert(partdesc->oids[k] == pinfo->relid_map[k] ||
1665+
pinfo->subplan_map[k] == -1);
1666+
}
1667+
#endif
16601668
}
16611669
else
16621670
{

‎src/backend/optimizer/path/allpaths.c

Lines changed: 11 additions & 169 deletions
Original file line numberDiff line numberDiff line change
@@ -139,9 +139,6 @@ static void subquery_push_qual(Query *subquery,
139139
static void recurse_push_qual(Node *setOp, Query *topquery,
140140
RangeTblEntry *rte, Index rti, Node *qual);
141141
static void remove_unused_subquery_outputs(Query *subquery, RelOptInfo *rel);
142-
static bool apply_child_basequals(PlannerInfo *root, RelOptInfo *rel,
143-
RelOptInfo *childrel,
144-
RangeTblEntry *childRTE, AppendRelInfo *appinfo);
145142

146143

147144
/*
@@ -396,8 +393,9 @@ set_rel_size(PlannerInfo *root, RelOptInfo *rel,
396393
else if (rte->relkind == RELKIND_PARTITIONED_TABLE)
397394
{
398395
/*
399-
* A partitioned table without any partitions is marked as
400-
* a dummy rel.
396+
* We could get here if asked to scan a partitioned table
397+
* with ONLY. In that case we shouldn't scan any of the
398+
* partitions, so mark it as a dummy rel.
401399
*/
402400
set_dummy_rel_pathlist(rel);
403401
}
@@ -946,8 +944,6 @@ set_append_rel_size(PlannerInfo *root, RelOptInfo *rel,
946944
double *parent_attrsizes;
947945
int nattrs;
948946
ListCell *l;
949-
Relids live_children = NULL;
950-
bool did_pruning = false;
951947

952948
/* Guard against stack overflow due to overly deep inheritance tree. */
953949
check_stack_depth();
@@ -965,21 +961,6 @@ set_append_rel_size(PlannerInfo *root, RelOptInfo *rel,
965961
if (rte->relkind == RELKIND_PARTITIONED_TABLE)
966962
rel->partitioned_child_rels = list_make1_int(rti);
967963

968-
/*
969-
* If the partitioned relation has any baserestrictinfo quals then we
970-
* attempt to use these quals to prune away partitions that cannot
971-
* possibly contain any tuples matching these quals. In this case we'll
972-
* store the relids of all partitions which could possibly contain a
973-
* matching tuple, and skip anything else in the loop below.
974-
*/
975-
if (enable_partition_pruning &&
976-
rte->relkind == RELKIND_PARTITIONED_TABLE &&
977-
rel->baserestrictinfo != NIL)
978-
{
979-
live_children = prune_append_rel_partitions(rel);
980-
did_pruning = true;
981-
}
982-
983964
/*
984965
* If this is a partitioned baserel, set the consider_partitionwise_join
985966
* flag; currently, we only consider partitionwise joins with the baserel
@@ -1034,30 +1015,17 @@ set_append_rel_size(PlannerInfo *root, RelOptInfo *rel,
10341015
childrel = find_base_rel(root, childRTindex);
10351016
Assert(childrel->reloptkind == RELOPT_OTHER_MEMBER_REL);
10361017

1037-
if (did_pruning && !bms_is_member(appinfo->child_relid, live_children))
1038-
{
1039-
/* This partition was pruned; skip it. */
1040-
set_dummy_rel_pathlist(childrel);
1018+
/* We may have already proven the child to be dummy. */
1019+
if (IS_DUMMY_REL(childrel))
10411020
continue;
1042-
}
10431021

10441022
/*
10451023
* We have to copy the parent's targetlist and quals to the child,
1046-
* with appropriate substitution of variables. If any constant false
1047-
* or NULL clauses turn up, we can disregard the child right away. If
1048-
* not, we can apply constraint exclusion with just the
1049-
* baserestrictinfo quals.
1024+
* with appropriate substitution of variables. However, the
1025+
* baserestrictinfo quals were already copied/substituted when the
1026+
* child RelOptInfo was built. So we don't need any additional setup
1027+
* before applying constraint exclusion.
10501028
*/
1051-
if (!apply_child_basequals(root, rel, childrel, childRTE, appinfo))
1052-
{
1053-
/*
1054-
* Some restriction clause reduced to constant FALSE or NULL after
1055-
* substitution, so this child need not be scanned.
1056-
*/
1057-
set_dummy_rel_pathlist(childrel);
1058-
continue;
1059-
}
1060-
10611029
if (relation_excluded_by_constraints(root, childrel, childRTE))
10621030
{
10631031
/*
@@ -1069,7 +1037,8 @@ set_append_rel_size(PlannerInfo *root, RelOptInfo *rel,
10691037
}
10701038

10711039
/*
1072-
* CE failed, so finish copying/modifying targetlist and join quals.
1040+
* Constraint exclusion failed, so copy the parent's join quals and
1041+
* targetlist to the child, with appropriate variable substitutions.
10731042
*
10741043
* NB: the resulting childrel->reltarget->exprs may contain arbitrary
10751044
* expressions, which otherwise would not occur in a rel's targetlist.
@@ -3596,133 +3565,6 @@ generate_partitionwise_join_paths(PlannerInfo *root, RelOptInfo *rel)
35963565
list_free(live_children);
35973566
}
35983567

3599-
/*
3600-
* apply_child_basequals
3601-
* Populate childrel's quals based on rel's quals, translating them using
3602-
* appinfo.
3603-
*
3604-
* If any of the resulting clauses evaluate to false or NULL, we return false
3605-
* and don't apply any quals. Caller can mark the relation as a dummy rel in
3606-
* this case, since it needn't be scanned.
3607-
*
3608-
* If any resulting clauses evaluate to true, they're unnecessary and we don't
3609-
* apply then.
3610-
*/
3611-
static bool
3612-
apply_child_basequals(PlannerInfo *root, RelOptInfo *rel,
3613-
RelOptInfo *childrel, RangeTblEntry *childRTE,
3614-
AppendRelInfo *appinfo)
3615-
{
3616-
List *childquals;
3617-
Index cq_min_security;
3618-
ListCell *lc;
3619-
3620-
/*
3621-
* The child rel's targetlist might contain non-Var expressions, which
3622-
* means that substitution into the quals could produce opportunities for
3623-
* const-simplification, and perhaps even pseudoconstant quals. Therefore,
3624-
* transform each RestrictInfo separately to see if it reduces to a
3625-
* constant or pseudoconstant. (We must process them separately to keep
3626-
* track of the security level of each qual.)
3627-
*/
3628-
childquals = NIL;
3629-
cq_min_security = UINT_MAX;
3630-
foreach(lc, rel->baserestrictinfo)
3631-
{
3632-
RestrictInfo *rinfo = (RestrictInfo *) lfirst(lc);
3633-
Node *childqual;
3634-
ListCell *lc2;
3635-
3636-
Assert(IsA(rinfo, RestrictInfo));
3637-
childqual = adjust_appendrel_attrs(root,
3638-
(Node *) rinfo->clause,
3639-
1, &appinfo);
3640-
childqual = eval_const_expressions(root, childqual);
3641-
/* check for flat-out constant */
3642-
if (childqual && IsA(childqual, Const))
3643-
{
3644-
if (((Const *) childqual)->constisnull ||
3645-
!DatumGetBool(((Const *) childqual)->constvalue))
3646-
{
3647-
/* Restriction reduces to constant FALSE or NULL */
3648-
return false;
3649-
}
3650-
/* Restriction reduces to constant TRUE, so drop it */
3651-
continue;
3652-
}
3653-
/* might have gotten an AND clause, if so flatten it */
3654-
foreach(lc2, make_ands_implicit((Expr *) childqual))
3655-
{
3656-
Node *onecq = (Node *) lfirst(lc2);
3657-
bool pseudoconstant;
3658-
3659-
/* check for pseudoconstant (no Vars or volatile functions) */
3660-
pseudoconstant =
3661-
!contain_vars_of_level(onecq, 0) &&
3662-
!contain_volatile_functions(onecq);
3663-
if (pseudoconstant)
3664-
{
3665-
/* tell createplan.c to check for gating quals */
3666-
root->hasPseudoConstantQuals = true;
3667-
}
3668-
/* reconstitute RestrictInfo with appropriate properties */
3669-
childquals = lappend(childquals,
3670-
make_restrictinfo((Expr *) onecq,
3671-
rinfo->is_pushed_down,
3672-
rinfo->outerjoin_delayed,
3673-
pseudoconstant,
3674-
rinfo->security_level,
3675-
NULL, NULL, NULL));
3676-
/* track minimum security level among child quals */
3677-
cq_min_security = Min(cq_min_security, rinfo->security_level);
3678-
}
3679-
}
3680-
3681-
/*
3682-
* In addition to the quals inherited from the parent, we might have
3683-
* securityQuals associated with this particular child node. (Currently
3684-
* this can only happen in appendrels originating from UNION ALL;
3685-
* inheritance child tables don't have their own securityQuals, see
3686-
* expand_inherited_rtentry().) Pull any such securityQuals up into the
3687-
* baserestrictinfo for the child. This is similar to
3688-
* process_security_barrier_quals() for the parent rel, except that we
3689-
* can't make any general deductions from such quals, since they don't
3690-
* hold for the whole appendrel.
3691-
*/
3692-
if (childRTE->securityQuals)
3693-
{
3694-
Index security_level = 0;
3695-
3696-
foreach(lc, childRTE->securityQuals)
3697-
{
3698-
List *qualset = (List *) lfirst(lc);
3699-
ListCell *lc2;
3700-
3701-
foreach(lc2, qualset)
3702-
{
3703-
Expr *qual = (Expr *) lfirst(lc2);
3704-
3705-
/* not likely that we'd see constants here, so no check */
3706-
childquals = lappend(childquals,
3707-
make_restrictinfo(qual,
3708-
true, false, false,
3709-
security_level,
3710-
NULL, NULL, NULL));
3711-
cq_min_security = Min(cq_min_security, security_level);
3712-
}
3713-
security_level++;
3714-
}
3715-
Assert(security_level <= root->qual_security_level);
3716-
}
3717-
3718-
/*
3719-
* OK, we've got all the baserestrictinfo quals for this child.
3720-
*/
3721-
childrel->baserestrictinfo = childquals;
3722-
childrel->baserestrict_min_security = cq_min_security;
3723-
3724-
return true;
3725-
}
37263568

37273569
/*****************************************************************************
37283570
* DEBUG SUPPORT

‎src/backend/optimizer/plan/initsplan.c

Lines changed: 2 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -20,6 +20,7 @@
2020
#include "nodes/nodeFuncs.h"
2121
#include "optimizer/clauses.h"
2222
#include "optimizer/cost.h"
23+
#include "optimizer/inherit.h"
2324
#include "optimizer/joininfo.h"
2425
#include "optimizer/optimizer.h"
2526
#include "optimizer/pathnode.h"
@@ -159,12 +160,7 @@ add_other_rels_to_query(PlannerInfo *root)
159160

160161
/* If it's marked as inheritable, look for children. */
161162
if (rte->inh)
162-
{
163-
/* Only relation and subquery RTEs can have children. */
164-
Assert(rte->rtekind == RTE_RELATION ||
165-
rte->rtekind == RTE_SUBQUERY);
166-
add_appendrel_other_rels(root, rel, rti);
167-
}
163+
expand_inherited_rtentry(root, rel, rte, rti);
168164
}
169165
}
170166

0 commit comments

Comments
 (0)