Re: Postgres 9.0 has a bias against indexes

> -----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.