The index reads 5 pages; the scan, 16. The scan wins.
A page read in order costs 1.0, because storage reads ahead. A random page costs 4.0. So 16 × 1.0 beats 5 × 4.0.
0.8%
where a plain index scan stops winning
On 10 million bookings the scan costs 225,000, whatever matches. Scattered rows cost a random page each, so past ≈ 77,000, one row in 125, the index loses.
Same 1% of rows: 1,000 pages in a row, or 66,700 scattered
Bookings are appended in time order, so booked_at matches sit together. At correlation 0.9 the index wins up to ≈ 41%.
Correlation 0.5 sounds like half the benefit of rows stored in index order.
The catch
The planner weights the in-order cost by correlation squared: 0.25. The crossover moves only from 0.8% to ≈ 1.2%.
The middle ground: a bitmap scan
- Mark each matching page in a bitmap
- Read marked pages once, in physical order
- AND or OR bitmaps from two indexes
- Rows lose index order; big bitmaps go lossy
Index-only scan: skips the table, until an hour of writes
Visibility lives in the heap. A write clears its page’s all-visible bit, sending lookups back to the table until VACUUM sets it again.
Planner skipped your index?
Bad: Disable seq scans
Leaving enable_seqscan off freezes today’s choice while the data keeps changing.
Good: Check the estimate
Right estimate: the scan is probably right. Wrong: fix the statistics, not the plan.
Remember
Random pages cost 4× sequential ones, so scattered matches lose near 1%. Check the estimate before blaming the planner.