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@gmail.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