Which slow query should you investigate first with pg_stat_statements?

This workflow is for backend and platform engineers who already know the database is slow and need to decide where to spend the next hour. Start it before opening any query plan. Without a ranking, you end up tuning whichever query someone complained about most recently.

pg_stat_statements groups queries by their normalized form. Literal values become placeholders, so thousands of calls that differ only by ID count as one entry. Sort those entries two ways:

  • By total execution time. This shows where the server spends its time overall. A 4 ms query called 2 million times a day costs far more than a 3-second report that runs twice. Cutting the cheap, frequent query in half returns more capacity, so total time usually points to the bigger win.
  • By mean execution time. This shows queries that hurt individual requests, such as a slow page load or an API call close to its timeout. These matter for latency even when their share of total load is small.

The column names for these timings have changed between major versions. Check the view definition on your server instead of copying a query from elsewhere.

There are trade-offs. The extension has to be listed in shared_preload_libraries, and changing that setting requires a server restart. After the restart you run CREATE EXTENSION in each database where you want to query the view. Tracking adds some overhead. Normalization also hides detail: a query that is fast for most tenants but slow for one large customer can look healthy because the slow calls are averaged in with the fast ones. Compare the maximum and standard deviation columns against the mean to find these cases.

The payoff is focus. When you see that a handful of entries account for most of the execution time, you can stop working on queries that don't affect the system.

-- Prerequisite in postgresql.conf (requires a server restart):
--   shared_preload_libraries = 'pg_stat_statements'
-- Then run once in each database where you want to query the view:
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- Column names below are for PostgreSQL 13 and later.
-- Older versions use total_time / mean_time. Check with: \d pg_stat_statements

-- 1. Where does the server spend its time overall? Usually the bigger win.
SELECT queryid,
       calls,
       round(total_exec_time::numeric, 0) AS total_ms,
       round(mean_exec_time::numeric, 2)  AS mean_ms,
       round((100 * total_exec_time / sum(total_exec_time) OVER ())::numeric, 1) AS pct_of_total,
       left(query, 80) AS query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

-- 2. Which queries hurt individual requests? Compare max and stddev to mean:
--    a max far above the mean suggests some parameter values are slow
--    and are hidden inside the normalized average.
SELECT queryid,
       calls,
       round(mean_exec_time::numeric, 2)   AS mean_ms,
       round(stddev_exec_time::numeric, 2) AS stddev_ms,
       round(max_exec_time::numeric, 2)    AS max_ms,
       left(query, 80) AS query
FROM pg_stat_statements
WHERE calls > 50  -- skip one-off statements whose mean is noise
ORDER BY mean_exec_time DESC
LIMIT 10;

Enables pg_stat_statements and ranks normalized queries first by total execution time, then by mean time, with max and stddev shown so you can spot queries that are slow only for certain parameter values.

What is the difference between EXPLAIN and EXPLAIN ANALYZE in Postgres?

Once you have picked a target query, the next step is to see how Postgres runs it. There are two ways to ask, and they answer different questions.

Plain EXPLAIN runs only the planner. It shows the plan Postgres would choose, with estimated startup and total cost, estimated row counts, and estimated row width. Nothing executes, so it returns almost instantly and is safe to run on any statement. EXPLAIN ANALYZE executes the statement in full, throws away the result rows, and adds actual time, actual rows, and loop counts to every node. You need both views because slow plans usually come from the gap between what the planner expected and what happened.

EXPLAIN EXPLAIN ANALYZE
Executes the query No Yes
Reports Estimated cost, rows, width Estimates plus actual time, rows, loops
Runtime Planning only Full execution time, plus instrumentation overhead
Side effects None Writes are applied

Because it executes the query, a query that takes 40 seconds will take 40 seconds again. An UPDATE or DELETE really modifies rows, so wrap it in BEGIN and ROLLBACK. A rollback does not undo everything: sequence increments stay, and triggers with external effects still fire.

Per-node timing reads the system clock repeatedly, and on some hardware this inflates the total noticeably. TIMING OFF keeps actual row counts but drops the per-node timings.

Run the analysis where the problem occurs. A laptop or staging database with less data, stale statistics, or different memory and cost settings can produce a completely different plan.

-- 1. Planner only: nothing executes, returns estimates (cost, rows, width).
EXPLAIN
SELECT o.id, o.total
FROM orders o
WHERE o.customer_id = 4821
  AND o.created_at >= now() - interval '30 days';

-- 2. Execute the query and report actual time, rows, and loops per node.
--    This runs the full query, so a slow query will be slow again.
EXPLAIN ANALYZE
SELECT o.id, o.total
FROM orders o
WHERE o.customer_id = 4821
  AND o.created_at >= now() - interval '30 days';

-- 3. Analyze a write without keeping its changes.
--    Sequence increments and external trigger effects are NOT undone.
BEGIN;
EXPLAIN ANALYZE
UPDATE orders
SET status = 'archived'
WHERE created_at < now() - interval '2 years';
ROLLBACK;

-- 4. Keep actual row counts but skip per-node clock reads
--    when timing overhead distorts the result.
EXPLAIN (ANALYZE, TIMING OFF)
SELECT o.id, o.total
FROM orders o
WHERE o.customer_id = 4821
  AND o.created_at >= now() - interval '30 days';

The same query examined with plain EXPLAIN, with EXPLAIN ANALYZE, inside BEGIN/ROLLBACK for a write, and with TIMING OFF to reduce instrumentation overhead.

How does EXPLAIN ANALYZE work?

EXPLAIN ANALYZE goes through the same stages as a normal query. The statement is parsed and rewritten, the planner picks a plan from its cost model and table statistics, and the executor runs that plan. The difference is that the executor runs with instrumentation turned on for every plan node.

While the query runs, each node keeps counters:

  • Rows: how many rows the node returned to its parent.
  • Loops: how many times the node was started. A node on the inner side of a nested loop is restarted once per outer row, so its counters are later reported as per-loop averages.
  • Timing: the time to the first row (startup) and to the last row (total), measured by reading the clock as rows pass through the node. These clock reads are the source of the overhead that TIMING OFF removes.
  • Buffers (with BUFFERS): shared, local, and temp page accesses attributed to the node and its children.

When execution finishes, Postgres prints the plan tree with estimates and actuals side by side, followed by planning time and execution time for the whole statement. Time spent in triggers is listed separately. The result rows are discarded instead of being sent to the client, so the reported execution time does not include network transfer or client-side processing. A query that looks fast here but is slow in the application may be spending its time returning a large result set, not executing.

The plan you see is the plan chosen for the literal values you typed. An application that uses prepared statements can end up with a generic plan that does not depend on parameter values, and that plan may differ from the one EXPLAIN ANALYZE shows for a specific value.

Who should use EXPLAIN ANALYZE?

EXPLAIN ANALYZE is for anyone who has a specific query to investigate and can run it against realistic data:

  • Backend engineers who own the queries their services issue and need to know why one endpoint is slow.
  • DBAs and platform engineers who take the top entries from pg_stat_statements and need to explain them, or who review migrations and new indexes before rollout.
  • Engineers reviewing a query change who want to confirm that a rewrite or a new index changes the plan the way they expected.

It requires permission to run the statement, access to a database whose data volume and statistics resemble production, and parameter values that reproduce the slow case. If you don't know which query is slow yet, start with pg_stat_statements as described above, not with EXPLAIN ANALYZE.

When should you avoid EXPLAIN ANALYZE?

Because EXPLAIN ANALYZE executes the statement, there are situations where you should use plain EXPLAIN or another tool instead:

  • Long-running queries on a busy production server. Rerunning a query that takes minutes adds the same load again. Run it on a replica or a restored copy of production, or start with plain EXPLAIN to see the plan shape.
  • Writes you cannot fully roll back. BEGIN/ROLLBACK does not undo sequence increments or side effects of triggers that call external systems. An UPDATE or DELETE inside the transaction also holds row locks until the rollback, which can block application traffic on a live system.
  • Problems caused by waiting, not by the plan. If a query is slow because it waits on a lock, EXPLAIN ANALYZE run at a quiet moment will look fast and tell you nothing. Check pg_stat_activity wait events and pg_locks while the problem is happening.
  • Intermittent slowness you cannot reproduce. If the slow case depends on parameter values, cache state, or concurrent load you can't recreate, one manual run will probably show the fast case. Capture the plan at the moment it is slow instead (see the alternatives below).
  • When timing overhead dominates. On queries that process very many rows through cheap nodes, per-node clock reads can distort the result. Use TIMING OFF to keep row counts, or rely on the total execution time.

What are the alternatives to EXPLAIN ANALYZE?

Several built-in tools cover the cases where a manual EXPLAIN ANALYZE doesn't fit:

Tool What it gives you When to use it instead
Plain EXPLAIN Estimated plan, no execution The query is too slow, too risky, or too expensive to run
pg_stat_statements Aggregated time, calls, and buffer counts per normalized query Finding which query to investigate, and confirming a fix holds under real traffic
auto_explain module Logs plans of statements that exceed auto_explain.log_min_duration, optionally with actual values via auto_explain.log_analyze Intermittent slowness or parameter-dependent plans that only appear under production traffic
log_min_duration_statement Logs statement text and duration above a threshold Capturing the exact slow statements and parameter values to reproduce later
pg_stat_activity and pg_locks Live view of running queries, their wait events, and held locks The query is slow because it is waiting, not because of its plan
pg_stat_user_tables and pg_stat_user_indexes Per-table sequential and index scan counts, per-index usage Spotting tables that are scanned sequentially often, or indexes that are never used

auto_explain with log_analyze enabled instruments every statement it covers, not only the slow ones, so it has a cost on a busy server. Disabling auto_explain.log_timing reduces that cost while keeping row counts. These tools complement EXPLAIN ANALYZE: they help you capture or locate the problem, and the node-by-node reading below is still how you diagnose it.

How does a Postgres query plan tree work?

A plan is a tree, and Postgres prints it top-down, but you read it inside-out. The most deeply indented nodes are the leaves, usually scans that read tables or indexes. Each line marked with an arrow is a child of the nearest less-indented node above it. Parents pull rows from their children on demand, so data flows upward: scans feed joins, joins feed sorts or aggregates, and the top node returns rows to the client. A parent's actual time includes the time spent in its children. To find where time is actually spent, subtract each child's total from its parent's.

Every node carries two sets of numbers:

  • Estimates: cost=startup..total, rows, and width. Cost is in the planner's abstract units, not milliseconds. Startup cost is the work done before the first row can be emitted, which is high for sorts and hash builds.
  • Actuals: actual time=startup..total in milliseconds, rows, and loops.

The detail that misleads most people is that actual time and actual rows are per-loop averages. A node showing 0.05 ms and 3 rows with loops=20000 cost about one second and produced 60,000 rows. This matters most on the inner side of a nested loop, where a cheap-looking index scan can run thousands of times.

That leads to the method this article uses. Don't scan the plan for "Seq Scan" and assume it is the culprit. Walk the tree node by node and compare estimated rows against actual rows multiplied by loops. Where those numbers diverge sharply, the planner was working from a wrong picture of the data. Every choice above that node, including join method, join order, and memory sizing, was built on that error.

-- Illustrative plan: hand-written to show how to read the tree, not a measured result.
EXPLAIN (ANALYZE, BUFFERS)
SELECT o.id, li.sku
FROM orders o
JOIN line_items li ON li.order_id = o.id
WHERE o.status = 'pending';

/*
Nested Loop  (cost=0.43..2981.00 rows=600 width=16) (actual time=0.031..1085.200 rows=60000 loops=1)
  ->  Seq Scan on orders o  (cost=0.00..2137.00 rows=200 width=8) (actual time=0.012..48.300 rows=20000 loops=1)
        Filter: (status = 'pending'::text)
        Rows Removed by Filter: 80000
  ->  Index Scan using line_items_order_id_idx on line_items li  (cost=0.43..4.17 rows=3 width=16) (actual time=0.045..0.050 rows=3 loops=20000)
        Index Cond: (order_id = o.id)

Read inside-out. The two arrowed nodes are children of Nested Loop.

Seq Scan (outer, leaf):
  estimated rows=200, actual rows=20000 x loops=1 = 20000
  A 100x underestimate. This is the root cause.

Index Scan (inner, leaf): figures are per-loop averages, so multiply by loops.
  time: 0.050 ms x 20000 loops = ~1000 ms
  rows: 3 x 20000 loops = 60000
  The per-loop estimate (rows=3) is accurate. The node is not the bug,
  it simply ran 100x more often than the planner expected.

Nested Loop (parent): its total time includes both children.
  self time = 1085.2 - 48.3 - ~1000 = ~37 ms
  The planner chose a nested loop because it expected about 200 outer rows.
  With 20000 outer rows, that choice is wrong.

Next step: find out why status = 'pending' was misestimated, for example
stale statistics or skewed values. Do not start by changing the scan type.
*/

An annotated, illustrative nested-loop plan showing how to multiply per-loop time and rows by loops, subtract child time from the parent, and trace the problem to the node where estimated and actual rows diverge.

How do you use BUFFERS and estimate errors to find the real bottleneck?

Two signals help you find which node is the real bottleneck: buffer counts and the gap between estimated and actual rows.

Buffers: where the time actually went

Adding BUFFERS to EXPLAIN ANALYZE reports page counts per node. shared hit counts pages found in shared buffers. shared read counts pages requested from outside them, either from the OS cache or from disk. temp read/written means a sort or hash spilled to disk.

Pattern Likely meaning
High time, mostly hits CPU-bound: too many rows processed, or expensive per-row work
High time, many reads I/O-bound, or a cold cache
Temp pages present work_mem too small for this sort or hash

A single run can mislead in both directions. The first execution after a restart reads everything and looks I/O-bound. The second hits a warm cache and hides an access pattern that will hurt under production memory pressure. Run the query several times and compare. With track_io_timing enabled, you can also see how long reads took.

Estimate errors: why the plan was chosen

Compare rows (estimated) with actual rows. A gap of several orders of magnitude is the usual cause of a bad plan, and it has a small set of causes:

  • Stale statistics: the table changed since the last ANALYZE. Run ANALYZE on the table.
  • Skewed distributions: the default sample misses them. Raise the column's statistics target with ALTER TABLE ... ALTER COLUMN ... SET STATISTICS, then run ANALYZE again.
  • Correlated columns (such as city and postal code): the planner multiplies their selectivities as if the columns were independent. CREATE STATISTICS with dependencies or mcv captures the relationship.
  • Functions on columns (such as lower(email)): the planner falls back to default selectivity guesses. An expression index makes ANALYZE gather statistics on the expression.
-- Show read timings in BUFFERS output (session-level; needs superuser or granted privilege)
SET track_io_timing = on;

-- Run several times: first run may be cold-cache, later runs warm
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM customers
WHERE city = 'Springfield' AND postal_code = '62704';
-- In each node, compare:
--   Buffers: shared hit=... read=...   (reads = outside shared buffers)
--   rows=... (estimate) vs actual ... rows=...

-- Fix 1: refresh stale statistics
ANALYZE customers;

-- Fix 2: larger sample for a skewed column (default target is 100)
ALTER TABLE customers ALTER COLUMN city SET STATISTICS 1000;
ANALYZE customers;

-- Fix 3: teach the planner that city and postal_code are correlated
CREATE STATISTICS customers_city_postal (dependencies, mcv)
  ON city, postal_code FROM customers;
ANALYZE customers;  -- extended statistics are populated by ANALYZE

-- Fix 4: an expression index gets its own statistics for lower(email)
CREATE INDEX customers_lower_email_idx ON customers (lower(email));
ANALYZE customers;

-- Re-check: estimated rows should now be close to actual rows
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM customers
WHERE city = 'Springfield' AND postal_code = '62704';

Running EXPLAIN with ANALYZE and BUFFERS, then fixing row-estimate errors with ANALYZE, a higher per-column statistics target, extended statistics for correlated columns, and an expression index for a function on a column.

When is a seq scan correct, and when does it mean a missing or unusable index?

A seq scan is often the correct plan. On a table that fits in a few pages, reading every page costs less than walking an index and then fetching heap pages. When a predicate matches a large share of rows, an index scan becomes many random heap fetches, and reading the table sequentially wins. Before you add an index, check rows against the table's total row count. If the query returns most of the table, the seq scan is right.

A seq scan is a problem when a Filter line discards most of the rows the scan read. That means an index could have avoided the work, if one existed and the planner could use it.

Cause Why the index is unusable Fix
No index on the column Nothing to use B-tree on the filtered column
Type mismatch (int_col = 42.0) The column is cast to numeric and no longer matches the index Pass parameters of the column's type
Function on column (lower(email)) The index stores raw values, not function results Expression index on lower(email)
LIKE '%foo' A B-tree can't seek without a known prefix Trigram GIN index (pg_trgm)
Index on (a, b), query filters on b Entries are ordered by a first Index with b leading, or a separate index

Every index has a cost. Each INSERT and each non-HOT UPDATE has to maintain it. It uses disk and cache, and vacuum has to process it. An index on a low-selectivity column such as a status flag may never be chosen. Confirm the planner uses a new index with EXPLAIN ANALYZE before you keep it.

Why do nested loops, hash joins, and sorts go wrong?

A nested loop is cheap when the outer side returns a few rows, because the inner side runs once per outer row. The planner picks it from its row estimate. If a node shows rows=1 estimated but thousands actual, the inner index scan shows a loops count in the thousands, and total time is per-loop time multiplied by that count. A hash join would have scanned the inner table once. The cause is usually stale statistics or correlated predicates that the planner treats as independent.

Sorts and hashes go wrong for a different reason: they run out of memory. A sort that exceeds work_mem shows Sort Method: external merge with a Disk: figure. A hash join that does not fit shows Batches greater than 1, which means it partitioned its input and wrote batches to temporary files.

Raising work_mem globally is risky because the limit applies to each sort or hash operation, not to each connection. A single query can run several such operations at once, and parallel workers each get their own allowance. Multiply that by hundreds of connections and the server can run out of memory.

Approach What it fixes Limit
SET work_mem per session One known heavy query Each session must opt in
ALTER ROLE ... SET work_mem A reporting or batch role Every query from that role gets it
enable_nestloop = off and other enable_* settings Shows whether a different plan is faster Hides the bad estimate behind the choice
Planner hint extensions Pins a plan shape The pinned plan can become wrong as data grows

Use enable_* settings and hints to test a theory, then fix the statistics or the schema that caused the bad plan.

Worked example: from pg_stat_statements entry to verified fix

Illustrative example: the table names, row counts and timings below are assumed for teaching purposes, not measured results.

Sorting pg_stat_statements by total execution time puts an order lookup at the top. Its mean time is a modest 180 ms, but it runs thousands of times an hour, so it costs more in aggregate than any rare slow report. That makes it the right query to investigate first.

EXPLAIN (ANALYZE, BUFFERS) shows a Nested Loop. Its outer side scans orders filtered on status = 'pending' AND region = 'eu', and the planner estimated 40 rows where 38,000 actually came back. The inner Index Scan on order_items looks cheap at 0.05 ms per execution, but it reports loops=38000. Multiplied out, that is about 1.9 seconds and hundreds of thousands of buffer touches. This node is the bottleneck. The 1,000× row misestimate on the outer side is the root cause, because the planner assumed status and region were independent when in practice they are correlated.

The fix is to create extended statistics on (status, region) and run ANALYZE orders. The planner now estimates roughly 36,000 rows and switches to a Hash Join.

To verify, run the new plan several times and compare runs at the same cache state, not a cold run against a warm one. Check shared read versus shared hit in the BUFFERS output to confirm the comparison is fair. Afterward, reset or snapshot pg_stat_statements so you can confirm the gain holds under production traffic.

Repeatable checklist

  1. Rank queries by total time, not mean time.
  2. Capture EXPLAIN (ANALYZE, BUFFERS).
  3. Multiply per-loop time and rows by loops.
  4. Find the first node where estimated and actual rows diverge.
  5. Fix statistics or the schema, not the symptom.
  6. Re-measure at an equal cache state.

Each step narrows the search using evidence from the plan. This replaces cycles of adding an index, rerunning and guessing with a single targeted change.

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 explain analyze postgres, 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.
  • Reviewers see the exact diff and the list of affected services up front, so approval is a decision rather than an investigation.

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.
  • Per-service approve/reject means a bad shared value can be refused where it would break something, before it is live.
  • 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.