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

Commita8ccf4e

Browse files
author
Richard Guo
committed
Reordering DISTINCT keys to match input path's pathkeys
The ordering of DISTINCT items is semantically insignificant, so wecan reorder them as needed. In fact, in the parser, we absorb thesorting semantics of the sortClause as much as possible into thedistinctClause, ensuring that one clause is a prefix of the other.This can help avoid a possible need to re-sort.In this commit, we attempt to adjust the DISTINCT keys to match theinput path's pathkeys. This can likewise help avoid re-sorting, orallow us to use incremental-sort to save efforts.For DISTINCT ON expressions, the parser already ensures that theymatch the initial ORDER BY expressions. When reordering the DISTINCTkeys, we must ensure that the resulting pathkey list matches theinitial distinctClause pathkeys.This introduces a new GUC, enable_distinct_reordering, which allowsthe optimization to be disabled if needed.Author: Richard GuoReviewed-by: Andrei LepikhovDiscussion:https://postgr.es/m/CAMbWs48dR26cCcX0f=8bja2JKQPcU64136kHk=xekHT9xschiQ@mail.gmail.com
1 parent5b8728c commita8ccf4e

File tree

11 files changed

+672
-201
lines changed

11 files changed

+672
-201
lines changed

‎doc/src/sgml/config.sgml

Lines changed: 14 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -5360,6 +5360,20 @@ ANY <replaceable class="parameter">num_sync</replaceable> ( <replaceable class="
53605360
</listitem>
53615361
</varlistentry>
53625362

5363+
<varlistentry id="guc-enable-distinct-reordering" xreflabel="enable_distinct_reordering">
5364+
<term><varname>enable_distinct_reordering</varname> (<type>boolean</type>)
5365+
<indexterm>
5366+
<primary><varname>enable_distinct_reordering</varname> configuration parameter</primary>
5367+
</indexterm>
5368+
</term>
5369+
<listitem>
5370+
<para>
5371+
Enables or disables the query planner's ability to reorder DISTINCT
5372+
keys to match the input path's pathkeys. The default is <literal>on</literal>.
5373+
</para>
5374+
</listitem>
5375+
</varlistentry>
5376+
53635377
<varlistentry id="guc-enable-gathermerge" xreflabel="enable_gathermerge">
53645378
<term><varname>enable_gathermerge</varname> (<type>boolean</type>)
53655379
<indexterm>

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

Lines changed: 38 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2207,6 +2207,41 @@ pathkeys_useful_for_grouping(PlannerInfo *root, List *pathkeys)
22072207
returnn;
22082208
}
22092209

2210+
/*
2211+
* pathkeys_useful_for_distinct
2212+
*Count the number of pathkeys that are useful for DISTINCT or DISTINCT
2213+
*ON clause.
2214+
*
2215+
* DISTINCT keys could be reordered to benefit from the given pathkey list. As
2216+
* with pathkeys_useful_for_grouping, we return the number of leading keys in
2217+
* the list that are shared by the distinctClause pathkeys.
2218+
*/
2219+
staticint
2220+
pathkeys_useful_for_distinct(PlannerInfo*root,List*pathkeys)
2221+
{
2222+
intn_common_pathkeys;
2223+
2224+
/*
2225+
* distinct_pathkeys may have become empty if all of the pathkeys were
2226+
* determined to be redundant. Return 0 in this case.
2227+
*/
2228+
if (root->distinct_pathkeys==NIL)
2229+
return0;
2230+
2231+
/* walk the pathkeys and search for matching DISTINCT key */
2232+
n_common_pathkeys=0;
2233+
foreach_node(PathKey,pathkey,pathkeys)
2234+
{
2235+
/* no matching DISTINCT key, we're done */
2236+
if (!list_member_ptr(root->distinct_pathkeys,pathkey))
2237+
break;
2238+
2239+
n_common_pathkeys++;
2240+
}
2241+
2242+
returnn_common_pathkeys;
2243+
}
2244+
22102245
/*
22112246
* pathkeys_useful_for_setop
22122247
*Count the number of leading common pathkeys root's 'setop_pathkeys' in
@@ -2240,6 +2275,9 @@ truncate_useless_pathkeys(PlannerInfo *root,
22402275
if (nuseful2>nuseful)
22412276
nuseful=nuseful2;
22422277
nuseful2=pathkeys_useful_for_grouping(root,pathkeys);
2278+
if (nuseful2>nuseful)
2279+
nuseful=nuseful2;
2280+
nuseful2=pathkeys_useful_for_distinct(root,pathkeys);
22432281
if (nuseful2>nuseful)
22442282
nuseful=nuseful2;
22452283
nuseful2=pathkeys_useful_for_setop(root,pathkeys);

0 commit comments

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