Robert Haas wrote:
> On Tue, Feb 1, 2011
> It would be pretty hard to make autoanalyze work on such tables
> without removing some of the performance benefits of having such
> tables in the first place - namely, the local buffer manager. But you
> could ANALYZE them by hand.
>
>
Not necessarily autoanalyze, some default rules for the situations when
stats is not there should be put in place,
like the following:
1) If there is a usable index on the temp table - use it.
2) It there isn't a usable index on the temp table and there is a join,
make the temp table the first table
in the nested loop join.
People are complaining about the optimizer not using the indexes all
over the place, there should be a way to
make the optimizer explicitly prefer the indexes, like was the case with
Oracle's venerable RBO (rules based
optimizer). RBO didn't use statistics, it had a rank of access method
and used the access method with the highest
rank of all available access methods. In practice, it translated into:
if an index exists - use it.
--
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