To: Alvaro Herrera <alvherre@commandprompt.com>
cc: Heikki Linnakangas <heikki@enterprisedb.com>,
	Guillaume Cottenceau <gc@mnc.ch>, pgsql-performance@postgresql.org
Subject: Re: Poor performance on seq scan 
In-reply-to: <20060912133254.GC11324@alvh.no-ip.org> 
References: <450684EC.4090304@designaproduct.biz>
	<4506902C.7090407@enterprisedb.com>
	<4506A9E7.2060303@designaproduct.biz>
	<87y7spw8v9.fsf@meuh.mnc.lan> <4506B508.80806@enterprisedb.com>
	<20060912133254.GC11324@alvh.no-ip.org>
Comments: In-reply-to Alvaro Herrera <alvherre@commandprompt.com>
	message dated "Tue, 12 Sep 2006 09:32:55 -0400"
Date: Tue, 12 Sep 2006 10:52:21 -0400
Message-ID: <16188.1158072741@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

Alvaro Herrera <alvherre@commandprompt.com> writes:
> Are you saying that an indexscan "Filter" only acts after getting the
> heap tuple?

Correct.

> If that's the case, then there's room for optimization
> here, namely if the affected column is part of the index key, then we
> could do the filtering before fetching the heap tuple.

Only if the index is capable of disgorging the original value of the
indexed column, a fact not in evidence in general (counterexample:
polygons indexed by their bounding boxes in an r-tree).  But yeah,
it's interesting to think about applying filters at the index fetch
step for index types that can hand back full values.  This has been
discussed before --- I think we had gotten as far as speculating about
doing joins with just index values.  See eg here:
http://archives.postgresql.org/pgsql-hackers/2004-05/msg00944.php
A lot of the low-level concerns have already been dealt with in order to
support bitmap indexscans, but applying non-indexable conditions before
fetching from the heap is still not done.

			regards, tom lane