PostgreSQL Slow Query Triage with pg_stat_statements and EXPLAIN

What is the pg_stat_statements and EXPLAIN ANALYZE triage loop?

The loop is a fixed sequence you repeat until the workload is acceptable. You rank normalized statements in pg_stat_statements by total execution time and pick the top one. You reproduce it with EXPLAIN (ANALYZE, BUFFERS) using parameter values that match production traffic. In the plan, you find the node where estimated rows diverge most from actual rows. You make one change, then confirm in pg_stat_statements that the statement's cost dropped.

Copy   +--------------------------------------+
   | 1. Rank by total time                |
   |    (pg_stat_statements)              |
   +------------------+-------------------+
                      |
                      v
   +--------------------------------------+
   | 2. Reproduce: EXPLAIN (ANALYZE,      |
   |    BUFFERS), representative params   |
   +------------------+-------------------+
                      |
                      v
   +--------------------------------------+
   | 3. Find estimate-vs-actual gaps      |
   +------------------+-------------------+
                      |
                      v
   +--------------------------------------+
   | 4. Make ONE change                   |
   +------------------+-------------------+
                      |
                      v
   +--------------------------------------+
   | 5. Verify in pg_stat_statements      |---> back to 1
   +--------------------------------------+

Total time is the ranking key because it equals per-call latency multiplied by call count. A 5 ms query that runs constantly can cost more than a 10-second report that runs nightly. Ad hoc tuning usually begins with whichever slow query someone noticed in a log or a complaint, which is rarely the most expensive one. Ranking by total time sends effort to the queries where a fix removes the most aggregate load.

Making one change per pass lets you attribute the result. If you add an index, raise work_mem, and rewrite a join all at once, you can't tell which change helped or which one caused a regression elsewhere.

This workflow is for backend and platform engineers who own database performance and need a repeatable process.

Copy+-------------------------------+        +-------------------------------+
| 1. RANK                       |        | 2. REPRODUCE                  |
| pg_stat_statements            |------->| EXPLAIN (ANALYZE, BUFFERS)    |
| order by total time           |        | on representative params      |
| (mean latency x calls)        |        |                               |
| pick the top statement        |        |                               |
+-------------------------------+        +-------------------------------+
               ^                                        |
               |                                        v
+-------------------------------+        +-------------------------------+
| 5. VERIFY                     |        | 3. DIAGNOSE                   |
| compare total time, calls,    |        | find the node where estimated |
| mean time for that statement  |        | rows diverge most from actual |
+-------------------------------+        +-------------------------------+
               ^                                        |
               |        +-----------------------+       |
               +--------| 4. CHANGE ONE THING   |<------+
                        | index, stats, rewrite |
                        +-----------------------+

The triage loop: rank statements by total time in pg_stat_statements, reproduce the top one with EXPLAIN (ANALYZE, BUFFERS), find the largest gap between estimated and actual rows, make one change, verify in pg_stat_statements, and repeat.

Who should use this triage loop?

Use it if you are responsible for how a PostgreSQL database performs under real application traffic and you can change at least one of the schema, the SQL, or the server configuration. In practice that usually means:

• Backend engineers who write the queries and can rewrite SQL or ORM calls once they know which statement is expensive.
• Platform, SRE and DBA staff who own postgresql.conf, can add pg_stat_statements to shared_preload_libraries, and schedule the restart it requires.
• On-call engineers who need a starting point when database CPU, I/O or latency rises and nobody yet knows which query is responsible.

You need enough privilege to create the extension and to read other users' query text in the view (a superuser or a member of pg_read_all_stats), and access to a database whose data volume and distribution resemble production. Without that, the plans you reproduce may not match the plans production gets.

The loop is less useful when the problem is not query execution at all, such as connection pool exhaustion or network latency. The last section covers how to recognize that case.

How does it work under the hood?

The loop combines three PostgreSQL mechanisms.

pg_stat_statements aggregates execution by statement shape. The library hooks into query processing. After the parser analyzes a statement, it computes a queryid from the structure of the parse tree, ignoring constant values. That is why WHERE id = 7 and WHERE id = 9 land in the same row, and why the stored text shows $1 instead of a literal. Each tracked entry, keyed by user, database and queryid, lives in shared memory and accumulates counters every time a matching statement finishes: calls, execution time, rows, and block counters such as shared blocks hit and read. Query texts are kept in a file outside shared memory, so long statements do not consume the fixed shared memory allocation. The number of entries is capped by pg_stat_statements.max, and pg_stat_statements.track controls whether only top-level statements or also statements nested inside functions are counted. Because the library must allocate shared memory at startup, it has to be preloaded, which is why a restart is required.

The planner chooses plans from statistics. ANALYZE, whether run by hand or by autovacuum, samples each table and stores per-column statistics: most common values and their frequencies, histograms, null fractions and distinct-value estimates. You can read them in the pg_stats view. The planner uses these to estimate how many rows each node will produce, then picks scan methods, join methods, join order and memory use based on those estimates. When the statistics are stale or cannot describe the data, for example with correlated columns, the estimates are wrong and so is the plan.

EXPLAIN ANALYZE exposes the gap. EXPLAIN alone prints the chosen plan with estimated costs and row counts. With ANALYZE, PostgreSQL runs the plan with instrumentation on every node, recording actual rows, loops and (unless TIMING OFF) elapsed time. BUFFERS adds per-node buffer counters. Placing estimated and actual rows side by side shows exactly where the planner's model of the data broke down.

Put together, pg_stat_statements tells you which statement to look at, EXPLAIN (ANALYZE, BUFFERS) tells you where in its plan the time goes and why the planner chose that plan, and returning to pg_stat_statements tells you whether the fix held up under real traffic.

How do you enable pg_stat_statements and rank slow queries?

pg_stat_statements must be loaded when the server starts, so add it to shared_preload_libraries in postgresql.conf and restart. A reload will not pick it up. Then run CREATE EXTENSION pg_stat_statements; in the database you want to query. The extension creates the view, and the preloaded library collects the data. Before you write any queries against the view, run \d pg_stat_statements in psql. Column names have changed between releases (timing columns in particular), and copying a query written for another version will fail or read the wrong field.

Rank by total execution time first, then mean time and calls. Total time measures how much server capacity a statement consumes. A 2 ms lookup called millions of times a day often costs more than a 10-second nightly report, and it is usually easier to fix. Use mean time to separate inherently slow statements from frequently called ones.

The view stores normalized text, with literals replaced by $1, $2, and so on. To get realistic values for testing, use application logs, auto_explain output, or your knowledge of skewed columns. A plan for a rare value can differ completely from a plan for a common one.

Keep these limits in mind:

• Overhead: Collection costs are small but not zero.
• Eviction: pg_stat_statements.max caps how many statements are tracked, and the least-used entries are evicted when the cap is reached.
• Cumulative counters: Stats accumulate until reset, so a regression from yesterday can be diluted by months of good history. Reset the stats or compare snapshots over time.
• Variance: Check stddev and max columns where your version has them. A modest mean can hide occasional multi-second outliers.

Copy-- 1. In postgresql.conf (restart required; a reload is not enough):
--      shared_preload_libraries = 'pg_stat_statements'
--      pg_stat_statements.max = 5000   -- tracked statements before eviction

-- 2. In the database you want to inspect:
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- 3. Confirm column names in psql before relying on them:
--      \d pg_stat_statements
--    PostgreSQL 13+ uses *_exec_time. Older releases use
--    total_time, mean_time, stddev_time and max_time.

-- 4. Rank by total cost first, then mean time and calls.
SELECT
    round(total_exec_time::numeric, 0)   AS total_ms,
    calls,
    round(mean_exec_time::numeric, 2)    AS mean_ms,
    round(stddev_exec_time::numeric, 2)  AS stddev_ms,  -- high relative to mean = erratic plans or skewed data
    round(max_exec_time::numeric, 2)     AS max_ms,     -- outliers that the mean hides
    rows,
    left(query, 80)                      AS query       -- normalized text with $1, $2 placeholders
FROM pg_stat_statements
ORDER BY total_exec_time DESC, mean_exec_time DESC, calls DESC
LIMIT 20;

-- 5. Counters are cumulative since the last reset. To spot a recent
--    regression, snapshot the view or reset and re-measure:
-- SELECT pg_stat_statements_reset();

This example enables pg_stat_statements, then ranks normalized statements by total execution time, with mean, stddev and max shown next to it so you can spot variance that the mean hides. It uses PostgreSQL 13+ column names.

How do you run EXPLAIN (ANALYZE, BUFFERS) safely?

Plain EXPLAIN only plans the statement. Adding ANALYZE actually executes it, and that is the only way to get real row counts and timings. For INSERT, UPDATE and DELETE, wrap the statement in BEGIN and ROLLBACK so the changes are discarded. Rollback does not undo everything, though. Triggers still fire, sequence values consumed by nextval are not returned, and row locks are held until the transaction ends.

Run the statement at least twice. The first run may read from disk. Later runs often hit shared buffers or the OS page cache. The BUFFERS output separates shared hit from read, so you can tell whether a slow first run was caused by I/O or by the plan itself. Decide which case matters for your workload; a report query that runs once per night is usually cold.

On production, keep these risks in mind:

• Resource use: a slow query stays slow when you explain it. It consumes CPU, I/O and possibly temp files for its full runtime.
• Locks: a write takes the same locks it would take normally, and can block other sessions.
• Timing overhead: per-node timing reads the clock on every row. Nodes with many loops, such as the inner side of a nested loop, can appear inflated. TIMING OFF keeps row counts and drops per-node timing.

Plans depend on parameter values and data distribution. A staging database with less or more uniformly distributed data can produce a completely different plan. Use the real slow parameter values from production. For prepared statements, the planner may switch from custom plans to a generic plan after several executions. Run EXPLAIN EXECUTE on the prepared statement to see the plan your application actually gets, because it can differ from the plan for a query with literal values.

Copy-- 1. Explain a write without keeping its changes.
--    Triggers still fire, nextval() values are consumed,
--    and row locks are held until ROLLBACK.
BEGIN;
SET LOCAL statement_timeout = '30s';  -- stop a runaway query
SET LOCAL lock_timeout = '2s';        -- give up instead of queueing behind locks
EXPLAIN (ANALYZE, BUFFERS)
UPDATE orders
   SET status = 'archived'
 WHERE customer_id = 4821
   AND created_at < now() - interval '1 year';
ROLLBACK;

-- 2. Run the same block again. Compare 'shared hit' and 'read' in the
--    Buffers lines: a cold run shows reads, a warm run mostly hits.

-- 3. Keep row counts but skip per-node clock reads, which can inflate
--    nodes with many loops (e.g. the inner side of a nested loop).
EXPLAIN (ANALYZE, BUFFERS, TIMING OFF)
SELECT o.id, i.sku
  FROM orders o
  JOIN order_items i ON i.order_id = o.id
 WHERE o.customer_id = 4821;

-- 4. Check the plan the application gets from a prepared statement.
PREPARE orders_by_customer(bigint) AS
  SELECT id, total FROM orders WHERE customer_id = $1;

-- Use the parameter value that was slow in production.
EXPLAIN (ANALYZE, BUFFERS) EXECUTE orders_by_customer(4821);

-- Force a generic plan (PostgreSQL 12+) to compare it with the custom plan above.
SET plan_cache_mode = force_generic_plan;
EXPLAIN (ANALYZE, BUFFERS) EXECUTE orders_by_customer(4821);
RESET plan_cache_mode;
DEALLOCATE orders_by_customer;

Shows how to run EXPLAIN (ANALYZE, BUFFERS) on a write inside BEGIN/ROLLBACK with timeouts, repeat it to compare cold and warm buffer reads, turn off per-node timing, and compare custom and generic plans for a prepared statement.

How do you read a PostgreSQL query plan for estimate-versus-actual gaps?

Read the plan from the most-indented nodes outward, because that is where rows originate and how execution actually flows. At each node, compare the estimated rows= with the actual rows=. A gap of an order of magnitude or more is the thing to chase. The planner chose join order, join method and memory sizing from that estimate, so an underestimate deep in the tree usually causes the slow Nested Loop or the disk spill you notice higher up. Fix the estimate and the symptom often disappears.

Actual time and rows are averaged per loop, so multiply them by loops before judging a node.

Illustrative estimate (assumed numbers, not a benchmark): Assume an Index Scan shows actual time=0.02 rows=3 loops=50000. Total time ≈ per-loop time × loops = 0.02 ms × 50,000 = 1,000 ms. Total rows ≈ rows × loops = 3 × 50,000 = 150,000. The node looks cheap, but it accounts for a full second.

In the Buffers: line, shared hit counts pages already in shared buffers and read counts pages fetched from the OS. Reads can still come from the OS page cache, so they are not necessarily physical disk reads. A high read count explains why a cold run is slower than a warm one.

Copy-- 1. Capture the real plan with buffer counts. ANALYZE executes the query.
EXPLAIN (ANALYZE, BUFFERS)
SELECT o.id, c.name
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'pending' AND o.region = 'eu-west';

-- Illustrative output (assumed numbers, trimmed, not a benchmark):
-- Nested Loop  (cost=0.85..412.30 rows=40 width=40) (actual time=0.05..1210.40 rows=50000 loops=1)
--   Buffers: shared hit=151200 read=3800
--   ->  Index Scan using orders_status_idx on orders o  (cost=0.42..95.10 rows=40 width=12) (actual time=0.03..85.10 rows=50000 loops=1)
--         Index Cond: (status = 'pending'::text)
--         Filter: (region = 'eu-west'::text)
--         Rows Removed by Filter: 120000
--   ->  Index Scan using customers_pkey on customers c  (cost=0.42..8.44 rows=1 width=36) (actual time=0.02..0.02 rows=1 loops=50000)
--         Index Cond: (id = o.customer_id)
--
-- Start at the innermost nodes. The orders scan estimated 40 rows but
-- returned 50000. That is the root cause.
-- The customers scan is the symptom: 0.02 ms x 50000 loops = about 1000 ms.

-- 2. Fix the estimate rather than forcing a join method.
-- status and region are correlated, so the planner multiplies their
-- selectivities and underestimates. Extended statistics record the dependency.
CREATE STATISTICS orders_status_region_dep (dependencies)
  ON status, region FROM orders;
ANALYZE orders;
-- Re-run step 1. The estimate should move toward the actual row count.

-- 3. If a Sort shows 'external merge  Disk:' or a Hash shows Batches > 1,
-- test a larger work_mem for this transaction only.
BEGIN;
SET LOCAL work_mem = '64MB';
EXPLAIN (ANALYZE, BUFFERS)
SELECT customer_id, count(*) FROM orders GROUP BY customer_id ORDER BY 2 DESC;
ROLLBACK;

This example reads an EXPLAIN (ANALYZE, BUFFERS) plan from the inside out. It traces a slow Nested Loop back to a row underestimate on correlated columns, fixes the estimate with extended statistics, and tests a disk spill with a transaction-scoped work_mem change.

Why isn't PostgreSQL using my index?

A sequential scan in the plan does not always mean an index is missing. Often a usable index exists, but the query is written in a form the index cannot serve, or the planner has correctly decided that the index would be slower.

To find indexes that are never used, select schemaname, relname, indexrelname and idx_scan from pg_stat_user_indexes where idx_scan = 0. Statistics are tracked per server and are cleared when counters are reset, so check replicas as well and confirm the observation window is long enough. Do not drop indexes that back unique or primary-key constraints.

Unused indexes are not free. Every INSERT, and every UPDATE that cannot be done as a HOT update, writes to each index on the table, which multiplies WAL volume. Each index also uses disk and cache space and adds work to every vacuum pass. On a write-heavy table, an index that speeds up one report can slow down every write.

Should you fix statistics, indexes, or work_mem first?

Start with the estimates. When the planner misjudges row counts, it can choose the wrong join order, the wrong join method, or a sequential scan. A new index will not fix that, because the planner may still decline to use it based on bad numbers.

Check pg_stat_user_tables for the last analyze time, then run ANALYZE on stale tables. If one column has a skewed distribution, raise its statistics target with ALTER TABLE ... ALTER COLUMN ... SET STATISTICS and analyze again. When filters combine correlated columns, such as city and postal code, the planner multiplies their selectivities as if they were independent and underestimates the result. CREATE STATISTICS tells it the columns are related.

Avoid raising work_mem globally. The limit applies to each sort or hash operation, not to each query. A single plan can contain several such nodes, every connection can run one of these plans, and parallel workers each get their own allowance. Raise it per session or per transaction with SET LOCAL, or per role with ALTER ROLE ... SET for a reporting user.

Make one change at a time and re-run EXPLAIN (ANALYZE, BUFFERS) after each. If you change two things together, you cannot tell which one helped, or which one caused a regression elsewhere.

How can this loop save engineering time?

Most time lost in query tuning goes to working on the wrong statement, guessing at causes, and untangling changes that were made together. The loop addresses each of these.

It picks the target for you. Ranking by total execution time replaces debate about which complaint to chase. The view already holds aggregated history, so you do not need to build log-parsing scripts or reproduce load just to find the expensive statements.

Illustrative estimate (assumed inputs, not a measurement): Assume statement A runs 2,000,000 times a day at a mean of 4 ms, and statement B is a nightly report that runs once and takes 30 seconds.

• A consumes 2,000,000 × 4 ms = 8,000 s, about 2.2 hours of execution time per day.
• B consumes 30 s per day.
• Cutting A's mean to 1 ms saves 2,000,000 × 3 ms = 6,000 s per day. Eliminating B entirely saves 30 s.

Under these assumptions, an engineer who starts with the report because it "feels slow" spends the same afternoon for a small fraction of the benefit. The ranking query makes that comparison visible in one result set.

It replaces guessing with a located cause. The estimate-versus-actual comparison points to a specific node and usually a specific reason, such as stale statistics, correlated columns, or a predicate the index cannot serve. That narrows the fix to one of a few known options in the tables above, rather than trial-and-error index creation.

It prefers cheap fixes first. Starting with statistics means many problems are resolved with ANALYZE or CREATE STATISTICS, which need no application deploy and add no ongoing write cost. Index additions and query rewrites, which need review and deployment, are reserved for cases where estimates are already accurate.

It avoids rework. One change per pass means a regression can be traced to a single change and reverted, instead of bisecting a bundle of edits. Verifying against pg_stat_statements after deploy catches fixes that only looked good on a warm cache, before they get closed out and reopened later.

It ends cleanly. The stopping rule in the next section tells you when the remaining latency is outside the database, so you stop tuning SQL that is no longer the bottleneck and move to the layer that is.

How do you verify the fix and when should you stop tuning SQL?

A single fast EXPLAIN ANALYZE proves little. The second run often hits a warm cache, and it uses one set of parameter values that may not match what production sends. Verify against real traffic instead.

Snapshot, deploy, compare

Before the change, either call pg_stat_statements_reset() or copy the current rows into a scratch table so you have a baseline. Deploy the fix, let it run through a representative load window, then compare the same queryid on calls, total execution time, and mean execution time. Total time shows whether the database is doing less work overall. Mean time shows whether individual calls got faster. A lower mean with a much higher call count can still mean more total load. Prefer a snapshot over a reset on shared systems, because a reset wipes the baseline for everyone else.

Optional: auto_explain

To capture slow plans without running them by hand, load the auto_explain module and set auto_explain.log_min_duration to a threshold such as a few hundred milliseconds. Plans for statements over that threshold go to the server log. Enable auto_explain.log_analyze with care, because it adds timing overhead to every statement it instruments, not just the slow ones.

When to stop

Stop tuning SQL when the plan looks fine but users still see latency. Lock waits, connection pool queuing, and network round trips never show up in a plan. Check wait_event_type and wait_event in pg_stat_activity to spot lock and I/O waits. Then compare application-side latency metrics against database execution time. A large gap between the two means the time is being spent outside the query.

How OneEnv can help

Disclosure: OneEnv is the product behind this blog.

OneEnv (oneenv.io) is a workspace-based governance platform for the things services share — environment variables, database structure, API routes and validation rules. A change to a shared value is staged on a branch, reviewed once, and then every affected service explicitly approves or rejects it before it goes live, so you can see who a change touches before it ships instead of finding out in production.

Applied to postgresql slow query pg_stat_statements, this is where it helps:

Saves time

• Every service that inherits a shared value is listed in one place, so nobody searches repos and chats to find out who is affected.
• One review per branch replaces coordinating each team separately; each service still keeps its own approve/reject.
• Changing a shared variable is one edit and one review instead of editing every service's .env by hand.

Saves cost

• Fewer production incidents and rollbacks caused by a shared value, route or column changing under a consumer unannounced.
• Less duplicated configuration to maintain: one shared definition inherited by many services.
• A wrong or stale value is caught at review time by the services it affects, not by a failed deploy.
• OneEnv has a free plan (1 workspace, unlimited services and members, full governance), so trying it costs nothing.

These are qualitative benefits, not measured results — how much you save depends on how many services share the value.

Want to try it? Start at oneenv.io.