Read a PostgreSQL EXPLAIN plan before adding an index

Start with estimates

Use plain EXPLAIN first when running the query might be expensive. Costs are planner units, not elapsed milliseconds. A sequential scan is not inherently wrong; reading much of a small table may make it sensible.

EXPLAIN SELECT * FROM orders WHERE customer_id = 42;

Measure in a suitable environment

For a safe SELECT, examine actual rows, loops and buffer activity. Large estimate errors suggest checking data distribution and statistics. ANALYZE executes writes too, so do not use it casually on modifying statements.

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE customer_id = 42;

Read one line before tackling the whole tree

The line below is an illustrative plan fragment, not a measurement from a real database. cost=0.43..8.61 shows estimated startup and total cost in planner units. rows=10 is the expected output count; width=16 is the estimated average output-row size in bytes.

The measured part would report 10,000 rows instead of the expected 10, with one execution. actual time=0.05..12.00 expresses the time to the first row and completion in milliseconds for that execution. The large row-count mismatch is a reason to investigate the estimate before assuming that another index is the answer.

Index Scan using orders_customer_idx on orders
  (cost=0.43..8.61 rows=10 width=16)
  (actual time=0.05..12.00 rows=10000 loops=1)

Read from the leaves and watch repeated work

Plan indentation shows which nodes supply data to their parents. A scan feeds rows into a join, sort or aggregate. Find where the amount of work grows: a filter that discards many rows, a large sort or an inner node executed repeatedly.

For an ordinary node, actual rows and times are averages per execution. If actual rows=5 and loops=1000, it has produced roughly 5,000 rows across those executions. Node time can include work in its children, so adding every node’s time double-counts work. Parallel plans need additional care; use overall Execution Time as an elapsed-time reference.

Use buffers and statistics to refine the diagnosis

BUFFERS helps distinguish data access from time alone. shared hit means a block was found in PostgreSQL’s shared buffer cache; shared read means it was read into those buffers, possibly from the operating system’s cache. These are access counts, not necessarily distinct blocks or physical disk reads.

Large estimate errors can follow outdated statistics or a skewed distribution. ANALYZE orders collects table statistics; it is a different command from EXPLAIN ANALYZE, which runs the query. After a bulk change, examine statistics before forcing an index or disabling a scan type. Sorting that spills to disk suggests a different investigation from a poorly estimated join.

ANALYZE orders;

Choose one change and compare fairly

State a hypothesis: the query reads too many unrelated rows, repeats a lookup excessively or sorts a large intermediate result. A selective filter may benefit from an appropriate index. A sequential scan can remain sensible for a small table or a query returning much of it.

Keep the query parameters and dataset comparable when evaluating a change. Repeat measurements because caches and concurrent load affect time; do not present a single warm run as a universal improvement. Consider the index’s storage and write overhead too. Start with a safe SELECT in a suitable environment: EXPLAIN ANALYZE also executes modifying statements and can produce side effects.