Re: allowing extensions to control planner behavior

On 10/10/24 23:51, Robert Haas wrote:
> On Wed, Sep 18, 2024 at 11:48 AM Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> 1. If you want to specify in-query hints using comments, how does your
> extension get access to the comments?
Having designed two features [1,2] that do the stuff mostly similar to
pg_hint_plan but based on real cardinalities earned from previous
executions, I can say the most annoying problem is hinting subqueries &
CTEs. Sometimes, you want to hint at the top query and not touch the
subquery and vice versa. Sometimes, users get stuck in accidents when a
flattened subquery influences your hint. So, the key property to invent
in advance should be an identification system.
I have chosen hash-based identification when each RelOptInfo has a hash
created over values of relids, restrictions and hashes of underlying
RelOptInfos. At the core, it seems inappropriate. But anyway, some
additional info must be pushed into the subquery to allow it to identify
itself and decide which hint can be used.

[1] https://github.com/postgrespro/aqo
[2] https://postgrespro.com/docs/enterprise/16/realtime-query-replanning

--
regards, Andrei Lepikhov