PostgreSQL: indexes and EXPLAIN for predictable latency

Production database work is less about “tuning PostgreSQL” than about aligning access paths with how the application queries. My default workflow is: measure the plan, align the index with the filter and sort, then re-measure on representative data.

Start with the plan, not the index

EXPLAIN (ANALYZE, BUFFERS) shows what actually ran: join order, index usage, and whether estimates diverge from reality (often a statistics issue).

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, status, updated_at
FROM orders
WHERE org_id = $1
  AND status = 'open'
  AND deleted_at IS NULL
ORDER BY updated_at DESC
LIMIT 50;

I look for sequential scans on large relations, rows removed by filter after a poor index choice, and buffer reads that suggest the working set is larger than expected.

Composite index shape

If the query filters on org_id and status and orders by updated_at, a composite index that matches that access pattern often outperforms three single-column indexes:

CREATE INDEX CONCURRENTLY idx_orders_org_status_updated
  ON orders (org_id, status, updated_at DESC)
  WHERE deleted_at IS NULL;

CONCURRENTLY limits lock duration during rollout. A partial WHERE keeps the index smaller when soft deletes are common.

Aftercare

Run ANALYZE after large bulk loads. For highly skewed columns, extended statistics or targeted histograms can correct bad row estimates that drive nested-loop decisions.

Takeaway: Let EXPLAIN drive the design; build the smallest index that matches the hot query shape, then verify under production-like volume.