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

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@peak6.com

______________________________________________

See  http://www.peak6.com/email_disclaimer.php
for terms and conditions related to this email