Rob Nagler <nagler@bivio.biz> writes:
> What I'm not sure is why does it decide to switch modes so "early",
> i.e., at about 5% of the table size or less?
Given the default cost parameters and cost models, that's the correct
place to switch. Since the estimate evidently doesn't match reality
for your case, you might want to play with the parameters. Reducing
random_page_cost would be the first thing I'd try. Some people think
that increasing effective_cache_size is a good idea too, though I feel
that that has only marginal impact on the planner's choices.
Keep in mind though that you seem to be experimenting with a
fully-cached database; you may find that the planner's beliefs more
nearly approach reality when actual I/O has to occur.
Another thing I'd be interested to know about is how closely the
physical order of the table entries correlates with min_date_time.
A high correlation reduces the actual cost of the indexscan (since
visiting the rows in index order becomes less of a random-access
proposition). We are aware that the planner doesn't model this effect
very well at present ...
regards, tom lane