---
title: "Postgres: EXPLAIN and Query Plans"
slug: "postgres-explain"
category: "backend"
tags: ["backend", "postgres", "explain", "performance", "query-planning"]
status: "stable"
last_updated: 2026-08-14
summary: "Read query plans before tuning: EXPLAIN ANALYZE with BUFFERS, top-down, compare estimated to actual rows, and feed pg_stat_statements into the index decisions."
related:
  [
    "[[backend/postgres]]",
    "[[backend/postgres-indexes]]",
    "[[backend/postgres-jsonb]]",
    "[[backend/postgres-vacuum]]",
    "[[backend/observability]]",
  ]
---

## Overview

Read the plan before you tune. Postgres 18 exposes the planner's decisions through `EXPLAIN`; the planner picks indexes, scan types, and join orders from statistics, so reading the plan is the only honest answer to "why is this slow?". This page covers the difference between `EXPLAIN` and `EXPLAIN ANALYZE`, how to read a plan, when each scan type is right, and how `pg_stat_statements` feeds the loop. The umbrella rules live in [[backend/postgres]].

## Run EXPLAIN ANALYZE with BUFFERS

`EXPLAIN` returns the planner's estimate without running the query. `EXPLAIN ANALYZE` runs the query and reports actual rows, actual time, and loop counts. `BUFFERS` adds the cache hit and disk read counts.

```sql
EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT id, total_cents FROM orders
WHERE user_id = $1 AND created_at > now() - interval '30 days'
ORDER BY created_at DESC LIMIT 50;
```

Use plain `EXPLAIN` when the query mutates data. For everything else, `ANALYZE` plus `BUFFERS` is the default. Postgres reports WAL bytes per node (since version 17) when you add `WAL` to the option list.

## Read the plan top-down, execute bottom-up

The output is a tree. The root is what the client receives; the leaves are scans on tables. The planner runs the leaves first, so debugging starts at the deepest node.

- Indentation marks parent-child. A nested loop with two children executes the left child once and the right child once per outer row.
- `actual rows=...` is the row count the node produced; multiply by `loops=...` for the total.
- `Buffers: shared hit=N read=M` distinguishes warm cache from cold reads. A large `read` means the heap or index page was not in the buffer cache.

Start at the node with the highest `actual time`. Fix that. Re-run.

## Compare estimated rows to actual rows

The planner picks a plan from row estimates. When estimates and reality diverge by more than ten times, the plan is suspect.

```text
Index Scan using orders_user_id_idx on orders
  (cost=0.43..2.50 rows=1 width=20) (actual time=0.04..18.23 rows=4200 loops=1)
```

That node estimated one row and produced 4,200. The parent join chose a nested loop on that estimate; the real shape wanted a hash join. Run `ANALYZE orders` to refresh statistics, or `ALTER TABLE ... ALTER COLUMN ... SET STATISTICS 1000` for skewed columns. See [[backend/postgres-vacuum]] for the autovacuum analyze schedule.

## Know when each scan type is right

Three scan types cover most plans.

- Sequential scan. Reads every page. Right when the table is small, when the query returns most rows, or when no useful index exists. Wrong when the table is large and the predicate is selective.
- Index scan. Walks the index, then fetches the heap. Right for selective predicates. Wrong when the index returns most rows; the random heap reads cost more than a seq scan.
- Index-only scan. Walks the index and skips the heap when every projected column lives in the index (or the page is all-visible). Right for hot read paths covered by `INCLUDE`; see [[backend/postgres-indexes]].

Bitmap heap scan sits between the two. The planner builds a bitmap of matching pages from one or more indexes, then visits each page once. It wins when an index is moderately selective and the rows are scattered.

## Read costs as ratios, not absolutes

The `cost=A..B` numbers are arbitrary planner units. They are useful as ratios: a node with `cost=1000` is roughly ten times the work the planner expects for a node with `cost=100`. They are not seconds.

- `actual time=A..B` reports milliseconds: `A` is time to first row, `B` is time to last row.
- A node with low cost and high actual time is the row estimate problem. The planner thought it was cheap.
- `Planning Time` and `Execution Time` print at the bottom. Planning under a millisecond is normal; over ten suggests prepared statements would help.

## Use BUFFERS to detect cold reads

`BUFFERS` reports cache hits and disk reads per node. A query that runs in 5 ms warm and 500 ms cold is an IO problem, not a plan problem. Run the query twice and compare; if `read` drops to zero on the second run, the working set does not fit in `shared_buffers`. Either raise `shared_buffers`, add a covering index that shrinks the working set, or partition the cold history; see [[backend/postgres-partitioning]].

## Feed pg_stat_statements into the loop

`pg_stat_statements` records the normalized text, call count, total time, and rows for every query since the last reset. Enable it once in `shared_preload_libraries`.

```sql
SELECT calls, mean_exec_time, total_exec_time, query
FROM pg_stat_statements
ORDER BY total_exec_time DESC LIMIT 20;
```

Top queries by `total_exec_time` are the right tuning targets; mean time hides queries that are cheap but run a million times an hour. Pair with [[backend/observability]] and the slow-query log to attribute pain back to routes.

## Related

- [[backend/postgres]]
- [[backend/postgres-indexes]]
- [[backend/postgres-jsonb]]
- [[backend/postgres-vacuum]]
- [[backend/postgres-partitioning]]
- [[backend/observability]]