On 23 September 2015 at 17:11, David Rowley <david.row...@2ndquadrant.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
estimation-with-fkeys-v2_david.patch
Description: Binary data
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: http://www.postgresql.org/mailpref/pgsql-hackers
