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(at)peak6(dot)com

______________________________________________

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