Postgres indexes we wish we had added sooner

Our slow-query log had become background noise. Every morning it held a few hundred entries, every morning somebody skimmed it, and every morning we all agreed that we should really look into that. This is what happened when we finally did.

The short version: six index changes removed 94% of the entries. None of them were clever. All of them had been visible in pg_stat_statements for the better part of a year. What follows is the reasoning behind each one, because the reasoning generalises even when the specific indexes do not.

Start with the data, not the hunches

Everyone on the team had a theory. The theories were confident, mutually contradictory, and mostly wrong. So we started with the only thing that settles an argument about performance, which is a measurement.

SELECT
    calls,
    round(total_exec_time::numeric, 1) AS total_ms,
    round(mean_exec_time::numeric, 2)  AS mean_ms,
    round(100 * total_exec_time / sum(total_exec_time) OVER (), 1) AS pct,
    query
FROM pg_stat_statements
WHERE query NOT LIKE '%pg_stat_statements%'
ORDER BY total_exec_time DESC
LIMIT 20;

The result was the usual shape: a very short head and a very long tail. Two queries accounted for just over half of all execution time. Neither was slow in isolation. Both ran constantly.

A query that takes 40ms and runs ten thousand times an hour costs more than one that takes four seconds and runs twice a day. Optimise the bill, not the outlier.

The six changes

1. A partial index for the rows we actually read

Our posts table had eleven million rows. Roughly four percent were in a state anyone ever queried. The index covered all eleven million.

-- Before: 11M rows indexed, ~380MB
CREATE INDEX idx_post_published ON blog_post (published_at DESC);

-- After: ~440K rows indexed, ~14MB
CREATE INDEX CONCURRENTLY idx_post_live
    ON blog_post (published_at DESC, id DESC)
    WHERE status = 'published' AND published_at IS NOT NULL;

A 27x smaller index that answers the same question. It stays in cache, and the planner stopped falling back to a sequential scan when the statistics drifted.

2. A composite index that matches the real sort order

Our feed pages on (published_at, id) to keep pagination stable when two posts share a timestamp. The index was on published_at alone, so every page did a sort.

3. Dropping what nothing used

Two indexes had not been read since 2024. They were still being written to on every insert and update.

SELECT relname, indexrelname, idx_scan, pg_size_pretty(pg_relation_size(indexrelid))
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;

Check the number against a window that includes your quarterly jobs before dropping anything, and drop concurrently.

4. An expression index for a case-insensitive lookup

-- The query the ORM generates
SELECT * FROM accounts_user WHERE lower(email) = lower($1);

CREATE INDEX CONCURRENTLY idx_user_email_lower ON accounts_user (lower(email));

5. Covering the count query

Pagination counts are a classic: cheap to write, expensive to run. We replaced exact counts above ten thousand with an estimate from the planner, which nobody has noticed and which costs nothing.

SELECT reltuples::bigint AS estimate
FROM pg_class
WHERE relname = 'blog_post';

6. Raising the statistics target on a skewed column

One column held 200 distinct values with a very uneven distribution. The default sample was too small to represent it, so the planner mis-estimated and picked a nested loop over a hash join.

ALTER TABLE blog_post ALTER COLUMN status SET STATISTICS 500;
ANALYZE blog_post;

What we would tell ourselves a year ago

1. Turn on pg_stat_statements on day one. It costs almost nothing and you cannot recover the history later.

2. Sort by total time, never by mean time. The expensive query is rarely the slow one.

3. Always build indexes CONCURRENTLY in production, and always check pg_stat_user_indexes before adding another.

4. An index that is not used is not free: it is paid for on every write, forever.

The whole exercise took two afternoons. The slow-query log now holds single-digit entries on a normal day, which means somebody actually reads it.