Re: Simple join optimized badly?

Denis,

> Wouldn't PG supporting simple optmizer hints get around this kinda
> problem? Seems to me that at least one customer posting per week
> would be solved via the use of simple hints.

... and add 100 other problems. Hints are used because the DBA thinks that
they are smarter than the optimizer; 99% of the time, they are wrong.
Just try manually optimizing a complex query, you'll see -- with three
join types, several scan types, aggregates, bitmaps, internal and external
sorts, and the ability to collapse subqueries it's significantly more than
a human can figure out accurately.

Given the availability of hints, the newbie DBA will attempt to use them
instead of fixing any of the underlying issues. Craig's post is a classic
example of that: what he really needs to do is ANALYZE HITLIST_ROWS after
populating it. If he had the option of hints, and was shortsighted (I'm
not assuming that Craig is shortsighted, but just for the sake of
argument) he'd fix this with a hint and move on ... and then add another
hint when he adds a another query which needs HITLIST_ROWS, and another.
And then he'll find out that some change in his data (the sample table
growing, for example) makes his hints obsolete and he has to go back and
re-tune them all.

And then ... it comes time to upgrade PostgreSQL. The hints which worked
well in version 8.0 won't necessarily work well in 8.2. In fact, many of
them may make queries disastrously slow. Ask any Oracle DBA, they'll
tell you that upgrading hint is a major PITA, and why Oracle is getting
away from Hints and has eliminated the rules-based optimizer.

Now, if you were offering us a patch to auto-populate the statistics as a
table is loaded, I'd be all for that. But I, personally, would need a
lot of convincing to believe that hints don't do more harm than good.

--
--Josh

Josh Berkus
PostgreSQL @ Sun
San Francisco