Slow query checklist
A method for any slow query, and a table from what the plan shows to the likely fix.
Draft. This page is new and has not yet been reviewed by a second person. If something looks wrong, report it.
Before thisReading EXPLAIN
On this page
A one-page method for a slow query, and a table that maps what you see in a plan to its usual causes and fixes. Every row of the table links to the page that explains it with real plans.
The method#
- Measure the real thing.
EXPLAIN (ANALYZE, BUFFERS)on production-like data, with the same parameters the application uses. An empty development table says nothing about a table of fifty million rows. (In PostgreSQL 18,ANALYZEalone already reports buffers.) - Find the expensive node. Read from the innermost nodes outwards. Look for the node whose own time, rows or buffers jump. The plan stepper does this for you.
- Check the estimates first. If estimated and actual rows differ by ten times or more, fix that before anything else: the rest of the plan was chosen on that number.
- Name one cause. "It reads the whole table because the condition wraps the column in a function" is a hypothesis you can test. "It is slow" is not.
- Change one thing, measure again. Compare buffers and rows, not only milliseconds; timings move with caches and load, page counts barely do.
- Check the cost of the fix. A new index makes writes slower and takes space.
SET work_memfor one report is fine; for the whole server it can exhaust memory.
What you see, and what it usually means#
| In the plan | Usual cause | Usual fix | Read more |
|---|---|---|---|
Seq Scan on a big table, large Rows Removed by Filter | No index on the filtered column | Add an index on it | B-tree indexes |
An index exists but is not used; the condition appears as Filter | A function, cast or arithmetic on the column | Compare the bare column with a value or range, or index the expression | Designing indexes |
Sort above an index scan on the same table | Index columns do not match ORDER BY | Index (equality columns, then sort columns) | Designing indexes |
Bitmap Heap Scan with Heap Blocks close to the table size | Matching rows spread over most pages | Fine if most rows match; otherwise a more selective or covering index | Scan types |
Index Only Scan with large Heap Fetches | Pages not marked all-visible | VACUUM the table; check autovacuum keeps up | How a table is stored |
| Estimated rows far from actual rows | Stale statistics, correlated columns, expressions | ANALYZE; CREATE STATISTICS; rewrite the expression | Planner statistics |
Nested Loop with huge loops on the inner side | Outer side underestimated, or no index on the inner join key | Fix the estimate; index the join key | Joins |
Hash with Batches greater than 1, or temp written | Not enough work_mem for the hash or sort | Join or sort fewer rows; raise work_mem for that query | Joins |
Sort Method: external merge Disk | Sort larger than work_mem | Add LIMIT, return rows from an index already in order, or raise work_mem locally | Reading EXPLAIN |
Append scanning every partition | The condition does not compare the partition key directly | Write a range on the bare partition key | Tuning exercises |
| Fast query, slow page | Many small queries (N+1), or OFFSET pagination | One query with a join or = any(array); keyset pagination | Pagination and N+1 |
SubPlan running once per row | Correlated subquery in the select list | Rewrite as a join, or index what it looks up | Tuning exercises |
Before you add an index#
- Is the query actually frequent or important? One slow nightly report may not be worth slowing every insert.
- Could an existing index serve it if its columns were in a different order, or with one
INCLUDEd column? - Will the new index be selective? An index on a column where most rows share one value is rarely used.
- After adding it, check
pg_stat_user_indexes.idx_scanlater: is it being used?
Things that are not the database's fault#
Sometimes the plan is fine and the time goes elsewhere: too many round trips, fetching thousands of rows to show twenty, a connection opened per request instead of taken from a pool, or waiting on a lock held by another transaction. If Execution Time is small but the request is slow, look outside the plan.
Sources#
- PostgreSQL documentation: Using EXPLAIN, Performance Tips, Statistics Used by the Planner, Examining Index Usage, Resource Consumption.
- Use The Index, Luke, Markus Winand.