Re: PATCH: use foreign keys to improve join estimates v1

On 23 September 2015 at 17:11, David Rowley <david(dot)rowley(at)2ndquadrant(dot)com>
wrote:

> find_foreign_key_clauses() should look for the longest match and return a
> Bitmap set of the list indexes to the caller.
> It might be possible to fool the longest match logic by duplicating
> clauses, e.g. a1 = b1 AND a1 = b1 and a1 = b1 AND a2 = b2 AND a3 = b3, but
> I can't imagine that matters, but if it did, we could code it to be smart
> enough to see through that.
>

I took a bash at implementing what I described, and I've ended up with the
attached.

git diff -stat gives me:

src/backend/optimizer/path/costsize.c | 717
++++++++----------------------
src/backend/optimizer/plan/analyzejoins.c | 1 +
src/backend/optimizer/util/plancat.c | 112 +++--
3 files changed, 228 insertions(+), 602 deletions(-)

So it's removed quite a bit of code. I also discovered that: 1.0 /
Max(rel->tuples,1.0) is no good for SEMI and ANTI joins. I've coded around
this in the attached, but I'm not certain it's the best way of doing things.

I thought that it might be possible to add some regression test around
this, if we simply just find a plan the uses a nested loop due to
underestimation of matching rows, and then make sure that it no longer uses
a nested loop when the foreign key is added. I've not yet done this in the
attached.

Patched attached in delta form and complete form.

I still need to perform more analysis on the plancat.c changes.

Have I made any changes that you disagree with?

Regards

David Rowley

--
David Rowley http://www.2ndQuadrant.com/
<http://www.2ndquadrant.com/>
PostgreSQL Development, 24x7 Support, Training & Services