Re: Big disappointment with Postgres
Date: Fri, 4 Feb 2011 16:07:55 -0800 (PST)
Message-ID: <0cca741f-2460-403d-82d0-a5057edd8179_at_k9g2000yqi.googlegroups.com>
On Feb 4, 6:05 pm, Mladen Gogala <n..._at_email.here.invalid> wrote:
> On Fri, 04 Feb 2011 23:50:30 +0100, Matthias Hoys wrote:
> > True. And if hints don't work, try the RBO.
>
> > Matthias
>
> True, too. People on the PostgreSQL performance list told me that the
> Postgres optimizer is smarter then us humans and that hints are not only
> bad but also immoral and full of cholesterol. They even have a fatwa
> against hints on their wiki, which I find religulous.
> One of them even called Dan Tow "a fossilized DBA". He must have had a
> run in with Dan, which probably didn't end up too well for him.
>
> --http://mgogala.byethost5.com
I would add my two cents about hints. Hints generally work well when you create them. But due to changes in data patterns and volumes, they may degrade and even more problematic, may make things worse than before. Seen it, got the T-shirt (also had recommended against it) . The major issue caused by this degradation is that now you must re-code your app. Any DBA worth his salt **should** be capable of tuning the query for optimal performance before it goes to production.
I would caveate this by saying for those one-off ad-hoc queries - hints can get you huge performance boosts, and if it is a truely a oneoff
- hint away...
Even the Oracle CBO can be a bit odd. At one time, there was a "feature", not really a hint, but could make some queries fly.
The case was that there was a non-unique index on tablea.col3 for example. The query even had " where tablea.col3 = 'somevalue' " and no matter what you did, it would never use this index (or any other index on that table - but resorted to FTS). By modifying the query like:
where tablea.col3 = 'somevalue'
AND tablea.col3 = 'somevalue'
.....
(same values twice)
The Oracle optimizer would now give a higher ranking for tablea.col3 and - wow - look at that - an index on tablea.col3. The result would be queries that took an hour+ to < 10-15 minutes.
For massive tables ( no 5M rows is not massive - maybe 5B+ ) parallel hints can work wonders in Oracle. Not sure about SS,DB2 or MySQL. Received on Fri Feb 04 2011 - 18:07:55 CST