Re: Index-only scans

Simon Riggs wrote:
> On Mon, 2009-07-13 at 10:16 +0300, Heikki Linnakangas wrote:
>
>> Implementing index-only scans requires a few changes:
>
> I would like to see a clear exposition of the use cases and an an
> analysis of the costs and benefits of doing this. It sounds cool, but I
> want to know it is cool before we spend time solving all of the juicy
> problems.

BTW, there's another trick that I'm *not* going to implement yet, which
is to allow joins using data from indexes only, and fetching the rest of
the columns after the join. For example:

CREATE TABLE a (aid integer PRIMARY KEY, adata text);
CREATE TABLE b (bid integer PRIMARY KEY, bdata text);

SELECT aid, adata, bid, bdata FROM a, b WHERE aid = bid;

If the join is very selective, IOW there's only a few matching rows, it
is a lot more efficient to perform the join first using just the
indexes, and fetch adata and bdata columns and check visibility for the
matching rows only.

You can get pretty close by clustering the tables, but the wider the
tables the bigger the difference.

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