Mark Kirkwood <mark.kirkwood@catalyst.net.nz> writes:
> This is with each version loading exactly the same dataset (generated by
> the attached scripty). Obviously this is a vast simplification of what
> Josh is looking at - but it is (hopefully) interesting that these later
> versions are doing so much better...
Actually, what I see when using this dataset is that both the estimated
cost and the actual runtime of the query are within a percent or so of
being the same when using either index. (Try forcing it to use the
non-preferred index by dropping the preferred one, and you'll see what
I mean.) The absolute magnitude of the cost estimate varies across
versions, but not the fact that we're getting about the same estimate
for both indexes.
I suspect the same may be true for Josh's real-world database, meaning
that the index choice is depending on phase-of-the-moon factors like
which index has the lower OID, which is doubtless contributing to
his frustration :-(
I think that the real key to this problem lies in the index bloat pattern,
which might be quite a bit different between the two indexes. This might
mean traversing many more index leaf pages in one case than the other,
which would account for the difference in real runtimes that he's seeing
and I'm not. I don't recall at the moment whether 9.2's cost estimation
rules would do a good job of accounting for such effects. (And even if
it's trying, it'd be working from an average-case estimate, which might
not have much to do with reality for this specific query.)
regards, tom lane