Useful Postgres Extension: Pg_stat_statements

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;

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

There are higher level interfaces on top of it like PoWA: https://powa.readthedocs.io/en/latest/index.html