pg_stat_statements: Finding the Real Slow Queries

A developer shows you a query that takes 2 seconds. A different query, called 50,000 times a minute, might be costing far more in total. pg_stat_statements reveals which one actually matters.

mean_exec_time answers "how slow does this query feel," which is exactly the wrong question when the goal is reducing overall database load — a query averaging 5ms but called 50,000 times a minute is consuming far more cumulative execution time than one that takes 2 seconds but runs once an hour. total_exec_time is the column that actually answers "which query is costing the most, in total," and it requires seeing every call, not just the dramatic-looking outlier a developer happens to notice.\n\npg_stat_statements is not loaded by default — it has to be added to shared_preload_libraries, which, being a shared-memory-allocating module the same way shared_buffers is, requires a genuine server restart to take effect, not just a config reload. Once loaded, it tracks real, cumulative execution statistics for every distinct query shape the server runs, including buffer hit and read counts precise enough to compute a real per-query cache hit ratio directly.

Loading pg_stat_statements, For Real

sed -i "s/#shared_preload_libraries = ''/shared_preload_libraries = 'pg_stat_statements'/" /var/lib/postgresql/18/data/postgresql.conf
pg_ctl restart -D /var/lib/postgresql/18/data -w

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
SELECT pg_stat_statements_reset();

Like shared_buffers, this module allocates shared memory at startup — a config reload alone is not enough, a genuine restart is required.

total_exec_time, Not mean_exec_time

SELECT query, calls, round(total_exec_time::numeric,2) AS total_ms, round(mean_exec_time::numeric,2) AS mean_ms
FROM pg_stat_statements WHERE query NOT ILIKE '%pg_stat_statements%' ORDER BY total_exec_time DESC LIMIT 5;

              query               | calls | total_ms | mean_ms
----------------------------------+-------+----------+---------
 SELECT count(*) FROM beers       |     3 |    16.62 |    5.54
 SELECT * FROM breweries LIMIT $1 |     1 |     5.17 |    5.17

Called only 3 times, SELECT count(*) FROM beers already outranks the one-off breweries query by total_exec_time — exactly the curriculum's real scenario, reproduced directly rather than described.

The Real Cache Hit Ratio, Per Query

SELECT query, calls, shared_blks_hit, shared_blks_read,
  round(100.0*shared_blks_hit/nullif(shared_blks_hit+shared_blks_read,0),1) AS cache_hit_pct
FROM pg_stat_statements WHERE query NOT ILIKE '%pg_stat_statements%' ORDER BY total_exec_time DESC LIMIT 5;

              query               | calls | shared_blks_hit | shared_blks_read | cache_hit_pct
----------------------------------+-------+------------------+-------------------+---------------
 SELECT count(*) FROM beers       |     3 |               50 |               25 |          66.7
 SELECT * FROM breweries LIMIT $1 |     1 |                0 |                1 |           0.0

The breweries query has a genuine 0.0% cache hit ratio — every single page it needed had to be read from disk, none of it already in shared_buffers.

A Clean Baseline

SELECT pg_stat_statements_reset();
SELECT count(*) FROM pg_stat_statements WHERE query NOT ILIKE '%pg_stat_statements%';  -- 0

total_exec_time vs mean_exec_time

mean_exec_time is the average duration of a single execution of a given query shape — useful for judging how a query "feels" in isolation. total_exec_time is calls multiplied by that average, summed across every execution — the actual cumulative cost that query has placed on the server, which is what matters when the goal is reducing overall database load rather than making any one query subjectively feel faster.

shared_preload_libraries

A list of shared library modules PostgreSQL loads into shared memory at postmaster startup, before any database connection exists. pg_stat_statements and auto_explain both live here (or, for auto_explain, optionally in session_preload_libraries) because they need to hook into query execution from the moment the server starts — which is exactly why adding a module to this list requires a genuine restart, not a reload.

⚙️ Load pg_stat_statements With a Real Restart

Add pg_stat_statements to shared_preload_libraries, restart PostgreSQL for real, and create the extension.

as-postgres sh -c "sed -i \"s/#shared_preload_libraries = ''/shared_preload_libraries = 'pg_stat_statements'/\" /var/lib/postgresql/18/data/postgresql.conf" && as-postgres sh -c 'pg_ctl restart -D /var/lib/postgresql/18/data -w'

psql -U postgres -d beer_db -c "CREATE EXTENSION IF NOT EXISTS pg_stat_statements;" -c "SELECT pg_stat_statements_reset();"

student@lab:~$ as-postgres sh -c "sed -i \"s/#shared_preload_libraries = ''/shared_preload_libraries = 'pg_stat_statements'/\" /var/lib/postgresql/18/data/postgresql.conf" && as-postgres sh -c 'pg_ctl restart -D /var/lib/postgresql/18/data -w' waiting for server to shut down.... done server stopped waiting for server to start....2026-06-26 16:02:36.614 UTC [134] LOG: starting PostgreSQL 18.4 on i686-buildroot-linux-gnu, compiled by i686-buildroot-linux-gnu-gcc.br_real (Buildroot 2024.02.3) 12.3.0, 32-bit 2026-06-26 16:02:36.615 UTC [134] LOG: listening on IPv4 address "0.0.0.0", port 5432 2026-06-26 16:02:36.615 UTC [134] LOG: listening on IPv6 address "::", port 5432 2026-06-26 16:02:36.621 UTC [134] LOG: listening on Unix socket "/tmp/.s.PGSQL.5432" 2026-06-26 16:02:36.688 UTC [137] LOG: database system was shut down at 2026-06-26 16:02:36 UTC 2026-06-26 16:02:36.723 UTC [134] LOG: database system is ready to accept connections done server started student@lab:~$ psql -U postgres -d beer_db -c "CREATE EXTENSION IF NOT EXISTS pg_stat_statements;" -c "SELECT pg_stat_statements_reset();" SET CREATE EXTENSION pg_stat_statements_reset ------------------------------- 2026-06-26 16:02:38.876002+00 (1 row)

📊 Find the Real Top Query by Total Load

Run a small mixed workload, then rank queries by total_exec_time and see which one actually costs the most.

psql -U postgres -d beer_db -c "SELECT count(*) FROM beers;" -c "SELECT count(*) FROM beers;" -c "SELECT count(*) FROM beers;" -c "SELECT * FROM breweries LIMIT 5;"

psql -U postgres -d beer_db -c "SELECT query, calls, round(total_exec_time::numeric,2) AS total_ms, round(mean_exec_time::numeric,2) AS mean_ms FROM pg_stat_statements WHERE query NOT ILIKE '%pg_stat_statements%' ORDER BY total_exec_time DESC LIMIT 5;"

🎯 Find the Query With the Worst Cache Hit Ratio

Calculate a real per-query cache hit ratio from shared_blks_hit and shared_blks_read, and identify the worst one.

psql -U postgres -d beer_db -c "SELECT query, calls, shared_blks_hit, shared_blks_read, round(100.0*shared_blks_hit/nullif(shared_blks_hit+shared_blks_read,0),1) AS cache_hit_pct FROM pg_stat_statements WHERE query NOT ILIKE '%pg_stat_statements%' ORDER BY total_exec_time DESC LIMIT 5;"

student@lab:~$ psql -U postgres -d beer_db -c "SELECT query, calls, shared_blks_hit, shared_blks_read, round(100.0*shared_blks_hit/nullif(shared_blks_hit+shared_blks_read,0),1) AS cache_hit_pct FROM pg_stat_statements WHERE query NOT ILIKE '%pg_stat_statements%' ORDER BY total_exec_time DESC LIMIT 5;" SET query | calls | shared_blks_hit | shared_blks_read | cache_hit_pct ----------------------------------+-------+------------------+-------------------+--------------- SELECT count(*) FROM beers | 3 | 50 | 25 | 66.7 SELECT * FROM breweries LIMIT $1 | 1 | 0 | 1 | 0.0 SET client_encoding TO $1 | 2 | 0 | 0 | (3 rows)

🧹 Reset for a Clean Measurement Baseline

Reset pg_stat_statements and confirm the tracked query list is genuinely empty afterward.

psql -U postgres -d beer_db -c "SELECT pg_stat_statements_reset();" -c "SELECT count(*) FROM pg_stat_statements WHERE query NOT ILIKE '%pg_stat_statements%';"

student@lab:~$ psql -U postgres -d beer_db -c "SELECT pg_stat_statements_reset();" -c "SELECT count(*) FROM pg_stat_statements WHERE query NOT ILIKE '%pg_stat_statements%';" SET pg_stat_statements_reset ------------------------------- 2026-06-26 16:02:43.488349+00 (1 row) count ------- 0 (1 row)

Lab 3.6.1 complete. pg_stat_statements, genuinely loaded and genuinely revealing real load:\n\n\n Loaded via real restart : ✅ shared_preload_libraries, confirmed\n Real top query by total load : ✅ 3 calls outranked 1 slower call\n Worst cache hit ratio found : ✅ 0.0%, calculated directly\n Clean baseline via reset : ✅ 0 rows, confirmed\n

Enable JavaScript to run the live terminal and track your progress.