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

On 24 Feb 2026, at 20:20, Andrei Lepikhov <lepihov@gmail.com> wrote:
> 
> On 24/2/26 17:48, Attila Soki wrote:
>>> On 24 Feb 2026, at 16:57, Andrei Lepikhov <lepihov@gmail.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