
Useful Postgres Extension: Pg_stat_statements - nzoschke
https://www.citusdata.com/blog/2019/02/08/the-most-useful-postgres-extension-pg-stat-statements/
======
briffle
If this is something you want to start looking at, This script has been used
for about 90% of my database performance troubleshooting. I set my minimum
number of calls (the 500 below) and uncomment which column I want to query
based on.

time_per is most common, but rows_per can tell you when people have crappy
filters in their code (ie, no where clause) and total calls can let you focus
on the ones with the biggest improvement.

Basically a quick/dirty top 20 list of your slowest, or biggest, or hardest on
the cpu queries.

    
    
      SELECT query
      , calls
      , total_time
      , total_time / calls as time_per
      , stddev_time
      , rows
      , rows / calls as rows_per
      ,100.0 * shared_blks_hit / nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent
      FROM pg_stat_statements
      WHERE query not similar to '%pg_%'
      and calls > 500
      --ORDER BY calls
      --ORDER BY total_time
      order by time_per
      --ORDER BY rows_per
      DESC LIMIT 20;

------
noahth
This sounds great! I'm no expert but it seems that the postgres documentation
disagrees about the process for enabling the extension -- "The module must be
loaded by adding pg_stat_statements to shared_preload_libraries in
postgresql.conf, because it requires additional shared memory. This means that
a server restart is needed to add or remove the module."[1]

What I was really looking for was a ballpark estimate or guideline of what
additional resources a server would need to run this extension.

[1][https://www.postgresql.org/docs/11/pgstatstatements.html](https://www.postgresql.org/docs/11/pgstatstatements.html)

~~~
craigkerstiens
It admittedly does vary based on the way you installed Postgres. Not all, but
many installation methods already come with it in the
shared_preload_libraries. You still have to run create extension for it to
exist or it has to be enabled by the superuser. Postgres.app for example on
the mac already has it there but you still have to run create extension.

------
aboutruby
There are higher level interfaces on top of it like PoWA:
[https://powa.readthedocs.io/en/latest/index.html](https://powa.readthedocs.io/en/latest/index.html)

