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

On Mon, Oct 09, 2006 at 06:07:29PM +0000, Chris Browne wrote:
> cjames@modgraph-usa.com ("Craig A. James") writes:
> > Mark Kirkwood wrote:
> >>> The result?  I can't use my function in any WHERE clause that
> >>> involves any other conditions or joins.  Only by itself.  PG will
> >>> occasionally decide to use my function as a filter instead of doing
> >>> the join or the other WHERE conditions first, and I'm dead.
> >> this is an argument for cost-for-functions rather than hints AFAICS.
> >
> > Perhaps you scanned past what I wrote a couple paragraphs farther
> > down.  I'm going to repeat it because it's the KEY POINT I'm trying
> > to make:
> >
> > Craig James wrote:
> >> Now you might argue that function-cost needs to be added to the
> >> optimizer's arsenal of tricks.  And I'd agree with you: That WOULD
> >> be a better solution than hints.  But I need my problem solved
> >> TODAY, not next year.  Hints can help solve problems NOW that can be
> >> brought to the PG team's attention later, and in the mean time let
> >> me get my application to work.
>
> Unfortunately, that "hint language" also needs to mandate a temporal
> awareness of when hints were introduced so that it doesn't worsen
> things down the road.
>
> e.g. - Suppose you upgrade to 8.4, where the query optimizer becomes
> smart enough (perhaps combined with entirely new kinds of scan
> strategies) to make certain of your hints obsolete and/or downright
> wrong.  Those hints (well, *some* of them) ought to be ignored, right?

Great, then you pull the hints back out of the application. They're a
last resort anyway; if you have more than a handful of them in your code
you really need to look at what you're doing.

> The trouble is that the "hint language" will be painfully large and
> complex.  Its likely-nonstandard interaction with SQL will make query
> parsing worse.
>
> All we really have, at this point, is a vague desire for a "hint
> language," as opposed to any clear direction as to what it should look
> like, and how it needs to interact with other system components.
> That's not nearly enough; there needs to be a clear design.

I can agree to that, but we'll never get any progress so long as every
time hints are brought up the response is that they're evil and should
never be in the database. I'll also say that a very simple hinting
language (ie: allowing you to specify access method for a table, and
join methods) would go a huge way towards enabling app developers to get
stuff done now while waiting for all these magical optimizer
improvements that have been talked about for years.
--
Jim Nasby                                            jim@nasby.net
EnterpriseDB      http://enterprisedb.com      512.569.9461 (cell)