Re: allowing extensions to control planner behavior

On 10/4/24 01:35, Robert Haas wrote:
> On Mon, Sep 30, 2024 at 5:50 AM Andrei Lepikhov <lepihov(at)gmail(dot)com> wrote:
>> In attachment - hooks for add_path and add_partial_path. As you can see,
>> because of differences in these routines hooks also implemented
>> differently. Also the compare_path_costs_fuzzily is exported, but it is
>> really good stuff for an extension.
>
> I agree that this is more flexible, but it also seems like it would be
> a lot more expensive. For every add_path() or add_partial_path() call,
> you'll have to examine the input path and decide what you want to do
> with it. If you want to do something like avoid nested loops with
> materialization, you'll need to first check the top-level node, and
> then if it's a nested loop, you have to check the inner subpath to see
> if it's a Materialize node.
I agree, and as you can see, the first simple test on a NestLoop already
avoids further checks for lots of calls for baserels, upper rels, Hash-
and MergeJoins, relieving the issue.
>
> I'm not completely against having something like this; I think there
> are cases where something along these lines is the only way to achieve
> some desired objective. But I don't think this kind of hook should be
> the primary way for extensions to control the planner; it seems too
> low-level to me.
It is a fact. Maybe I was unclear, but I usually use it as an addition
to planner_hook or pathlist hooks to have some guarantee or, at least,
have a chance to do something if another path is much better and is
going to displace my path. Sometimes it is just a way to remove a path
from the pathlist without playing games with costs of my path.

I spent some time discovering the pg_hint_plan extension to apprehend
when extensions need to make massive core code copying and IMO, Michael
has the most to say here.

--
regards, Andrei Lepikhov