8000 Add GUC enable_partition_pruning · tomdcc/postgres@055fb8d · GitHub
[go: up one dir, main page]
Skip to content

Commit 055fb8d

Browse files
alvherrealvherre
committed
Add GUC enable_partition_pruning
This controls both plan-time and execution-time new-style partition pruning. While finer-grain control is possible (maybe using an enum GUC instead of boolean), there doesn't seem to be much need for that. This new parameter controls partition pruning for all queries: trivially, SELECT queries that affect partitioned tables are naturally under its control since they are using the new technology. However, while UPDATE/DELETE queries do not use the new code, we make the new GUC control their behavior also (stealing control from constraint_exclusion), because it is more natural, and it leads to a more natural transition to the future in which those queries will also use the new pruning code. Constraint exclusion still controls pruning for regular inheritance situations (those not involving partitioned tables). Author: David Rowley Review: Amit Langote, Ashutosh Bapat, Justin Pryzby, David G. Johnston Discussion: https://postgr.es/m/CAKJS1f_0HwsxJG9m+nzU+CizxSdGtfe6iF_ykPYBiYft302DCw@mail.gmail.com
1 parent 4df58f7 commit 055fb8d

File tree

16 files changed

+322
-29
lines changed

16 files changed

+322
-29
lines changed

‎doc/src/sgml/config.sgml

Lines changed: 23 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -3826,6 +3826,23 @@ ANY <replaceable class="parameter">num_sync</replaceable> ( <replaceable class="
38263826
</listitem>
38273827
</varlistentry>
38283828

3829+
<varlistentry id="guc-enable-partition-pruning" xreflabel="enable_partition_pruning">
3830+
<term><varname>enable_partition_pruning</varname> (<type>boolean</type>)
3831+
<indexterm>
3832+
<primary><varname>enable_partition_pruning</varname> configuration parameter</primary>
3833+
</indexterm>
3834+
</term>
3835+
<listitem>
3836+
<para>
3837+
Enables or disables the query planner's ability to eliminate a
3838+
partitioned table's partitions from query plans. This also controls
3839+
the planner's ability to generate query plans which allow the query
3840+
executor to remove (ignore) partitions during query execution. The
3841+
default is <literal>on</literal>.
3842+
</para>
3843+
</listitem>
3844+
</varlistentry>
3845+
38293846
<varlistentry id="guc-enable-partitionwise-join" xreflabel="enable_partitionwise_join">
38303847
<term><varname>enable_partitionwise_join</varname> (<type>boolean</type>)
38313848
<indexterm>
@@ -4417,8 +4434,7 @@ ANY <replaceable class="parameter">num_sync</replaceable> ( <replaceable class="
44174434
<literal>partition</literal> (examine constraints only for inheritance child
44184435
tables and <literal>UNION ALL</literal> subqueries).
44194436
<literal>partition</literal> is the default setting.
4420-
It is often used with inheritance and partitioned tables to
4421-
improve performance.
4437+
It is often used with inheritance tables to improve performance.
44224438
</para>
44234439

44244440
<para>
@@ -4441,11 +4457,11 @@ SELECT * FROM parent WHERE key = 2400;
44414457

44424458
<para>
44434459
Currently, constraint exclusion is enabled by default
4444-
only for cases that are often used to implement table partitioning.
4445-
Turning it on for all tables imposes extra planning overhead that is
4446-
quite noticeable on simple queries, and most often will yield no
4447-
benefit for simple queries. If you have no partitioned tables
4448-
you might prefer to turn it off entirely.
4460+
only for cases that are often used to implement table partitioning via
4461+
inheritance tables. Turning it on for all tables imposes extra
4462+
planning overhead that is quite noticeable on simple queries, and most
4463+
often will yield no benefit for simple queries. If you have no
4464+
inheritance partitioned tables you might prefer to turn it off entirely.
44494465
</para>
44504466

44514467
<para>

‎doc/src/sgml/ddl.sgml

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -3196,7 +3196,7 @@ CREATE INDEX ON measurement (logdate);
31963196

31973197
<listitem>
31983198
<para>
3199-
Ensure that the <xref linkend="guc-constraint-exclusion"/>
3199+
Ensure that the <xref linkend="guc-enable-partition-pruning"/>
32003200
configuration parameter is not disabled in <filename>postgresql.conf</filename>.
32013201
If it is, queries will not be optimized as desired.
32023202
</para>

‎src/backend/nodes/outfuncs.c

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -2291,7 +2291,7 @@ _outPlannerInfo(StringInfo str, const PlannerInfo *node)
22912291
WRITE_FLOAT_FIELD(tuple_fraction, "%.4f");
22922292
WRITE_FLOAT_FIELD(limit_tuples, "%.0f");
22932293
WRITE_UINT_FIELD(qual_security_level);
2294-
WRITE_BOOL_FIELD(hasInheritedTarget);
2294+
WRITE_ENUM_FIELD(inhTargetKind, InheritanceKind);
22952295
WRITE_BOOL_FIELD(hasJoinRTEs);
22962296
WRITE_BOOL_FIELD(hasLateralRTEs);
22972297
WRITE_BOOL_FIELD(hasDeletedRTEs);

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

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -901,7 +901,8 @@ set_append_rel_size(PlannerInfo *root, RelOptInfo *rel,
901901
* store the relids of all partitions which could possibly contain a
902902
* matching tuple, and skip anything else in the loop below.
903903
*/
904-
if (rte->relkind == RELKIND_PARTITIONED_TABLE &&
904+
if (enable_partition_pruning &&
905+
rte->relkind == RELKIND_PARTITIONED_TABLE &&
905906
rel->baserestrictinfo != NIL)
906907
{
907908
live_children = prune_append_rel_partitions(rel);

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

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -138,6 +138,7 @@ bool enable_partitionwise_join = false;
138138
bool enable_partitionwise_aggregate = false;
139139
bool enable_parallel_append = true;
140140
bool enable_parallel_hash = true;
141+
bool enable_partition_pruning = true;
141142

142143
typedef struct
143144
{

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

Lines changed: 4 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -1077,7 +1077,8 @@ create_append_plan(PlannerInfo *root, AppendPath *best_path)
10771077
subplans = lappend(subplans, subplan);
10781078
}
10791079

1080-
if (rel->reloptkind == RELOPT_BASEREL &&
1080+
if (enable_partition_pruning &&
1081+
rel->reloptkind == RELOPT_BASEREL &&
10811082
best_path->partitioned_rels != NIL)
10821083
{
10831084
List *prunequal;
@@ -1979,7 +1980,7 @@ create_groupingsets_plan(PlannerInfo *root, GroupingSetsPath *best_path)
19791980
* create_modifytable_plan). Fortunately we can't be because there would
19801981
* never be grouping in an UPDATE/DELETE; but let's Assert that.
19811982
*/
1982-
Assert(!root->hasInheritedTarget);
1983+
Assert(root->inhTargetKind == INHKIND_NONE);
19831984
Assert(root->grouping_map == NULL);
19841985
root->grouping_map = grouping_map;
19851986

@@ -2141,7 +2142,7 @@ create_minmaxagg_plan(PlannerInfo *root, MinMaxAggPath *best_path)
21412142
* create_modifytable_plan). Fortunately we can't be because there would
21422143
* never be aggregates in an UPDATE/DELETE; but let's Assert that.
21432144
*/
2144-
Assert(!root->hasInheritedTarget);
2145+
Assert(root->inhTargetKind == INHKIND_NONE);
21452146
Assert(root->minmax_aggs == NIL);
21462147
root->minmax_aggs = best_path->mmaggregates;
21472148

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

Lines changed: 8 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -623,7 +623,7 @@ subquery_planner(PlannerGlobal *glob, Query *parse,
623623
root->grouping_map = NULL;
624624
root->minmax_aggs = NIL;
625625
root->qual_security_level = 0;
626-
root->hasInheritedTarget = false;
626+
root->inhTargetKind = INHKIND_NONE;
627627
root->hasRecursion = hasRecursion;
628628
if (hasRecursion)
629629
root->wt_param_id = SS_assign_special_param(root);
@@ -1424,8 +1424,13 @@ inheritance_planner(PlannerInfo *root)
14241424
Assert(subroot->join_info_list == NIL);
14251425
/* and we haven't created PlaceHolderInfos, either */
14261426
Assert(subroot->placeholder_list == NIL);
1427-
/* hack to mark target relation as an inheritance partition */
1428-
subroot->hasInheritedTarget = true;
1427+
1428+
/*
1429+
* Mark if we're planning a query to a partitioned table or an
1430+
* inheritance parent.
1431+
*/
1432+
subroot->inhTargetKind =
1433+
partitioned_relids ? INHKIND_PARTITIONED : INHKIND_INHERITED;
14291434

14301435
/*
14311436
* If the child is further partitioned, remember it as a parent. Since

‎src/backend/optimizer/prep/prepjointree.c

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -914,7 +914,7 @@ pull_up_simple_subquery(PlannerInfo *root, Node *jtnode, RangeTblEntry *rte,
914914
subroot->grouping_map = NULL;
915915
subroot->minmax_aggs = NIL;
916916
subroot->qual_security_level = 0;
917-
subroot->hasInheritedTarget = false;
917+
subroot->inhTargetKind = INHKIND_NONE;
918918
subroot->hasRecursion = false;
919919
subroot->wt_param_id = -1;
920920
subroot->non_recursive_path = NULL;

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

Lines changed: 36 additions & 9 deletions
Original file line numberDiff line numberDiff line change
@@ -1272,7 +1272,7 @@ get_relation_constraints(PlannerInfo *root,
12721272
* descriptor, instead of constraint exclusion which is driven by the
12731273
* individual partition's partition constraint.
12741274
*/
1275-
if (root->parse->commandType != CMD_SELECT)
1275+
if (enable_partition_pruning && root->parse->commandType != CMD_SELECT)
12761276
{
12771277
List *pcqual = RelationGetPartitionQual(relation);
12781278

@@ -1415,14 +1415,41 @@ relation_excluded_by_constraints(PlannerInfo *root,
14151415
return true;
14161416
}
14171417

1418-
/* Skip further tests if constraint exclusion is disabled for the rel */
1419-
if (constraint_exclusion == CONSTRAINT_EXCLUSION_OFF ||
1420-
(constraint_exclusion == CONSTRAINT_EXCLUSION_PARTITION &&
1421-
!(rel->reloptkind == RELOPT_OTHER_MEMBER_REL ||
1422-
(root->hasInheritedTarget &&
1423-
rel->reloptkind == RELOPT_BASEREL &&
1424-
rel->relid == root->parse->resultRelation))))
1425-
return false;
1418+
/*
1419+
* Skip further tests, depending on constraint_exclusion.
1420+
*/
1421+
switch (constraint_exclusion)
1422+
{
1423+
case CONSTRAINT_EXCLUSION_OFF:
1424+
/*
1425+
* Don't prune if feature turned off -- except if the relation is
1426+
* a partition. While partprune.c-style partition pruning is not
1427+
* yet in use for all cases (update/delete is not handled), it
1428+
* would be a UI horror to use different user-visible controls
1429+
* depending on such a volatile implementation detail. Therefore,
1430+
* for partitioned tables we use enable_partition_pruning to
1431+
* control this behavior.
1432+
*/
1433+
if (root->inhTargetKind == INHKIND_PARTITIONED)
1434+
break;
1435+
return false;
1436+
1437+
case CONSTRAINT_EXCLUSION_PARTITION:
1438+
/*
1439+
* When constraint_exclusion is set to 'partition' we only handle
1440+
* OTHER_MEMBER_RELs, or BASERELs in cases where the result target
1441+
* is an inheritance parent or a partitioned table.
1442+
*/
1443+
if ((rel->reloptkind != RELOPT_OTHER_MEMBER_REL) &&
1444+
!(rel->reloptkind == RELOPT_BASEREL &&
1445+
root->inhTargetKind != INHKIND_NONE &&
1446+
rel->relid == root->parse->resultRelation))
1447+
return false;
1448+
break;
1449+
1450+
case CONSTRAINT_EXCLUSION_ON:
1451+
break; /* always try to exclude */
1452+
}
14261453

14271454
/*
14281455
* Check for self-contradictory restriction clauses. We dare not make

‎src/backend/utils/misc/guc.c

Lines changed: 11 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -951,6 +951,17 @@ static struct config_bool ConfigureNamesBool[] =
951951
true,
952952
NULL, NULL, NULL
953953
},
954+
{
955+
{"enable_partition_pruning", PGC_USERSET, QUERY_TUNING_METHOD,
956+
gettext_noop("Enable plan-time and run-time partition pruning."),
957+
gettext_noop("Allows the query planner and executor to compare partition "
958+
"bounds to conditions in the query to determine which "
959+
"partitions must be scanned.")
960+
},
961+
&enable_partition_pruning,
962+
true,
963+
NULL, NULL, NULL
964+
},
954965
{
955966
{"geqo", PGC_USERSET, QUERY_TUNING_GEQO,
956967
gettext_noop("Enables genetic query optimization."),

‎src/backend/utils/misc/postgresql.conf.sample

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -308,6 +308,7 @@
308308
#enable_partitionwise_join = off
309309
#enable_partitionwise_aggregate = off
310310
#enable_parallel_hash = on
311+
#enable_partition_pruning = on
311312

312313
# - Planner Cost Constants -
313314

‎src/include/nodes/relation.h

Lines changed: 14 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -82,6 +82,17 @@ typedef enum UpperRelationKind
8282
/* NB: UPPERREL_FINAL must be last enum entry; it's used to size arrays */
8383
} UpperRelationKind;
8484

85+
/*
86+
* This enum identifies which type of relation is being planned through the
87+
* inheritance planner. INHKIND_NONE indicates the inheritance planner
88+
* was not used.
89+
*/
90+
typedef enum InheritanceKind
91+
{
92+
INHKIND_NONE,
93+
INHKIND_INHERITED,
94+
INHKIND_PARTITIONED
95+
} InheritanceKind;
8596

8697
/*----------
8798
* PlannerGlobal
@@ -298,8 +309,9 @@ typedef struct PlannerInfo
298309
Index qual_security_level; /* minimum security_level for quals */
299310
/* Note: qual_security_level is zero if there are no securityQuals */
300311

301-
bool hasInheritedTarget; /* true if parse->resultRelation is an
302-
* inheritance child rel */
312+
InheritanceKind inhTargetKind; /* indicates if the target relation is an
313+
* inheritance child or partition or a
314+
* partitioned table */
303315
bool hasJoinRTEs; /* true if any RTEs are RTE_JOIN kind */
304316
bool hasLateralRTEs; /* true if any RTEs are marked LATERAL */
305317
bool hasDeletedRTEs; /* true if any RTE was deleted from jointree */

‎src/include/optimizer/cost.h

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -71,6 +71,7 @@ extern PGDLLIMPORT bool enable_partitionwise_join;
7171
extern PGDLLIMPORT bool enable_partitionwise_aggregate;
7272
extern PGDLLIMPORT bool enable_parallel_append;
7373
extern PGDLLIMPORT bool enable_parallel_hash;
74+
extern PGDLLIMPORT bool enable_partition_pruning;
7475
extern PGDLLIMPORT int constraint_exclusion;
7576

7677
extern double clamp_row_est(double nrows);

0 commit comments

Comments
 (0)
0