Message-ID: <4C1256FD.6070707@2ndquadrant.com>
Date: Fri, 11 Jun 2010 11:32:13 -0400
From: Greg Smith <greg@2ndquadrant.com>
User-Agent: Thunderbird 2.0.0.24 (X11/20100317)
MIME-Version: 1.0
To: Jayadevan M <Jayadevan.Maymala@ibsplc.com>
CC: pgsql-performance@postgresql.org
Subject: Re: Query about index usage
References: <AANLkTikihIMleazgqo1NhLo7LA5Tiujz4TIfBL1agxAp@mail.gmail.com>
	<AANLkTikZKwBCR8QfGph3aH34A2dJ2NQyOjq-Pt3eZ3_z@mail.gmail.com>
	<4C119B3E.3090608@squeakycode.net>
	<AANLkTinjUmGWmB3jsjj-6w_44c0K7f_wSrH_1TALPaOY@mail.gmail.com>
	<AANLkTikjNlFDmQyZuUrDMtYhFe7sB7GOxS6t9eFibSpV@mail.gmail.com>
	<4C12050A.5060100@enterprisedb.com>
	<OF118B1453.1AD6958E-ON6525773F.00355BBB-6525773F.00369464@ibsplc.com>
In-Reply-To: 
 <OF118B1453.1AD6958E-ON6525773F.00355BBB-6525773F.00369464@ibsplc.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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