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
  1. The method
  2. What you see, and what it usually means
  3. Before you add an index
  4. Things that are not the database's fault
  5. Sources

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#

  1. 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, ANALYZE alone already reports buffers.)
  2. 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.
  3. 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.
  4. 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.
  5. Change one thing, measure again. Compare buffers and rows, not only milliseconds; timings move with caches and load, page counts barely do.
  6. Check the cost of the fix. A new index makes writes slower and takes space. SET work_mem for one report is fine; for the whole server it can exhaust memory.

What you see, and what it usually means#

In the planUsual causeUsual fixRead more
Seq Scan on a big table, large Rows Removed by FilterNo index on the filtered columnAdd an index on itB-tree indexes
An index exists but is not used; the condition appears as FilterA function, cast or arithmetic on the columnCompare the bare column with a value or range, or index the expressionDesigning indexes
Sort above an index scan on the same tableIndex columns do not match ORDER BYIndex (equality columns, then sort columns)Designing indexes
Bitmap Heap Scan with Heap Blocks close to the table sizeMatching rows spread over most pagesFine if most rows match; otherwise a more selective or covering indexScan types
Index Only Scan with large Heap FetchesPages not marked all-visibleVACUUM the table; check autovacuum keeps upHow a table is stored
Estimated rows far from actual rowsStale statistics, correlated columns, expressionsANALYZE; CREATE STATISTICS; rewrite the expressionPlanner statistics
Nested Loop with huge loops on the inner sideOuter side underestimated, or no index on the inner join keyFix the estimate; index the join keyJoins
Hash with Batches greater than 1, or temp writtenNot enough work_mem for the hash or sortJoin or sort fewer rows; raise work_mem for that queryJoins
Sort Method: external merge DiskSort larger than work_memAdd LIMIT, return rows from an index already in order, or raise work_mem locallyReading EXPLAIN
Append scanning every partitionThe condition does not compare the partition key directlyWrite a range on the bare partition keyTuning exercises
Fast query, slow pageMany small queries (N+1), or OFFSET paginationOne query with a join or = any(array); keyset paginationPagination and N+1
SubPlan running once per rowCorrelated subquery in the select listRewrite as a join, or index what it looks upTuning 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_scan later: 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#

Esc
↑ ↓ to move↵ to open