Re: An Idea for planner hints

On Tue, Aug 08, 2006 at 01:55:35PM +0200, Florian G. Pflug wrote:
> Hi
>
> Since the discussion about how to force a specific plan has
> come up, I though I'd post an idea I had for this a while ago.
> It's not reall well though out yet, but anyway.

<snip>

> Image a query like "select ... from t1 join t2 on t1.t2_id = t2.id and
> <expr>". Lets say that "<expr>" is true for only 1% of the rows in t2 -
> but those are exactly the rows that have matching rows in t1.
>
> Postgres would probably guess that this join will produce about 1/100
> of the rows that t1 has - but I _know_ that it will produce 100 (!)
> times more rows.

ISTM theat the easiest way would be to introduce a sort of predicate
like so:

SELECT * FROM foo, bar WHERE pg_selectivity(foo.a = bar.a, 0.1);

If you teach the optimiser that pg_selectivity always has the
selectivity of the second argument, you're done. Other than that you
just need to define pg_selectivity as a no-op.

One thing though: when people think of selectivity, they think "number
of rows in foo that have a match in bar" whereas selectivity for
postgres means "chance this expression will be true". They are related
but not the same thing. Converting from one to the other will have it's
own pitfalls...

Hope this helps,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.