PostgreSQL Query Optimization: The EXPLAIN ANALYZE Workflow

Slow queries get "optimized" by intuition — adding indexes on vibes, rewriting joins blindly — and usually stay slow, because the actual problem was elsewhere. PostgreSQL ships the tool that ends guesswork: EXPLAIN ANALYZE, which shows both the query's execution plan and its real measured costs. This post is the workflow for using it: read the plan, find the dominant cost, apply the matching fix, verify.

Step 1: Measure before touching anything#

EXPLAIN ANALYZE
SELECT o.id, c.name
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'pending'
ORDER BY o.created_at DESC
LIMIT 50;

The output is a tree read bottom-up (inner nodes feed outer ones), with three numbers per node that matter:

• rows= — estimated rows vs actual — real rows. Wide gaps mean stale statistics
• loops= — how many times this node ran (multiply actual by loops to compare fairly)
• Actual time per node — find where time concentrates

Optimization rule zero: identify the single most expensive node first (measure before optimizing). Half of all query tuning is discovering that the "slow join" was fine and one early filter wasn't.

Step 2: Diagnose the usual suspects#

Sequential Scan on a big table — Postgres reading every row:

Seq Scan on orders  (cost=0.00..35811.00 rows=2000 ...)
  Filter: (status = 'pending'::text)
  Rows Removed by Filter: 998000

That last line is damning: it scanned a million rows to keep two thousand. If the filter is selective and this query matters → index it. (Note: seq scans are often correct for small tables or low-selectivity filters — don't reflexively index everything.)

Sort + Limit patterns — ORDER BY ... LIMIT 10 should ideally use an index on the sort column to stop after 10 rows; without one it sorts everything then discards. On large tables this alone is the bottleneck.

Nested Loop over huge sets — fine when the outer side is small (looping 10 rows against an indexed inner lookup is great); catastrophic when both sides are large. Hash joins replacing nested loops mid-optimization is common progress.

Step 3: Apply the matched fix#

Diagnosis	Fix
Seq scan + high selectivity	Index the filtered columns (composite order matters)
Sort dominating	Index matching the ORDER BY; or partial index if filtered
Stale row estimates	ANALYZE table; refresh statistics
Functions on indexed columns	Expression indexes (lower(email))
Fetching columns you don't use	Select less; occasionally covering indexes
N+1 from application code	Batch into one query with joins/arrays

That last row deserves emphasis: many "database" problems are application-shaped. An ORM lazily issuing one query per item in a loop produces a thousand fast queries that sum to a slow page — no index fixes architecture.

Step 4: Re-measure and compare honestly#

Run EXPLAIN ANALYZE again. Compare the top-node times, not vibes. Legit improvements show up as: Seq Scan → Index Scan, removed Sort nodes, collapsed actual times. If the plan didn't change, your index isn't being used — check why indexes get ignored (function-wrapped columns, type mismatches, low selectivity).

One caution: ANALYZE executes the query for real. For write queries or production-heavy reads, wrap in a transaction and roll back, or test against realistic staging data volumes — plans on empty dev tables lie about production behavior.

Beyond the query: structural escalations#

When a query is already optimal but still heavy, the fix is architectural, and interviews probe exactly this escalation ladder:

1. Denormalize deliberately — maintain a summary column/table updated on write (consistency tradeoffs documented)
2. Materialized views — precompute expensive aggregations, refresh on schedule
3. Partitioning — split huge tables by range (dates) so queries touch slices
4. Caching layers — serve hot read results from Redis-class stores
5. Read replicas — scale read traffic horizontally

Each step trades complexity for performance — reach for them in order only after the query itself is provably lean.

The checklist habit#

Next slow query, resist instinct and run the loop instead: EXPLAIN ANALYZE → find dominant node → diagnose (scan type? estimates? sort?) → one targeted change → re-run → confirm or revert. Ten minutes of this beats days of speculative tweaking, and the plans you read along the way compound into genuine database fluency — the kind interviews and incidents both reward.

Related: index deep dive · isolation levels · system design framework