Received: from maia.hub.org (maia-5.hub.org [200.46.204.29]) by mail.postgresql.org (Postfix) with ESMTP id BAB601337B4E for ; Thu, 27 Jan 2011 17:21:02 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024) with ESMTP id 13960-08 for ; Thu, 27 Jan 2011 21:20:56 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from mh7.mail.rice.edu (mh7.mail.rice.edu [128.42.199.46]) by mail.postgresql.org (Postfix) with ESMTP id 2473D133798E for ; Thu, 27 Jan 2011 17:20:55 -0400 (AST) Received: from mh7.mail.rice.edu (localhost.localdomain [127.0.0.1]) by mh7.mail.rice.edu (Postfix) with ESMTP id B144328F8AF; Thu, 27 Jan 2011 15:20:54 -0600 (CST) X-Virus-Scanned: by amavis-2.6.4 at mh7.mail.rice.edu, auth channel Received: from mh7.mail.rice.edu ([127.0.0.1]) by mh7.mail.rice.edu (mh7.mail.rice.edu [127.0.0.1]) (amavis, port 10026) with ESMTP id J7ppS4cpYj8z; Thu, 27 Jan 2011 15:20:54 -0600 (CST) X-SMTP-Auth: no X-SMTP-Auth: no X-SMTP-Auth: no X-SMTP-Auth: no X-SMTP-Auth: no X-SMTP-Auth: no Received: from aart.is.rice.edu (aart.is.rice.edu [168.7.56.48]) by mh7.mail.rice.edu (Postfix) with ESMTP id 783A728F889; Thu, 27 Jan 2011 15:20:54 -0600 (CST) Received: by aart.is.rice.edu (Postfix, from userid 18612) id A7C922D5A6; Thu, 27 Jan 2011 15:20:53 -0600 (CST) Date: Thu, 27 Jan 2011 15:20:53 -0600 From: Kenneth Marshall To: Igor Neyman Cc: Scott Marlowe , Mladen Gogala , Tom Lane , David Wilson , pgsql-performance@postgresql.org Subject: Re: Postgres 9.0 has a bias against indexes Message-ID: <20110127212053.GL6475@aart.is.rice.edu> References: MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: User-Agent: Mutt/1.5.16 (2007-06-09) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.91 tagged_above=-10 required=5 tests=BAYES_00=-1.9, T_RP_MATCHES_RCVD=-0.01 X-Spam-Level: X-Archive-Number: 201101/296 X-Sequence-Number: 42083 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 > > wrote: > > > On 1/27/2011 3:37 PM, Scott Marlowe wrote: > > >> > > >> On Thu, Jan 27, 2011 at 1:31 PM, Mladen Gogala > > >> ?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