> -----Original Message----- > From: Scott Marlowe [mailto:scott.marlowe@xxxxxxxxx] > Sent: Thursday, January 27, 2011 4:25 PM > To: Igor Neyman > Cc: Mladen Gogala; 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 2:18 PM, Igor Neyman > <ineyman@xxxxxxxxxxxxxx> wrote: > > > >> -----Original Message----- > >> From: Scott Marlowe [mailto:scott.marlowe@xxxxxxxxx] > >> Sent: Thursday, January 27, 2011 4:16 PM > >> To: Igor Neyman > >> Cc: Mladen Gogala; 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 2:12 PM, Igor Neyman > <ineyman@xxxxxxxxxxxxxx> > >> 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. > >> > >> 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. > > On Oracle? Then how can it get the values it needs without > having to hit the data store? > > It doesn't. It does "INDEX UNIQUE SCAN" and then "TABLE ACCESS BY INDEX ROWID". -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
