Re: Query about index usage

Jayadevan M wrote:
> One query about PostgreSQL's index usage. If I select just one column on
> which there is an index (or select only columns on which there is an
> index), and the index is used by PostgreSQL, does PostgreSQL avoid table
> access if possible?

PostgreSQL keeps information about what rows are visible or not in with
the row data.  It's therefore impossible at this time for it to answer
queries just based on what's in an index.  Once candidate rows are found
using one, the database must then also retrieve the row(s) and do a
second check as to whether it's visible to the running transaction or
not before returning them to the client.

Improving this situation is high up on the list of things to improve in
PostgreSQL and the value of it recognized, it just hasn't been built yet.

--
Greg Smith  2ndQuadrant US  Baltimore, MD
PostgreSQL Training, Services and Support
greg@2ndQuadrant.com   www.2ndQuadrant.us