Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id DF4BC47685E; Wed, 8 Jan 2003 08:45:13 -0500 (EST) Received: from matrix.gatewaynet.com (unknown [217.19.69.50]) by postgresql.org (Postfix) with ESMTP id AC2154767AA; Wed, 8 Jan 2003 08:45:12 -0500 (EST) Received: from matrix.gatewaynet.com (matrix.gatewaynet.com [217.19.69.50]) by matrix.gatewaynet.com (8.12.5/8.12.5) with ESMTP id h08HrvXR014444; Wed, 8 Jan 2003 15:53:57 -0200 Received: from localhost (achill@localhost) by matrix.gatewaynet.com (8.12.5/8.12.5/Submit) with ESMTP id h08Hrs5Q014440; Wed, 8 Jan 2003 15:53:54 -0200 Date: Wed, 8 Jan 2003 15:53:54 -0200 (GMT+2) From: Achilleus Mantzios To: Tom Lane Cc: Rod Taylor , Stephan Szabo , Pgsql Performance , , Subject: Re: [SQL] [PERFORM] 7.3.1 index use / performance In-Reply-To: <19889.1041967568@sss.pgh.pa.us> Message-ID: MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200301/287 X-Sequence-Number: 35408 On Tue, 7 Jan 2003, Tom Lane wrote: > 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: > This bizarre index decreased cost (when adding conditions) behaviour maybe was due to some vacuums. (i cant remember how many reloads and vacuums i did to the database in the period petween the two emails). However my linux machine with the same pgsql 7.3.1, with a full clean installation also gives the same symptoms: Choosing the slow index, and after some (random) vacuums choosing the right index, and then after some vacuums chooses the bad index again. > > regards, tom lane > ================================================================== Achilleus Mantzios S/W Engineer IT dept Dynacom Tankers Mngmt Nikis 4, Glyfada Athens 16610 Greece tel: +30-10-8981112 fax: +30-10-8981877 email: achill@matrix.gatewaynet.com mantzios@softlab.ece.ntua.gr