Useful Postgres Extension: Pg_stat_statements

https://www.citusdata.com/blog/2019/02/08/the-most-useful-postgres-extension-pg-stat-statements/

by nzoschke • 7 years ago

141 4 7 years ago

noahth• 7 years ago

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

craigkerstiens• 7 years ago

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.

briffle• 7 years ago

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;

aboutruby• 7 years ago

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

Useful Postgres Extension: Pg_stat_statements

Related Stories