Mark Kirkwood <mark.kirkwood@xxxxxxxxxxxxxxx> 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 -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
