Re: [SQL] [PERFORM] 7.3.1 index use / performance

Rod Taylor <rbt(at)rbt(dot)ca> writes:
>> I am wondering about a compiler bug, or some other peculiarity on your
>> platform. Can anyone else using FreeBSD try the above experiment and
>> see if they get different results from mine on 7.3.* (or CVS tip)?

> On FreeBSD 4.7 I received the exact same results as Tom using the
> statements shown by Tom.

On looking at the code, I do see part of a possible mechanism for this
behavior: cost_index calculates the estimated cost for qual-clause
evaluation like this:

/*
* Estimate CPU costs per tuple.
*
* Normally the indexquals will be removed from the list of restriction
* clauses that we have to evaluate as qpquals, so we should subtract
* their costs from baserestrictcost. XXX For a lossy index, not all
* the quals will be removed and so we really shouldn't subtract their
* costs; but detecting that seems more expensive than it's worth.
* Also, if we are doing a join then some of the indexquals are join
* clauses and shouldn't be subtracted. Rather than work out exactly
* how much to subtract, we don't subtract anything.
*/
cpu_per_tuple = cpu_tuple_cost + baserel->baserestrictcost;

if (!is_injoin)
cpu_per_tuple -= cost_qual_eval(indexQuals);

In theory, indexQuals will always be a subset of the qual list on which
baserestrictcost was computed, so we should always end up with a
cpu_per_tuple value at least as large as cpu_tuple_cost. I am wondering
if somehow in Achilleus's situation, cost_qual_eval() is producing a
silly result leading to negative cpu_per_tuple. I don't see how that
could happen though --- nor why it would happen on his machine and not
other people's.

regards, tom lane