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

Re: Postgres 9.0 has a bias against indexes

From: "Igor Neyman" <ineyman(at)perceptron(dot)com>
To: "Scott Marlowe" <scott(dot)marlowe(at)gmail(dot)com>
Cc: "Mladen Gogala" <mladen(dot)gogala(at)vmsinfo(dot)com>,"Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>,"David Wilson" <david(dot)t(dot)wilson(at)gmail(dot)com>,"Kenneth Marshall" <ktm(at)rice(dot)edu>,<pgsql-performance(at)postgresql(dot)org>
Subject: Re: Postgres 9.0 has a bias against indexes
Date: 2011-01-27 21:32:02
Message-ID: F4C27E77F7A33E4CA98C19A9DC6722A2070C25C8@EXCHANGE.corp.perceptron.com (view raw or flat)
Thread:
Lists: pgsql-performance
 

> -----Original Message-----
> From: Scott Marlowe [mailto:scott(dot)marlowe(at)gmail(dot)com] 
> Sent: Thursday, January 27, 2011 4:25 PM
> To: Igor Neyman
> Cc: Mladen Gogala; Tom Lane; David Wilson; Kenneth Marshall; 
> pgsql-performance(at)postgresql(dot)org
> Subject: Re: [PERFORM] Postgres 9.0 has a bias against indexes
> 
> On Thu, Jan 27, 2011 at 2:18 PM, Igor Neyman 
> <ineyman(at)perceptron(dot)com> wrote:
> >
> >> -----Original Message-----
> >> From: Scott Marlowe [mailto:scott(dot)marlowe(at)gmail(dot)com]
> >> Sent: Thursday, January 27, 2011 4:16 PM
> >> To: Igor Neyman
> >> Cc: Mladen Gogala; Tom Lane; David Wilson; Kenneth Marshall; 
> >> pgsql-performance(at)postgresql(dot)org
> >> Subject: Re: [PERFORM] Postgres 9.0 has a bias against indexes
> >>
> >> On Thu, Jan 27, 2011 at 2:12 PM, Igor Neyman 
> <ineyman(at)perceptron(dot)com> 
> >> wrote:
> >> >
> >> >
> >> >> -----Original Message-----
> >> >> From: Scott Marlowe [mailto:scott(dot)marlowe(at)gmail(dot)com]
> >> >> Sent: Thursday, January 27, 2011 3:59 PM
> >> >> To: Mladen Gogala
> >> >> Cc: Igor Neyman; Tom Lane; David Wilson; Kenneth Marshall; 
> >> >> pgsql-performance(at)postgresql(dot)org
> >> >> Subject: Re: [PERFORM] Postgres 9.0 has a bias against indexes
> >> >>
> >> >> On Thu, Jan 27, 2011 at 1:44 PM, Mladen Gogala 
> >> >> <mladen(dot)gogala(at)vmsinfo(dot)com> wrote:
> >> >> > On 1/27/2011 3:37 PM, Scott Marlowe wrote:
> >> >> >>
> >> >> >> On Thu, Jan 27, 2011 at 1:31 PM, Mladen Gogala 
> >> >> >> <mladen(dot)gogala(at)vmsinfo(dot)com>  wrote:
> >> >> >>>
> >> >> >>> There is INDEX UNIQUE SCAN PK_EMP.  Oracle will use 
> an index.
> >> >> >>
> >> >> >> That's because Oracle has covering indexes.
> >> >> >>
> >> >> > I am not sure what you mean by "covering indexes" but I
> >> >> hope that for
> >> >> > the larger table I have in mind,  indexes will be used.
> >> >> For a small
> >> >> > table like
> >> >>
> >> >> In Oracle you can hit JUST the index to get the data 
> you need (and 
> >> >> maybe rollback logs, which are generally pretty small)
> >> >>
> >> >> In Pgsql, once you hit the index you must then hit the 
> actual data 
> >> >> store to get the right version of your tuple.  So, index
> >> access in pg
> >> >> is more expensive than in Oracle.  However, updates are cheaper.
> >> >> Always a trade off
> >> >>
> >> >>
> >> >
> >> > Scott,
> >> > What you describe here isn't about "covering indexes" -
> >> it's about different ways implementing MVCC in Oracle and PG.
> >>
> >> It is about covering indexes AND it's about the difference in how 
> >> MVCC is implemented in both databases.
> >>
> >>
> >
> > Well, Mladen's query doesn't involve covering indexes.
> 
> On Oracle?  Then how can it get the values it needs without 
> having to hit the data store?
> 
> 

It doesn't.
It does "INDEX UNIQUE SCAN" and then "TABLE ACCESS BY INDEX ROWID".

In response to

Responses

pgsql-performance by date

Next:From: Scott MarloweDate: 2011-01-27 21:33:17
Subject: Re: Postgres 9.0 has a bias against indexes
Previous:From: Mladen GogalaDate: 2011-01-27 21:31:24
Subject: Re: Postgres 9.0 has a bias against indexes

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