On Thu, Jan 27, 2011 at 04:12:53PM -0500, Igor Neyman wrote: > > > > -----Original Message----- > > From: Scott Marlowe [mailto:scott.marlowe@xxxxxxxxx] > > Sent: Thursday, January 27, 2011 3:59 PM > > To: Mladen Gogala > > Cc: Igor Neyman; Tom Lane; David Wilson; Kenneth Marshall; > > pgsql-performance@xxxxxxxxxxxxxx > > Subject: Re: Postgres 9.0 has a bias against indexes > > > > On Thu, Jan 27, 2011 at 1:44 PM, Mladen Gogala > > <mladen.gogala@xxxxxxxxxxx> wrote: > > > On 1/27/2011 3:37 PM, Scott Marlowe wrote: > > >> > > >> On Thu, Jan 27, 2011 at 1:31 PM, Mladen Gogala > > >> <mladen.gogala@xxxxxxxxxxx> ?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 -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
