On 23 September 2015 at 17:11, David Rowley <david.row...@2ndquadrant.com>

> 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

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

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?


David Rowley

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

Attachment: estimation-with-fkeys-v2_david.patch
Description: Binary data

Attachment: estimation-with-fkeys-v2_david_delta.patch
Description: Binary data

Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:

Reply via email to