Re: unstable query plan on pg 16,17,18

On 26/2/26 17:22, Attila Soki wrote:
> On 24 Feb 2026, at 20:20, Andrei Lepikhov <lepihov(at)gmail(dot)com> wrote:
>>
>> On 24/2/26 17:48, Attila Soki wrote:
>>>> On 24 Feb 2026, at 16:57, Andrei Lepikhov <lepihov(at)gmail(dot)com> wrote:
>>>> On 24/2/26 16:50, Attila Soki wrote:
>>> I can provide more details on other parts of the query too, if that helps.
>>
>> Only query and reproduction make sense for me to discover more deeply at
>> the moment.
>> It looks like we have managed to reproduce the potential 'Hash over
>> parameterised subtree' issue. Please check the attachment: there are two
>> plans. One plan has a longer execution time and more blocks hit, but its
>> cost estimate is four times lower. The EXPLAIN output does not show any
>> obvious estimation errors. This suggests there may be a bug in the cost
>> model.
>>
>
> I looked your repro, and I tried to find the corresponding part in my query.
> If that not the right place is, please point me to the part in explain, so I can compare your repro and that part of my query.
No problem. Issues with your query plan starts in exactly the following
line:

-> Hash Right Join (cost=210369.25..210370.30 rows=8 width=99)
(actual time=150.790..150.853 rows=44.56 loops=21798)

Schema of this part of the query tree is as the following:

Hash Right Join (loops=21798)
│
├─ [Left/Probe] GroupAggregate (loops=14426)
│ └─ Merge Right Anti Join
│ └─ Merge Join
│ └─ Index Only Scan on table_k gkal_2 (loops=14426)
│
└─ [Right/Build = Hash] Nested Loop (loops=21798)
├─ Index Scan on table_o goftr_1 (loops=21798)
│ Index Cond: goftr_1.au_id = gauf_1.id
└─ Index Scan on table_k gkal_1
Index Cond: gkal_1.oo_id = goftr_1.id

So, the hash table is rebuilt each rescan based on the changed
'gauf_1.id' external parameter.
Without the query, it is hard to say exactly what the trigger of this
problem is. Having a reproduction, we could use planner advising
extensions and see how additional knowledge of true cardinalities
rebuilds the query plan. Sometimes, additional LATERAL restriction,
added by the planner to pull-up subplan, restricts the join search scope
badly, but I doubt if we have this type of problem here.

--
regards, Andrei Lepikhov,
pgEdge