Re: plan shape work - Mailing list pgsql-hackers

On Wed, May 21, 2025 at 7:29 PM Robert Haas <robertmhaas@gmail.com> wrote:
>
> On Tue, May 20, 2025 at 2:45 PM Tomas Vondra <tomas@vondra.me> wrote:

> I have a sense - possibly an incorrect one - that the core of the
> problem here is that the planner considers lots of very similar
> alternatives. A hypothetical feature that showed the second-cheapest
> plan would be all but useless, because the second-cheapest plan would
> just be a very minor variation of the cheapest plan in almost all
> cases. One idea that crossed my mind was to display information in
> EXPLAIN about what would have happened if we'd done something really
> different. For instance, suppose that at a certain level of the plan
> tree we actually chose a merge join, but we also show the estimated
> cost of the cheapest hash join (if any) and the cheapest nested loop
> (if any) that we considered at that level. The user might be able to
> draw useful conclusions based on whether those numbers were altogether
> absent (i.e. that join type was not viable at all) or whether the cost
> was a little higher or a lot higher than that of the path actually
> chosen. For scans, you could list which indexes were believed to be
> usable and perhaps what the cost would have been for the cheapest one
> not actually selected; and what the cost of a sequential scan would
> have been if you hadn't picked one.
>
> I'm not sure how useful this would be, so the whole idea might
> actually suck, or maybe it's sort of the right idea but needs a bunch
> of refinement to really be useful. I don't have a better idea right
> now, though.

Having detailed information on the costs of alternative join
methods/scan method, even when a different method is chosen, would be
valuable information. For example, if a merge join is selected for
tables t1 and t2 in a subquery, showing the estimated costs for both a
hash join and a nested loop join would provide a more complete picture
of the planner's decision-making process.

And I believe, this information would be particularly useful if the
cost of a non-selected plan, such as a nested loop join, is very close
to the cost of the chosen merge join. In such cases, a database
administrator or query optimizer could use this insight to manually
override the planner's choice and opt for the nested loop join for
specific tables in a subquery. This level of detail would empower
users to fine-tune query performance and explore alternative execution
strategies.

IIUC, one of the goal of this work is where operator can say I want to
use this scan method while scanning a particular table in a particular
subquery, that means if the planner can give the information about non
selected paths as well then it would be really helpful in making this
process more smooth otherwise without much information on what path
got rejected its very hard to provide hints.

--
Regards,
Dilip Kumar
Google