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

Commit499be01

Browse files
committed
Support partition pruning at execution time
Existing partition pruning is only able to work at plan time, for queryquals that appear in the parsed query. This is good but limiting, asthere can be parameters that appear later that can be usefully used tofurther prune partitions.This commit adds support for pruning subnodes of Append which cannotpossibly contain any matching tuples, during execution, by evaluatingParams to determine the minimum set of subnodes that can possibly match.We support more than just simple Params in WHERE clauses. Supportadditionally includes:1. Parameterized Nested Loop Joins: The parameter from the outer side of the join can be used to determine the minimum set of inner side partitions to scan.2. Initplans: Once an initplan has been executed we can then determine which partitions match the value from the initplan.Partition pruning is performed in two ways. When Params external to the planare found to match the partition key we attempt to prune away unneeded Appendsubplans during the initialization of the executor. This allows us to bypassthe initialization of non-matching subplans meaning they won't appear in theEXPLAIN or EXPLAIN ANALYZE output.For parameters whose value is only known during the actual executionthen the pruning of these subplans must wait. Subplans which areeliminated during this stage of pruning are still visible in the EXPLAINoutput. In order to determine if pruning has actually taken place, theEXPLAIN ANALYZE must be viewed. If a certain Append subplan was neverexecuted due to the elimination of the partition then the executiontiming area will state "(never executed)". Whereas, if, for example inthe case of parameterized nested loops, the number of loops stated inthe EXPLAIN ANALYZE output for certain subplans may appear lower thanothers due to the subplan having been scanned fewer times. This is dueto the list of matching subnodes having to be evaluated whenever aparameter which was found to match the partition key changes.This commit required some additional infrastructure that permits thebuilding of a data structure which is able to perform the translation ofthe matching partition IDs, as returned by get_matching_partitions, intothe list index of a subpaths list, as exist in node types such asAppend, MergeAppend and ModifyTable. This allows us to translate a listof clauses into a Bitmapset of all the subpath indexes which must beincluded to satisfy the clause list.Author: David Rowley, based on an earlier effort by Beena EmersonReviewers: Amit Langote, Robert Haas, Amul Sul, Rajkumar Raghuwanshi,Jesper PedersenDiscussion:https://postgr.es/m/CAOG9ApE16ac-_VVZVvv0gePSgkg_BwYEV1NBqZFqDR2bBE0X0A@mail.gmail.com
1 parent5c06752 commit499be01

File tree

24 files changed

+2714
-112
lines changed

24 files changed

+2714
-112
lines changed

‎doc/src/sgml/perform.sgml

Lines changed: 12 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -894,6 +894,18 @@ EXPLAIN ANALYZE SELECT * FROM tenk1 WHERE unique1 < 100 AND unique2 > 9000
894894
BitmapAnd and BitmapOr nodes always report their actual row counts as zero,
895895
due to implementation limitations.
896896
</para>
897+
898+
<para>
899+
Generally, the <command>EXPLAIN</command> output will display details for
900+
every plan node which was generated by the query planner. However, there
901+
are cases where the executor is able to determine that certain nodes are
902+
not required; currently, the only node type to support this is the
903+
<literal>Append</literal> node. This node type has the ability to discard
904+
subnodes which it is able to determine won't contain any records required
905+
by the query. It is possible to determine that nodes have been removed in
906+
this way by the presence of a "Subplans Removed" property in the
907+
<command>EXPLAIN</command> output.
908+
</para>
897909
</sect2>
898910

899911
</sect1>

‎src/backend/commands/explain.c

Lines changed: 33 additions & 18 deletions
Original file line numberDiff line numberDiff line change
@@ -118,8 +118,8 @@ static void ExplainModifyTarget(ModifyTable *plan, ExplainState *es);
118118
staticvoidExplainTargetRel(Plan*plan,Indexrti,ExplainState*es);
119119
staticvoidshow_modifytable_info(ModifyTableState*mtstate,List*ancestors,
120120
ExplainState*es);
121-
staticvoidExplainMemberNodes(List*plans,PlanState**planstates,
122-
List*ancestors,ExplainState*es);
121+
staticvoidExplainMemberNodes(PlanState**planstates,intnsubnodes,
122+
intnplans,List*ancestors,ExplainState*es);
123123
staticvoidExplainSubPlans(List*plans,List*ancestors,
124124
constchar*relationship,ExplainState*es);
125125
staticvoidExplainCustomChildren(CustomScanState*css,
@@ -1811,28 +1811,33 @@ ExplainNode(PlanState *planstate, List *ancestors,
18111811
switch (nodeTag(plan))
18121812
{
18131813
caseT_ModifyTable:
1814-
ExplainMemberNodes(((ModifyTable*)plan)->plans,
1815-
((ModifyTableState*)planstate)->mt_plans,
1814+
ExplainMemberNodes(((ModifyTableState*)planstate)->mt_plans,
1815+
((ModifyTableState*)planstate)->mt_nplans,
1816+
list_length(((ModifyTable*)plan)->plans),
18161817
ancestors,es);
18171818
break;
18181819
caseT_Append:
1819-
ExplainMemberNodes(((Append*)plan)->appendplans,
1820-
((AppendState*)planstate)->appendplans,
1820+
ExplainMemberNodes(((AppendState*)planstate)->appendplans,
1821+
((AppendState*)planstate)->as_nplans,
1822+
list_length(((Append*)plan)->appendplans),
18211823
ancestors,es);
18221824
break;
18231825
caseT_MergeAppend:
1824-
ExplainMemberNodes(((MergeAppend*)plan)->mergeplans,
1825-
((MergeAppendState*)planstate)->mergeplans,
1826+
ExplainMemberNodes(((MergeAppendState*)planstate)->mergeplans,
1827+
((MergeAppendState*)planstate)->ms_nplans,
1828+
list_length(((MergeAppend*)plan)->mergeplans),
18261829
ancestors,es);
18271830
break;
18281831
caseT_BitmapAnd:
1829-
ExplainMemberNodes(((BitmapAnd*)plan)->bitmapplans,
1830-
((BitmapAndState*)planstate)->bitmapplans,
1832+
ExplainMemberNodes(((BitmapAndState*)planstate)->bitmapplans,
1833+
((BitmapAndState*)planstate)->nplans,
1834+
list_length(((BitmapAnd*)plan)->bitmapplans),
18311835
ancestors,es);
18321836
break;
18331837
caseT_BitmapOr:
1834-
ExplainMemberNodes(((BitmapOr*)plan)->bitmapplans,
1835-
((BitmapOrState*)planstate)->bitmapplans,
1838+
ExplainMemberNodes(((BitmapOrState*)planstate)->bitmapplans,
1839+
((BitmapOrState*)planstate)->nplans,
1840+
list_length(((BitmapOr*)plan)->bitmapplans),
18361841
ancestors,es);
18371842
break;
18381843
caseT_SubqueryScan:
@@ -3173,18 +3178,28 @@ show_modifytable_info(ModifyTableState *mtstate, List *ancestors,
31733178
*
31743179
* The ancestors list should already contain the immediate parent of these
31753180
* plans.
3176-
*
3177-
* Note: we don't actually need to examine the Plan list members, but
3178-
* we need the list in order to determine the length of the PlanState array.
3181+
*
3182+
* nsubnodes indicates the number of items in the planstates array.
3183+
* nplans indicates the original number of subnodes in the Plan, some of these
3184+
* may have been pruned by the run-time pruning code.
31793185
*/
31803186
staticvoid
3181-
ExplainMemberNodes(List*plans,PlanState**planstates,
3187+
ExplainMemberNodes(PlanState**planstates,intnsubnodes,intnplans,
31823188
List*ancestors,ExplainState*es)
31833189
{
3184-
intnplans=list_length(plans);
31853190
intj;
31863191

3187-
for (j=0;j<nplans;j++)
3192+
/*
3193+
* The number of subnodes being lower than the number of subplans that was
3194+
* specified in the plan means that some subnodes have been ignored per
3195+
* instruction for the partition pruning code during the executor
3196+
* initialization. To make this a bit less mysterious, we'll indicate
3197+
* here that this has happened.
3198+
*/
3199+
if (nsubnodes<nplans)
3200+
ExplainPropertyInteger("Subplans Removed",NULL,nplans-nsubnodes,es);
3201+
3202+
for (j=0;j<nsubnodes;j++)
31883203
ExplainNode(planstates[j],ancestors,
31893204
"Member",NULL,es);
31903205
}

0 commit comments

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