What is the performance impact of pg_stat_statements?

Question

I am using pg_stat_statements to find the slow queries in my production PostgreSQL 13. Two things:

• I am unsure of the performance impact of this extension.

• Is there anything I can do to improve its performance? The query below takes > 1s - any suggestions?

    -- add the plugin 
    create extension pg_stat_statements;
    select pg_stat_reset();
    select pg_stat_statements_reset();

and then:

-- find slow queries using the extension:

select * from pg_stat_statements order by total_exec_time desc limit 50;

Answer 1

From here:

• What are the performance implications of pganalyze?

The performance overhead of the pg_stat_statements extension is small

• it requires a bit of additional shared memory and will consume about ~1% of CPU time.

and here is a quote from a PostgreSQL contributor who says:

I always enable it on production databases. The benefits are well worth the small performance hit.

Particularly on a production database you want to know which statements cause the most pain and should be optimized.

From here (another big hitter in the PostgreSQL field):

If you use Postgres and you haven’t yet used pg_stat_statements, it is a must to add it to your toolbox.

This is old - two separate results:

With enabling pg_stat_statements module, the pgbench score was 468.2 tps. And without (loading) the pg_stat_statements module, pgbench score was 470.3 tps. The difference (overhead) was just 0.5%

     - Shared Buffers : 2048MB
     - Checkpoint Segments : 32
     - WAL Buffers : 1024kB
     - Pgbench: Scale factor 10, 1,000 Transactions * 32 Clients

Under certain (specialised IMHO) conditions, he found that there was a 10% peformance impact.

It strikes me that the first test was a much more "server-like" scenario.

Overall:

The fact that all of the bigger players in the PostgreSQL sphere (highly) recommend it and the fact that there are no queries either here or on StackOverflow (that I could find) of the sort: "I enabled pg_stat_statements" and now my server is running slowly", appears to augur well for the tool on that score.

Having said all of the above, there can be no substitute for benchmarking on your own system with your hardware, software and networking configuration. Initially, you could try switching it on and off during quiet times - but bear in mind that doing this requires a server restart!