40 rows in 1ms. 6 million rows in 9 seconds, with a sequential scan — on a table that has an index on user_id.

01Symptom

A 100M-row events table with a B-tree index on user_id. SELECT * FROM events WHERE user_id = $1 takes 1ms for a typical user with about 40 rows. For one whale account with 6M rows it takes 9s, and EXPLAIN shows a sequential scan even though the index exists. A teammate set enable_seqscan = off to force the index. The query now takes 40s. He asks why the planner is stupid and why the index made it worse.

02Constraints

• 100M rows, ~100 bytes each, 8KB pages — roughly 1.25M heap pages, about 10GB
• The user_id index is ~2.5GB, and a user's rows are scattered across the heap because inserts interleave across users over time
• 16GB RAM with shared_buffers = 4GB, so the table does not fit in cache
• SSD: ~0.1ms per random 8KB read, ~1GB/s sequential throughput
• The table is heavily updated and autovacuum sometimes lags
• The query must return full rows — SELECT * — for the whale's 6M rows

03Evidence

• →The typical-user plan is an index scan with ~40 heap fetches; the whale's plan is a seq scan over 1.25M pages
• →Forcing the index produces a plan dominated by random single-page reads across the whole table
• →The whale's rows are spread over nearly every one of the 1.25M pages — about 5 matches per page on average
• →EXPLAIN (ANALYZE, BUFFERS) on the typical user shows ~40 heap fetches and ~40 buffers read, so the index path touches almost nothing — the same plan is not slow, it is being asked to do a different job

→The question

Why is the index slower than reading the entire table, what is the planner actually comparing, and what would you do about a user with 6M rows?

04Your prediction