Re: [HACKERS] Slow count(*) again...

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