"Nigel J. Andrews" <nandrews@investsystems.co.uk> writes:
> I really don't know sufficient to be able to see why the stats would
> favour one index over the other. Although looking at the pg_stats
> entries below now I notice that the correlation for the time column is
> 1, compared to 0.058 for the poster_id.
Ah, of course, that would do it. Thinking about it, I see that the
system is not really very bright about ordering considerations for
multicolumn indexes. On a macro scale, the posterid/time index is
poorly correlated with the physical table order --- but when you
consider only the set of entries for a single posterid over a small
time range, the index is pretty well correlated. The planner doesn't
consider that effect, so it mistakenly credits the time/posterid
index with much higher correlation and hence lower scan cost than the
other.
I already had a todo item to reconsider the costing estimates for
multicolumn indexes --- will see if we can be smarter about cases like
this.
regards, tom lane