What is it? #
When a query is slow, guessing is expensive and unnecessary. PostgreSQL will tell you exactly what it did.
EXPLAIN shows the plan — the strategy PostgreSQL intends to use. EXPLAIN ANALYZE actually runs the query and shows the plan annotated with what really happened: real timings and real row counts.
The single most useful skill is comparing estimated rows against actual rows. When PostgreSQL expects 10 rows and gets 400,000, it almost certainly chose the wrong strategy, and that mismatch is usually the root cause rather than the symptom.
Everything else is learning to recognise a few plan node types and knowing what each one implies.
Think of it like this #
You ask someone to fetch twelve files.
EXPLAIN is them telling you the plan first: "I'll go through every drawer in order." EXPLAIN ANALYZE is them doing it and reporting back: "I planned to check about 10 files, but there were 400,000, and it took seven minutes."
That gap between expectation and reality is the important part. If they had known there were 400,000, they would have used the card index instead of opening every drawer — so the fix is usually to correct what they know, or to give them a better index, not to ask them to walk faster.
Simple example #
A dashboard query that took 40 milliseconds in testing now takes 9 seconds in production.
Nothing in the query changed. What changed is the amount of data, and a plan that was reasonable for 5,000 rows is a disaster for 5,000,000. EXPLAIN ANALYZE shows precisely where the time goes.
Code #
-- ---------- The three forms you will use ----------
EXPLAIN SELECT * FROM orders WHERE customer_id = 42;
-- Plan only. Does NOT run the query. Safe on anything, including UPDATE/DELETE.
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 42;
-- ACTUALLY RUNS the query and reports real timings.
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM orders WHERE customer_id = 42;
-- BUFFERS also shows how much data came from cache vs disk. Very useful.---------- !! CAUTION with EXPLAIN ANALYZE !! ----------
EXPLAIN ANALYZE genuinely EXECUTES the statement.
On a SELECT that is harmless. On an UPDATE or DELETE, THE CHANGE HAPPENS.
To inspect a write safely, wrap it in a transaction and roll back:
BEGIN;
EXPLAIN ANALYZE DELETE FROM orders WHERE placed_at < '2020-01-01';
ROLLBACK; -- nothing was actually deleted
Use plain EXPLAIN (no ANALYZE) if you only need the plan.---------- Reading a plan ----------
Seq Scan on orders (cost=0.00..89234.00 rows=12 width=48)
(actual time=0.31..412.80 rows=12 loops=1)
│ │ │
cost=startup..total ────┘ │ │
rows= PostgreSQL's ESTIMATE │ │
actual time=first row..last row (milliseconds) ┘ │
rows= what REALLY came back ──────────────────────────┘
loops= how many times this node ran
Plans are TREES, read INSIDE-OUT and BOTTOM-UP.
The most indented lines run first and feed their parent.---------- The scan types, and what each means ----------
Seq Scan Reads EVERY row in the table.
Fine on small tables, or when returning most of the rows.
A problem when returning a few rows from a big table.
Index Scan Walks the index, then fetches each matching row from the table.
Best when returning a SMALL fraction of rows.
Index Only Scan Answered entirely from the index — the table is never touched.
Fastest. Happens when the index contains every column needed.
Bitmap Heap Scan Collects matching locations from the index first, sorts them,
+ Bitmap Index Scan then reads the table in physical order.
PostgreSQL's choice for a MEDIUM number of matches — too many
for scattered single lookups, too few to read the whole table.---------- Join types ----------
Nested Loop For each row on one side, look up matches on the other.
Excellent when one side is small. Catastrophic when both are large.
Hash Join Builds a hash table from the smaller side, then probes it.
The usual choice for joining two large tables on equality.
Merge Join Both sides sorted, then walked together.
Good when the inputs are ALREADY sorted (e.g. by an index).-- ---------- A slow query, diagnosed step by step ----------
-- The query: revenue per city for completed orders this year
EXPLAIN (ANALYZE, BUFFERS)
SELECT c.city, sum(o.total)
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'completed'
AND o.placed_at >= '2026-01-01'
GROUP BY c.city;---------- STEP 1: read the output ----------
HashAggregate (actual time=8914.2..8914.9 rows=38 loops=1)
-> Hash Join (actual time=412.1..8203.7 rows=1204118 loops=1)
Hash Cond: (o.customer_id = c.id)
-> Seq Scan on orders o (cost=0.00..189234.00 rows=6021 width=24)
(actual time=0.04..6122.8 rows=1204118 loops=1)
Filter: ((status = 'completed') AND (placed_at >= '2026-01-01'))
Rows Removed by Filter: 3795882
-> Hash (actual time=410.9..410.9 rows=50000 loops=1)
-> Seq Scan on customers c (actual time=0.01..201.3 rows=50000 loops=1)
Planning Time: 0.３ ms
Execution Time: 8915.4 ms
---------- STEP 2: find the problems ----------
(a) Seq Scan on orders: estimated rows=6021, ACTUAL rows=1204118
-> a 200x underestimate. The planner is working from bad information.
(b) "Rows Removed by Filter: 3795882"
-> it read 5 million rows and threw away 3.8 million. That is the 6.1 seconds.
(c) No index is being used for status/placed_at at all.-- ---------- STEP 3: fix the estimate first ----------
ANALYZE orders; -- refresh the planner's statistics about this table
-- Bad estimates often come from stale statistics. Always check this BEFORE
-- adding indexes, because a wrong estimate can make the planner ignore a
-- perfectly good index you just built.
-- ---------- STEP 4: give it the index the filter needs ----------
CREATE INDEX CONCURRENTLY orders_completed_placed_idx
ON orders (placed_at)
WHERE status = 'completed';
-- Partial: only completed orders are indexed, matching the query exactly.
-- ---------- STEP 5: measure again ----------
EXPLAIN (ANALYZE, BUFFERS) SELECT ... ; -- same query
-- HashAggregate (actual time=402.1..402.8 rows=38 loops=1)
-- -> Hash Join (actual time=88.2..344.1 rows=1204118 loops=1)
-- -> Bitmap Heap Scan on orders (actual time=44.1..201.7 rows=1204118)
-- Recheck Cond: (placed_at >= '2026-01-01')
-- -> Bitmap Index Scan on orders_completed_placed_idx
-- (actual time=41.2..41.2 rows=1204118)
-- Execution Time: 403.1 ms <-- 8915ms -> 403ms-- ---------- Finding WHICH queries to optimise ----------
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
-- (also needs shared_preload_libraries = 'pg_stat_statements' + a restart)
SELECT round(total_exec_time::numeric, 0) AS total_ms,
calls,
round(mean_exec_time::numeric, 2) AS avg_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;
-- Optimise by TOTAL time, not average. A 20ms query run 500,000 times costs
-- far more than a 5-second report that runs twice a day.-- ---------- Currently running queries ----------
SELECT pid,
now() - query_start AS running_for,
state,
left(query, 100) AS query
FROM pg_stat_activity
WHERE state != 'idle'
AND query NOT LIKE '%pg_stat_activity%'
ORDER BY query_start;
-- To stop one (ask first if it is not yours):
-- SELECT pg_cancel_backend(pid); -- polite: asks the query to stop
-- SELECT pg_terminate_backend(pid); -- forceful: kills the whole connectionHow it works #
PostgreSQL's planner considers several possible strategies for a query and estimates the cost of each, then runs the cheapest. Costs are in arbitrary units, not milliseconds — their only purpose is comparing plans against one another.
Those estimates come from statistics: samples of each column's distribution collected by ANALYZE and kept current by autovacuum. This is why stale statistics cause bad plans. If PostgreSQL believes a filter matches 6,000 rows when it really matches 1.2 million, it will happily pick a nested loop that is correct but thousands of times slower than the alternative. Always check statistics before adding indexes — a fresh ANALYZE sometimes fixes a "slow query" outright, and a bad estimate can cause the planner to ignore an index you have just created.
That is what makes the estimated-versus-actual comparison so valuable. It is not merely a symptom; it usually points straight at the cause.
A sequential scan is not automatically bad. Reading a whole small table is cheaper than index indirection, and when a query returns most of a table's rows, a sequential scan is genuinely the fastest option. What matters is the ratio: returning 12 rows out of 5 million via a sequential scan is the problem, and Rows Removed by Filter quantifies exactly how much wasted reading is happening.
An Index Only Scan is the best outcome. It occurs when the index contains every column the query needs, so the table itself is never read. Adding a column with INCLUDE can sometimes turn an Index Scan into an Index Only Scan.
A Bitmap Heap Scan is PostgreSQL choosing a middle path — too many matches for efficient one-at-a-time lookups, too few to justify reading everything. It gathers the locations first, sorts them, and reads the table in physical order to avoid random I/O. Seeing one is normal and usually healthy.
Among joins, a Nested Loop with a large row count on both sides is the classic pathology, and it is almost always the downstream consequence of an underestimate: the planner thought one side had 5 rows, so a loop looked cheap. Fixing the estimate fixes the join choice.
loops= matters when reading nested loop plans. A node showing actual time=0.8 with loops=200000 did not take 0.8 milliseconds — it took 0.8 milliseconds per loop, which is 160 seconds. Multiply before drawing conclusions.
BUFFERS distinguishes "slow because it read a lot of data from disk" from "slow because it did a lot of computation", which point at different fixes.
Real-world use #
Optimise by total time, not by how slow a query feels. pg_stat_statements ranks queries by cumulative cost, and the winner is very often a fast query run enormously often rather than the report everyone complains about. Fixing a 20ms query called half a million times a day frees far more capacity than fixing a 5-second nightly job.
The most common real-world causes of a slow query, roughly in order: a missing index on a filter or join column; stale statistics producing a bad plan; an unnecessary SELECT * pulling large columns; a function wrapped around an indexed column preventing index use; a large OFFSET; and fan-out from a join multiplying rows before aggregation.
Test on realistic data volumes. A plan that is optimal for 5,000 rows is frequently wrong for 5,000,000, which is exactly why queries that pass testing fall over in production. If you cannot copy production data, generate a comparable volume.
Be careful with EXPLAIN ANALYZE on writes — it executes the statement. Wrap it in BEGIN; ... ROLLBACK; when inspecting an UPDATE or DELETE.
When a query is already running and blocking things, pg_stat_activity shows what is in flight. Prefer pg_cancel_backend over pg_terminate_backend: cancelling stops the query and leaves the connection intact, while terminating drops the whole connection and any transaction it held. Neither loses committed data, but on a shared system, check whose query it is before stopping it.
Finally, keep a record. Saving the EXPLAIN ANALYZE output from before and after a change is what lets you prove the fix worked, and what tells you months later why that odd-looking partial index exists.
Common mistakes #
- Running EXPLAIN ANALYZE on an UPDATE or DELETE without a transaction, actually performing the change.
- Adding indexes before running ANALYZE, when stale statistics were the real cause of the bad plan.
- Treating every Seq Scan as a bug — on small tables or wide result sets it is the correct choice.
- Ignoring loops= and reading a per-loop time as the total time for that node.
- Optimising the query that feels slowest instead of the one with the highest total time.
Practice #
Take a query against a table with at least 100,000 rows and run EXPLAIN (ANALYZE, BUFFERS) on it. Write down three things: the scan type, the estimated versus actual row counts, and the value of "Rows Removed by Filter". Then run ANALYZE on the table and re-check whether the estimate improved. Add the index the plan suggests, measure again, and record the before and after execution times. Finally, enable pg_stat_statements and list your top five queries by total time.