Re: Postgres 9.0 has a bias against indexes

From: Kenneth Marshall <ktm(at)rice(dot)edu>
To: Igor Neyman <ineyman(at)perceptron(dot)com>
Cc: Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com>, 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>, pgsql-performance(at)postgresql(dot)org
Subject: Re: Postgres 9.0 has a bias against indexes
Date: 2011-01-27 21:20:53
Message-ID: 20110127212053.GL6475@aart.is.rice.edu
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

On Thu, Jan 27, 2011 at 04:12:53PM -0500, Igor Neyman 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.
>
> Mladen,
> you were right.
> For recursive query like yours Oracle uses index even on small table.
> I made an assumption without testing it.
> However some other (non-recursive) queries against the same small table that also require reading all 14 rows do "table scan".
>
> Regards,
> Igor Neyman
>
Interesting. Can you force it to use a Seqential Scan and if so, how
does that affect the timing? i.e. Is the index scan actually faster?

Cheers,
Ken

In response to

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Scott Marlowe 2011-01-27 21:25:04 Re: Postgres 9.0 has a bias against indexes
Previous Message Igor Neyman 2011-01-27 21:18:12 Re: Postgres 9.0 has a bias against indexes