Re: [GENERAL] Is index enough to do simple fetch, or table is always used too?
On Sun, Dec 14, 2008 at 4:15 PM, David Rowley dgrow...@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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Is index enough to do simple fetch, or table is always used too?
2008/12/14 Dmitry Koterov dmi...@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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Is index enough to do simple fetch, or table is always used too?
On Sun, Dec 14, 2008 at 3:36 PM, David Rowley dgrow...@gmail.com wrote: 2008/12/14 Dmitry Koterov dmi...@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? If no, how could a single-versioned index be used to fetch the data from a past snapshot?
Re: [GENERAL] Is index enough to do simple fetch, or table is always used too?
2008/12/14 Dmitry Koterov dmi...@koterov.ru: On Sun, Dec 14, 2008 at 3:36 PM, David Rowley dgrow...@gmail.com wrote: 2008/12/14 Dmitry Koterov dmi...@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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
