Reading Your Own Query Plans

The most common performance debugging mistake is reading the query and reasoning about what it should do. The database has already told you what it actually did, and it disagrees with you more often than you would like.

The tool

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT id, title, published_at
FROM posts
WHERE status = 'published'
  AND published_at > NOW() - INTERVAL '90 days'
ORDER BY published_at DESC
LIMIT 9;

ANALYZE runs the query and reports real timings. BUFFERS reports how much data came from cache versus disk. Without ANALYZE you get the planner's estimate, which is exactly the thing you are trying to check.

Never run bare EXPLAIN on a slow query and draw conclusions. The estimate is derived from statistics that may be stale, and the whole reason the query is slow is often that the estimate is wrong.

Reading it

Each node reports three numbers that matter:

Limit  (cost=1024.55..1024.57 rows=9 width=48) (actual time=8.412..8.419 rows=9 loops=1)
  Buffers: shared hit=1804 read=42
  ->  Sort  (cost=1024.55..1031.18 rows=2652 width=48) (actual time=8.404..8.406 rows=9 loops=1)
        Sort Key: published_at DESC
        Sort Method: top-N heapsort  Memory: 25kB
        Buffers: shared hit=1804 read=42
        ->  Seq Scan on posts  (cost=0.00..948.00 rows=2652 width=48) (actual time=0.021..6.881 rows=2654 loops=1)
              Filter: ((status = 'published') AND (published_at > (now() - '90 days'::interval)))
              Rows Removed by Filter: 7346
              Buffers: shared hit=1804 read=42

rows= estimated versus actual

This is the single most informative comparison. Here the planner estimated 2 652 rows and got 2 654 — an accurate estimate, and the plan is reasonable.

When these diverge by more than about 10×, the planner has made a decision based on bad information, and no amount of index tuning will help until the estimate is fixed.

Rows Removed by Filter

Seven thousand three hundred and forty-six rows were read and discarded. That means the index did not cover the predicate, so the database scanned the table and threw most of it away.

This is the number that tells you to add a partial index. If you filter on status = 'published' and most rows are drafts, a partial index means those 7 346 rows are never read at all.

Buffers: shared hit versus read

hit=1804, read=42 means almost everything came from the page cache. On a cold cache this query would be dramatically slower, which is worth knowing before you conclude it is fast.

If read matches hit in magnitude, you are I/O bound and the fix is usually a smaller working set — fewer columns, a covering index — not more RAM, which just moves the problem to the next data growth increment.

loops=

The most commonly missed number. Every node reports actual time=<startup>..<total> and rows=<rows per loop>, with loops=<count>.

A node showing rows=1 with loops=5000 returned five thousand rows in total, and the actual time is per loop. Multiply before you conclude a node is cheap:

->  Index Scan using categories_pkey on categories
      (cost=0.28..8.30 rows=1 width=32)
      (actual time=0.008..0.009 rows=1 loops=5000)

Nine microseconds per loop sounds free. Multiply by five thousand and it is 45 milliseconds of the query's total, for a lookup that an eager load would have turned into one scan of fifty rows.

This is how a nested loop hides an N+1 that the application layer would have made obvious.

The three fixes, in order

Bad row estimate → fix the statistics.

ANALYZE posts;
-- Or, for a column with a skewed distribution:
ALTER TABLE posts ALTER COLUMN status SET STATISTICS 1000;

Rows removed by filter → add a partial or covering index.

CREATE INDEX posts_published_recent_index
    ON posts (published_at DESC)
    WHERE status = 'published' AND deleted_at IS NULL;

High loops → fix it in the application. No index helps a query that issues five thousand separate lookups. Add the eager load.

What to stop doing

Stop adding indexes because a query "looks slow." The plan tells you whether the bottleneck is a scan (index), an estimate (statistics), an I/O pattern (working set), or a loop count (application). Each has a different fix, and applying the wrong one costs you write throughput while leaving the read path exactly as slow as it was.

Read the plan first. It is right there, and it is not lying to you.