PostgreSQL / Intermediate: concurrency and maintainable SQL / Lesson 8

Indexes and EXPLAIN plans

Choose a PostgreSQL index for a concrete lookup, then read estimates and measured execution as different evidence. Balance read benefits with write and maintenance cost.

About 4 min readContent reviewed 2026-10-25

On this page

Lesson goals

Relate B-tree ordering, selectivity and covering access.
Interpret EXPLAIN and EXPLAIN ANALYZE safely.

Indexes are maintained access paths

A PostgreSQL B-tree index stores ordered keys pointing toward table tuples; it does not make the heap permanently clustered in primary-key order. A selective lookup may benefit, while a small table or broad match may be cheaper to scan sequentially. Every extra index consumes space, WAL and mutation work. PostgreSQL also offers GIN, GiST, SP-GiST, BRIN and hash indexes for suitable operators and workloads. Choose from supported access requirements rather than assuming one index family solves every search.

Match equality, range and order

For one customer’s recent shipments, customer_id is an equality key, followed by created_at for a range and shipment_id for deterministic ties. This is a candidate index, not a promise the planner chooses it. The selected columns fit within the index, but an index-only scan additionally depends on visibility information and the actual plan. Recent updates can require heap visibility checks even when all projected values are indexed. Review a representative populated fixture before claiming reduced heap access.

sql Illustrative SQL; expected results are reasoned, not measured.

CREATE INDEX ix_ship_customer_time
ON quay_lab.shipments (customer_id, created_at, shipment_id);
EXPLAIN SELECT shipment_id, created_at FROM quay_lab.shipments
WHERE customer_id = 7 AND created_at >= timestamptz '2026-10-01 00:00+00'
ORDER BY created_at, shipment_id LIMIT 20;

No plan cells or timing ratios are invented. The DDL is only an isolated-lab illustration.

Cost is not elapsed milliseconds

EXPLAIN without ANALYZE shows planner estimates. Costs are relative planner units, rows estimates describe output from a node and width estimates describe average row size. A sequential scan is not automatically a defect; it can be efficient for a broad filter. Read the tree from scans through joins, sorts and aggregates, and distinguish an index condition from a residual filter. A cheap-looking upper node includes child costs, so adding every displayed total double-counts work.

ANALYZE executes the statement

EXPLAIN (ANALYZE, BUFFERS) executes the query and reports runtime row information and buffer activity. Use a bounded read-only query on an isolated representative fixture, as illustrated below; even SELECT can create substantial load. Do not casually apply it to UPDATE or DELETE, because those changes really execute. Planning and instrumentation overhead, cache state and loop counts affect interpretation. Buffer hits do not mean the whole operation was free, and reads do not by themselves prove a physical disk access at every layer.

sql Illustrative SQL; expected results are reasoned, not measured.

EXPLAIN (ANALYZE, BUFFERS)
SELECT shipment_id FROM quay_lab.shipments
WHERE customer_id = 7
ORDER BY created_at, shipment_id LIMIT 20;

Expected output is an executed plan, whose actual rows, loops and timing depend on the fixture. None were measured here.

Partial and expression indexes have conditions

A partial index stores only rows satisfying its predicate, such as pending shipments. A query must imply that predicate for the index to be useful, which matters with parameterized or generic plans. An expression index can support a matching expression such as lower(label), but its equality policy must match business intent. PostgreSQL 18 adds B-tree skip-scan behavior for some multicolumn cases; this does not abolish column-order design. Test the exact release and workload before relying on planner transformations or newer capabilities.

Common errors and fixes

A sequential scan is treated as automatic failure

Why it happens: The plan was judged by a label instead of work and selectivity.
How to fix it: Compare estimated and actual work on realistic data.
Prevent it next time: Keep small and broad-match queries in index evaluations.

EXPLAIN ANALYZE unexpectedly changes data

Why it happens: Executed analysis was used on a modifying statement.
How to fix it: Use the reviewed recovery boundary and switch diagnostics to a safe fixture.
Prevent it next time: Label estimated and executed analysis distinctly in runbooks.

An index-only scan still fetches heap tuples

Why it happens: Visibility checks cannot be satisfied solely from current visibility-map state.
How to fix it: Inspect heap-fetch metrics and maintenance context before changing indexes.
Prevent it next time: Include update churn and vacuum behavior in covering-index tests.

Check your understanding

1. An EXPLAIN node estimates ten rows but an executed plan emits ten thousand per loop. What evidence should you collect?

Show worked solution

Record loops, predicates, parameter values and actual data distribution, then inspect statistics freshness and correlations. Multiply per-loop figures only according to the metric definitions. A forced index can hide the symptom for one parameter while worsening another. Test a justified statistics, query or index change on representative data and preserve the result contract.

2. Why might adding an index to a twenty-row lookup not improve a report that reads most of a large table?