On 7 April 2015 at 13:41, Tomas Vondra <tomas.von...@2ndquadrant.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
