Re: [PATCH] Query Jumbling for CALL and SET utility statements

st 31. 8. 2022 v 17:50 odesílatel Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
napsal:

> Hi
>
>
> st 31. 8. 2022 v 17:34 odesílatel Drouvot, Bertrand <bdrouvot(at)amazon(dot)com>
> napsal:
>
>> Hi hackers,
>>
>> While query jumbling is provided for function calls that’s currently not
>> the case for procedures calls.
>> The reason behind this is that all utility statements are currently
>> discarded for jumbling.
>>
>> We’ve recently seen performance impacts (LWLock contention) due to the
>> lack of jumbling on procedure calls with pg_stat_statements and
>> pg_stat_statements.track_utility enabled (think an application with a high
>> rate of procedure calls with unique parameters for each call).
>>
>> Jeremy has had this conversation on twitter (see
>> https://twitter.com/jer_s/status/1560003560116342785) and Nikolay
>> reported that he also had to work on a similar performance issue with SET
>> being used.
>>
>> That’s why we think it would make sense to allow jumbling for those 2
>> utility statements: CALL and SET.
>>
>> Please find attached a patch proposal for doing so.
>>
>> With the attached patch we would get things like:
>> CALL MINUS_TWO(3);
>> CALL MINUS_TWO(7);
>> CALL SUM_TWO(3, 8);
>> CALL SUM_TWO(7, 5);
>> set enable_seqscan=false;
>> set enable_seqscan=true;
>> set seq_page_cost=2.0;
>> set seq_page_cost=1.0;
>>
>> postgres=# SELECT query, calls, rows FROM pg_stat_statements;
>> query | calls | rows
>> -----------------------------------+-------+------
>> set seq_page_cost=$1 | 2 | 0
>> CALL MINUS_TWO($1) | 2 | 0
>> set enable_seqscan=$1 | 2 | 0
>> CALL SUM_TWO($1, $2) | 2 | 0
>>
>> Looking forward to your feedback,
>>
> The idea is good, but I think you should use pg_stat_functions instead.
> Maybe it is supported already (I didn't test it). I am not sure so SET
> statement should be traced in pg_stat_statements - it is usually pretty
> fast, and without context it says nothing. It looks like just overhead.
>

I was wrong - there is an analogy with SELECT fx, and the statistics are in
pg_stat_statements, and in pg_stat_function too.

Regards

Pavel

>
> Regards
>
> Pavel
>
>
>> Thanks,
>>
>> Jeremy & Bertrand
>>
>> --
>> Bertrand Drouvot
>> Amazon Web Services: https://aws.amazon.com
>>
>>