public inbox for [email protected]
help / color / mirror / Atom feedFrom: Quan Zongliang <[email protected]>
To: [email protected]
Subject: Re: Improvement discussion of custom and generic plans
Date: Mon, 19 Feb 2024 16:05:03 +0800
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
<[email protected]>
Add the GUC parameter.
On 2024/1/30 21:25, Quan Zongliang wrote:
>
>
> On 2023/11/3 15:27, Quan Zongliang wrote:
>> Hi
>>
>> We have one such problem. A table field has skewed data. Statistics:
>> n_distinct | -0.4481973
>> most_common_vals | {5f006ca25b52ed78e457b150ee95a30c}
>> most_common_freqs | {0.5518474}
>>
>>
>> Data generation:
>>
>> CREATE TABLE s_user (
>> user_id varchar(32) NOT NULL,
>> corp_id varchar(32),
>> status int NOT NULL
>> );
>>
>> insert into s_user
>> select md5('user_id ' || a), md5('corp_id ' || a),
>> case random()<0.877675 when true then 1 else -1 end
>> FROM generate_series(1,10031) a;
>>
>> insert into s_user
>> select md5('user_id ' || a), md5('corp_id 10032'),
>> case random()<0.877675 when true then 1 else -1 end
>> FROM generate_series(10031,22383) a;
>>
>> CREATE INDEX s_user_corp_id_idx ON s_user USING btree (corp_id);
>>
>> analyze s_user;
>>
>>
>> 1. First, define a PREPARE statement
>> prepare stmt as select count(*) from s_user where status=1 and corp_id
>> = $1;
>>
>> 2. Run it five times. Choose the custom plan.
>> explain (analyze,buffers) execute
>> stmt('5f006ca25b52ed78e457b150ee95a30c');
>>
>> Here's the plan:
>> Aggregate (cost=639.84..639.85 rows=1 width=8) (actual
>> time=4.653..4.654 rows=1 loops=1)
>> Buffers: shared hit=277
>> -> Seq Scan on s_user (cost=0.00..612.76 rows=10830 width=0)
>> (actual time=1.402..3.747 rows=10836 loops=1)
>> Filter: ((status = 1) AND ((corp_id)::text =
>> '5f006ca25b52ed78e457b150ee95a30c'::text))
>> Rows Removed by Filter: 11548
>> Buffers: shared hit=277
>> Planning Time: 0.100 ms
>> Execution Time: 4.674 ms
>> (8 rows)
>>
>> 3.From the sixth time. Choose generic plan.
>> We can see that there is a huge deviation between the estimate and the
>> actual value:
>> Aggregate (cost=11.83..11.84 rows=1 width=8) (actual
>> time=4.424..4.425 rows=1 loops=1)
>> Buffers: shared hit=154 read=13
>> -> Bitmap Heap Scan on s_user (cost=4.30..11.82 rows=2 width=0)
>> (actual time=0.664..3.371 rows=10836 loops=1)
>> Recheck Cond: ((corp_id)::text = $1)
>> Filter: (status = 1)
>> Rows Removed by Filter: 1517
>> Heap Blocks: exact=154
>> Buffers: shared hit=154 read=13
>> -> Bitmap Index Scan on s_user_corp_id_idx
>> (cost=0.00..4.30 rows=2 width=0) (actual time=0.635..0.635 rows=12353
>> loops=1)
>> Index Cond: ((corp_id)::text = $1)
>> Buffers: shared read=13
>> Planning Time: 0.246 ms
>> Execution Time: 4.490 ms
>> (13 rows)
>>
>> This is because in the choose_custom_plan function, the generic plan
>> is attempted after executing the custom plan five times.
>>
>> if (plansource->num_custom_plans < 5)
>> return true;
>>
>> The generic plan uses var_eq_non_const to estimate the average
>> selectivity.
>>
>> These are facts that many people already know. So a brief introduction.
>>
>>
>> Our users actually use such parameter conditions in very complex
>> PREPARE statements. Once they use the generic plan for the sixth time.
>> The execution time will change from 5 milliseconds to 5 minutes.
>>
>>
>> To improve this problem. The following approaches can be considered:
>>
>> 1. Determine whether data skew exists in the PREPARE statement
>> parameter conditions based on the statistics.
>> However, there is no way to know if the user will use the skewed
>> parameter.
>>
>> 2.When comparing the cost of the generic plan with the average cost of
>> the custom plan(function choose_custom_plan). Consider whether the
>> maximum cost of a custom plan executed is an order of magnitude
>> different from the cost of a generic plan.
>> If the first five use a small selectivity condition. And after the
>> sixth use a high selectivity condition. Problems will still arise.
>>
>> 3.Trace the execution time of the PREPARE statement. When an execution
>> time is found to be much longer than the average execution time, the
>> custom plan is forced to run.
>>
>>
>> Is there any better idea?
>>
> I tried to do a demo. Add a member paramid to Const. When Const is
> generated by Param, the Const is identified as coming from Param. Then
> check in var_eq_const to see if the field in the condition using this
> parameter is skewed. If so, choose_custom_plan returns true every time,
> forcing custom_plan to be used.
> Only conditional expressions such as var eq param or param eq var can be
> supported.
> If it makes sense. Continue to improve this patch.
>
>> --
>> Quan Zongliang
>>
>>
diff --git a/src/backend/optimizer/path/costsize.c b/src/backend/optimizer/path/costsize.c
index 8b76e98529..14b8bec6ff 100644
--- a/src/backend/optimizer/path/costsize.c
+++ b/src/backend/optimizer/path/costsize.c
@@ -154,6 +154,8 @@ bool enable_partition_pruning = true;
bool enable_presorted_aggregate = true;
bool enable_async_append = true;
+double skewed_param_factor = DEFAULT_SKEWED_PARAM_FACTOR;
+
typedef struct
{
PlannerInfo *root;
diff --git a/src/backend/optimizer/util/clauses.c b/src/backend/optimizer/util/clauses.c
index edc25d712e..8b922c0c95 100644
--- a/src/backend/optimizer/util/clauses.c
+++ b/src/backend/optimizer/util/clauses.c
@@ -2493,6 +2493,8 @@ eval_const_expressions_mutator(Node *node,
pval,
prm->isnull,
typByVal);
+ if (paramLI->paramFetch == NULL)
+ con->paramid = param->paramid;
con->location = param->location;
return (Node *) con;
}
diff --git a/src/backend/utils/adt/selfuncs.c b/src/backend/utils/adt/selfuncs.c
index cea777e9d4..9fa6548d23 100644
--- a/src/backend/utils/adt/selfuncs.c
+++ b/src/backend/utils/adt/selfuncs.c
@@ -273,10 +273,32 @@ eqsel_internal(PG_FUNCTION_ARGS, bool negate)
* in the query.)
*/
if (IsA(other, Const))
- selec = var_eq_const(&vardata, operator, collation,
- ((Const *) other)->constvalue,
- ((Const *) other)->constisnull,
- varonleft, negate);
+ {
+ Const * con = (Const *) other;
+
+ if (con->paramid > 0)
+ {
+ bool skewed_stat = false;
+
+ selec = var_eq_const_ext(&vardata, operator, collation,
+ ((Const *) other)->constvalue,
+ ((Const *) other)->constisnull,
+ varonleft, negate, &skewed_stat);
+
+ if (skewed_stat)
+ {
+ ParamExternData *prm;
+
+ prm = &root->glob->boundParams->params[con->paramid - 1];
+ prm->pflags |= PARAM_FLAG_SKEWEDSTAT;
+ }
+ }
+ else
+ selec = var_eq_const(&vardata, operator, collation,
+ ((Const *) other)->constvalue,
+ ((Const *) other)->constisnull,
+ varonleft, negate);
+ }
else
selec = var_eq_non_const(&vardata, operator, collation, other,
varonleft, negate);
@@ -295,6 +317,16 @@ double
var_eq_const(VariableStatData *vardata, Oid oproid, Oid collation,
Datum constval, bool constisnull,
bool varonleft, bool negate)
+{
+ return var_eq_const_ext(vardata, oproid, collation,
+ constval, constisnull,
+ varonleft, negate, NULL);
+}
+
+double
+var_eq_const_ext(VariableStatData *vardata, Oid oproid, Oid collation,
+ Datum constval, bool constisnull,
+ bool varonleft, bool negate, bool *skewed_stat)
{
double selec;
double nullfrac = 0.0;
@@ -387,6 +419,39 @@ var_eq_const(VariableStatData *vardata, Oid oproid, Oid collation,
break;
}
}
+
+ Assert(sslot.nvalues > 0);
+ if (skewed_stat)
+ {
+ /* Check whether the values of the field is skewed according to the MCVs. */
+ if (sslot.nvalues > 1 &&
+ sslot.numbers[0] > sslot.numbers[sslot.nvalues - 1] * skewed_param_factor)
+ /* Compare the maximum and minimum quantities in the MCVs. */
+ *skewed_stat = true;
+ else
+ {
+ /*
+ * Compare the maximum number in the MCVs with the average number of
+ * other values(not in the MCVs).
+ */
+ int j;
+ double sumcommon = 0.0;
+ double otherdistinct, avgselec;
+
+ for (j = 0; j < sslot.nnumbers; j++)
+ sumcommon += sslot.numbers[j];
+
+ otherdistinct = get_variable_numdistinct(vardata, &isdefault) -
+ sslot.nnumbers;
+
+ if (otherdistinct > 0)
+ {
+ avgselec = (1.0 - sumcommon - nullfrac) / otherdistinct;
+ if (sslot.numbers[0] > (avgselec * skewed_param_factor))
+ *skewed_stat = true;
+ }
+ }
+ }
}
else
{
diff --git a/src/backend/utils/cache/plancache.c b/src/backend/utils/cache/plancache.c
index 5194cbf2cc..d1ee00139e 100644
--- a/src/backend/utils/cache/plancache.c
+++ b/src/backend/utils/cache/plancache.c
@@ -991,6 +991,20 @@ BuildCachedPlan(CachedPlanSource *plansource, List *qlist,
else
plan_context = CurrentMemoryContext;
+ if (boundParams)
+ {
+ int i;
+
+ for (i =0; i < boundParams->numParams; i++)
+ {
+ if (boundParams->params[i].pflags & PARAM_FLAG_SKEWEDSTAT)
+ {
+ plansource->hasSkewedparam = true;
+ break;
+ }
+ }
+ }
+
/*
* Create and fill the CachedPlan struct within the new context.
*/
@@ -1066,6 +1080,12 @@ choose_custom_plan(CachedPlanSource *plansource, ParamListInfo boundParams)
if (plan_cache_mode == PLAN_CACHE_MODE_FORCE_CUSTOM_PLAN)
return true;
+ if (plan_cache_mode == PLAN_CACHE_MODE_STAT_ADAPTIVE_PLAN)
+ {
+ if (plansource->hasSkewedparam)
+ return true;
+ }
+
/* See if caller wants to force the decision */
if (plansource->cursor_options & CURSOR_OPT_GENERIC_PLAN)
return false;
diff --git a/src/backend/utils/misc/guc_tables.c b/src/backend/utils/misc/guc_tables.c
index 70652f0a3f..93f85baedb 100644
--- a/src/backend/utils/misc/guc_tables.c
+++ b/src/backend/utils/misc/guc_tables.c
@@ -403,6 +403,7 @@ static const struct config_enum_entry debug_parallel_query_options[] = {
static const struct config_enum_entry plan_cache_mode_options[] = {
{"auto", PLAN_CACHE_MODE_AUTO, false},
+ {"stat_adaptive_plan", PLAN_CACHE_MODE_STAT_ADAPTIVE_PLAN, false},
{"force_generic_plan", PLAN_CACHE_MODE_FORCE_GENERIC_PLAN, false},
{"force_custom_plan", PLAN_CACHE_MODE_FORCE_CUSTOM_PLAN, false},
{NULL, 0, false}
@@ -3866,6 +3867,17 @@ struct config_real ConfigureNamesReal[] =
NULL, NULL, NULL
},
+ {
+ {"skewed_param_factor", PGC_SUSET, LOGGING_WHEN,
+ gettext_noop("Sets the skewed factor for parameters of the prepare statement."),
+ gettext_noop("Use a thredhold to determine whether a skewed parameter exists."
+ "If true, use the custom plan.")
+ },
+ &skewed_param_factor,
+ DEFAULT_SKEWED_PARAM_FACTOR, 2.0, 10000.0,
+ NULL, NULL, NULL
+ },
+
/* End-of-list marker */
{
{NULL, 0, 0, NULL, NULL}, NULL, 0.0, 0.0, 0.0, NULL, NULL, NULL
diff --git a/src/include/nodes/params.h b/src/include/nodes/params.h
index 10c9fc5413..051a12bb1d 100644
--- a/src/include/nodes/params.h
+++ b/src/include/nodes/params.h
@@ -85,7 +85,13 @@ struct ParseState;
* and paramCompileArg is rather arbitrary.
*/
-#define PARAM_FLAG_CONST 0x0001 /* parameter is constant */
+/* parameter is constant */
+#define PARAM_FLAG_CONST 0x0001
+/*
+ * The var side of 'var eq param' or 'param eq var' expression
+ * corresponding to the parameter contains skewed data.
+ */
+#define PARAM_FLAG_SKEWEDSTAT 0x0002
typedef struct ParamExternData
{
diff --git a/src/include/nodes/primnodes.h b/src/include/nodes/primnodes.h
index 4a154606d2..7e6762de47 100644
--- a/src/include/nodes/primnodes.h
+++ b/src/include/nodes/primnodes.h
@@ -314,6 +314,12 @@ typedef struct Const
*/
bool constbyval pg_node_attr(query_jumble_ignore);
+ /*
+ * >0 indicates that the constant is extracted from Param.
+ * Its value is paramid of the Param struct.
+ */
+ int paramid pg_node_attr(query_jumble_ignore);
+
/*
* token location, or -1 if unknown. All constants are tracked as
* locations in query jumbling, to be marked as parameters.
diff --git a/src/include/optimizer/cost.h b/src/include/optimizer/cost.h
index b1c51a4e70..3606e47681 100644
--- a/src/include/optimizer/cost.h
+++ b/src/include/optimizer/cost.h
@@ -28,6 +28,7 @@
#define DEFAULT_CPU_OPERATOR_COST 0.0025
#define DEFAULT_PARALLEL_TUPLE_COST 0.1
#define DEFAULT_PARALLEL_SETUP_COST 1000.0
+#define DEFAULT_SKEWED_PARAM_FACTOR 10.0
/* defaults for non-Cost parameters */
#define DEFAULT_RECURSIVE_WORKTABLE_FACTOR 10.0
@@ -71,6 +72,7 @@ extern PGDLLIMPORT bool enable_partition_pruning;
extern PGDLLIMPORT bool enable_presorted_aggregate;
extern PGDLLIMPORT bool enable_async_append;
extern PGDLLIMPORT int constraint_exclusion;
+extern PGDLLIMPORT double skewed_param_factor;
extern double index_pages_fetched(double tuples_fetched, BlockNumber pages,
double index_pages, PlannerInfo *root);
diff --git a/src/include/utils/plancache.h b/src/include/utils/plancache.h
index a90dfdf906..c742c6449c 100644
--- a/src/include/utils/plancache.h
+++ b/src/include/utils/plancache.h
@@ -30,6 +30,7 @@ struct RawStmt;
typedef enum
{
PLAN_CACHE_MODE_AUTO,
+ PLAN_CACHE_MODE_STAT_ADAPTIVE_PLAN,
PLAN_CACHE_MODE_FORCE_GENERIC_PLAN,
PLAN_CACHE_MODE_FORCE_CUSTOM_PLAN,
} PlanCacheMode;
@@ -124,6 +125,7 @@ typedef struct CachedPlanSource
bool is_complete; /* has CompleteCachedPlan been done? */
bool is_saved; /* has CachedPlanSource been "saved"? */
bool is_valid; /* is the query_list currently valid? */
+ bool hasSkewedparam; /* see the definition of PARAM FLAG SKEWEDSTAT */
int generation; /* increments each time we create a plan */
/* If CachedPlanSource has been saved, it is a member of a global list */
dlist_node node; /* list link, if is_saved */
diff --git a/src/include/utils/selfuncs.h b/src/include/utils/selfuncs.h
index 2fa4c4fc1b..a7532b8d7b 100644
--- a/src/include/utils/selfuncs.h
+++ b/src/include/utils/selfuncs.h
@@ -184,6 +184,10 @@ extern double var_eq_const(VariableStatData *vardata,
Oid oproid, Oid collation,
Datum constval, bool constisnull,
bool varonleft, bool negate);
+extern double var_eq_const_ext(VariableStatData *vardata,
+ Oid oproid, Oid collation,
+ Datum constval, bool constisnull,
+ bool varonleft, bool negate, bool *skewed_stat);
extern double var_eq_non_const(VariableStatData *vardata,
Oid oproid, Oid collation,
Node *other,
Attachments:
[text/plain] skewedparam-v2.patch (9.5K, ../[email protected]/2-skewedparam-v2.patch)
download | inline diff:
diff --git a/src/backend/optimizer/path/costsize.c b/src/backend/optimizer/path/costsize.c
index 8b76e98529..14b8bec6ff 100644
--- a/src/backend/optimizer/path/costsize.c
+++ b/src/backend/optimizer/path/costsize.c
@@ -154,6 +154,8 @@ bool enable_partition_pruning = true;
bool enable_presorted_aggregate = true;
bool enable_async_append = true;
+double skewed_param_factor = DEFAULT_SKEWED_PARAM_FACTOR;
+
typedef struct
{
PlannerInfo *root;
diff --git a/src/backend/optimizer/util/clauses.c b/src/backend/optimizer/util/clauses.c
index edc25d712e..8b922c0c95 100644
--- a/src/backend/optimizer/util/clauses.c
+++ b/src/backend/optimizer/util/clauses.c
@@ -2493,6 +2493,8 @@ eval_const_expressions_mutator(Node *node,
pval,
prm->isnull,
typByVal);
+ if (paramLI->paramFetch == NULL)
+ con->paramid = param->paramid;
con->location = param->location;
return (Node *) con;
}
diff --git a/src/backend/utils/adt/selfuncs.c b/src/backend/utils/adt/selfuncs.c
index cea777e9d4..9fa6548d23 100644
--- a/src/backend/utils/adt/selfuncs.c
+++ b/src/backend/utils/adt/selfuncs.c
@@ -273,10 +273,32 @@ eqsel_internal(PG_FUNCTION_ARGS, bool negate)
* in the query.)
*/
if (IsA(other, Const))
- selec = var_eq_const(&vardata, operator, collation,
- ((Const *) other)->constvalue,
- ((Const *) other)->constisnull,
- varonleft, negate);
+ {
+ Const * con = (Const *) other;
+
+ if (con->paramid > 0)
+ {
+ bool skewed_stat = false;
+
+ selec = var_eq_const_ext(&vardata, operator, collation,
+ ((Const *) other)->constvalue,
+ ((Const *) other)->constisnull,
+ varonleft, negate, &skewed_stat);
+
+ if (skewed_stat)
+ {
+ ParamExternData *prm;
+
+ prm = &root->glob->boundParams->params[con->paramid - 1];
+ prm->pflags |= PARAM_FLAG_SKEWEDSTAT;
+ }
+ }
+ else
+ selec = var_eq_const(&vardata, operator, collation,
+ ((Const *) other)->constvalue,
+ ((Const *) other)->constisnull,
+ varonleft, negate);
+ }
else
selec = var_eq_non_const(&vardata, operator, collation, other,
varonleft, negate);
@@ -295,6 +317,16 @@ double
var_eq_const(VariableStatData *vardata, Oid oproid, Oid collation,
Datum constval, bool constisnull,
bool varonleft, bool negate)
+{
+ return var_eq_const_ext(vardata, oproid, collation,
+ constval, constisnull,
+ varonleft, negate, NULL);
+}
+
+double
+var_eq_const_ext(VariableStatData *vardata, Oid oproid, Oid collation,
+ Datum constval, bool constisnull,
+ bool varonleft, bool negate, bool *skewed_stat)
{
double selec;
double nullfrac = 0.0;
@@ -387,6 +419,39 @@ var_eq_const(VariableStatData *vardata, Oid oproid, Oid collation,
break;
}
}
+
+ Assert(sslot.nvalues > 0);
+ if (skewed_stat)
+ {
+ /* Check whether the values of the field is skewed according to the MCVs. */
+ if (sslot.nvalues > 1 &&
+ sslot.numbers[0] > sslot.numbers[sslot.nvalues - 1] * skewed_param_factor)
+ /* Compare the maximum and minimum quantities in the MCVs. */
+ *skewed_stat = true;
+ else
+ {
+ /*
+ * Compare the maximum number in the MCVs with the average number of
+ * other values(not in the MCVs).
+ */
+ int j;
+ double sumcommon = 0.0;
+ double otherdistinct, avgselec;
+
+ for (j = 0; j < sslot.nnumbers; j++)
+ sumcommon += sslot.numbers[j];
+
+ otherdistinct = get_variable_numdistinct(vardata, &isdefault) -
+ sslot.nnumbers;
+
+ if (otherdistinct > 0)
+ {
+ avgselec = (1.0 - sumcommon - nullfrac) / otherdistinct;
+ if (sslot.numbers[0] > (avgselec * skewed_param_factor))
+ *skewed_stat = true;
+ }
+ }
+ }
}
else
{
diff --git a/src/backend/utils/cache/plancache.c b/src/backend/utils/cache/plancache.c
index 5194cbf2cc..d1ee00139e 100644
--- a/src/backend/utils/cache/plancache.c
+++ b/src/backend/utils/cache/plancache.c
@@ -991,6 +991,20 @@ BuildCachedPlan(CachedPlanSource *plansource, List *qlist,
else
plan_context = CurrentMemoryContext;
+ if (boundParams)
+ {
+ int i;
+
+ for (i =0; i < boundParams->numParams; i++)
+ {
+ if (boundParams->params[i].pflags & PARAM_FLAG_SKEWEDSTAT)
+ {
+ plansource->hasSkewedparam = true;
+ break;
+ }
+ }
+ }
+
/*
* Create and fill the CachedPlan struct within the new context.
*/
@@ -1066,6 +1080,12 @@ choose_custom_plan(CachedPlanSource *plansource, ParamListInfo boundParams)
if (plan_cache_mode == PLAN_CACHE_MODE_FORCE_CUSTOM_PLAN)
return true;
+ if (plan_cache_mode == PLAN_CACHE_MODE_STAT_ADAPTIVE_PLAN)
+ {
+ if (plansource->hasSkewedparam)
+ return true;
+ }
+
/* See if caller wants to force the decision */
if (plansource->cursor_options & CURSOR_OPT_GENERIC_PLAN)
return false;
diff --git a/src/backend/utils/misc/guc_tables.c b/src/backend/utils/misc/guc_tables.c
index 70652f0a3f..93f85baedb 100644
--- a/src/backend/utils/misc/guc_tables.c
+++ b/src/backend/utils/misc/guc_tables.c
@@ -403,6 +403,7 @@ static const struct config_enum_entry debug_parallel_query_options[] = {
static const struct config_enum_entry plan_cache_mode_options[] = {
{"auto", PLAN_CACHE_MODE_AUTO, false},
+ {"stat_adaptive_plan", PLAN_CACHE_MODE_STAT_ADAPTIVE_PLAN, false},
{"force_generic_plan", PLAN_CACHE_MODE_FORCE_GENERIC_PLAN, false},
{"force_custom_plan", PLAN_CACHE_MODE_FORCE_CUSTOM_PLAN, false},
{NULL, 0, false}
@@ -3866,6 +3867,17 @@ struct config_real ConfigureNamesReal[] =
NULL, NULL, NULL
},
+ {
+ {"skewed_param_factor", PGC_SUSET, LOGGING_WHEN,
+ gettext_noop("Sets the skewed factor for parameters of the prepare statement."),
+ gettext_noop("Use a thredhold to determine whether a skewed parameter exists."
+ "If true, use the custom plan.")
+ },
+ &skewed_param_factor,
+ DEFAULT_SKEWED_PARAM_FACTOR, 2.0, 10000.0,
+ NULL, NULL, NULL
+ },
+
/* End-of-list marker */
{
{NULL, 0, 0, NULL, NULL}, NULL, 0.0, 0.0, 0.0, NULL, NULL, NULL
diff --git a/src/include/nodes/params.h b/src/include/nodes/params.h
index 10c9fc5413..051a12bb1d 100644
--- a/src/include/nodes/params.h
+++ b/src/include/nodes/params.h
@@ -85,7 +85,13 @@ struct ParseState;
* and paramCompileArg is rather arbitrary.
*/
-#define PARAM_FLAG_CONST 0x0001 /* parameter is constant */
+/* parameter is constant */
+#define PARAM_FLAG_CONST 0x0001
+/*
+ * The var side of 'var eq param' or 'param eq var' expression
+ * corresponding to the parameter contains skewed data.
+ */
+#define PARAM_FLAG_SKEWEDSTAT 0x0002
typedef struct ParamExternData
{
diff --git a/src/include/nodes/primnodes.h b/src/include/nodes/primnodes.h
index 4a154606d2..7e6762de47 100644
--- a/src/include/nodes/primnodes.h
+++ b/src/include/nodes/primnodes.h
@@ -314,6 +314,12 @@ typedef struct Const
*/
bool constbyval pg_node_attr(query_jumble_ignore);
+ /*
+ * >0 indicates that the constant is extracted from Param.
+ * Its value is paramid of the Param struct.
+ */
+ int paramid pg_node_attr(query_jumble_ignore);
+
/*
* token location, or -1 if unknown. All constants are tracked as
* locations in query jumbling, to be marked as parameters.
diff --git a/src/include/optimizer/cost.h b/src/include/optimizer/cost.h
index b1c51a4e70..3606e47681 100644
--- a/src/include/optimizer/cost.h
+++ b/src/include/optimizer/cost.h
@@ -28,6 +28,7 @@
#define DEFAULT_CPU_OPERATOR_COST 0.0025
#define DEFAULT_PARALLEL_TUPLE_COST 0.1
#define DEFAULT_PARALLEL_SETUP_COST 1000.0
+#define DEFAULT_SKEWED_PARAM_FACTOR 10.0
/* defaults for non-Cost parameters */
#define DEFAULT_RECURSIVE_WORKTABLE_FACTOR 10.0
@@ -71,6 +72,7 @@ extern PGDLLIMPORT bool enable_partition_pruning;
extern PGDLLIMPORT bool enable_presorted_aggregate;
extern PGDLLIMPORT bool enable_async_append;
extern PGDLLIMPORT int constraint_exclusion;
+extern PGDLLIMPORT double skewed_param_factor;
extern double index_pages_fetched(double tuples_fetched, BlockNumber pages,
double index_pages, PlannerInfo *root);
diff --git a/src/include/utils/plancache.h b/src/include/utils/plancache.h
index a90dfdf906..c742c6449c 100644
--- a/src/include/utils/plancache.h
+++ b/src/include/utils/plancache.h
@@ -30,6 +30,7 @@ struct RawStmt;
typedef enum
{
PLAN_CACHE_MODE_AUTO,
+ PLAN_CACHE_MODE_STAT_ADAPTIVE_PLAN,
PLAN_CACHE_MODE_FORCE_GENERIC_PLAN,
PLAN_CACHE_MODE_FORCE_CUSTOM_PLAN,
} PlanCacheMode;
@@ -124,6 +125,7 @@ typedef struct CachedPlanSource
bool is_complete; /* has CompleteCachedPlan been done? */
bool is_saved; /* has CachedPlanSource been "saved"? */
bool is_valid; /* is the query_list currently valid? */
+ bool hasSkewedparam; /* see the definition of PARAM FLAG SKEWEDSTAT */
int generation; /* increments each time we create a plan */
/* If CachedPlanSource has been saved, it is a member of a global list */
dlist_node node; /* list link, if is_saved */
diff --git a/src/include/utils/selfuncs.h b/src/include/utils/selfuncs.h
index 2fa4c4fc1b..a7532b8d7b 100644
--- a/src/include/utils/selfuncs.h
+++ b/src/include/utils/selfuncs.h
@@ -184,6 +184,10 @@ extern double var_eq_const(VariableStatData *vardata,
Oid oproid, Oid collation,
Datum constval, bool constisnull,
bool varonleft, bool negate);
+extern double var_eq_const_ext(VariableStatData *vardata,
+ Oid oproid, Oid collation,
+ Datum constval, bool constisnull,
+ bool varonleft, bool negate, bool *skewed_stat);
extern double var_eq_non_const(VariableStatData *vardata,
Oid oproid, Oid collation,
Node *other,
view thread (2+ messages)
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]
Subject: Re: Improvement discussion of custom and generic plans
In-Reply-To: <[email protected]>
* 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