Re: Sample rate added to pg_stat_statements

Hi hackers,

I've decided to explore a slightly different approach to reducing
spinlock contention—by introducing a simple execution time threshold. If
a query’s execution time exceeds this threshold, it is recorded in
pg_stat_statements; otherwise, it is ignored. As Alexander [0] pointed
out, this helps retain valuable queries for further analysis. A similar
mechanism is already present in auto_explain and pg_store_plans. When
pg_stat_statements.track_min_duration = -1, disable tracking. If
pg_stat_statements.track_min_duration = -1, all statements are tracked.

I benchmarked this approach using -M prepared -S on my machine with 48
CPUs. However, I couldn’t reproduce spinlock contention because the
machine isn’t large enough to create sufficient concurrency.
Nevertheless, I’m sharing my results for reference and checking correct
results of threshold.

Here’s the benchmarking procedure I followed:
    createdb pgbench
    pgbench -i -s 3000 pgbench
    psql -c 'SELECT pg_stat_statements_reset()'
    pgbench -c 46 -j 46 -T 120 -M prepared -S --progress=10 pgbench

select query, calls, min_exec_time, max_exec_time, mean_exec_time,
stddev_exec_time from pg_stat_statements where query = 'SELECT abalance
FROM pgbench_accounts WHERE aid = $1';

track_min_duration |     calls | min_exec_time | max_exec_time |      
mean_exec_time | stddev_exec_time
                 0 | 111282955 |       0.00365 |      15.56946 |
0.015042374707317802 | 0.06067634978916631
                 5 |       458 |       5.00627 |      15.699129 |   
5.962879746724887 | 1.1432124887616204
                10 |        14 |      10.538461 |      16.113204 |  
12.415218999999999 | 1.5598854455354354
                20 |         - |              - |              - |    
               - | -
                -1 |         - |              - |              - |    
               - | -

I’d greatly appreciate any feedback on this alternative approach, as
well as benchmarking on a pretty large machine to see its impact at scale.

[0]:
https://www.postgresql.org/message-id/CAPpHfdsTKAQqC3A48-MGQhrhfEamXZPb64w%3Dutk7thQcOMNr7Q%40mail.gmail.com

--
Best regards,
Ilia Evdokimov,
Tantor Labs LLC.