pg_stat_statements.md

How to identify my most expensive queries using pg_stat_statements

First enable the library for the server by adding the following line to postgresql.conf:

shared_preload_libraries = 'pg_stat_statements'

If you've installed postgresql with homebrew, the configuration file is at /usr/local/var/postgres/postgresql.conf

Then enable the extension for the databases that you're interested in with

CREATE EXTENSION pg_stat_statements;

Now you can go ahead and generate the load to the database, and see the stats with:

SELECT
  queryid,
  total_time,
  substring(query FROM 0 FOR 80),
  calls,
  total_time / calls AS avg
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 5;

A trick we've used to help identify longer queries is to prefix them with identifiers, like so:

const getMyLatestStatsQuery = `-- getMyLatestStatsQuery
select 1`

To reset the statistics, execute:

select pg_stat_statements_reset();

If you're unsure where your postgresql.conf is located you can run the following command to find out:

psql -d postgres -c 'SHOW config_file'

For me it was located in /opt/homebrew/var/postgres/postgresql.conf