Re: Is index enough to do simple fetch, or table is always used too?

2008/12/14 Dmitry Koterov <dmitry(at)koterov(dot)ru>:
>
> On Sun, Dec 14, 2008 at 3:36 PM, David Rowley <dgrowley(at)gmail(dot)com> wrote:
>>
>> 2008/12/14 Dmitry Koterov <dmitry(at)koterov(dot)ru>:
>> > The question: if the table "tbl" scanned to fetch "id" and calculate
>> > md5(id), or the value of "id" is brought directly from "idx" index with
>> > no
>> > table data access at all? The second behaviour is logical: why should we
>> > access the table if all the needed data is already in the index entry...
>>
>> In fact not all the required information is in the index. Postgresql
>> uses Multi-version-concurrency-control, which means there may be multi
>> versions of the same row. Postgresql must hit the heap (table) no get
>> the visibility information.
>
> But isn't an index data is also multi-version?

Yes. with some exections where HOT does not add another index entry if
the new row fits on the same page as the old one and the index value
does not change.

The index does not know if the row is dead or alive to the current
transaction. This is only known by the heap. So for your example query
to execute, it may be possible to scan the index but the heap will
need to be checked to see if the row is alive or dead.

> If no, how could a single-versioned index be used to fetch the data from a
> past snapshot?
>

David