From: | David Rowley <dgrowleyml(at)gmail(dot)com> |
---|---|
To: | Tomas Vondra <tomas(dot)vondra(at)2ndquadrant(dot)com> |
Cc: | PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: PATCH: use foreign keys to improve join estimates v1 |
Date: | 2015-05-17 12:31:25 |
Message-ID: | CAApHDvojqqO6ULGFVRXXOCyvu8tb+o6KA8CSUm2vq0d3EB_qKg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
On 7 April 2015 at 13:41, Tomas Vondra <tomas(dot)vondra(at)2ndquadrant(dot)com> wrote:
>
> (1) The current patch only does the trick when the FK matches the
> conditions perfectly - when there are no missing columns (present
> in the FK, not covered by a condition).
>
>
Hi Tomas,
I did glance at this patch a while back, but just thinking on it again.
I think if you find any quals that are a part of *any* foreign key between
the 2 join tables, then you should be never assume these quals to reduce
the number of rows. I believe this should be the case even if you don't
fully match all columns of the foreign key.
If we modify your example a little, let's say your foreign key between fact
and dim is made from 3 columns (a,b,c)
If we do:
EXPLAIN SELECT * FROM fact f JOIN dim d USING (a,b);
Then we should always (under normal circumstances) find at least one
matching row, although in this case since the join qual for c is missing,
we could find more than 1 matching row.
Without digging too deep here, I'd say that the best way to do this would
be to either have calc_joinrel_size_estimate() build a list of restrictinfo
items of all quals that are NOT part of any foreign key and pass that
trimmed list down to clauselist_selectivity() for selectivity estimates. Or
perhaps a better way would be just to teach clauselist_selectivity() about
foreign keys. Likely clauselist_selectivity() would just have to skip over
RestrictInfo items that are part of a foreign key.
Regards
David Rowley
From | Date | Subject | |
---|---|---|---|
Next Message | Magnus Hagander | 2015-05-17 12:58:34 | Re: fix typos |
Previous Message | Robert Haas | 2015-05-17 12:07:15 | Re: jsonb concatenate operator's semantics seem questionable |