public inbox for [email protected]
help / color / mirror / Atom feedFrom: Simon Riggs <[email protected]>
To: Andres Freund <[email protected]>
Cc: Robert Haas <[email protected]>
Cc: Peter Eisentraut <[email protected]>
Cc: PostgreSQL Hackers <[email protected]>
Subject: Re: Parameter for planner estimate of recursive queries
Date: Wed, 23 Mar 2022 14:53:04 +0000
Message-ID: <CANbhV-EPrq9Z1gSHTeXVrxRHZq91=5VWDTBrbG=DaPn5oHe08A@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <CANbhV-EuaLm4H3g0+BSTYHEGxJj3Kht0R+rJ8vT57Dejnh=_nA@mail.gmail.com>
<CANbhV-HGhrkoF9BoHXScVZY8_HnBoHzGRQU15E6iPaF6ij0Cmw@mail.gmail.com>
<[email protected]>
<CA+TgmoaGDw2baysiK+qqL+c3roa-9cPSvJ_Kwdgx7RoPQR0TaA@mail.gmail.com>
<CANbhV-HTc4=fK4tMb+1UPRLs4MYe9y21cxNbg9nHUGVZWbUBYA@mail.gmail.com>
<[email protected]>
On Tue, 22 Mar 2022 at 00:04, Andres Freund <[email protected]> wrote:
> On 2022-03-10 17:42:14 +0000, Simon Riggs wrote:
> > Shall I set this as Ready For Committer?
>
> Currently this CF entry fails on cfbot: https://cirrus-ci.com/task/4531771134967808?logs=test_world#L1158
>
> [16:27:35.772] # Failed test 'no parameters missing from postgresql.conf.sample'
> [16:27:35.772] # at t/003_check_guc.pl line 82.
> [16:27:35.772] # got: '1'
> [16:27:35.772] # expected: '0'
> [16:27:35.772] # Looks like you failed 1 test of 3.
> [16:27:35.772] [16:27:35] t/003_check_guc.pl ..............
>
> Marked as waiting on author.
Thanks.
I've corrected that by adding a line to postgresql.conf.sample, as
well as adding docs.
--
Simon Riggs http://www.EnterpriseDB.com/
Attachments:
[application/octet-stream] recursive_worktable_estimate.v2.patch (4.7K, ../CANbhV-EPrq9Z1gSHTeXVrxRHZq91=5VWDTBrbG=DaPn5oHe08A@mail.gmail.com/2-recursive_worktable_estimate.v2.patch)
download | inline diff:
diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index 7a48973b3c..86673b4fba 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -5919,6 +5919,25 @@ SELECT * FROM parent WHERE key = 2400;
</listitem>
</varlistentry>
+ <varlistentry id="guc-recursive-worktable-estimate" xreflabel="recursive_worktable_estimate">
+ <term><varname>recursive_worktable_estimate</varname> (<type>floating point</type>)
+ <indexterm>
+ <primary><varname>recursive_worktable_estimate</varname> configuration parameter</primary>
+ </indexterm>
+ </term>
+ <listitem>
+ <para>
+ Sets the planner's estimate of the average size of the worktable, if
+ a recursive query is requested, as a multiple of the number of rows in the
+ initial non-recursive term. The default is 10.0. Smaller values of this
+ setting bias the planner towards using <quote>fast start</quote> plans
+ for recursive queries, which will retrieve the first few rows quickly while
+ perhaps taking a long time to fetch all rows. A setting of 1.0 is appropriate
+ for shortest path queries; larger values may be better for graph analytics.
+ </para>
+ </listitem>
+ </varlistentry>
+
</variablelist>
</sect2>
</sect1>
diff --git a/src/backend/optimizer/path/costsize.c b/src/backend/optimizer/path/costsize.c
index 4d9f3b4bb6..31e8de4bf3 100644
--- a/src/backend/optimizer/path/costsize.c
+++ b/src/backend/optimizer/path/costsize.c
@@ -123,6 +123,7 @@ double cpu_index_tuple_cost = DEFAULT_CPU_INDEX_TUPLE_COST;
double cpu_operator_cost = DEFAULT_CPU_OPERATOR_COST;
double parallel_tuple_cost = DEFAULT_PARALLEL_TUPLE_COST;
double parallel_setup_cost = DEFAULT_PARALLEL_SETUP_COST;
+double recursive_worktable_estimate = DEFAULT_RECURSIVE_WORKTABLE_ESTIMATE;
int effective_cache_size = DEFAULT_EFFECTIVE_CACHE_SIZE;
@@ -5665,10 +5666,11 @@ set_cte_size_estimates(PlannerInfo *root, RelOptInfo *rel, double cte_rows)
if (rte->self_reference)
{
/*
- * In a self-reference, arbitrarily assume the average worktable size
- * is about 10 times the nonrecursive term's size.
+ * Use recursive_worktable_estimate to get average size of worktable,
+ * across all iterations. This will vary depending upon how bushy the
+ * data is, so allow the user to set based upon their data.
*/
- rel->tuples = 10 * cte_rows;
+ rel->tuples = recursive_worktable_estimate * cte_rows;
}
else
{
diff --git a/src/backend/utils/misc/guc.c b/src/backend/utils/misc/guc.c
index 932aefc777..60dfa66b28 100644
--- a/src/backend/utils/misc/guc.c
+++ b/src/backend/utils/misc/guc.c
@@ -3740,6 +3740,19 @@ static struct config_real ConfigureNamesReal[] =
NULL, NULL, NULL
},
+ {
+ {"recursive_worktable_estimate", PGC_USERSET, QUERY_TUNING_OTHER,
+ gettext_noop("Sets the planner's estimate of the size of "
+ "the recursive_worktable as a multiple of the "
+ "initial non-recursive query size."),
+ NULL,
+ GUC_EXPLAIN
+ },
+ &recursive_worktable_estimate,
+ DEFAULT_RECURSIVE_WORKTABLE_ESTIMATE, 0.0, 1000000.0,
+ NULL, NULL, NULL
+ },
+
{
{"geqo_selection_bias", PGC_USERSET, QUERY_TUNING_GEQO,
gettext_noop("GEQO: selective pressure within the population."),
diff --git a/src/backend/utils/misc/postgresql.conf.sample b/src/backend/utils/misc/postgresql.conf.sample
index 4cf5b26a36..c7eeb11a17 100644
--- a/src/backend/utils/misc/postgresql.conf.sample
+++ b/src/backend/utils/misc/postgresql.conf.sample
@@ -426,6 +426,7 @@
# JOIN clauses
#plan_cache_mode = auto # auto, force_generic_plan or
# force_custom_plan
+#recursive_worktable_estimate = 10.0 # Set to 1.0 for shortest path queries
#------------------------------------------------------------------------------
diff --git a/src/include/optimizer/cost.h b/src/include/optimizer/cost.h
index 356a51f370..1afaae5e61 100644
--- a/src/include/optimizer/cost.h
+++ b/src/include/optimizer/cost.h
@@ -29,6 +29,8 @@
#define DEFAULT_PARALLEL_TUPLE_COST 0.1
#define DEFAULT_PARALLEL_SETUP_COST 1000.0
+#define DEFAULT_RECURSIVE_WORKTABLE_ESTIMATE 10.0
+
#define DEFAULT_EFFECTIVE_CACHE_SIZE 524288 /* measured in pages */
typedef enum
diff --git a/src/include/optimizer/optimizer.h b/src/include/optimizer/optimizer.h
index 6b8ee0c69f..5e60f0bb27 100644
--- a/src/include/optimizer/optimizer.h
+++ b/src/include/optimizer/optimizer.h
@@ -92,6 +92,7 @@ extern PGDLLIMPORT double cpu_operator_cost;
extern PGDLLIMPORT double parallel_tuple_cost;
extern PGDLLIMPORT double parallel_setup_cost;
extern PGDLLIMPORT int effective_cache_size;
+extern PGDLLIMPORT double recursive_worktable_estimate;
extern double clamp_row_est(double nrows);
view thread (16+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected]
Subject: Re: Parameter for planner estimate of recursive queries
In-Reply-To: <CANbhV-EPrq9Z1gSHTeXVrxRHZq91=5VWDTBrbG=DaPn5oHe08A@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox