Re: allowing extensions to control planner behavior

On 18/9/2024 17:48, Robert Haas wrote:
> Comments?
Let me share my personal experience on path management in the planner.
The main thing important for extensions is flexibility - I would discuss
a decision that is not limited by join ordering but could be applied to
implement an index picking strategy, Memoize/Material choice versus a
non-cached one, choice of custom paths, etc.

The most flexible way I have found to this moment is a collaboration
between the get_relation_info_hook and add_path hook. In
get_relation_info, we have enough context and can add some information
to RelOptInfo - I added an extra list to this structure where extensions
can add helpful info. Using extensible nodes, we can tackle interference
between extensions.
The add_path hook can analyse new and old paths and also look into the
extensible data inside RelOptInfo. The issue with lots of calls eases by
quick return on the out-of-scope paths: usually extensions manage some
specific path type or relation and quick test of RelOptInfo::extra_list
allow to sort out unnecessary cases.

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.

--
regards, Andrei Lepikhov