Re: New design for FK-based join selectivity estimation

Hi

On 06/18/2016 06:52 PM, Tom Lane wrote:
> Tomas Vondra <tomas(dot)vondra(at)2ndquadrant(dot)com> writes:
>> 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).
>
> I concur, actually, but I feel that it's out of scope for this
> particular patch, which is only trying to replace the functionality
> that was committed previously. If you want to come up with a patch on
> top of this that adds some accounting for NULLs, I'd be willing to
> consider it as a post-beta2 improvement.

Sure, fair enough. By post-beta2 you mean for 9.7, or still for 9.6?

>
>> 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.
>
> Yeah, I was thinking along the same lines: max of the per-column null
> fractions is probably an OK estimate.

OK

>
>> 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.
>
> I think you'd actually have to write redundant outer join quals,
> along the lines of
> select ... a left join b on (a.x = b.y and a.x = b.y)
> I don't believe we take the trouble to eliminate such duplicates
> unless they get absorbed by an EC, which outer-join quals would
> not be. (Haven't tried this, though, as I don't have the patch
> installed right now.)

OK. Let's look into this post-beta2 then.

>
> The beta2 deadline is just about upon us; I feel that if we're going
> to get this into this release at all, we need to push it today so
> that we get a full buildfarm cycle on it before the wrap.
>
> I plan to spend an hour or two adjusting the qual match logic as
> discussed above, and re-reading the whole patch another time for
> sanity. If I've not heard objections by the time I'm done,
> I will push it.
>

Thanks!

If I could wish one more thing - could you briefly explain why you
rewrote the patch the way you did? I'd like to learn from this and while
I think I kinda understand most of the changes, I'm not really sure I
got it right.

regards

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