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

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.

Regards

Pavel

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