Re: New design for FK-based join selectivity estimation

From: Tomas Vondra <tomas(dot)vondra(at)2ndquadrant(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-hackers(at)postgresql(dot)org
Subject: Re: New design for FK-based join selectivity estimation
Date: 2016-06-19 10:54:40
Message-ID: 747f8de5-266e-cf7e-b0ab-57b8a6725517@2ndquadrant.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Michael Paquier 2016-06-19 10:57:42 Re: [BUGS] BUG #14199: The pg_ctl status check on server start is not compatible with the silent_mode=on
Previous Message Tomas Vondra 2016-06-19 10:44:38 Re: New design for FK-based join selectivity estimation