DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta;
	h=received:message-id:date:from:user-agent:mime-version:to:cc:subject:references:in-reply-to:content-type:content-transfer-encoding:sender;
	b=maTOftZoD7B00Xucfp14TGYVIBq0o5a/enzKszYFshslndZC7CLLL3vgtJSfUUBO7u4a+cu9tJx6IXlU66s/Tdre/Tyv7qAEQBK1+uDqVxCyNWK5ES9gSPptLwzAHxRX60gdJww037UdpqsIUisjOTUE/spZK/OkqGkufv9klCU=
Message-ID: <46BCC4F9.70903@enterprisedb.com>
Date: Fri, 10 Aug 2007 21:05:13 +0100
From: Heikki Linnakangas <heikki@enterprisedb.com>
User-Agent: Icedove 1.5.0.10 (X11/20070329)
MIME-Version: 1.0
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>
CC: pgsql-performance@postgresql.org
Subject: Re: Bitmap Index Scan optimization opportunity
References: <46BC7737.EE98.0025.0@wicourts.gov>
In-Reply-To: <46BC7737.EE98.0025.0@wicourts.gov>
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

Kevin Grittner wrote:
> These query times are the "fully cached" times for both, from doing a previous run of the same query.  (The first one took 193.772 ms on its first run; I don't have a good "uncached" timing for the second one at this point.)
>  
> It seems like the first query could move the searchName filter to the Bitmap Index Scan phase, and save 97.5% of the page retrievals in the Bitmap Heap Scan.

Yes it could in theory, but unfortunately the planner/executor doesn't
have the capability to do that. An indexed value is never handed back
from the index; the indexed values are only used to satisfy index
conditions, not filters. It's been discussed before (see
http://archives.postgresql.org/pgsql-performance/2006-09/msg00080.php),
but it's not easy to implement so no one's done it yet.

-- 
  Heikki Linnakangas
  EnterpriseDB   http://www.enterprisedb.com