Message-ID: <4D49C482.4010707@vmsinfo.com>
Date: Wed, 02 Feb 2011 15:54:26 -0500
From: Mladen Gogala <mladen.gogala@vmsinfo.com>
Organization: VMS
User-Agent: Thunderbird 2.0.0.24 (X11/20101027)
MIME-Version: 1.0
To: Greg Smith <greg@2ndquadrant.com>
CC: Robert Haas <robertmhaas@gmail.com>, Andrew Dunstan <andrew@dunslane.net>,
	Bruce Momjian <bruce@momjian.us>, Tom Lane <tgl@sss.pgh.pa.us>,
	"david@lang.hm" <david@lang.hm>,
	Craig Ringer <craig@postnewspapers.com.au>,
	Vitalii Tymchyshyn <tivv00@gmail.com>,
	"pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
Subject: Re: [HACKERS] Slow count(*) again...
References: <201102012247.p11Ml6u02682@momjian.us>
	<4D48914B.4050403@dunslane.net>	<4D489AD1.1010105@vmsinfo.com>
	<AANLkTiknZi97dDwm7sHqy0EL7tpNEP9sY5uPV6AN+q=u@mail.gmail.com>
	<4D499E55.4020107@vmsinfo.com> <4D49A6B9.4060608@2ndquadrant.com>
In-Reply-To: <4D49A6B9.4060608@2ndquadrant.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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.

> 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.

> If I were to work on improving this area, it would be executing on some 
> plans a few of us have sketched out for exposing some notion about what 
> indexes are actually in memory to the optimizer.  There are more obvious 
> fixes to the specific case of temp tables though.
>
>   
I've had a run in with a temporary table, that I had to resolve by 
disabling hash join and merge join, that really irritated me.

-- 
 
Mladen Gogala 
Sr. Oracle DBA
1500 Broadway
New York, NY 10036
(212) 329-5251
http://www.vmsinfo.com 
The Leader in Integrated Media Intelligence Solutions