Skip site navigation (1) Skip section navigation (2)

Re: index usage

From: "scott(dot)marlowe" <scott(dot)marlowe(at)ihs(dot)com>
To: Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)com>
Cc: <brad-pgperf(at)duttonbros(dot)com>, <pgsql-performance(at)postgresql(dot)org>
Subject: Re: index usage
Date: 2004-04-28 15:40:08
Message-ID: Pine.LNX.4.33.0404280935510.6453-100000@css120.ihs.com (view raw or flat)
Thread:
Lists: pgsql-performance
On Mon, 26 Apr 2004, Stephan Szabo wrote:

> 
> On Fri, 23 Apr 2004 brad-pgperf(at)duttonbros(dot)com wrote:
> 
> > I have a query which I think should be using an index all of the time but
> > postgres only uses the index part of the time.  The index
> > (ticket_crm_map_crm_id_suppid) has the where clause column (crm_id) listed
> > first followed by the selected column (support_person_id).  Wouldn't the
> > most efficient plan be to scan the index each time because the only columns
> > needed are in the index?  Below is the table, 2 queries showing the
> 
> Not necessarily.  The rows in the actual file still need to be checked to
> see if they're visible to the select and if it's expected that the entire
> file (or a reasonable % of the pages anyway) will need to be loaded using
> the index isn't necessarily a win.

While those of us familiar with PostgreSQL are well aware of the fact that 
indexes can't be used directly to garner information, but only as a lookup 
to a tuple in the table, it seems this misconception is quite common among 
those coming to postgreSQL from other databases.

Is there any information that directly reflects this issue in the docs?  
There are tons of hints that it works this way in how they're written, but 
nothing that just comes out and says that with pgsql's mvcc 
implementation, an index scan still has to hit the pages that contain the 
tuples, so often in pgsql a seq scan is a win where in other databases and 
index scan would have been a win?

If not, where would I add it if I were going to write something up for the 
docs?  Just wondering...


In response to

Responses

pgsql-performance by date

Next:From: Tom LaneDate: 2004-04-28 16:41:40
Subject: Re: index usage
Previous:From: James RobinsonDate: 2004-04-28 13:29:15
Subject: History of oids in postgres?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group