Re: New design for FK-based join selectivity estimation

On mardi 13 décembre 2016 09:10:47 CET Adrien Nayrat wrote:
> Hi hackers,
>
> The commit 100340e2dcd05d6505082a8fe343fb2ef2fa5b2a introduce an
> estimation error :
[....]
>
> Estimated row is 10x larger since 100340e2d
>
> Regards,

Hello,

I think I understand what the problem is. In get_foreign_key_join_selectiviy,
we remove the restrict info clauses which match a foreign key. This is done so
that the selectivy is not applied twice (once in the function itself, once
when processing the restrictinfos).

The problem is, for semi and anti joins, we assume that we have nohing to do
(costsize.c:4253):

else if (jointype == JOIN_SEMI || jointype == JOIN_ANTI)
{
/*
* For JOIN_SEMI and JOIN_ANTI, the selectivity is defined as the
* fraction of LHS rows that have matches. If the referenced
* table is on the inner side, that means the selectivity is 1.0
* (modulo nulls, which we're ignoring for now). We already
* covered the other case, so no work here.
*/
}

This results in assuming that the whole outerrel will match, no matter the
selectivity of the innerrel.

If I understand it correctly and the above is right, I think we should ignore
SEMI or ANTI joins altogether when considering FKs, and keep the corresponding
restrictinfos for later processing since they are already special-cased later
on.

Regards,

--
Ronan Dunklau