Read a PostgreSQL Query Plan Before Adding an Index

A sequential scan is not a diagnosis. Reading most of a small table sequentially can be cheaper than following an index and fetching scattered rows. The useful question is where the planner's picture of the work differs from what the query actually does.

Start with the plan and its units

Plain EXPLAIN shows the chosen plan without running the statement. Planner costs are relative units, not milliseconds. EXPLAIN (ANALYZE, BUFFERS) executes it and reports observed timing and row counts. Use a controlled environment and representative parameters. On a write statement it performs the write; even a rollback may not undo every external side effect.

Read from scans toward their parents. Look for the first substantial difference between estimated and actual rows. In nodes executed repeatedly, reported actual rows and times are generally per-loop averages. Multiply by loops to understand the scale of repeated work; do not add parent and child timings as though they were independent.

Follow a bad estimate

Imagine a nested loop whose inner scan returns one row quickly but runs 100,000 times. The inner node alone looks cheap. If the outer input was estimated at only 50 rows, investigate statistics, skew or correlated filters. An index on the inner table might help, but correcting the outer estimate or reducing the outer input could matter more.

Buffer hits and reads add context. A read counted by PostgreSQL need not imply a physical disk access if the operating system supplied it from cache.

Compare the proposed change fairly

Save the original plan, data size, parameters and observed runtime. Repeat under comparable cache and load conditions after a change. Note the write and storage cost of a new index. Prefer the change that improves the important workload without shifting disproportionate cost onto inserts, updates or another common query.

Further reading

PostgreSQL: Using EXPLAIN

Related guide: Measure Storage Latency Before Buying Faster Disks.