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

Commit11ea45f

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 parent1432349 commit11ea45f

File tree

6 files changed

+177
-64
lines changed

6 files changed

+177
-64
lines changed

‎doc/src/sgml/config.sgml

Lines changed: 11 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -4410,10 +4410,11 @@ ANY <replaceable class="parameter">num_sync</replaceable> ( <replaceable class="
44104410
The allowed values of <varname>constraint_exclusion</varname> are
44114411
<literal>on</literal> (examine constraints for all tables),
44124412
<literal>off</literal> (never examine constraints), and
4413-
<literal>partition</literal> (examine constraints only for inheritance child
4414-
tables and <literal>UNION ALL</literal> subqueries).
4413+
<literal>partition</literal> (examine constraints only for inheritance
4414+
childtables and <literal>UNION ALL</literal> subqueries).
44154415
<literal>partition</literal> is the default setting.
4416-
It is often used with inheritance tables to improve performance.
4416+
It is often used with traditional inheritance trees to improve
4417+
performance.
44174418
</para>
44184419

44194420
<para>
@@ -4437,15 +4438,19 @@ SELECT * FROM parent WHERE key = 2400;
44374438
<para>
44384439
Currently, constraint exclusion is enabled by default
44394440
only for cases that are often used to implement table partitioning via
4440-
inheritancetables. Turning it on for all tables imposes extra
4441+
inheritancetrees. Turning it on for all tables imposes extra
44414442
planning overhead that is quite noticeable on simple queries, and most
44424443
often will yield no benefit for simple queries. If you have no
4443-
inheritance partitioned tables you might prefer to turn it off entirely.
4444+
tables that are partitioned using traditional inheritance, you might
4445+
prefer to turn it off entirely. (Note that the equivalent feature for
4446+
partitioned tables is controlled by a separate parameter,
4447+
<xref linkend="guc-enable-partition-pruning"/>.)
44444448
</para>
44454449

44464450
<para>
44474451
Refer to <xref linkend="ddl-partitioning-constraint-exclusion"/> for
4448-
more information on using constraint exclusion and partitioning.
4452+
more information on using constraint exclusion to implement
4453+
partitioning.
44494454
</para>
44504455
</listitem>
44514456
</varlistentry>

‎doc/src/sgml/ddl.sgml

Lines changed: 5 additions & 16 deletions
Original file line numberDiff line numberDiff line change
@@ -3918,22 +3918,11 @@ EXPLAIN SELECT count(*) FROM measurement WHERE logdate &gt;= DATE '2008-01-01';
39183918

39193919
<note>
39203920
<para>
3921-
Currently, pruning of partitions during the planning of an
3922-
<command>UPDATE</command> or <command>DELETE</command> command is
3923-
implemented using the constraint exclusion method (however, it is
3924-
controlled by the <literal>enable_partition_pruning</literal> rather than
3925-
<literal>constraint_exclusion</literal>) &mdash; see the following section
3926-
for details and caveats that apply.
3927-
</para>
3928-
3929-
<para>
3930-
Also, execution-time partition pruning currently only occurs for the
3931-
<literal>Append</literal> node type, not <literal>MergeAppend</literal>.
3932-
</para>
3933-
3934-
<para>
3935-
Both of these behaviors are likely to be changed in a future release
3936-
of <productname>PostgreSQL</productname>.
3921+
Execution-time partition pruning currently only occurs for the
3922+
<literal>Append</literal> node type, not
3923+
for <literal>MergeAppend</literal> or <literal>ModifyTable</literal>
3924+
nodes. That is likely to be changed in a future release of
3925+
<productname>PostgreSQL</productname>.
39373926
</para>
39383927
</note>
39393928
</sect2>

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

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

13261326
/*
1327-
* Mark whether we're planning a query to a partitioned table or an
1328-
* inheritance parent.
1327+
* HACK: setting this to a value other than INHKIND_NONE signals to
1328+
* relation_excluded_by_constraints() to treat the result relation as
1329+
* being an appendrel member.
13291330
*/
13301331
subroot->inhTargetKind=
13311332
partitioned_relids ?INHKIND_PARTITIONED :INHKIND_INHERITED;

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

Lines changed: 96 additions & 40 deletions
Original file line numberDiff line numberDiff line change
@@ -66,7 +66,9 @@ static bool infer_collation_opclass_match(InferenceElem *elem, Relation idxRel,
6666
staticint32get_rel_data_width(Relationrel,int32*attr_widths);
6767
staticList*get_relation_constraints(PlannerInfo*root,
6868
OidrelationObjectId,RelOptInfo*rel,
69-
boolinclude_notnull);
69+
boolinclude_noinherit,
70+
boolinclude_notnull,
71+
boolinclude_partition);
7072
staticList*build_index_tlist(PlannerInfo*root,IndexOptInfo*index,
7173
RelationheapRelation);
7274
staticList*get_relation_statistics(RelOptInfo*rel,Relationrelation);
@@ -1157,24 +1159,32 @@ get_relation_data_width(Oid relid, int32 *attr_widths)
11571159
/*
11581160
* get_relation_constraints
11591161
*
1160-
* Retrieve thevalidated CHECK constraint expressions of the given relation.
1162+
* Retrieve theapplicable constraint expressions of the given relation.
11611163
*
11621164
* Returns a List (possibly empty) of constraint expressions. Each one
11631165
* has been canonicalized, and its Vars are changed to have the varno
11641166
* indicated by rel->relid. This allows the expressions to be easily
11651167
* compared to expressions taken from WHERE.
11661168
*
1169+
* If include_noinherit is true, it's okay to include constraints that
1170+
* are marked NO INHERIT.
1171+
*
11671172
* If include_notnull is true, "col IS NOT NULL" expressions are generated
11681173
* and added to the result for each column that's marked attnotnull.
11691174
*
1175+
* If include_partition is true, and the relation is a partition,
1176+
* also include the partitioning constraints.
1177+
*
11701178
* Note: at present this is invoked at most once per relation per planner
11711179
* run, and in many cases it won't be invoked at all, so there seems no
11721180
* point in caching the data in RelOptInfo.
11731181
*/
11741182
staticList*
11751183
get_relation_constraints(PlannerInfo*root,
11761184
OidrelationObjectId,RelOptInfo*rel,
1177-
boolinclude_notnull)
1185+
boolinclude_noinherit,
1186+
boolinclude_notnull,
1187+
boolinclude_partition)
11781188
{
11791189
List*result=NIL;
11801190
Indexvarno=rel->relid;
@@ -1198,10 +1208,13 @@ get_relation_constraints(PlannerInfo *root,
11981208

11991209
/*
12001210
* If this constraint hasn't been fully validated yet, we must
1201-
* ignore it here.
1211+
* ignore it here. Also ignore if NO INHERIT and we weren't told
1212+
* that that's safe.
12021213
*/
12031214
if (!constr->check[i].ccvalid)
12041215
continue;
1216+
if (constr->check[i].ccnoinherit&& !include_noinherit)
1217+
continue;
12051218

12061219
cexpr=stringToNode(constr->check[i].ccbin);
12071220

@@ -1266,13 +1279,9 @@ get_relation_constraints(PlannerInfo *root,
12661279
}
12671280

12681281
/*
1269-
* Append partition predicates, if any.
1270-
*
1271-
* For selects, partition pruning uses the parent table's partition bound
1272-
* descriptor, instead of constraint exclusion which is driven by the
1273-
* individual partition's partition constraint.
1282+
* Add partitioning constraints, if requested.
12741283
*/
1275-
if (enable_partition_pruning&&root->parse->commandType!=CMD_SELECT)
1284+
if (include_partition&&relation->rd_rel->relispartition)
12761285
{
12771286
List*pcqual=RelationGetPartitionQual(relation);
12781287

@@ -1377,7 +1386,7 @@ get_relation_statistics(RelOptInfo *rel, Relation relation)
13771386
*
13781387
* Detect whether the relation need not be scanned because it has either
13791388
* self-inconsistent restrictions, or restrictions inconsistent with the
1380-
* relation'svalidated CHECK constraints.
1389+
* relation'sapplicable constraints.
13811390
*
13821391
* Note: this examines only rel->relid, rel->reloptkind, and
13831392
* rel->baserestrictinfo; therefore it can be called before filling in
@@ -1387,6 +1396,9 @@ bool
13871396
relation_excluded_by_constraints(PlannerInfo*root,
13881397
RelOptInfo*rel,RangeTblEntry*rte)
13891398
{
1399+
boolinclude_noinherit;
1400+
boolinclude_notnull;
1401+
boolinclude_partition= false;
13901402
List*safe_restrictions;
13911403
List*constraint_pred;
13921404
List*safe_constraints;
@@ -1395,6 +1407,13 @@ relation_excluded_by_constraints(PlannerInfo *root,
13951407
/* As of now, constraint exclusion works only with simple relations. */
13961408
Assert(IS_SIMPLE_REL(rel));
13971409

1410+
/*
1411+
* If there are no base restriction clauses, we have no hope of proving
1412+
* anything below, so fall out quickly.
1413+
*/
1414+
if (rel->baserestrictinfo==NIL)
1415+
return false;
1416+
13981417
/*
13991418
* Regardless of the setting of constraint_exclusion, detect
14001419
* constant-FALSE-or-NULL restriction clauses. Because const-folding will
@@ -1415,6 +1434,17 @@ relation_excluded_by_constraints(PlannerInfo *root,
14151434
return true;
14161435
}
14171436

1437+
/*
1438+
* Partition pruning will not have been applied to an inherited target
1439+
* relation, so if enable_partition_pruning is true, force consideration
1440+
* of the rel's partition constraints. (Thus constraint_exclusion will be
1441+
* effectively forced 'on' for this case. This is done better in v12.)
1442+
*/
1443+
if (enable_partition_pruning&&
1444+
rel->relid==root->parse->resultRelation&&
1445+
root->inhTargetKind!=INHKIND_NONE)
1446+
include_partition= true;
1447+
14181448
/*
14191449
* Skip further tests, depending on constraint_exclusion.
14201450
*/
@@ -1423,33 +1453,43 @@ relation_excluded_by_constraints(PlannerInfo *root,
14231453
caseCONSTRAINT_EXCLUSION_OFF:
14241454

14251455
/*
1426-
* Don't prune if feature turned off -- except if the relation is
1427-
* a partition. While partprune.c-style partition pruning is not
1428-
* yet in use for all cases (update/delete is not handled), it
1429-
* would be a UI horror to use different user-visible controls
1430-
* depending on such a volatile implementation detail. Therefore,
1431-
* for partitioned tables we use enable_partition_pruning to
1432-
* control this behavior.
1456+
* In 'off' mode, never make any further tests, except if forcing
1457+
* include_partition.
14331458
*/
1434-
if (root->inhTargetKind==INHKIND_PARTITIONED)
1459+
if (include_partition)
14351460
break;
14361461
return false;
14371462

14381463
caseCONSTRAINT_EXCLUSION_PARTITION:
14391464

14401465
/*
14411466
* When constraint_exclusion is set to 'partition' we only handle
1442-
* OTHER_MEMBER_RELs, or BASERELs in cases where the result target
1443-
* is an inheritance parent or a partitioned table.
1467+
* appendrel members. Normally, they are RELOPT_OTHER_MEMBER_REL
1468+
* relations, but we also consider inherited target relations as
1469+
* appendrel members for the purposes of constraint exclusion.
1470+
*
1471+
* In the former case, partition pruning was already applied, so
1472+
* there is no need to consider the rel's partition constraints
1473+
* here. In the latter case, we already set include_partition
1474+
* properly (i.e., do it if enable_partition_pruning).
14441475
*/
1445-
if ((rel->reloptkind!=RELOPT_OTHER_MEMBER_REL)&&
1446-
!(rel->reloptkind==RELOPT_BASEREL&&
1447-
root->inhTargetKind!=INHKIND_NONE&&
1448-
rel->relid==root->parse->resultRelation))
1449-
return false;
1450-
break;
1476+
if (rel->reloptkind==RELOPT_OTHER_MEMBER_REL||
1477+
(rel->relid==root->parse->resultRelation&&
1478+
root->inhTargetKind!=INHKIND_NONE))
1479+
break;/* appendrel member, so process it */
1480+
return false;
14511481

14521482
caseCONSTRAINT_EXCLUSION_ON:
1483+
1484+
/*
1485+
* In 'on' mode, always apply constraint exclusion. If we are
1486+
* considering a baserel that is a partition (i.e., it was
1487+
* directly named rather than expanded from a parent table), then
1488+
* its partition constraints haven't been considered yet, so
1489+
* include them in the processing here.
1490+
*/
1491+
if (rel->reloptkind==RELOPT_BASEREL)
1492+
include_partition= true;
14531493
break;/* always try to exclude */
14541494
}
14551495

@@ -1478,24 +1518,40 @@ relation_excluded_by_constraints(PlannerInfo *root,
14781518
return true;
14791519

14801520
/*
1481-
* Only plain relations have constraints. In a partitioning hierarchy,
1482-
* but not with regular table inheritance, it's OK to assume that any
1483-
* constraints that hold for the parent also hold for every child; for
1484-
* instance, table inheritance allows the parent to have constraints
1485-
* marked NO INHERIT, but table partitioning does not. We choose to check
1486-
* whether the partitioning parents can be excluded here; doing so
1487-
* consumes some cycles, but potentially saves us the work of excluding
1488-
* each child individually.
1521+
* Only plain relations have constraints, so stop here for other rtekinds.
1522+
*/
1523+
if (rte->rtekind!=RTE_RELATION)
1524+
return false;
1525+
1526+
/*
1527+
* In a partitioning hierarchy, but not with regular table inheritance,
1528+
* it's OK to assume that any constraints that hold for the parent also
1529+
* hold for every child; for instance, table inheritance allows the parent
1530+
* to have constraints marked NO INHERIT, but table partitioning does not.
1531+
* We choose to check whether the partitioning parents can be excluded
1532+
* here; doing so consumes some cycles, but potentially saves us the work
1533+
* of excluding each child individually.
1534+
*
1535+
* This is unnecessarily stupid, but making it smarter seems out of scope
1536+
* for v11.
14891537
*/
1490-
if (rte->rtekind!=RTE_RELATION||
1491-
(rte->inh&&rte->relkind!=RELKIND_PARTITIONED_TABLE))
1538+
if (rte->inh&&rte->relkind!=RELKIND_PARTITIONED_TABLE)
14921539
return false;
14931540

14941541
/*
1495-
* OK to fetch the constraint expressions. Include "col IS NOT NULL"
1496-
* expressions for attnotnull columns, in case we can refute those.
1542+
* Given the above restriction, we can always include NO INHERIT and NOT
1543+
* NULL constraints.
1544+
*/
1545+
include_noinherit= true;
1546+
include_notnull= true;
1547+
1548+
/*
1549+
* Fetch the appropriate set of constraint expressions.
14971550
*/
1498-
constraint_pred=get_relation_constraints(root,rte->relid,rel, true);
1551+
constraint_pred=get_relation_constraints(root,rte->relid,rel,
1552+
include_noinherit,
1553+
include_notnull,
1554+
include_partition);
14991555

15001556
/*
15011557
* 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
@@ -3417,4 +3417,46 @@ select * from listp where a = (select 2) and b <> 10;
34173417
Filter: ((b <> 10) AND (a = $0))
34183418
(5 rows)
34193419

3420+
--
3421+
-- check that a partition directly accessed in a query is excluded with
3422+
-- constraint_exclusion = on
3423+
--
3424+
-- turn off partition pruning, so that it doesn't interfere
3425+
set enable_partition_pruning to off;
3426+
-- setting constraint_exclusion to 'partition' disables exclusion
3427+
set constraint_exclusion to 'partition';
3428+
explain (costs off) select * from listp1 where a = 2;
3429+
QUERY PLAN
3430+
--------------------
3431+
Seq Scan on listp1
3432+
Filter: (a = 2)
3433+
(2 rows)
3434+
3435+
explain (costs off) update listp1 set a = 1 where a = 2;
3436+
QUERY PLAN
3437+
--------------------------
3438+
Update on listp1
3439+
-> Seq Scan on listp1
3440+
Filter: (a = 2)
3441+
(3 rows)
3442+
3443+
-- constraint exclusion enabled
3444+
set constraint_exclusion to 'on';
3445+
explain (costs off) select * from listp1 where a = 2;
3446+
QUERY PLAN
3447+
--------------------------
3448+
Result
3449+
One-Time Filter: false
3450+
(2 rows)
3451+
3452+
explain (costs off) update listp1 set a = 1 where a = 2;
3453+
QUERY PLAN
3454+
--------------------------------
3455+
Update on listp1
3456+
-> Result
3457+
One-Time Filter: false
3458+
(3 rows)
3459+
3460+
reset constraint_exclusion;
3461+
reset enable_partition_pruning;
34203462
drop table listp;

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

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

902+
--
903+
-- check that a partition directly accessed in a query is excluded with
904+
-- constraint_exclusion = on
905+
--
906+
907+
-- turn off partition pruning, so that it doesn't interfere
908+
set enable_partition_pruning to off;
909+
910+
-- setting constraint_exclusion to 'partition' disables exclusion
911+
set constraint_exclusion to'partition';
912+
explain (costs off)select*from listp1where a=2;
913+
explain (costs off)update listp1set a=1where a=2;
914+
-- constraint exclusion enabled
915+
set constraint_exclusion to'on';
916+
explain (costs off)select*from listp1where a=2;
917+
explain (costs off)update listp1set a=1where a=2;
918+
919+
reset constraint_exclusion;
920+
reset enable_partition_pruning;
921+
902922
droptable listp;

0 commit comments

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