Re: [HACKERS] Slow count(*) again... - Mailing list pgsql-performance

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