Re: allowing extensions to control planner behavior

On Mon, Sep 30, 2024 at 5:50 AM Andrei Lepikhov <lepihov(at)gmail(dot)com> wrote:
> Being flexible, this approach is less invasive. Now, I use it to
> implement heuristics demanded by clients for cases when the estimator
> predicts only one row - usually, it means that the optimiser
> underestimates cardinality. For example, in-place switch-off of NestLoop
> if it uses too many clauses, or rules to pick up index scan if we have
> alternative scans, each of them predicts only one tuple.
>
> Positive outcomes includes: we don't alter path costs; extension may be
> sure that core doesn't remove path from the list if the extension
> forbids it.
>
> 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'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.

--
Robert Haas
EDB: http://www.enterprisedb.com