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-18 04:09:39 |
Message-ID: | ee1a76b0-43ed-a6a4-2acb-7c410d0be3ac@2ndquadrant.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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 above effects applied
* to just some of the columns. For the moment, we go ahead and
* remove those clauses and apply the FK's selectivity anyway. It
* might be better to put back the removed clauses and ignore the FK;
* but that amounts to betting 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
From | Date | Subject | |
---|---|---|---|
Next Message | Josh Berkus | 2016-06-18 04:20:06 | Re: 10.0 |
Previous Message | Amit Kapila | 2016-06-18 03:48:21 | Re: pgsql: Try again to fix the way the scanjoin_target is used with partia |