Re: [PATCH] Query Jumbling for CALL and SET utility statements - Mailing list pgsql-hackers

Hi

st 31. 8. 2022 v 17:34 odesílatel Drouvot, Bertrand <bdrouvot@amazon.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