Re: Big disappointment with Postgres
Date: Mon, 7 Feb 2011 06:15:03 -0800 (PST)
Message-ID: <2438c922-6c8a-4670-b3dd-90e781663925_at_o18g2000prh.googlegroups.com>
On Feb 4, 6:07 pm, onedbguru <onedbg..._at_yahoo.com> wrote:
> 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.
>
And I agree but many DBAs don't have the luxury of tuning queries before they get to production as the companies they work for use canned apps where the code cannot be touched. Hints and outlines can save the day in such situations.
> 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 one-
> off - hint away...
>
Not for prepackaged code that is 'untouchable'. Or don't you have to administer systems like Portal?
> 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.
David Fitzjarrell Received on Mon Feb 07 2011 - 08:15:03 CST