On 02/03/2011 03:01 PM, Mladen Gogala wrote:
As I hinted before, this is actually a purist argument which was made by someone who has never had to support a massive production database with many users for living.
Our database handles 9000 transactions per second and over 200-million transactions per day just fine, thanks. It may not be a "real database" in your world, but it's real enough for us.
I must say that this purist attitude is extremely surprising to me. All the major DB vendors support optimizer hints, yet in the Postgres community, they are considered bad with almost religious fervor. Postgres community is quite unique with the fatwa against hints.
You missed the argument. The community, or at least the devs, see hints as an ugly hack. Do I agree? Not completely, but I can definitely understand the perspective. Saying every other "vendor" has hints is really just admitting every other vendor has a crappy optimizer. Is that something to be proud of?
In almost every single case I've seen a query with bad performance, it's the fault of the author or the DBA. Not enough where clauses; not paying attention to cardinality or selectivity; inappropriate or misapplied indexes; insufficient table statistics... the list of worse grievances out there is endless.
And here's something I never saw you consider: hints making performance worse. Sure, for now, forcing a sequence scan or forcing it to use indexes on a specific table is faster for some specific edge-case. But hints are like most code, and tend to miss frequent refactor. As the optimizer improves, hints likely won't, meaning code is likely to be slower than if the hints didn't exist. This of course ignores the contents of a table are likely to evolve or grow in volume, which can also drastically alter the path the optimizer would choose, but can't because a hint is forcing it to take a specific path.
Want to remove a reverse index scan? Reindex with DESC on the column being reversed. That was added in 8.3. Getting too many calls for nested loops when a merge or hash would be faster? Increase the statistics target for the column causing the problems and re-analyze. Find an actual bug in the optimizer? Tell the devs and they'll fix it. Just stay current, and you get all those benefits. This is true for any database; bugs get fixed, things get faster and more secure.
Or like I said, if you really need hints that badly, use EnterpriseDB instead. It's basically completely Oracle-compatible at this point. But pestering the PostgreSQL dev community about how inferior they are, and how they're doing it wrong, and how they're just another vendor making a database product that can't support massive production databases, is doing nothing but ensuring they'll ignore you. Flies, honey, vinegar, etc.
-- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 800 | Chicago IL, 60604 312-676-8870 sthomas@xxxxxxxxx ______________________________________________ See http://www.peak6.com/email_disclaimer.php for terms and conditions related to this email -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
