pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  

help / color / mirror / Atom feed
From: Greg Smith <greg@2ndquadrant.com>
To: Jayadevan M <Jayadevan.Maymala@ibsplc.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Query about index usage
Date: Fri, 11 Jun 2010 11:32:13 -0400
Message-ID: <4C1256FD.6070707@2ndquadrant.com> (raw)
In-Reply-To: <OF118B1453.1AD6958E-ON6525773F.00355BBB-6525773F.00369464@ibsplc.com>
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>

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




view thread (8+ messages)  latest in thread

Message-ID: <4C1256FD.6070707@2ndquadrant.com>
Permalink:  ../4C1256FD.6070707@2ndquadrant.com/
Also on:    postgresql.org/message-id/4C1256FD.6070707@2ndquadrant.com

 · 

reply
Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: greg@2ndquadrant.com, Jayadevan.Maymala@ibsplc.com
  Subject: Re: Query about index usage
  In-Reply-To: <4C1256FD.6070707@2ndquadrant.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox