Re: [SQL] 7.3.1 index use / performance

Rod Taylor <rbt@rbt.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