Index-only scans

Implementing index-only scans requires a few changes:

1. indexam API changes

There's currently no way to return data from an index scan. You only get
TID pointers to heap tuples.

2. Make visibility map crash-safe

After crash, the visibility map can currently be left in state where it
has some bits set that shouldn't be. That's OK for the 8.4 use, but
until that's fixed we can't use it to skip visibility checks from the
heap. The problem is described in the comments at the top of
visibilitymap.c. I'll start a new thread on how to tackle that.

3. Executor changes

The IndexScan node needs to be able to evaluate expressions and return
tuples straight from the index, skipping heap fetches. Those are
actually two separate features. Even if we don't solve the visibility
map problem, just allowing the executor to evaluate quals that are not
directly indexable using data from the index, would be useful. For
example, "SELECT * FROM foo WHERE textcol LIKE '%bar%', and you have a
b-tree index on textcol, the planner could choose a full-index-scan,
apply the '%bar%' filter on the index tuples, and only fetch those heap
tuples that match that qual.

4. Planner changes

The planner obviously needs to be changed to support the executor
changes. It needs to identify those quals that can be evaluated using
index columns only, and if the target list can be satisfied using index
columns only. It also needs to consider using a full index scan in some
queries where we didn't consider using an index before. The cost
evaluation also needs to be adjusted to take all that into account.

I have a prototype of this, except for the visibility map changes, at
http://git.postgresql.org/gitweb?p=heikki.git;a=shortlog;h=refs/heads/index-only-scans.
It's still work-in-progress, but should give an indication of where I'm
heading. For July commitfest, I'd like to get the indexam API changes
committed, and support for evaluating quals using data from index (which
doesn't require the visibility map). I'll submit patches for those shortly.

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