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