public inbox for [email protected]  

help / color / mirror / Atom feed

From: Attila Soki <[email protected]>
To: Andrei Lepikhov <[email protected]>
Cc: [email protected]
Cc: Laurenz Albe <[email protected]>
Subject: Re: unstable query plan on pg 16,17,18
Date: Thu, 26 Feb 2026 17:22:47 +0100
Message-ID: <[email protected]> (raw)
In-Reply-To: <CAMMNXXnWxgaYYn0xEH36do=UWG_3a47Qq9cyvQ6MucGHh9c=Qw@mail.gmail.com>
References: <[email protected]>
	<[email protected]>
	<[email protected]>
	<[email protected]>
	<[email protected]>
	<[email protected]>
	<[email protected]>
	<[email protected]>
	<[email protected]>
	<[email protected]>
	<[email protected]>
	<CAMMNXXnWxgaYYn0xEH36do=UWG_3a47Qq9cyvQ6MucGHh9c=Qw@mail.gmail.com>

On 24 Feb 2026, at 20:20, Andrei Lepikhov <[email protected]> wrote:
> 
> On 24/2/26 17:48, Attila Soki wrote:
>>> On 24 Feb 2026, at 16:57, Andrei Lepikhov <[email protected]> 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

view thread (25+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected]
  Subject: Re: unstable query plan on pg 16,17,18
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox