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

Commit0bd7af0

Browse files
committed
Invent recursive_worktable_factor GUC to replace hard-wired constant.
Up to now, the planner estimated the size of a recursive query'sworktable as 10 times the size of the non-recursive term. It's hardto see how to do significantly better than that automatically, butwe can give users control over the multiplier to allow tuning forspecific use-cases. The default behavior remains the same.Simon RiggsDiscussion:https://postgr.es/m/CANbhV-EuaLm4H3g0+BSTYHEGxJj3Kht0R+rJ8vT57Dejnh=_nA@mail.gmail.com
1 parenta476514 commit0bd7af0

File tree

6 files changed

+44
-3
lines changed

6 files changed

+44
-3
lines changed

‎doc/src/sgml/config.sgml

Lines changed: 23 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -5919,6 +5919,29 @@ SELECT * FROM parent WHERE key = 2400;
59195919
</listitem>
59205920
</varlistentry>
59215921

5922+
<varlistentry id="guc-recursive-worktable-factor" xreflabel="recursive_worktable_factor">
5923+
<term><varname>recursive_worktable_factor</varname> (<type>floating point</type>)
5924+
<indexterm>
5925+
<primary><varname>recursive_worktable_factor</varname> configuration parameter</primary>
5926+
</indexterm>
5927+
</term>
5928+
<listitem>
5929+
<para>
5930+
Sets the planner's estimate of the average size of the working
5931+
table of a <link linkend="queries-with-recursive">recursive
5932+
query</link>, as a multiple of the estimated size of the initial
5933+
non-recursive term of the query. This helps the planner choose
5934+
the most appropriate method for joining the working table to the
5935+
query's other tables.
5936+
The default value is <literal>10.0</literal>. A smaller value
5937+
such as <literal>1.0</literal> can be helpful when the recursion
5938+
has low <quote>fan-out</quote> from one step to the next, as for
5939+
example in shortest-path queries. Graph analytics queries may
5940+
benefit from larger-than-default values.
5941+
</para>
5942+
</listitem>
5943+
</varlistentry>
5944+
59225945
</variablelist>
59235946
</sect2>
59245947
</sect1>

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

Lines changed: 5 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -123,6 +123,7 @@ doublecpu_index_tuple_cost = DEFAULT_CPU_INDEX_TUPLE_COST;
123123
doublecpu_operator_cost=DEFAULT_CPU_OPERATOR_COST;
124124
doubleparallel_tuple_cost=DEFAULT_PARALLEL_TUPLE_COST;
125125
doubleparallel_setup_cost=DEFAULT_PARALLEL_SETUP_COST;
126+
doublerecursive_worktable_factor=DEFAULT_RECURSIVE_WORKTABLE_FACTOR;
126127

127128
inteffective_cache_size=DEFAULT_EFFECTIVE_CACHE_SIZE;
128129

@@ -5665,10 +5666,11 @@ set_cte_size_estimates(PlannerInfo *root, RelOptInfo *rel, double cte_rows)
56655666
if (rte->self_reference)
56665667
{
56675668
/*
5668-
* In a self-reference, arbitrarily assume the average worktable size
5669-
* is about 10 times the nonrecursive term's size.
5669+
* In a self-reference, we assume the average worktable size is a
5670+
* multiple of the nonrecursive term's size. The best multiplier will
5671+
* vary depending on query "fan-out", so make its value adjustable.
56705672
*/
5671-
rel->tuples=10*cte_rows;
5673+
rel->tuples=clamp_row_est(recursive_worktable_factor*cte_rows);
56725674
}
56735675
else
56745676
{

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

Lines changed: 12 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -3740,6 +3740,18 @@ static struct config_real ConfigureNamesReal[] =
37403740
NULL,NULL,NULL
37413741
},
37423742

3743+
{
3744+
{"recursive_worktable_factor",PGC_USERSET,QUERY_TUNING_OTHER,
3745+
gettext_noop("Sets the planner's estimate of the average size "
3746+
"of a recursive query's working table."),
3747+
NULL,
3748+
GUC_EXPLAIN
3749+
},
3750+
&recursive_worktable_factor,
3751+
DEFAULT_RECURSIVE_WORKTABLE_FACTOR,0.001,1000000.0,
3752+
NULL,NULL,NULL
3753+
},
3754+
37433755
{
37443756
{"geqo_selection_bias",PGC_USERSET,QUERY_TUNING_GEQO,
37453757
gettext_noop("GEQO: selective pressure within the population."),

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

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -426,6 +426,7 @@
426426
# JOIN clauses
427427
#plan_cache_mode = auto# auto, force_generic_plan or
428428
# force_custom_plan
429+
#recursive_worktable_factor = 10.0# range 0.001-1000000
429430

430431

431432
#------------------------------------------------------------------------------

‎src/include/optimizer/cost.h

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -29,6 +29,8 @@
2929
#defineDEFAULT_PARALLEL_TUPLE_COST 0.1
3030
#defineDEFAULT_PARALLEL_SETUP_COST 1000.0
3131

32+
/* defaults for non-Cost parameters */
33+
#defineDEFAULT_RECURSIVE_WORKTABLE_FACTOR 10.0
3234
#defineDEFAULT_EFFECTIVE_CACHE_SIZE 524288/* measured in pages */
3335

3436
typedefenum

‎src/include/optimizer/optimizer.h

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -91,6 +91,7 @@ extern PGDLLIMPORT double cpu_index_tuple_cost;
9191
externPGDLLIMPORTdoublecpu_operator_cost;
9292
externPGDLLIMPORTdoubleparallel_tuple_cost;
9393
externPGDLLIMPORTdoubleparallel_setup_cost;
94+
externPGDLLIMPORTdoublerecursive_worktable_factor;
9495
externPGDLLIMPORTinteffective_cache_size;
9596

9697
externdoubleclamp_row_est(doublenrows);

0 commit comments

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