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.