Re: LIKE, leading percent, bind parameters and indexes

Heikki Linnakangas <hlinnaka(at)iki(dot)fi> writes:
> On Sat, 27 May 2006, Martijn van Oosterhout wrote:
>> Actually, for a first pass I was considering doing it within the
>> nodeIndexScan.c/nodeBitmapScan.c and not within the AM at all. But I
>> just remembered, the index interface has no way to return the actual
>> values in the index, so you can't do that :(

> This discussion reminds me of the idea to do index-only scans,
> returning tuples directly from an index without hitting the heap
> at all. MVCC is the main problem there, but it would be nice that
> whatever you come up with here would be usable if we ever implement
> index-only scans.

Given my worries about needing to copy the index tuples anyway, maybe
the right way to approach this is to add a separate AM entry point
that's like amgettuple except it hands you back whole index tuples and
not just the heap TID part. This would only be implemented by those
AMs that store the unmodified original index tuple (ie, not GiST/GIN).
Then the filtering on auxiliary conditions can be done once in the
executor code as envisioned by Martijn, and we'd also have the AM support
in place to do generalized separate index and heap scans.

I recall some discussion of using something like this to implement
joining before visiting the heap, in situations where all the join
keys are available in an index but there are too many rows for nestloop
index joining to be sufficient. You'd pull the join keys from the
index, run merge or hash join, and then visit the heap only for the
candidate join rows. It hasn't got done yet but it seemed like a
potentially good idea at the time. [ pokes around... ] The original
discussion was Red Hat private, apparently, but I mentioned it here:
http://archives.postgresql.org/pgsql-hackers/2004-05/msg00944.php
Some of that is probably superseded now by bitmap indexscans, but
not all of it.

regards, tom lane