Re: New design for FK-based join selectivity estimation

Hi,

A few more comments, about re-reading the patch more thoroughly. I 
wouldn't say any of those qualify as bugs, but rather as discussion 
about some of the design choices:

1) NULL handling

I'd argue that we should do something about this, although I agree it's 
non-trivial to estimate - at least until we get some sort of correlation 
stats (e.g. my patch already provides most of the pieces, I believe). 
But I'd argue that in the case of multi-column foreign keys we can do 
better even without it - my experience is that in such cases either all 
values are NULL or none of them, and a single NULL value breaks the FK 
of course. So I think max(null_frac) would work.

2) get_foreign_key_join_selectivity vs. incomplete matches

The comment right before checking (removedlist == NIL) says:
 * For a multi-column FK, it's possible that we found matches to some * columns but not all, implying that one of the
aboveeffects applied * to just some of the columns.  For the moment, we go ahead and * remove those clauses and apply
theFK's selectivity anyway.  It * might be better to put back the removed clauses and ignore the FK; * but that amounts
tobetting on independence of the clauses, which * doesn't seem like a good bet in such messy cases.
 

Is this a good idea? I'd probably vote to do what's proposed (and 
rejected) in the second half of the comment, i.e. put back the clauses 
and skip the FK as that pretty much says "improve estimate or keep the 
current one, but don't risk making it worse."

3) ForeignKeyOptInfo->rinfos as a List

Can we actually get a list of matching RestrictInfos for a single 
foreign key? I've been unable to construct such query.


regards

-- 
Tomas Vondra                  http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services