Re: An Idea for planner hints

Martijn van Oosterhout wrote:
> 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);

Ideally, though it needs to be defined upon the table(s) in question,
possibly with a WHERE clause as with indexes:

CREATE STATISTIC <...defn here...>
ON invoices (cli_id), clients (id)
WHERE invoices.paid = false
WITH PRIORITY 100;

(I'm thinking the priority so you can delete any rules with a low
priority while keeping ones you think are vital)

--
Richard Huxton
Archonet Ltd