Re: Poor performance on seq scan

Alvaro Herrera <alvherre(at)commandprompt(dot)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