Improve planner cost estimations for alternative subplans

From: Alexey Bashtanov <bashtanov(at)imap(dot)cc>
To: PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Improve planner cost estimations for alternative subplans
Date: 2020-06-05 16:08:02
Message-ID: 07b3fa88-aa4e-2e13-423d-8389eb1712cf@imap.cc
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Hello,

Currently, in case of alternative subplans that do hashed vs per-row
lookups,
the per-row estimate is used when planning the rest of the query.
It's also coded in not quite an explicit way.

In [1] we found a situation where it leads to a suboptimal plan,
as it bloats the overall cost into large figures,
a decision related to an outer part of the plan look negligible to the
planner,
and as a result it doesn't elaborate on choosing the optimal one.

The patch is to fix it. Our linear model for costs cannot quite accommodate
the piecewise linear matter of alternative subplans,
so it is based on ugly heuristics and still cannot be very precise,
but I think it's better than the current one.

Thoughts?

Best, Alex

[1]
https://www.postgresql.org/message-id/flat/ff42b25b-ff03-27f8-ed11-b8255d658cd5%40imap.cc

Attachment Content-Type Size
alternatives-cost-as-geom-mean-v2.patch text/x-patch 9.4 KB

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 2020-06-05 16:17:54 Re: valgrind error
Previous Message Tom Lane 2020-06-05 16:05:07 Re: Make more use of RELKIND_HAS_STORAGE()