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
>
>