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

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.

As far as I can identify, there are two candidates:
The first one because of "lateral", but I think this is not the problematic part
select
from
left join lateral ()

somewhere around this line:
Output: dim_kal.oo_id, dim_stamm.dmn_gew, dim_kal.art_vk, dim_stamm.dmn_anz, dim_kal.art_bl, dim_kal.art_dp, dim_stamm.dmn_vol, dim_ext_dd.table_d_id, dim_ext_dd_dpe.enabled, dim_kal.rti_id, dim_stamm.ist_divers_rti, dim_stamm.ist_psa_rti

The second one because of your prior comment about "odg" and "rebuilt multiple times (around 1k) due to an external parameter (gauf_1.id)"
gauf_1 refers to a view and this view is used multiple times in the query. see my previous mail for more details about gauf_1.

select
...
from (
with (
select
...
from table_k kal
where ...
AND not ( exists (
select oo_id from "view_gauf_1" gdt_2
where gdt_2.rti_id = kal.rti_id ... AND gdt_2.datum >= ('now'::cstring)::date) .. and gauf_2....
))
) spaet
select
...
from view
left join spaet on spaet.rti_id::text = akd.dp_rti_id::text
left join lateral ( select from where )
left join lateral ( select from where )
...

somewhere around this line:
"Filter: ((ext_dd.table_d_id IS NULL) OR ((ext_dd.table_d_id)::text = 'schema1'::text) OR (NOT COALESCE(ext_dd_dpe.enabled, false)))"

) table_k_dly

Thank you.

Regards,
Attila