Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commite03ff73

Browse files
committed
Clean up handling of constraint_exclusion and enable_partition_pruning.
The interaction of these parameters was a bit confused/confusing,and in fact v11 entirely misses the opportunity to apply partitionconstraints when a partition is accessed directly (rather thanindirectly from its parent).In HEAD, establish the principle that enable_partition_pruning controlspartition pruning and nothing else. When accessing a partition via itsparent, we do partition pruning (if enabled by enable_partition_pruning)and then there is no need to consider partition constraints in theconstraint_exclusion logic. When accessing a partition directly, itspartition constraints are applied by the constraint_exclusion logic,only if constraint_exclusion = on.In v11, we can't have such a clean division of these GUCs' effects,partly because we don't want to break compatibility too much in areleased branch, and partly because the clean coding requiresinheritance_planner to have applied partition pruning to a partitionedtarget table, which it doesn't in v11. However, we can tweak thingsenough to cover the missed case, which seems like a good idea sinceit's potentially a performance regression from v10. This patch keepsv11's previous behavior in which enable_partition_pruning overridesconstraint_exclusion for an inherited target table, though.In HEAD, also teach relation_excluded_by_constraints that it's okay to useinheritable constraints when trying to prune a traditional inheritancetree. This might not be thought worthy of effort given that that featureis semi-deprecated now, but we have enough infrastructure that it onlytakes a couple more lines of code to do it correctly.Amit Langote and Tom LaneDiscussion:https://postgr.es/m/9813f079-f16b-61c8-9ab7-4363cab28d80@lab.ntt.co.jpDiscussion:https://postgr.es/m/29069.1555970894@sss.pgh.pa.us
1 parentad23adc commite03ff73

File tree

6 files changed

+156
-67
lines changed

6 files changed

+156
-67
lines changed

‎doc/src/sgml/config.sgml

Lines changed: 11 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -5084,10 +5084,11 @@ ANY <replaceable class="parameter">num_sync</replaceable> ( <replaceable class="
50845084
The allowed values of <varname>constraint_exclusion</varname> are
50855085
<literal>on</literal> (examine constraints for all tables),
50865086
<literal>off</literal> (never examine constraints), and
5087-
<literal>partition</literal> (examine constraints only for inheritance child
5088-
tables and <literal>UNION ALL</literal> subqueries).
5087+
<literal>partition</literal> (examine constraints only for inheritance
5088+
childtables and <literal>UNION ALL</literal> subqueries).
50895089
<literal>partition</literal> is the default setting.
5090-
It is often used with inheritance tables to improve performance.
5090+
It is often used with traditional inheritance trees to improve
5091+
performance.
50915092
</para>
50925093

50935094
<para>
@@ -5111,15 +5112,19 @@ SELECT * FROM parent WHERE key = 2400;
51115112
<para>
51125113
Currently, constraint exclusion is enabled by default
51135114
only for cases that are often used to implement table partitioning via
5114-
inheritancetables. Turning it on for all tables imposes extra
5115+
inheritancetrees. Turning it on for all tables imposes extra
51155116
planning overhead that is quite noticeable on simple queries, and most
51165117
often will yield no benefit for simple queries. If you have no
5117-
inheritance partitioned tables you might prefer to turn it off entirely.
5118+
tables that are partitioned using traditional inheritance, you might
5119+
prefer to turn it off entirely. (Note that the equivalent feature for
5120+
partitioned tables is controlled by a separate parameter,
5121+
<xref linkend="guc-enable-partition-pruning"/>.)
51185122
</para>
51195123

51205124
<para>
51215125
Refer to <xref linkend="ddl-partitioning-constraint-exclusion"/> for
5122-
more information on using constraint exclusion and partitioning.
5126+
more information on using constraint exclusion to implement
5127+
partitioning.
51235128
</para>
51245129
</listitem>
51255130
</varlistentry>

‎doc/src/sgml/ddl.sgml

Lines changed: 2 additions & 15 deletions
Original file line numberDiff line numberDiff line change
@@ -4534,25 +4534,12 @@ EXPLAIN SELECT count(*) FROM measurement WHERE logdate &gt;= DATE '2008-01-01';
45344534
</para>
45354535

45364536
<note>
4537-
<para>
4538-
Currently, pruning of partitions during the planning of an
4539-
<command>UPDATE</command> or <command>DELETE</command> command is
4540-
implemented using the constraint exclusion method (however, it is
4541-
controlled by the <literal>enable_partition_pruning</literal> rather than
4542-
<literal>constraint_exclusion</literal>) &mdash; see the following section
4543-
for details and caveats that apply.
4544-
</para>
4545-
45464537
<para>
45474538
Execution-time partition pruning currently only occurs for the
45484539
<literal>Append</literal> and <literal>MergeAppend</literal> node types.
45494540
It is not yet implemented for the <literal>ModifyTable</literal> node
4550-
type.
4551-
</para>
4552-
4553-
<para>
4554-
Both of these behaviors are likely to be changed in a future release
4555-
of <productname>PostgreSQL</productname>.
4541+
type, but that is likely to be changed in a future release of
4542+
<productname>PostgreSQL</productname>.
45564543
</para>
45574544
</note>
45584545
</sect2>

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

Lines changed: 3 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -1513,8 +1513,9 @@ inheritance_planner(PlannerInfo *root)
15131513
parent_rte->securityQuals=NIL;
15141514

15151515
/*
1516-
* Mark whether we're planning a query to a partitioned table or an
1517-
* inheritance parent.
1516+
* HACK: setting this to a value other than INHKIND_NONE signals to
1517+
* relation_excluded_by_constraints() to treat the result relation as
1518+
* being an appendrel member.
15181519
*/
15191520
subroot->inhTargetKind=
15201521
(rootRelation!=0) ?INHKIND_PARTITIONED :INHKIND_INHERITED;

‎src/backend/optimizer/util/plancat.c

Lines changed: 78 additions & 44 deletions
Original file line numberDiff line numberDiff line change
@@ -67,7 +67,9 @@ static bool infer_collation_opclass_match(InferenceElem *elem, Relation idxRel,
6767
List*idxExprs);
6868
staticList*get_relation_constraints(PlannerInfo*root,
6969
OidrelationObjectId,RelOptInfo*rel,
70-
boolinclude_notnull);
70+
boolinclude_noinherit,
71+
boolinclude_notnull,
72+
boolinclude_partition);
7173
staticList*build_index_tlist(PlannerInfo*root,IndexOptInfo*index,
7274
RelationheapRelation);
7375
staticList*get_relation_statistics(RelOptInfo*rel,Relationrelation);
@@ -1134,24 +1136,32 @@ get_relation_data_width(Oid relid, int32 *attr_widths)
11341136
/*
11351137
* get_relation_constraints
11361138
*
1137-
* Retrieve thevalidated CHECK constraint expressions of the given relation.
1139+
* Retrieve theapplicable constraint expressions of the given relation.
11381140
*
11391141
* Returns a List (possibly empty) of constraint expressions. Each one
11401142
* has been canonicalized, and its Vars are changed to have the varno
11411143
* indicated by rel->relid. This allows the expressions to be easily
11421144
* compared to expressions taken from WHERE.
11431145
*
1146+
* If include_noinherit is true, it's okay to include constraints that
1147+
* are marked NO INHERIT.
1148+
*
11441149
* If include_notnull is true, "col IS NOT NULL" expressions are generated
11451150
* and added to the result for each column that's marked attnotnull.
11461151
*
1152+
* If include_partition is true, and the relation is a partition,
1153+
* also include the partitioning constraints.
1154+
*
11471155
* Note: at present this is invoked at most once per relation per planner
11481156
* run, and in many cases it won't be invoked at all, so there seems no
11491157
* point in caching the data in RelOptInfo.
11501158
*/
11511159
staticList*
11521160
get_relation_constraints(PlannerInfo*root,
11531161
OidrelationObjectId,RelOptInfo*rel,
1154-
boolinclude_notnull)
1162+
boolinclude_noinherit,
1163+
boolinclude_notnull,
1164+
boolinclude_partition)
11551165
{
11561166
List*result=NIL;
11571167
Indexvarno=rel->relid;
@@ -1175,10 +1185,13 @@ get_relation_constraints(PlannerInfo *root,
11751185

11761186
/*
11771187
* If this constraint hasn't been fully validated yet, we must
1178-
* ignore it here.
1188+
* ignore it here. Also ignore if NO INHERIT and we weren't told
1189+
* that that's safe.
11791190
*/
11801191
if (!constr->check[i].ccvalid)
11811192
continue;
1193+
if (constr->check[i].ccnoinherit&& !include_noinherit)
1194+
continue;
11821195

11831196
cexpr=stringToNode(constr->check[i].ccbin);
11841197

@@ -1243,13 +1256,9 @@ get_relation_constraints(PlannerInfo *root,
12431256
}
12441257

12451258
/*
1246-
* Append partition predicates, if any.
1247-
*
1248-
* For selects, partition pruning uses the parent table's partition bound
1249-
* descriptor, instead of constraint exclusion which is driven by the
1250-
* individual partition's partition constraint.
1259+
* Add partitioning constraints, if requested.
12511260
*/
1252-
if (enable_partition_pruning&&root->parse->commandType!=CMD_SELECT)
1261+
if (include_partition&&relation->rd_rel->relispartition)
12531262
{
12541263
List*pcqual=RelationGetPartitionQual(relation);
12551264

@@ -1366,7 +1375,7 @@ get_relation_statistics(RelOptInfo *rel, Relation relation)
13661375
*
13671376
* Detect whether the relation need not be scanned because it has either
13681377
* self-inconsistent restrictions, or restrictions inconsistent with the
1369-
* relation'svalidated CHECK constraints.
1378+
* relation'sapplicable constraints.
13701379
*
13711380
* Note: this examines only rel->relid, rel->reloptkind, and
13721381
* rel->baserestrictinfo; therefore it can be called before filling in
@@ -1376,6 +1385,9 @@ bool
13761385
relation_excluded_by_constraints(PlannerInfo*root,
13771386
RelOptInfo*rel,RangeTblEntry*rte)
13781387
{
1388+
boolinclude_noinherit;
1389+
boolinclude_notnull;
1390+
boolinclude_partition= false;
13791391
List*safe_restrictions;
13801392
List*constraint_pred;
13811393
List*safe_constraints;
@@ -1384,6 +1396,13 @@ relation_excluded_by_constraints(PlannerInfo *root,
13841396
/* As of now, constraint exclusion works only with simple relations. */
13851397
Assert(IS_SIMPLE_REL(rel));
13861398

1399+
/*
1400+
* If there are no base restriction clauses, we have no hope of proving
1401+
* anything below, so fall out quickly.
1402+
*/
1403+
if (rel->baserestrictinfo==NIL)
1404+
return false;
1405+
13871406
/*
13881407
* Regardless of the setting of constraint_exclusion, detect
13891408
* constant-FALSE-or-NULL restriction clauses. Because const-folding will
@@ -1410,35 +1429,41 @@ relation_excluded_by_constraints(PlannerInfo *root,
14101429
switch (constraint_exclusion)
14111430
{
14121431
caseCONSTRAINT_EXCLUSION_OFF:
1413-
1414-
/*
1415-
* Don't prune if feature turned off -- except if the relation is
1416-
* a partition. While partprune.c-style partition pruning is not
1417-
* yet in use for all cases (update/delete is not handled), it
1418-
* would be a UI horror to use different user-visible controls
1419-
* depending on such a volatile implementation detail. Therefore,
1420-
* for partitioned tables we use enable_partition_pruning to
1421-
* control this behavior.
1422-
*/
1423-
if (root->inhTargetKind==INHKIND_PARTITIONED)
1424-
break;
1432+
/* In 'off' mode, never make any further tests */
14251433
return false;
14261434

14271435
caseCONSTRAINT_EXCLUSION_PARTITION:
14281436

14291437
/*
14301438
* When constraint_exclusion is set to 'partition' we only handle
1431-
* OTHER_MEMBER_RELs, or BASERELs in cases where the result target
1432-
* is an inheritance parent or a partitioned table.
1439+
* appendrel members. Normally, they are RELOPT_OTHER_MEMBER_REL
1440+
* relations, but we also consider inherited target relations as
1441+
* appendrel members for the purposes of constraint exclusion
1442+
* (since, indeed, they were appendrel members earlier in
1443+
* inheritance_planner).
1444+
*
1445+
* In both cases, partition pruning was already applied, so there
1446+
* is no need to consider the rel's partition constraints here.
14331447
*/
1434-
if ((rel->reloptkind!=RELOPT_OTHER_MEMBER_REL)&&
1435-
!(rel->reloptkind==RELOPT_BASEREL&&
1436-
root->inhTargetKind!=INHKIND_NONE&&
1437-
rel->relid==root->parse->resultRelation))
1438-
return false;
1439-
break;
1448+
if (rel->reloptkind==RELOPT_OTHER_MEMBER_REL||
1449+
(rel->relid==root->parse->resultRelation&&
1450+
root->inhTargetKind!=INHKIND_NONE))
1451+
break;/* appendrel member, so process it */
1452+
return false;
14401453

14411454
caseCONSTRAINT_EXCLUSION_ON:
1455+
1456+
/*
1457+
* In 'on' mode, always apply constraint exclusion. If we are
1458+
* considering a baserel that is a partition (i.e., it was
1459+
* directly named rather than expanded from a parent table), then
1460+
* its partition constraints haven't been considered yet, so
1461+
* include them in the processing here.
1462+
*/
1463+
if (rel->reloptkind==RELOPT_BASEREL&&
1464+
!(rel->relid==root->parse->resultRelation&&
1465+
root->inhTargetKind!=INHKIND_NONE))
1466+
include_partition= true;
14421467
break;/* always try to exclude */
14431468
}
14441469

@@ -1467,24 +1492,33 @@ relation_excluded_by_constraints(PlannerInfo *root,
14671492
return true;
14681493

14691494
/*
1470-
* Only plain relations have constraints. In a partitioning hierarchy,
1471-
* but not with regular table inheritance, it's OK to assume that any
1472-
* constraints that hold for the parent also hold for every child; for
1473-
* instance, table inheritance allows the parent to have constraints
1474-
* marked NO INHERIT, but table partitioning does not. We choose to check
1475-
* whether the partitioning parents can be excluded here; doing so
1476-
* consumes some cycles, but potentially saves us the work of excluding
1477-
* each child individually.
1495+
* Only plain relations have constraints, so stop here for other rtekinds.
14781496
*/
1479-
if (rte->rtekind!=RTE_RELATION||
1480-
(rte->inh&&rte->relkind!=RELKIND_PARTITIONED_TABLE))
1497+
if (rte->rtekind!=RTE_RELATION)
14811498
return false;
14821499

14831500
/*
1484-
* OK to fetch the constraint expressions. Include "col IS NOT NULL"
1485-
* expressions for attnotnull columns, in case we can refute those.
1501+
* If we are scanning just this table, we can use NO INHERIT constraints,
1502+
* but not if we're scanning its children too. (Note that partitioned
1503+
* tables should never have NO INHERIT constraints; but it's not necessary
1504+
* for us to assume that here.)
1505+
*/
1506+
include_noinherit= !rte->inh;
1507+
1508+
/*
1509+
* Currently, attnotnull constraints must be treated as NO INHERIT unless
1510+
* this is a partitioned table. In future we might track their
1511+
* inheritance status more accurately, allowing this to be refined.
1512+
*/
1513+
include_notnull= (!rte->inh||rte->relkind==RELKIND_PARTITIONED_TABLE);
1514+
1515+
/*
1516+
* Fetch the appropriate set of constraint expressions.
14861517
*/
1487-
constraint_pred=get_relation_constraints(root,rte->relid,rel, true);
1518+
constraint_pred=get_relation_constraints(root,rte->relid,rel,
1519+
include_noinherit,
1520+
include_notnull,
1521+
include_partition);
14881522

14891523
/*
14901524
* We do not currently enforce that CHECK constraints contain only

‎src/test/regress/expected/partition_prune.out

Lines changed: 42 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -3639,4 +3639,46 @@ select * from listp where a = (select 2) and b <> 10;
36393639
-> Result (never executed)
36403640
(4 rows)
36413641

3642+
--
3643+
-- check that a partition directly accessed in a query is excluded with
3644+
-- constraint_exclusion = on
3645+
--
3646+
-- turn off partition pruning, so that it doesn't interfere
3647+
set enable_partition_pruning to off;
3648+
-- setting constraint_exclusion to 'partition' disables exclusion
3649+
set constraint_exclusion to 'partition';
3650+
explain (costs off) select * from listp1 where a = 2;
3651+
QUERY PLAN
3652+
--------------------
3653+
Seq Scan on listp1
3654+
Filter: (a = 2)
3655+
(2 rows)
3656+
3657+
explain (costs off) update listp1 set a = 1 where a = 2;
3658+
QUERY PLAN
3659+
--------------------------
3660+
Update on listp1
3661+
-> Seq Scan on listp1
3662+
Filter: (a = 2)
3663+
(3 rows)
3664+
3665+
-- constraint exclusion enabled
3666+
set constraint_exclusion to 'on';
3667+
explain (costs off) select * from listp1 where a = 2;
3668+
QUERY PLAN
3669+
--------------------------
3670+
Result
3671+
One-Time Filter: false
3672+
(2 rows)
3673+
3674+
explain (costs off) update listp1 set a = 1 where a = 2;
3675+
QUERY PLAN
3676+
--------------------------------
3677+
Update on listp1
3678+
-> Result
3679+
One-Time Filter: false
3680+
(3 rows)
3681+
3682+
reset constraint_exclusion;
3683+
reset enable_partition_pruning;
36423684
drop table listp;

‎src/test/regress/sql/partition_prune.sql

Lines changed: 20 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -990,4 +990,24 @@ create table listp2_10 partition of listp2 for values in (10);
990990
explain (analyze, costs off, summary off, timing off)
991991
select*from listpwhere a= (select2)and b<>10;
992992

993+
--
994+
-- check that a partition directly accessed in a query is excluded with
995+
-- constraint_exclusion = on
996+
--
997+
998+
-- turn off partition pruning, so that it doesn't interfere
999+
set enable_partition_pruning to off;
1000+
1001+
-- setting constraint_exclusion to 'partition' disables exclusion
1002+
set constraint_exclusion to'partition';
1003+
explain (costs off)select*from listp1where a=2;
1004+
explain (costs off)update listp1set a=1where a=2;
1005+
-- constraint exclusion enabled
1006+
set constraint_exclusion to'on';
1007+
explain (costs off)select*from listp1where a=2;
1008+
explain (costs off)update listp1set a=1where a=2;
1009+
1010+
reset constraint_exclusion;
1011+
reset enable_partition_pruning;
1012+
9931013
droptable listp;

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp