Обсуждение: Is index enough to do simple fetch, or table is always used too?

Hello.

Suppose I have the following index:

CREATE INDEX idx ON tbl  USING btree (abc, def, id)

and perform the query with index scan:

SELECT md5(id)
FROM tbl
WHERE abc=1 AND def=2
LIMIT 200

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...

(Some time ago I have read something about this behaviour somewhere, but now cannot find it in the PostgreSQL documentation. Possibly it were words about another database, not Postges?)

2008/12/14 Dmitry Koterov <dmitry@koterov.ru>:
> Hello.
>
> Suppose I have the following index:
>
> CREATE INDEX idx ON tbl  USING btree (abc, def, id)
>
> and perform the query with index scan:
>
> SELECT md5(id)
> FROM tbl
> WHERE abc=1 AND def=2
> LIMIT 200
>
> 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.

>
> (Some time ago I have read something about this behaviour somewhere, but now
> cannot find it in the PostgreSQL documentation. Possibly it were words about
> another database, not Postges?)
>
>

Maybe here?  http://www.postgresql.org/docs/8.3/static/mvcc.html

David

But isn't an index data is also multi-version?
If no, how could a single-versioned index be used to fetch the data from a past snapshot?

2008/12/14 Dmitry Koterov <dmitry@koterov.ru>:
>
> On Sun, Dec 14, 2008 at 3:36 PM, David Rowley <dgrowley@gmail.com> wrote:
>>
>> 2008/12/14 Dmitry Koterov <dmitry@koterov.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

On Sun, Dec 14, 2008 at 4:15 PM, David Rowley <dgrowley@gmail.com> wrote:
> 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.

There are some plans to use the new visibility map feature (coming in
8.4) to allow index only lookups under certain conditions.  That
wouldn't happen until 8.5 at the earliest however.

merlin