Re: PATCH: use foreign keys to improve join estimates v1
| От | David Rowley |
|---|---|
| Тема | Re: PATCH: use foreign keys to improve join estimates v1 |
| Дата | |
| Msg-id | CAKJS1f-X0UrLSR9JaHp4F1Gj=La-4PVtcW8HfqakZ==e_xmmQw@mail.gmail.com обсуждение исходный текст |
| Ответ на | Re: PATCH: use foreign keys to improve join estimates v1 (David Rowley <david.rowley@2ndquadrant.com>) |
| Ответы |
Re: PATCH: use foreign keys to improve join estimates v1
|
| Список | pgsql-hackers |
On 23 September 2015 at 17:11, David Rowley <david.rowley@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/
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
Вложения
В списке pgsql-hackers по дате отправления: