How to read PostgreSQL EXPLAIN without guessing

By Anil Gautam · Published and reviewed September 20, 2026

Run EXPLAIN first to see the plan PostgreSQL expects to use. EXPLAIN ANALYZE goes further: it executes the query and reports what happened. Use that second option only when you’re ready to run the statement.

Capture a plan for a specific question

The example uses a made-up public.orders table with id, customer_id and created_at columns. Replace those names with your own.

EXPLAIN
SELECT id, created_at
FROM public.orders
WHERE customer_id = 42
ORDER BY created_at DESC
LIMIT 50;

Read the scan, filtering and sorting steps. Cost values are planner units, not elapsed milliseconds. An index scan is not automatically good, and a sequential scan is not automatically bad. PostgreSQL's plan-reading guide explains how to interpret the tree.

Compare estimates with observed work

On a suitable test environment, replace the first line with EXPLAIN (ANALYZE, BUFFERS). Focus on large gaps between estimated and actual row counts, repeated work inside loops, and where most time is spent. Keep the parameter values and dataset consistent between comparisons.

ANALYZE runs the statement. For a write, that means writes and associated side effects. Even a SELECT can call functions with effects or place substantial load on the server. Do not treat EXPLAIN ANALYZE as a harmless preview. See the EXPLAIN command reference.

Change one hypothesis at a time

Suppose the result suggests many rows are read and then filtered. Investigate the predicate, existing indexes and statistics before creating an index. Save the old plan, make one reviewed change on a development copy, and rerun the identical query. Measure the complete user task too: a fast database plan does not include every cost of transferring and rendering a large result in the client.

Use a GUI without losing the evidence

plainDB includes visual plan inspection on Mac and Windows; both also support EXPLAIN through the SQL editor. Keep the textual plan and SQL in your investigation record so another person can reproduce the analysis independently of the interface.

A useful conclusion is specific: which query shape improved, on which data, and what changed. One favorable run is not a general benchmark for a database client or a guarantee for production.

Continue reading

Inspect existing indexes · Review pagination queries

For the desktop workflow, compare plainDB features on Mac and Windows or download the 14-day trial.