Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 18BC74762A4 for ; Tue, 7 Jan 2003 14:26:14 -0500 (EST) Received: from sss.pgh.pa.us (unknown [192.204.191.242]) by postgresql.org (Postfix) with ESMTP id C764647607B for ; Tue, 7 Jan 2003 14:26:11 -0500 (EST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.6/8.12.6) with ESMTP id h07JQ80U019890; Tue, 7 Jan 2003 14:26:08 -0500 (EST) To: Rod Taylor Cc: Achilleus Mantzios , Stephan Szabo , Pgsql Performance , pgsql-general@postgresql.org, pgsql-sql@postgresql.org Subject: Re: [SQL] [PERFORM] 7.3.1 index use / performance In-reply-to: <1041965114.39376.39.camel@jester> References: <14598.1041961456@sss.pgh.pa.us> <1041965114.39376.39.camel@jester> Comments: In-reply-to Rod Taylor message dated "07 Jan 2003 13:45:14 -0500" Date: Tue, 07 Jan 2003 14:26:08 -0500 Message-ID: <19889.1041967568@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200301/252 X-Sequence-Number: 35373 Rod Taylor 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