Reading EXPLAIN ANALYZE

A slow query is a question about its plan. EXPLAIN ANALYZE answers that question by showing each step, how long it took and how many rows it produced. This page walks through one slow query from the plan to the fix. Read the plan from the bottom up, find the step that costs the time, and ask whether an index or a rewrite can remove it.

The big idea

Each line of the plan is a node. Data flows from the bottom nodes up to the top. Each node shows two things.

• Estimated rows= and cost=, from the planner.
• Actual time= and rows=, from the run.

The fix is usually found where the actual numbers are much bigger than they should be, or where estimate and actual disagree.

The slow query

The table has 20 million orders. This query is hot in the app: recent paid orders for one customer.

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, created_at, total_cents
FROM orders
WHERE customer_id = 42
  AND status = 'paid'
  AND created_at >= now() - interval '30 days'
ORDER BY created_at DESC
LIMIT 50;

The plan looks like this. These numbers are illustrative.

Limit  (actual time=840.1..840.2 rows=50 loops=1)
  ->  Sort  (actual time=840.1..840.1 rows=50 loops=1)
        Sort Key: created_at DESC
        ->  Seq Scan on orders  (actual time=0.05..790.0 rows=11840 loops=1)
              Filter: (customer_id = 42 AND status = 'paid' AND created_at >= …)
              Rows Removed by Filter: 19988160
              Buffers: shared hit=250000

Reading it bottom up

1. Seq Scan read every row. It kept 11,840 and removed 19,988,160. That is 20 million rows read to answer one customer. It also touched 250,000 buffer pages. At 8 KB each, that is about 2 GB of pages read to return 50 rows.
2. Sort sorted about 11,840 rows, only to keep the newest 50.
3. Limit needed only 50 rows. A good index could return them in order without the sort.

The time is in the scan. The sort is a symptom of the scan.

The fix: an index that matches the query

The query filters on two equal values and one range, then orders by the range column. The index should follow the same shape: equality columns first, then the range and order column. The INCLUDE adds the selected column so the query can skip the table.

CREATE INDEX orders_cust_paid_created
  ON orders (customer_id, status, created_at DESC)
  INCLUDE (total_cents);

Run EXPLAIN ANALYZE again. The plan should look like this.

Limit  (actual time=0.05..0.08 rows=50 loops=1)
  ->  Index Only Scan using orders_cust_paid_created on orders
        Index Cond: (customer_id = 42 AND status = 'paid' AND created_at >= …)
        (actual rows=50 loops=1)

There is no sort node, because the index is already in the right order. The scan touches about 50 index entries. The time drops from hundreds of milliseconds to under a millisecond on a warm cache.

Patterns that cause slow plans

• N+1 queries from an ORM. One query loads 50 orders, then 50 more load each customer. pg_stat_statements shows thousands of identical small queries. Fix it with a join, WHERE id IN (…), or eager loading.
• Deep OFFSET pages. OFFSET 100000 still reads and throws away 100,000 rows. Use keyset pagination: WHERE (created_at, id) < ($ts, $id) ORDER BY created_at DESC, id DESC LIMIT 50.
• Functions on indexed columns. date_trunc('day', created_at) = … cannot use a plain index on created_at. Use a range instead.
• Implicit casts. Pass parameters with the same type as the column, so the index can match.
• SELECT *. It pulls wide rows and rules out index-only scans.
• A missing join condition. The result can be billions of rows. Use LIMIT on exploratory queries.

Common mistakes

• Adding an index before comparing estimate with actual. A bad estimate may be the real cause. See statistics and cardinality.
• Building an index that does not match the filter order. The leftmost-column rule applies to composite indexes. See the B-tree page.
• Running EXPLAIN ANALYZE on production writes. It executes the statement.

Check yourself

1. Why is a Sort node in the plan a clue to look at the scan? The sort is usually a symptom. The scan delivered far more rows than needed, and the sort then threw most of them away.
2. Why should the composite index put customer_id and status before created_at? Equality columns come first so the index narrows the rows, and the range column after them keeps the rows in order for the ORDER BY.

Key takeaways

• Read the plan bottom up. Find the step with the biggest actual time or the most buffer reads.
• Design an index from the query: equality columns, then the range and order column, then INCLUDE for the selected columns.
• A sort after a scan is a clue that an index could return rows already in order.
• Fix N+1 queries, deep offsets and functions on indexed columns in the query, not just with new indexes.

Further learning

Use The Index, Luke covers the same ideas with many worked examples.

Use The Index, LukeSQL indexing and tuning tutorial for developers. No unnecessary database details—just what developers need to know. Covers all major SQL databases.use-the-index-luke.com

PostgreSQL docs: Using EXPLAIN14.1. Using EXPLAIN # 14.1.1. EXPLAIN Basics 14.1.2. EXPLAIN ANALYZE 14.1.3. Caveats PostgreSQL devises a query plan for each query it …PostgreSQL Documentation