A user’s profile page loads their recent points activity: earn, spend, clawback, expire. The query is straightforward.

SELECT type, amount, source_id, created_at
FROM points_entries
WHERE user_id = $1
  AND created_at >= $2
  AND created_at < $3
ORDER BY created_at DESC
LIMIT 50;

The table has an index on (user_id, created_at). The query runs in single-digit milliseconds. It has for months. Then during a flash sale, the profile page starts timing out.

What changed

Nothing. The query didn’t change. The index didn’t change. The table grew.

points_entries is append-only. Every transaction inserts a row. Earn, redeem, clawback, expire – four types, one direction. The table had passed 30 million rows sometime in the last quarter. Nobody noticed because the queries were still fast. Until they weren’t.

EXPLAIN ANALYZE
SELECT type, amount, source_id, created_at
FROM points_entries
WHERE user_id = 48291
  AND created_at >= '2019-09-01'
  AND created_at < '2019-09-15'
ORDER BY created_at DESC
LIMIT 50;

Limit  (cost=0.57..8952.34 rows=50 width=48)
  (actual time=2145.823..2145.891 rows=50 loops=1)
  ->  Index Scan Backward using idx_user_created on points_entries
        (cost=0.57..1428762.91 rows=7981 width=48)
        (actual time=2145.819..2145.871 rows=50 loops=1)

Wait. That’s an index scan. It’s using the index. Why is it taking two seconds?

Look at the row estimates. The planner thinks it’ll find 7,981 rows for this user in this date range. The actual is 50 (the LIMIT stops early). But the planner still has to walk the index far enough to guarantee it found all 50. The user has 15,000 entries in the window, and the index scan touches a large fraction of the index before the LIMIT cuts it off. The storage is random, not sequential.

A week later, for a heavy user during a campaign spike:

Limit  (cost=1000.00..2134567.00 rows=50 width=48)
  (actual time=3124.667..3124.691 rows=50 loops=1)
  ->  Gather Merge
        (actual time=3124.664..3124.685 rows=50 loops=1)
        Workers Planned: 2
        Workers Launched: 2
        ->  Sort
              (actual time=3118.234..3118.241 rows=17 loops=3)
              Sort Key: created_at DESC
              Sort Method: top-N heapsort
              ->  Parallel Seq Scan on points_entries
                    (cost=0.00..2133456.00 rows=7981 width=48)
                    (actual time=8.912..3116.567 rows=5234 loops=3)
                    Filter: ((user_id = 12834) AND
                      (created_at >= '2019-09-01'::date) AND
                      (created_at < '2019-09-15'::date))

The planner abandoned the index entirely. Parallel sequential scan. Three workers, reading 30 million rows, filtering by user_id. Three seconds. The index is still there. Postgres decided not to use it.

Why the planner switched

The cost model has constants you never think about until they matter. random_page_cost defaults to 4.0. seq_page_cost defaults to 1.0. These numbers assume a spinning disk where random I/O is four times more expensive than sequential I/O.

On cloud SSD, the ratio is closer to 1.0:1.0. Random pages are nearly free. But Postgres doesn’t know you’re on SSD. It sees a query filtering by user_id – an equality filter that scatters across the table, one row here, another fifty pages away, another thirty pages after that. The cost model multiplies each scattered page fetch by 4.0. When the estimated row count is low, the penalty is small. When it crosses a threshold, the planner decides reading the whole table sequentially is cheaper than 8,000 random page reads at 4x cost.

The threshold is lower than you think. On a table with 30 million rows and a user with 8,000 entries, the planner does the math: 8,000 random reads × 4.0 = 32,000 cost units. Sequential scan of 30 million rows: lower. The planner is right, given its assumptions. The assumptions are wrong.

What fixed it

Three things, in the order we tried them.

First: statistics. ANALYZE points_entries gave the planner fresh histograms. It helped for a day. The table was growing too fast for the default autovacuum threshold to keep up. Increasing the statistics target on user_id from 100 to 1000 gave the planner better data about the distribution – some users have 10 entries, some have 50,000. The histogram matters when the gap is that wide.

ALTER TABLE points_entries ALTER COLUMN user_id SET STATISTICS 1000;
ANALYZE points_entries;

Second: hardware reality. The production database lived on SSD. The planner didn’t know.

ALTER DATABASE points_db SET random_page_cost = 1.5;

One line. The planner’s cost estimate for index scans dropped by 62.5%. It started choosing the index again. Query times returned to single-digit milliseconds.

The default value of 4.0 shipped with Postgres in 2003. It was correct for the hardware of that year. It has not been correct for most production databases in over a decade. Every database you inherit probably hasn’t been told.

Third: the index that should have been there from the start. A covering index.

CREATE INDEX idx_user_created_covering ON points_entries
  (user_id, created_at DESC)
  INCLUDE (type, amount, source_id);

The original index required a trip to the table for every matching row – the index had user_id and created_at, but type, amount, and source_id had to come from the heap. Each trip was a random read. With INCLUDE, the index carries all the columns the query needs. No heap access. The index is the table, for this query.

Postgres 11 added covering indexes in October 2018. The feature was new enough that nobody had gone back and rebuilt old indexes to use it. This query was the one that made us do it.

What stuck

The defaults are not your friend. random_page_cost is a hardware setting masquerading as a query planner constant. Every Postgres instance you touch, check what it thinks the storage is. If it’s not a spinning disk from 2003, tell it.

Indexes earn their place by being used. The planner decides. When the planner decides wrong, don’t add an index. Look at the assumptions first. Statistics freshness. Cost constants. Selectivity estimates. The gap between estimated rows and actual rows in EXPLAIN ANALYZE is the diagnostic. Everything else follows from closing that gap.

EXPLAIN (ANALYZE, BUFFERS) became the first thing I run, not the last. The query plan is a conversation with the planner. It tells you what it thinks. If what it thinks is wrong, the fix is rarely in the SQL.