Re: Improvement discussion of custom and generic plans - Mailing list pgsql-hackers

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