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