> -----Original Message----- > From: Scott Marlowe [mailto:scott.marl...@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.gog...@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.gog...@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 "table scan". Regards, Igor Neyman -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
