pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  

help / color / mirror / Atom feed
From: Chris Browne <cbbrowne@acm.org>
To: pgsql-performance@postgresql.org
Subject: Re: Simple join optimized badly?
Date: Mon, 09 Oct 2006 18:07:29 +0000
Message-ID: <604pud8ise.fsf@dba2.int.libertyrms.com> (raw)
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>

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



view thread (56+ messages)  latest in thread

Message-ID: <604pud8ise.fsf@dba2.int.libertyrms.com>
Permalink:  ../604pud8ise.fsf@dba2.int.libertyrms.com/
Also on:    postgresql.org/message-id/604pud8ise.fsf@dba2.int.libertyrms.com

 · 

reply
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: cbbrowne@acm.org
  Subject: Re: Simple join optimized badly?
  In-Reply-To: <604pud8ise.fsf@dba2.int.libertyrms.com>

* 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