From: Mark Kirkwood <markir@paradise.net.nz>
To: Craig A. James <cjames@modgraph-usa.com>
Cc: josh@agliodbs.com
Cc: pgsql-performance@postgresql.org
Subject: Re: Simple join optimized badly?
Date: Mon, 09 Oct 2006 15:41:20 +1300
Message-ID: <4529B6D0.2010206@paradise.net.nz> (raw)
In-Reply-To: <4529A0CA.8000904@modgraph-usa.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>
Craig A. James wrote:
>
>
> My example, discussed previously in this forum, is a classic. I have a
> VERY expensive function (it's in the class of NP-complete problems, so
> there is no faster way to do it). There is no circumstance when my
> function should be used as a filter, and no circumstance when it should
> be done before a join. But PG has no way of knowing the cost of a
> function, and so the optimizer assigns the same cost to every function.
> Big disaster.
>
> 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.
It seems to me that if (in addition to the function cost) we come up
with some efficient way of recording cross column statistics we would be
well on the way to silencing *most* of the demands for hints.
We would still be left with some of the really difficult problems - a
metric for "locally correlated" column distributions and a reliable
statistical algorithm for most common value sampling (or a different way
of approaching this). These sound like interesting computer science or
mathematics thesis topics, maybe we could try (again?) to get some
interest at that level?
Cheers
Mark
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: markir@paradise.net.nz, cjames@modgraph-usa.com, josh@agliodbs.com
Subject: Re: Simple join optimized badly?
In-Reply-To: <4529B6D0.2010206@paradise.net.nz>
* 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