From: Jim C. Nasby <jim@nasby.net>
To: Chris Browne <cbbrowne@acm.org>
Cc: pgsql-performance@postgresql.org
Subject: Re: Simple join optimized badly?
Date: Mon, 9 Oct 2006 16:18:27 -0500
Message-ID: <20061009211827.GL72517@nasby.net> (raw)
In-Reply-To: <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>
<604pud8ise.fsf@dba2.int.libertyrms.com>
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)
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: jim@nasby.net, cbbrowne@acm.org
Subject: Re: Simple join optimized badly?
In-Reply-To: <20061009211827.GL72517@nasby.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox