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

Kenneth Marshall wrote:
>
>
> I see them come up regularly. However, there really are not all that
> many when you consider how many people are using PostgreSQL. Its
> optimizer works quite well. Knowing how hints can be misused, I would
> rather have the developers use their resource to improve the optimizer
> than spend time on a hint system that would be mis-used over and over
> by beginners, with the attendent posts to HACKERS/PERFORM/NOVICE/...
> groups. I certainly have had a fun time or two in my limited Oracle
> experience tracking down a hint-based performance problem, so it
> works both ways.
>
> Regards,
> Ken
>

Ken, the story is really simple: when a problem with a bad query arises,
the DBA has to make it work, one way or another. The weapon of choice
are usually hints, but there is also the ability to set the critical
statistic variables to the desired values. If my users are screaming
that the application response time is slow, I cannot afford to wait for
developers to fix the optimizer. I will therefore not use Postgres for
my mission critical applications, as long as there are no hints.

Oracle is expensive, but not as expensive as the downtime. And that's
the bottom line. Yes, hints can cause problems, but the absence of hints
and wait interface can cause even bigger problems. This is not a choice
between good and evil, as in the Nick Cage movies, it is a choice
between evil and lesser evil. I would love to be able to use Postgres
for some of my mission critical applications. Saving tens of thousands
of dollars would make me a company hero and earn me a hefty bonus, so I
have a personal incentive to do so. Performance is normally not a
problem. If the application is carefully crafted and designed, it will
work more or less the same as Oracle. However, applications sometimes
need maintenance. Ruth from sales wants the IT to start ingesting data
in UTF8 because we have clients in other countries. She also wants us to
track language and countries. Columns have to be added to the tables,
applications have to be changed, foreign keys added, triggers altered,
etc, etc. What you end up with is usually less than optimal.
Applications have life cycle and they move from being young and sexy to
being an old fart application, just as people do. Hints are Viagra for
applications. Under the ideal conditions, it is not needed, but once the
app is past certain age....

The other problem is that plans change with the stats, not necessarily
for the better. People clean a large table, Postgres runs auto-vacuum,
stats change and all the plans change, too. If some of the new plans are
unacceptable, there isn't much you can do about it, but to hint it to
the proper plan. Let's not pretend, Postgres does support sort of hints
with the "set enable_<access method>" and random/sequential scan cost.
Also, effective cache size is openly used to trick the optimizer into
believing that there is more memory than there actually is. Hints are
already there, they're just not as elegant as Oracle's solution. If I
set sequential page cost to 4 and random page cost to 1, I have,
effectively, introduced rule based optimizer to Postgres. I am not sure
why is there such a puritanical resistance to hints on one side and, on
other side, there are means to achieve exactly the same thing. As my
signature line says, I am a senior Oracle DBA, with quite a bit of
experience. What I need to approve moving mission critical applications
to Postgres are better monitoring tools and something to help me with
quick and dirty fixes when necessary. I am willing to learn, I got the
company to invest some money and do pilot projects, but I am not
prepared to have my boss saying "we could have fixed the problem, had we
stayed on Oracle".

BTW:
On my last airplane trip, I saw Nick Cage in the "Sorcerer's Apprentice"
and my brain still hurts.

--

Mladen Gogala
Sr. Oracle DBA
1500 Broadway
New York, NY 10036
(212) 329-5251
http://www.vmsinfo.com
The Leader in Integrated Media Intelligence Solutions