Received: from maia.hub.org (maia-5.hub.org [200.46.204.29]) by mail.postgresql.org (Postfix) with ESMTP id 710DE1337B6E for ; Wed, 2 Feb 2011 17:14:17 -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 54541-07 for ; Wed, 2 Feb 2011 21:14:10 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from mh4.mail.rice.edu (mh4.mail.rice.edu [128.42.199.11]) by mail.postgresql.org (Postfix) with ESMTP id C890F1337999 for ; Wed, 2 Feb 2011 17:14:09 -0400 (AST) Received: from mh4.mail.rice.edu (localhost.localdomain [127.0.0.1]) by mh4.mail.rice.edu (Postfix) with ESMTP id 3EEDB28F993; Wed, 2 Feb 2011 15:14:09 -0600 (CST) X-Virus-Scanned: by amavis-2.6.4 at mh4.mail.rice.edu, auth channel Received: from mh4.mail.rice.edu ([127.0.0.1]) by mh4.mail.rice.edu (mh4.mail.rice.edu [127.0.0.1]) (amavis, port 10026) with ESMTP id 0ZbIOUyTg2mv; Wed, 2 Feb 2011 15:14:09 -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 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 mh4.mail.rice.edu (Postfix) with ESMTP id 0155D28F97C; Wed, 2 Feb 2011 15:14:09 -0600 (CST) Received: by aart.is.rice.edu (Postfix, from userid 18612) id F399A2D5A6; Wed, 2 Feb 2011 15:14:07 -0600 (CST) Date: Wed, 2 Feb 2011 15:14:06 -0600 From: Kenneth Marshall To: Mladen Gogala Cc: Greg Smith , Robert Haas , Andrew Dunstan , Bruce Momjian , Tom Lane , "david@lang.hm" , Craig Ringer , Vitalii Tymchyshyn , "pgsql-performance@postgresql.org" Subject: Re: [HACKERS] Slow count(*) again... Message-ID: <20110202211406.GN24931@aart.is.rice.edu> References: <201102012247.p11Ml6u02682@momjian.us> <4D48914B.4050403@dunslane.net> <4D489AD1.1010105@vmsinfo.com> <4D499E55.4020107@vmsinfo.com> <4D49A6B9.4060608@2ndquadrant.com> <4D49C482.4010707@vmsinfo.com> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <4D49C482.4010707@vmsinfo.com> User-Agent: Mutt/1.5.16 (2007-06-09) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-4.21 tagged_above=-10 required=5 tests=BAYES_00=-1.9, RCVD_IN_DNSWL_MED=-2.3, T_RP_MATCHES_RCVD=-0.01 X-Spam-Level: X-Archive-Number: 201102/44 X-Sequence-Number: 42182 On Wed, Feb 02, 2011 at 03:54:26PM -0500, Mladen Gogala wrote: > Greg Smith wrote: >> Given that even Oracle kicked out the RBO a long time ago, I'm not so sure >> longing for those good old days will go very far. I regularly see queries >> that were tweaked to always use an index run at 1/10 or less the speed of >> a sequential scan against the same data. The same people complaining "all >> over the place" about this topic are also the sort who write them. There >> are two main fallacies at play here that make this happen: >> > Oracle just gives an impression that RBO is gone. It's actually still > there, even in 11.2: > > Connected to: > Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production > With the Partitioning, OLAP, Data Mining and Real Application Testing > options > > SQL> alter session set optimizer_mode=rule; > > Session altered. > > Oracle people were just as puritanical as Postgres people, if not more so. > However, the huge backlash made them reconsider the decision. RBO is > officially de-supported, obsolete and despised but it is also widely used, > even in the Oracle's own SYS schema. Oracle is having huge problems with > trying to get people to the cost based optimizer, but they are not yet > quite done. > This problem in getting people to migrate to the cost-based optimizer seems to stem from the original use of the rule based optimizer and the ability to (mis)hint every option in the DB. If I were running a shop with 100k-1m lines of SQL code with embedded hints, I would run screaming at the QA required to move to the cost-based system. In many ways, the RBO itself + hints is hindering the adoption of the CBO. Are there any stats on the adoption/use of the CBO on new Oracle users/shops? >> 1) Even if you use an index, PostgreSQL must still retrieve the associated >> table data to execute the query in order to execute its version of MVCC >> > Of course. Nobody contests that. However, index scans for OLTP are > indispensable. Sequential scans just don't do the trick in some situations. > > >> 2) The sort of random I/O done by index lookups can be as much as 50X as >> expensive on standard hard drives as sequential, if every block goes to >> physical hardware. >> > > Greg, how many questions about queries not using an index have you seen? > There is a reason why people keep asking that. The sheer number of > questions like that on this group should tell you that there is a problem > there. There must be a relatively simple way of influencing optimizer > decisions. With all due respect, I consider myself smarter than the > optimizer. I'm 6'4", 235LBS so telling me that you disagree and that I am > more stupid than a computer program, would not be a smart thing to do. > Please, do not misunderestimate me. > I see them come up regularly. However, there really are not all that many when you consider how many people are using PostgreSQL. Its optimizer works quite well. Knowing how hints can be misused, I would rather have the developers use their resource to improve the optimizer than spend time on a hint system that would be mis-used over and over by beginners, with the attendent posts to HACKERS/PERFORM/NOVICE/... groups. I certainly have had a fun time or two in my limited Oracle experience tracking down a hint-based performance problem, so it works both ways. Regards, Ken