Indexes and EXPLAIN

Capture the plan first

EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT id, customer_id, placed_at
FROM orders
WHERE customer_id = 42
ORDER BY placed_at DESC
LIMIT 20;

EXPLAIN shows estimates without executing.
ANALYZE executes and reports actual rows and timing. Wrap writes in a transaction and roll back.
BUFFERS reports shared/local/temp block hits and reads.
Compare estimated rows with actual rows, loop counts, expensive nodes, and disk sorts.

ANALYZE executes the statement

EXPLAIN ANALYZE DELETE ... really deletes. Inspect a write with BEGIN; EXPLAIN (ANALYZE, BUFFERS) ...; ROLLBACK; only after confirming there are no non-transactional external effects.

Index the query shape

The filter and ordering above can use:

CREATE INDEX CONCURRENTLY orders_customer_placed_idx
ON orders (customer_id, placed_at DESC)
INCLUDE (id);

A multicolumn B-tree normally matches from its left side. Actual predicates, range conditions, and ordering determine column order—not a simplistic “most selective first” rule.

INCLUDE columns do not participate in search ordering but may enable an index-only scan. The visibility map still determines whether heap access is avoidable.

Common index types

Type	Fits
B-tree	Equality, ranges, ordering; the default
GIN	`jsonb` containment, arrays, full-text search
GiST	Geometry, ranges, and extension operators
SP-GiST	Tries, quadtrees, k-d trees, and other partitioned search spaces
BRIN	Huge tables whose physical order correlates with values, such as append-only time data
Hash	Equality only; B-tree is usually more versatile
Bloom extension	Equality across arbitrary combinations of many columns; lossy and rechecked; bundled classes only cover `int4` and `text`

These labels still do not prove an index is usable: the operator class determines exact operators and data types. See index and storage access methods for table access methods, HNSW/IVFFlat, and the full selection map.

Two high-value patterns

A partial index covers only relevant rows:

CREATE INDEX orders_unfinished_idx
ON orders (placed_at)
WHERE status IN ('pending', 'paid');

An expression index accelerates normalized lookup:

CREATE UNIQUE INDEX customers_email_ci_idx
ON customers (lower(email));

The query predicate must match the expression or imply the partial condition for the planner to use it.

Why an index is not used

The table is small and a sequential scan is cheaper.
The query returns a large fraction of rows.
Statistics are stale or miss cross-column correlation.
A function or implicit cast does not match the index expression.
The leftmost prefix of a multicolumn index is not usable.
Cost parameters do not reflect the storage system.

Run ANALYZE orders; and inspect estimate errors before disabling sequential scans.

Production creation and cleanup

CREATE INDEX CONCURRENTLY reduces write blocking but takes longer, cannot run in a transaction block, and can leave an invalid index after failure. Inspect with:

SELECT indexrelid::regclass, indisvalid, indisready
FROM pg_index
WHERE indrelid = 'orders'::regclass;

Every index adds write amplification, WAL, cache pressure, and vacuum work. Review unused indexes with pg_stat_user_indexes over a representative business cycle.

Comparing plans across versions

PostgreSQL 19 (Beta as of this update) adds an IO option to EXPLAIN ANALYZE that reports asynchronous I/O activity for scan nodes: prefetch-queue distance and capacity, plus issued I/O requests, their average size, I/O waits, and concurrency. EXPLAIN (ANALYZE, WAL) additionally reports full-page-write bytes. Both make storage behavior visible without external tooling; see the PostgreSQL 19 EXPLAIN documentation.

JIT is also disabled by default in 19 because its cost model proved unreliable. When comparing plans or runtimes between 18 and 19, set jit explicitly on both sides — otherwise a difference may reflect the changed default rather than an optimizer or index change.