On Thu, Jan 27, 2011 at 04:12:53PM -0500, Igor Neyman wrote:
>
>
> > -----Original Message-----
> > From: Scott Marlowe [mailto:scott.marlowe@gmail.com]
> > Sent: Thursday, January 27, 2011 3:59 PM
> > To: Mladen Gogala
> > Cc: Igor Neyman; Tom Lane; David Wilson; Kenneth Marshall;
> > pgsql-performance@postgresql.org
> > Subject: Re: [PERFORM] Postgres 9.0 has a bias against indexes
> >
> > On Thu, Jan 27, 2011 at 1:44 PM, Mladen Gogala
> > <mladen.gogala@vmsinfo.com> wrote:
> > > On 1/27/2011 3:37 PM, Scott Marlowe wrote:
> > >>
> > >> On Thu, Jan 27, 2011 at 1:31 PM, Mladen Gogala
> > >> <mladen.gogala@vmsinfo.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
"tablescan".
>
> 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
