A query that ran in 20 milliseconds locally suddenly takes 4 seconds in production once a table crosses a million rows. The instinctive fix is to slap an index on the column in the WHERE clause and hope it helps. Sometimes it does, often it doesn't, because the real cause is elsewhere: a composite index with the wrong column order, stale statistics, or a query shape that structurally prevents PostgreSQL from using the index that already exists. EXPLAIN ANALYZE is the tool that replaces guessing with evidence.
What the planner is actually doing
PostgreSQL doesn't pick an execution plan at random. For every query, the planner estimates the cost of several candidate strategies (sequential scan, index scan, bitmap index scan, nested loop, hash join, merge join...) based on statistics it maintains per table: estimated row count, column cardinality, value distribution histograms. Those statistics are refreshed by ANALYZE, which autovacuum runs automatically, but not always as often as you'd assume.
The key thing to internalize: having an index is never a guarantee it gets used. If the planner estimates a sequential scan is cheaper than an index scan, for example because the WHERE condition matches 40% of the table's rows, it will pick the sequential scan, and it will usually be right. An index only pays off when the condition's selectivity is strong enough to make a targeted lookup cheaper than reading the whole table.
Reading an EXPLAIN ANALYZE plan
The diagnostic command to run is:
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT o.id, o.total_amount, c.email
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'pending'
AND o.created_at > now() - interval '30 days'
ORDER BY o.created_at DESC
LIMIT 50;
ANALYZE actually executes the query (be careful on an UPDATE/DELETE, wrap it in a transaction you roll back) and prints real timings next to the estimates. BUFFERS adds the number of blocks read from cache (shared hit) versus from disk (shared read), which is often more telling than the raw time.
A degraded plan typically looks like this:
Limit (cost=15234.12..15234.25 rows=50 width=48) (actual time=812.441..812.498 rows=50 loops=1)
-> Sort (cost=15234.12..15298.67 rows=25820 width=48) (actual time=812.439..812.460 rows=50 loops=1)
Sort Key: o.created_at DESC
Sort Method: top-N heapsort Memory: 28kB
-> Hash Join (cost=892.00..14560.33 rows=25820 width=48) (actual time=4.102..798.215 rows=26104 loops=1)
Hash Cond: (o.customer_id = c.id)
-> Seq Scan on orders o (cost=0.00..13201.00 rows=25820 width=24) (actual time=0.015..742.330 rows=26104 loops=1)
Filter: ((status = 'pending') AND (created_at > (now() - '30 days'::interval)))
Rows Removed by Filter: 473896
-> Hash (cost=612.00..612.00 rows=22000 width=32) (actual time=3.890..3.891 rows=22000 loops=1)
-> Seq Scan on customers c (cost=0.00..612.00 rows=22000 width=32)
Planning Time: 0.412 ms
Execution Time: 814.102 ms
Signals worth checking, in order:
Seq ScanonorderswithRows Removed by Filter: 473896: the database reads roughly half a million rows to keep only 26,000. That's the core of the problem.- Read the plan from the bottom up: execution starts at the innermost nodes.
- Compare estimated
rowstoactual rows: a large gap (estimated 500, actual 26,000) points to stale statistics and should prompt a manualANALYZE ordersbefore anything else. Execution Timeversus the sum of per-node times: aSortorHashnode that outgrowswork_memspills to disk (Sort Method: external merge), which costs far more than an in-memory sort.
The fix: an index that matches the real filter
The query filters on status and created_at, then sorts by created_at. A composite index that mirrors that usage order (equality column first, range/sort column second) lets PostgreSQL do a direct index scan with no extra sort step:
CREATE INDEX CONCURRENTLY idx_orders_status_created_at
ON orders (status, created_at DESC)
WHERE status = 'pending';
The index is partial (WHERE status = 'pending') because only that status value is queried in this hot path; a partial index stays small and cheap to maintain even as the table grows. CONCURRENTLY avoids locking writers while the index builds, at the cost of a slower build and the need to handle a possible failure (the index is then left INVALID and must be dropped and recreated).
After creating it, the plan becomes:
Limit (cost=0.43..892.31 rows=50 width=48) (actual time=0.052..1.845 rows=50 loops=1)
-> Nested Loop (cost=0.43..5102.88 rows=286 width=48) (actual time=0.051..1.832 rows=50 loops=1)
-> Index Scan using idx_orders_status_created_at on orders o
(cost=0.43..1890.21 rows=286 width=24) (actual time=0.028..0.912 rows=50 loops=1)
Index Cond: (created_at > (now() - '30 days'::interval))
-> Index Scan using customers_pkey on customers c
(cost=0.29..0.35 rows=1 width=32) (actual time=0.015..0.016 rows=1 loops=50)
Index Cond: (id = o.customer_id)
Execution Time: 2.103 ms
The sort step is gone (the index already provides created_at DESC ordering), the sequential scan is gone, and execution time drops from 814 ms to roughly 2 ms.
The traps that keep an index invisible
Even after creating the right index, a few common mistakes still make the planner ignore it:
- Implicit type mismatch: comparing a
varcharcolumn against a numeric literal, or atimestampcolumn against a poorly cast string, forces PostgreSQL to cast every row, which disqualifies the index. - A function applied to the indexed column:
WHERE lower(email) = 'x@y.com'cannot use a plain index onemail. The fix is either an expression index (CREATE INDEX ON customers (lower(email))) or rewriting the query. ORacross different columns:WHERE status = 'pending' OR priority = 'high'can't efficiently use a single composite index; the fix is usually two separate indexes, which the planner can combine automatically via aBitmapOrnode when both exist.- Column order in a composite index: an index on
(created_at, status)doesn't help a query that filters onstatusalone, for the same reason a phone book sorted by city then name doesn't help you search by name alone.
Covering indexes and index-only scans
There's one more tier of optimization worth knowing once the basic composite index is in place: if every column the query needs is already present in the index itself, PostgreSQL can skip visiting the table's heap entirely and answer straight from the index. This shows up in a plan as Index Only Scan instead of Index Scan:
CREATE INDEX CONCURRENTLY idx_orders_status_created_at_covering
ON orders (status, created_at DESC) INCLUDE (total_amount, customer_id)
WHERE status = 'pending';
The INCLUDE clause adds total_amount and customer_id as payload columns without making them part of the index's sort key, so they ride along for an index-only scan without affecting how the index is searched. This only pays off if the table's visibility map is mostly up to date, which depends on VACUUM running often enough; a table with heavy churn and infrequent vacuuming will fall back to regular index scans anyway because PostgreSQL still has to check row visibility against the heap. Running VACUUM ANALYZE orders manually and re-checking the plan is the fastest way to confirm whether vacuum frequency, not the index itself, is the bottleneck.
Finding slow queries before a user reports them
Everything above assumes you already know which query to investigate. In practice, that query surfaces reactively, from a user complaint or a timeout alert, unless pg_stat_statements is enabled. That extension tracks aggregate execution statistics for every normalized query PostgreSQL runs, which turns "something is slow" into a ranked list:
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
SELECT query, calls, mean_exec_time, total_exec_time
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;
Sorting by total_exec_time rather than mean_exec_time surfaces the queries that cost the most aggregate database time, which is often a moderately slow query called ten thousand times an hour rather than a rare three-second outlier. That distinction matters for prioritization: optimizing the first gives a real, measurable reduction in overall database load, while the second may not be worth the engineering time if it only affects a rarely used admin screen.
When indexing isn't enough
Adding indexes has a cost that's easy to forget: every INSERT/UPDATE has to maintain all of a table's indexes, so a table with fifteen indexes under heavy write traffic can see write latency balloon while reads fly. At that point the serious options are:
- Partitioning the table (typically by date range) once volume crosses tens of millions of rows and queries naturally filter on the partition key.
- A materialized view refreshed on a schedule, when the query feeds a dashboard that can tolerate a few minutes of staleness.
- Revisiting the application model itself (targeted denormalization, a Redis cache in front) when the query runs at very high frequency and even a couple of milliseconds per call adds up across thousands of calls per second.
Takeaway
EXPLAIN ANALYZE with BUFFERS always gives you a fact, never a guess: read it bottom-up, compare estimated rows to actual rows, and look for high Rows Removed by Filter counts. An index is only a solution when it matches the real selectivity and sort order of the actual query, not just a hunch about "the WHERE column." Test any new index with EXPLAIN ANALYZE against representative data before shipping it, and create it with CONCURRENTLY.
