For a long time my approach to a slow query was "add an index and see." Sometimes it worked. More often the planner ignored the new index entirely, or the index made writes slower without helping the read path I cared about. Eventually I forced myself to slow down and actually read query plans before touching the schema.
Start With the Plan, Not the Schema
The first move is always EXPLAIN (ANALYZE, BUFFERS), not CREATE INDEX. Without it you're guessing.
EXPLAIN (ANALYZE, BUFFERS)
SELECT o.id, o.total_cents, o.created_at
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE c.region = 'eu-west'
AND o.status = 'fulfilled'
ORDER BY o.created_at DESC
LIMIT 50;
On a table with a few million rows, this came back with a sequential scan on orders and a nested loop that fell over the moment region filtered down to a smaller set. The Buffers output told the real story: thousands of shared block hits just to throw most of the rows away.
Composite Indexes Beat Single-Column Guesses
My first instinct was to index orders.status alone. That helped a little, but the planner still had to sort the result by created_at afterward. A composite index that matches the filter and the sort order removed the separate sort step entirely:
CREATE INDEX CONCURRENTLY idx_orders_status_created_at
ON orders (status, created_at DESC)
WHERE status = 'fulfilled';
That last WHERE clause makes it a partial index — Postgres only indexes the rows I actually query for, which keeps it small and cheap to maintain. The plan afterward showed an index scan instead of a sequential scan, and the query went from roughly 900ms to under 15ms on our staging snapshot.
Why CONCURRENTLY Matters
Building an index normally takes a lock that blocks writes to the table for the duration. On a table that's actively receiving orders, that's not acceptable in production. CONCURRENTLY builds the index without blocking writes, at the cost of taking longer and requiring a bit more care (it can fail and leave an invalid index behind, so you check pg_index.indisvalid afterward).
Indexes You Don't Need Are Still Expensive
The tempting failure mode after this is to add an index for every query pattern you can imagine. Every index is extra work on every INSERT and UPDATE, and extra space the planner has to consider. I keep a short checklist before adding one:
- Does
EXPLAIN ANALYZEactually show a sequential scan or an expensive sort on a large row count? - Is this query on a hot path, or does it run twice a month from an admin panel?
- Can an existing index be extended (as a composite) instead of adding a new one?
I ended up dropping two indexes that looked reasonable on paper but were never used according to pg_stat_user_indexes:
SELECT relname, indexrelname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY relname;
That view is the single most useful thing I check before a migration review — it tells you honestly which indexes are dead weight.
Watching It in Production
After deploying the index change, the thing that mattered wasn't the local EXPLAIN output but the actual query duration in production, since data distribution and cache state are never quite the same as staging. I set up a quick dashboard query on pg_stat_statements to compare mean execution time before and after the migration window, which confirmed the improvement held up under real traffic rather than just on my seeded test data.
None of this is exotic. It's just the difference between guessing at the schema and reading what the database is actually telling you it's doing. The database service itself runs in the same multi-stage image I write about here, which made it easy to reproduce the exact plan locally before shipping the migration.