Re: Improvement discussion of custom and generic plans
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 @@ boolenable_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
Re: Improvement discussion of custom and generic plans
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/util/clauses.c
b/src/backend/optimizer/util/clauses.c
index 94eb56a1e7..3384520dc1 100644
--- a/src/backend/optimizer/util/clauses.c
+++ b/src/backend/optimizer/util/clauses.c
@@ -2489,6 +2489,8 @@ eval_const_expressions_mutator(Node *node,
pval,
prm->isnull,
typByVal);
+ if (paramLI->paramFetch
== NULL)
+ con-
Improvement discussion of custom and generic plans
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?
--
Quan Zongliang
