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
