Re: minimizing pg_stat_statements performance overhead

Hello Raymond,

> Would it make sense to check for pgss_enabled in the post_parse_analyze_hook function?

Probably.

> -       /* Safety check... */
> -       if (!pgss || !pgss_hash)
> +       /* Safety check...and ensure that pgss is enabled before we do any work */
> +       if (!pgss || !pgss_hash || !pgss_enabled())
>                 return;
>
> **Simple Mini Benchmark**
> I ran a simple test on my local machine with this spec: 16 core/32 GB memory/Windows Server 2016.
> The simple query I used was 'select 1'.

> I called pg_stat_statements_reset() before each simple query to clear
> the pg_stat_statements query hash.

This sentence seems to suggest that reset is called before each 'select
1'? I assume it is before each test run.

> The majority of the latency happens the first time a query is run.

> Median runtime of 100 simple queries in milliseconds:
> PGSS loaded (ms) PGSS loaded + this patch (ms)
> track = top          0.53 0.55
> track = none       0.41 0.20
>
> PGSS not loaded: 0.18ms

This means 0.0018 ms latency per transaction, which seems rather fast, on
my laptop I have typically 0.0XX ms...

I could not reproduce these results on my ubuntu laptop. Could you be more
precise about the test? Did you use pgbench? Did it run in parallel? What
options were used? What is the test script?

I tried With "pgbench" on one thread on a local socket directory
connection on a 11.2 server:

sh> vi one.sql # SELECT 1;
sh> pgbench -n -T 100 -P 1 -M prepared -f one.sql

And I had the following latencies :

pgss not loaded: 0.026 ms
pgss top: 0.026/0.027 ms
pgss none: 0.027 ms

The effect is minimal. More precise per second analysis suggest a few
percent.

Ok, maybe my habit of -M prepared would hide some of the processing cost,
so:

sh> pgbench -n -T 100 -P 1 -f one.sql

pgss top: 0.035 ms
pgss none: 0.035 ms
pgss dropped but loaded: 0.035 ms
pgss not loaded: 0.032 ms

There I have an impact of 10% in these ideal testing conditions wrt
latency where the DB does basically nothing, thus which would not warrant
to disable pg_stat_statements given the great service this extension
brings to performance analysis.

Note that this does not mean that the patch should not be applied, it
looks like an oversight, but really I do not have the performance
degradation you are suggesting.

--
Fabien.