Re: Simple join optimized badly? - Mailing list pgsql-performance

From Jim C. Nasby
Subject Re: Simple join optimized badly?
Date
Msg-id 20061010192623.GL72517@nasby.net
Whole thread Raw
In response to Re: Simple join optimized badly?  (Josh Berkus <josh@agliodbs.com>)
List pgsql-performance
On Tue, Oct 10, 2006 at 10:28:29AM -0700, Josh Berkus wrote:
> Jim,
>
> > We've depricated things before, I'm sure we'll do it again. Yes, it's a
> > pain, but it's better than not having anything release after release.
> > And having a formal hint language would at least allow us to eventually
> > clean up some of these oddball cases, like the OFFSET 0 hack.
> >
> > I'm also not convinced that even supplimental statistics will be enough
> > to ensure the planner always does the right thing, so query-level hints
> > may have to stay (though it'd be great if that wasn't the case).
>
> "stay"?   I don't think that the general developers of PostgreSQL are going
> to *accept* anything that stands a significant chance of breaking in one
> release.   You have you challange for the EDB development team: come up
> with a hinting language which is flexible enough not to do more harm than
> good (hint: it's not Oracle's hints).

My point was that I think we'll always have a need for fine-grained (ie:
table and join level) hints, even if we do get the ability for users to
over-ride the statistics system. It's just not possible to come up with
automation that will handle every possible query that can be thrown at a
system. I don't see how that means breaking anything in a given release.
Worst-case, the optimizer might be able to do a better job of something
than hints written for an older version of the database, but that's
going to be true of any planner override we come up with.

BTW, I'm not speaking for EnterpriseDB or it's developers here... query
hints are something I feel we've needed for a long time.
--
Jim Nasby                                            jim@nasby.net
EnterpriseDB      http://enterprisedb.com      512.569.9461 (cell)

pgsql-performance by date:

Previous
From: Tobias Brox
Date:
Subject: Re: long running transactions
Next
From: Brendan Curran
Date:
Subject: Scrub one large table against another