Thread: Are cost estimates based on asserts?

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

"Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:
> [$subject]

No, the planner has no idea about the cost of Asserts.
        regards, tom lane

Hmmm... Poorly phrased question on my part, I guess.
When I saw that the cost estimates tracked so much better
with the actual execution time when the asserts were being
evaluated, it got me wondering whether some significant part
of the costing development and testing occured in that
environment.

I assume we care more about accurate costing in
environments where asserts are not checked, although I
hate to assume anything.  If there is a chance that asserts
affected costing calculations, it would give me a vague clue
about where adjustments might be most useful.  For a
discussion about a better way to get clues, see my next
post.

-Kevin


>>> Tom Lane <tgl@sss.pgh.pa.us> 10/12/05 5:15 PM >>>
"Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:
> [$subject]

No, the planner has no idea about the cost of Asserts.
        regards, tom lane