Postgres Query Stats using pg_stat_statements

Just wanted to share how to configure Postgres to capture query statistics using pg_stat_statements, including how often queries are run, how long they take to execute, along with a whole host of other useful information.

First, you need to edit the Postgres configuration, adding pg_stat_statements to preshared libraries

shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.track = all

After editing postgresql.conf, restart postgres. On Ubuntu

sudo /etc/init.d/postgresql stop
sudo /etc/init.d/postgresql start

After restarting Postgres, you need to enabled the pg_stat_statements extension. You can enable it per database, or for all databases on the same Postgres server by installing on the postgres database.

CREATE EXTENSION pg_stat_statements;

Once pg_stat_statements is added to the preshared libraries and the extension is enabled, you can query pg_stat_statements to get useful information about running queries.

SELECT
  query,
  sum(calls) AS calls,
  sum(total_time) AS total_time,
  sum(rows) as rows,
  sum(shared_blks_hit) AS shared_blks_hit,
  sum(shared_blks_read) AS shared_blks_read
FROM pg_stat_statements
GROUP BY 1
ORDER BY 3 desc

 Here's a sample of the data available

By default, Postgres will track approximately 5K distinct queries. Check the pg_stat_statements documentation for tuning these settings.

You can reset these settings via the pg_stat_statements_reset function

select pg_stat_statements_reset();

pg_stat_statments is a handy tool to quickly determine the most expensive queries, which can then the analyzed for potential optimizations. I highly recommend Tatiyants Postgres Query Plan Visualization.

pg_stat_statements is fairly low overhead, and should be safe to use in production. But your milage may vary.