Re: How to retain lesser paths at add_path()?

On Sun, Oct 6, 2019 at 3:23 PM Tomas Vondra
<tomas(dot)vondra(at)2ndquadrant(dot)com> wrote:
> >I don't think this hook is a very useful approach to this problem, and
> >I'm concerned that it might have a measurable performance cost.
>
> Can you be more specific why you don't think this approach is not
> useful? I'm not sure whether you consider all hooks to have this issue
> or just this proposed one.

I'll start by admitting that that remark was rather off-the-cuff. On
further reflection, add_path() is not really a crazy place to try to
add a new dimension of merit, which is really what KaiGai wants to do
here. On the other hand, as Tom and I noted upthread, that system is
creaking under its weight as it is, and making it extensible seems
like it might therefore be a bad idea - specifically, because it might
slow down planning quite a bit on large join problems, either because
of the additional cost testing the additional dimension of merit or
because of the additional cost of dealing with the extra paths that
get kept.

It is maybe worth noting that join/aggregate pushdown for FDWs has a
somewhat similar problem, and we didn't solve it this way. Should we
have? Maybe it would have worked better and been less buggy. But
slowing down all planning for the benefit of that feature also sounds
bad. I think any changes in this area need careful though.

> As for the performance impact, I think that's not difficult to measure.
> I'd be surprised if it has measurable impact on cases with no hook
> installed (there's plenty more expensive stuff going on). Of course, it
> may have some impact for cases when the hook retains many more paths
> and/or does something expensive, but that's kinda up to whoever writes
> that particular hook. I think the assumption is that the savings from
> building better plans far outweight that extra cost.

You might be right, but add_path() is a pretty darn hot path in the
planner. You probably wouldn't see a significant overhead on a
single-table query, but on a query with many tables I would not be
surprised if even the overhead of an empty function call was
measurable. But I could be wrong, too.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company