The Postgres index that took 9 seconds off a query

Most "add an index" advice stops right where it gets interesting. Here's the version I wish I'd had — written after a query that ran in 4ms on my laptop took 9 seconds in production.

The shape of the problem

We had a straightforward listing query:

SELECT id, title, published_at
FROM posts
WHERE status = 'PUBLISHED'
  AND author_id = $1
ORDER BY published_at DESC
LIMIT 20;

With 300 rows locally, everything is fast. Everything is always fast with 300 rows. That's the trap.

Read the plan, not the query

EXPLAIN (ANALYZE, BUFFERS) is the whole game:

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, title, published_at FROM posts
WHERE status = 'PUBLISHED' AND author_id = 'usr_123'
ORDER BY published_at DESC LIMIT 20;

The line that mattered:

Sort  (cost=48210.19..48338.44 rows=51301 width=48) (actual time=8914.221..8931.004 rows=20 loops=1)
  Sort Method: external merge  Disk: 24184kB
  ->  Seq Scan on posts  (actual time=0.019..8102.442 rows=51301 loops=1)

external merge Disk means Postgres gave up on sorting in memory and started writing to disk. It read every row, sorted 51,000 of them, and then threw away all but twenty.

Column order is not cosmetic

The fix was a composite index whose column order matches how the query filters and sorts:

CREATE INDEX CONCURRENTLY idx_posts_author_published
  ON posts (author_id, status, published_at DESC);

Three rules, in priority order:

1. Equality columns first — author_id, status.
2. Then the sort column, in the direction you sort it — published_at DESC.
3. Range predicates last, because they end the index's usefulness for everything after them.

Same query, after:

Limit  (actual time=0.048..0.121 rows=20 loops=1)
  ->  Index Scan using idx_posts_author_published on posts (actual time=0.045..0.108 rows=20 loops=1)

9 seconds to 0.12ms. No sort node at all — the index was already in the right order.

Always CONCURRENTLY

-- Locks the table. Your API is down for the duration.
CREATE INDEX idx_foo ON posts (author_id);
 
-- Doesn't. Slower, can fail and leave an INVALID index you must drop.
CREATE INDEX CONCURRENTLY idx_foo ON posts (author_id);

If a CONCURRENTLY build fails, clean it up before retrying:

SELECT indexrelid::regclass FROM pg_index WHERE NOT indisvalid;
DROP INDEX CONCURRENTLY idx_foo;

What I check now, before shipping any query

• Does the plan show a Seq Scan on a table that will grow? Fix it now, not later.
• Does it show Sort Method: external merge? Your index doesn't cover the ORDER BY.
• Is rows estimated wildly different from rows actual? Your statistics are stale — ANALYZE the table.
• Are you indexing a column with two distinct values on its own? That index will never be used.

None of this is clever. It's just the difference between a system that gets faster as you learn it and one that gets slower as it grows.