EXPLAIN ANALYZE in PostgreSQL: how to read a plan and what indexes are really needed
Where to start: not with a plan, but with a list of requests
Indexes are added for specific queries, and not “for all columns from WHERE”. First, find which queries are really expensive. The pg_stat_statements extension collects statistics on normalized queries:
CREATE EXTENSION IF NOT EXISTS pg_stat_statements; -- требует shared_preload_libraries
SELECT calls, round(total_exec_time) AS total_ms, round(mean_exec_time, 1) AS mean_ms,
left(query, 120) AS query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 15;Sorting by total_exec_time shows where the database spends its time in total. A 3 ms query called a million times is often more important than a 2 second report once a day.
How to remove a plan
EXPLAIN (ANALYZE, BUFFERS) SELECT id, total, created_at FROM orders WHERE user_id = 4815 AND status = 'paid' ORDER BY created_at DESC LIMIT 20;
ANALYZE actually executes the request. For UPDATE and DELETE wrap it in a rollback transaction:
BEGIN; EXPLAIN (ANALYZE, BUFFERS) DELETE FROM sessions WHERE expires_at < now(); ROLLBACK;
BUFFERS shows how many pages have been read from the cache (shared hit) and from disk (read). In PostgreSQL 18, this information is displayed along with ANALYZE automatically; in earlier versions, the parameter must be specified explicitly.
How to read nodes
Plan - tree. Read from the inside out: the most nested nodes are executed first. Each node has two groups of numbers:
Limit (cost=0.00..1843.20 rows=20 width=24) (actual time=0.041..96.512 rows=20 loops=1)
-> Sort (cost=... rows=1180 ...) (actual time=96.501..96.506 rows=20 loops=1)
Sort Key: created_at DESC
Sort Method: top-N heapsort Memory: 27kB
-> Seq Scan on orders (cost=0.00..41250.00 rows=1180 width=24)
(actual time=0.030..95.880 rows=1342 loops=1)
Filter: ((user_id = 4815) AND (status = 'paid'::text))
Rows Removed by Filter: 1998658This is an illustrative plan, but the pattern is typical:
- `cost` — scheduler evaluation in conventional units. It makes no sense to compare it between different requests.
- `actual time=A..B` — milliseconds before the first line and before the last. The parent's time includes the children's time.
- `rows` in the assessment and in the fact. A discrepancy by orders of magnitude is the main sign of a problem: the scheduler chose a plan for the wrong number of lines.
- `loops` — how many times the node was executed. Real time and strings must be multiplied by
loops. Internal Nested Loop node withloops=50000and time 0.02 ms is a second. - `Rows Removed by Filter` - lines read in vain. Here, two million lines were looked through for the sake of 1342 needed ones.
What index does this query need?
The query filters by user_id and status and sorts by created_at. The order of columns in a composite index is equal columns first, then sort column.
CREATE INDEX CONCURRENTLY orders_user_status_created_idx ON orders (user_id, status, created_at DESC);
With such an index, the plan turns into Index Scan with Limit: the database reads exactly 20 records by index in the required order and does not sort anything. CONCURRENTLY does not block writing to the table during construction, but does not work inside a transaction and, if it fails, leaves an invalid index that must be deleted and recreated.
If almost all queries look at a single status value, a partial index is smaller and cheaper to maintain:
CREATE INDEX CONCURRENTLY orders_pending_idx ON orders (created_at) WHERE status = 'pending';
It will only be used if the query condition includes status = 'pending' literally.
Knots to watch
- `Sort Method: external merge Disk: ...` — the sorting did not fit in
work_memand went to disk. It is treated with an index with the desired order or increasingwork_memfor a specific session, and not globally. - `Bitmap Heap Scan` with `Recheck Cond` and `Heap Blocks: lossy` - the bitmap did not fit into memory and became paged.
- `Index Only Scan` with a large `Heap Fetches` - the index covers the query, but the visibility map is outdated, and the database still goes to the table.
VACUUMhelps (or setting autovacuum for this table). - `Hash Join` with `Batches: 8` - the hash table did not fit into memory.
- Estimation `rows=1`, but fact - thousands. Update statistics
ANALYZE orders;. If the columns are correlated (city and country), advanced statistics help:CREATE STATISTICS ... (dependencies) ON city, country FROM addresses;.
Why is there an index and not used?
- 1.Function or cast on a column:
WHERE lower(email) = $1does not use an index onemail. We need an index based on the expression(lower(email)). - 2.
LIKE '%abc'with leading percentage. B-tree will not help here, you needpg_trgmwith GIN. - 3.The condition selects a large portion of the table. Sequential reading is then indeed cheaper, and the scheduler is right.
- 4.Different types: column
bigint, parameter passed asnumeric. - 5.Small table. For a couple thousand lines,
Seq Scanis normal.
Extra indexes also cost money
Each index slows down inserts and updates and takes up space. Candidates for removal:
SELECT relname, indexrelname, idx_scan, pg_size_pretty(pg_relation_size(indexrelid)) FROM pg_stat_user_indexes WHERE idx_scan = 0 ORDER BY pg_relation_size(indexrelid) DESC;
Statistics are local to the server: the index, which is not needed on the primary, can serve reports on the replica. And don't drop indexes that provide UNIQUE or the primary key.
When EXPLAIN won't show the problem
If a query is fast in a manual run, but slow in production, it could be due to blocking, connection contention, or a different plan for the parameterized query. Enable auto_explain with the auto_explain.log_min_duration threshold to get the slow execution plan from the logs, and watch pg_stat_activity for the Lock expectations.