Skip to main content

Posts

Showing posts from 2018

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_t…