Re: allowing extensions to control planner behavior - Mailing list pgsql-hackers

From Robert Haas
Subject Re: allowing extensions to control planner behavior
Date
Msg-id CA+TgmoZQVNwvn_nFOqDkn6i7U=KO4197JzcAk_6OO_Q+Xx=7Gg@mail.gmail.com
Whole thread Raw
In response to Re: allowing extensions to control planner behavior  (Tom Lane <tgl@sss.pgh.pa.us>)
Responses Re: allowing extensions to control planner behavior
List pgsql-hackers
On Mon, Sep 30, 2024 at 5:50 AM Andrei Lepikhov <lepihov@gmail.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



pgsql-hackers by date:

Previous
From: Masahiko Sawada
Date:
Subject: Re: Using per-transaction memory contexts for storing decoded tuples
Next
From: Alexander Lakhin
Date:
Subject: Re: query_id, pg_stat_activity, extended query protocol