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

Re: Postgres 9.0 has a bias against indexes

From: Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com>
To: Igor Neyman <ineyman(at)perceptron(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:16:29
Message-ID: AANLkTi=Ox8O7Y4J3P2tk=UJtLXrOmNpF1xgymWbsOKu1@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
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.

In response to

Responses

pgsql-performance by date

Next:From: Igor NeymanDate: 2011-01-27 21:18:12
Subject: Re: Postgres 9.0 has a bias against indexes
Previous:From: Igor NeymanDate: 2011-01-27 21:12:53
Subject: Re: Postgres 9.0 has a bias against indexes

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