Hi Heikki,
I was recollecting our conversation that, if the index-only quals were implemented through indexes by storing snapshots, broken data-types may not be supported. I feel this problem might exist, if we go on design a IOT(Index Organized Tables) / Clustered Indexes. IOT is again a index which stores snapshot info together with it.
So are we not going to have this feature ever in PG? What is our stand in that case? I am asking this because i have an IOT implementation on PG 8.2.
Thanks,
Gokul
On Tue, Jul 14, 2009 at 2:08 PM, Heikki Linnakangas
<heikki.linnakangas@enterprisedb.com> wrote:
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.