A 40-second query that should have been 8ms. The plan was right; the statistics were four months stale.
This query does a seq scan on a 40M row table even though there's an index on created_at:
select * from events where created_at > now() - interval '1 hour' order by created_at desc limit 100;
Get the plan with real numbers first:
explain (analyze, buffers) select * from events
where created_at > now() - interval '1 hour'
order by created_at desc limit 100;
The thing to compare is rows= (the estimate) against actual rows=. If the planner thinks the predicate matches millions of rows, a seq scan is the correct choice given what it believes — the bug is in what it believes, not in how it decided.
On an append-only events table this is usually stale statistics: the planner's histogram thinks the newest created_at is months old, so "the last hour" looks like it covers most of the table.
Estimated 12M rows, actual 1,847. Statistics were last updated in April.
That's the whole bug. Fix it now, then stop it recurring:
analyze events;
Then make autovacuum keep up on this table specifically. The defaults scale with table size, so on a 40M-row table it waits for ~4M changes before analysing:
alter table events set (
autovacuum_analyze_scale_factor = 0.01,
autovacuum_analyze_threshold = 10000
);
And consider raising the histogram resolution on the column you filter by:
alter table events alter column created_at set statistics 500;
analyze events;
Append-only tables with time-range queries are the classic case for this — the newest values are always outside the recorded histogram, which is precisely the range everyone queries.
Sign in to join the conversation.