From: Chris Browne <cbbrowne@acm.org>
Subject: Re: Simple join optimized badly?
Date: Mon, 09 Oct 2006 18:07:29 +0000
Organization: cbbrowne Computing Inc
Lines: 41
Message-ID: <604pud8ise.fsf@dba2.int.libertyrms.com>
References: <45274A77.7080005@modgraph-usa.com>
	<18722.1160236300@sss.pgh.pa.us>
	<5f820f750610071850o6ed0b19o162d65d1987b2a8c@mail.gmail.com>
	<200610081605.03501.josh@agliodbs.com>
	<4529A0CA.8000904@modgraph-usa.com>
	<4529B6D0.2010206@paradise.net.nz>
	<4529B7E8.8080606@modgraph-usa.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
User-Agent: Gnus/5.1007 (Gnus v5.10.7) XEmacs/21.4.19 (linux)
Cancel-Lock: sha1:S0bcMNvAcmhshFI0td9yX8S8gAo=
To: pgsql-performance@postgresql.org

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?

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.
-- 
(format nil "~S@~S" "cbbrowne" "cbbrowne.com")
http://cbbrowne.com/info/advocacy.html
'Typos in FINNEGANS WAKE? How could you tell?' -- Kim Stanley Robinson