Find Slow Queries With pg_stat_statements

"The database is slow" does not name a query. EXPLAIN helps when you already know which statement to look at. To find out which one, you need the history: which statements ran, how often, and how much time each took in total. The pg_stat_statements extension keeps exactly that, per normalized statement, and it is the first place to look on a server you do not know. It ships with PostgreSQL, but it needs one setting and a restart before it works.

Step 1: see the error you get without the setting

The commands use a container named shop-pg. If you do not have one, start it with docker run -d --name shop-pg -e POSTGRES_PASSWORD=demo postgres:18.6. On a fresh container, the extension can be created, and it still cannot be read.

docker exec shop-pg psql -U postgres -c "CREATE EXTENSION pg_stat_statements" -c "SELECT count(*) FROM pg_stat_statements"

CREATE EXTENSION
ERROR:  pg_stat_statements must be loaded via "shared_preload_libraries"

Step 2: load it at server start

The extension has to be in shared_preload_libraries, and that setting is read only when the server starts. ALTER SYSTEM writes it to the server's configuration, and the container needs a restart. The second command waits until the server accepts connections again.

docker exec shop-pg psql -U postgres -c "ALTER SYSTEM SET shared_preload_libraries = 'pg_stat_statements'"
docker restart shop-pg
docker exec shop-pg sh -c "until pg_isready -q; do sleep 1; done"
docker exec shop-pg psql -U postgres -At -c "SHOW shared_preload_libraries"

ALTER SYSTEM
shop-pg
pg_stat_statements

On a server you run yourself, put shared_preload_libraries = 'pg_stat_statements' in postgresql.conf and restart. A managed database service usually has a switch for it, so check its documentation.

Step 3: ask which statements cost the most

The view is per database, so create the extension in the database you want to watch. Reset the counters, then run a small workload: one heavy query three times, a medium one three times, and a cheap lookup three times with different ids.

CREATE EXTENSION pg_stat_statements;
SELECT pg_stat_statements_reset() IS NOT NULL AS reset_done;
SELECT count(*) FROM generate_series(1, 3000000) AS g WHERE g % 7 = 0;
SELECT count(*) FROM generate_series(1, 3000000) AS g WHERE g % 7 = 0;
SELECT count(*) FROM generate_series(1, 3000000) AS g WHERE g % 7 = 0;
SELECT max(g) FROM generate_series(1, 300000) AS g WHERE g % 5 = 0;
SELECT max(g) FROM generate_series(1, 300000) AS g WHERE g % 5 = 0;
SELECT max(g) FROM generate_series(1, 300000) AS g WHERE g % 5 = 0;
SELECT name FROM customers WHERE customer_id = 3;
SELECT name FROM customers WHERE customer_id = 5;
SELECT name FROM customers WHERE customer_id = 7;

CREATE EXTENSION
 reset_done
------------
 t
(1 row)

 count
--------
 428571
(1 row)

 count
--------
 428571
(1 row)

 count
--------
 428571
(1 row)

  max
--------
 300000
(1 row)

  max
--------
 300000
(1 row)

  max
--------
 300000
(1 row)

    name
------------
 Carla Diaz
(1 row)

   name
----------
 Ella Tan
(1 row)

    name
------------
 Gina Ortiz
(1 row)

Now read the view, most expensive first. total_exec_time is the time spent in a statement across all its calls, in milliseconds, and it is the number that finds the real cost: a fast query that runs a million times can cost more than a slow one that runs twice. The WHERE leaves out this query itself.

SELECT calls, rows, query
FROM pg_stat_statements
WHERE query NOT LIKE '%pg_stat_statements%'
ORDER BY total_exec_time DESC
LIMIT 3;

 calls | rows |                                query
-------+------+---------------------------------------------------------------------
     3 |    3 | SELECT count(*) FROM generate_series($1, $2) AS g WHERE g % $3 = $4
     3 |    3 | SELECT max(g) FROM generate_series($1, $2) AS g WHERE g % $3 = $4
     3 |    3 | SELECT name FROM customers WHERE customer_id = $1
(3 rows)

Three things to notice. The three lookups with ids 3, 5 and 7 are one row, because PostgreSQL replaces the constants with $1 and groups the calls. The first row is the heavy query, which counted up to 3,000,000, and the second is the medium one, which went up to 300,000, with a different function. The constants are hidden in the view, so you cannot tell the two apart by their text, and the order is the clue: the list is sorted by total time, so the most expensive statement comes first. The calls column tells you whether a cost comes from one slow statement or from one cheap statement run too often. The timing columns are left out here because they change from run to run. In your own database, add total_exec_time, mean_exec_time and calls to the select list.

Step 4: look at the plan of the top statement

Take the text of the top statement, put the real constants back, and run EXPLAIN (ANALYZE) on it, as in the reading-plans tutorial. The counters keep growing until you reset them. Reset after a change you want to measure, and compare before and after.

SELECT pg_stat_statements_reset() IS NOT NULL AS reset_done;

 reset_done
------------
 t
(1 row)

Related course: SQL Advanced.