I'm running right now with a dev build which was configured with
--enable-cassert (and --enable-debug). Just out of curiosity, I re-ran
the query which chose the sort and mergejoin over the (faster) nested
index scan. The results seem interesting, although I'd need more tests
to consider them entirely reliable. I've divided the actual run time
(in ms) by the cost estimate, to come up with the ratio of ms per unit
of estimated cost. I'll round to three significant digits, based on
observed variation from one test to another.
==============================
WITH --enable-cassert
==============================
uncached sort/merge: 0.194
uncached nested scan: 0.288
cached sort/merge: 0.0340
cached nested scan: 0.0560
==============================
WITHOUT --enable-cassert
==============================
uncached sort/merge: 0.182
uncached nested scan: 0.0478
cached sort/merge: 0.0256
cached nested scan: 0.00494
This shows me that when assertions are being tested, the cost estimates
are slightly skewed to favor nested scans. When assertions are not
tested, costs seem to be significantly distorted in favor of the
sort/merge. The assertion load on the index scan seems to be extreme.
It'll take some time to make sure that I can reproduce this, but I
wanted to put this out there for comment and possible independent
confirmation.
-Kevin