Content-Type: text/plain;
	charset=us-ascii
Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3864.400.21\))
Subject: Re: unstable query plan on pg 16,17,18
From: Attila Soki <atiware@gmx.net>
In-Reply-To: 
 <CAMMNXXnWxgaYYn0xEH36do=UWG_3a47Qq9cyvQ6MucGHh9c=Qw@mail.gmail.com>
Date: Thu, 26 Feb 2026 17:22:47 +0100
Cc: pgsql-performance@postgresql.org,
 Laurenz Albe <laurenz.albe@cybertec.at>
Content-Transfer-Encoding: quoted-printable
Message-Id: <4FEE2E28-5A05-4423-B49E-CA23E2B78420@gmx.net>
References: <ECA94B5A-E5B4-45F7-B1B4-9BF754083164@gmx.net>
 <e94e4d006975792d72e667bbd0499f9ddec4ed58.camel@cybertec.at>
 <64ddfe83-317c-4cf1-b04f-cb74d9f40629@gmail.com>
 <529E2365-6C3F-4BDA-9625-312F3A023C5B@gmx.net>
 <5bace3e5-3d6b-4f81-92a9-9ff4e9e5de53@gmail.com>
 <1C445A2F-3256-4F04-B55D-9850581FF39A@gmx.net>
 <0dd76506-053e-4a30-9f95-9dc324e8e1fb@gmail.com>
 <FF816253-DD7D-4AC2-8CD1-8A5ACB1D1113@gmx.net>
 <1388B912-7E9A-475A-93AA-67FB99A084BC@gmx.net>
 <0c8b9845-a2ac-4613-8075-45dc55911143@gmail.com>
 <45AA83DB-A6B4-43A2-879B-290E4A7845AE@gmx.net>
 <CAMMNXXnWxgaYYn0xEH36do=UWG_3a47Qq9cyvQ6MucGHh9c=Qw@mail.gmail.com>
To: Andrei Lepikhov <lepihov@gmail.com>
UI-OutboundReport: notjunk:1;M01:P0:svgAojE0+/Q=;1hkSwcfwix9iTG2o1HF8FZq91PP
 oQEqMYGGe2FFgxePc3CwBjuYCj70s2xQqnWxVj0ZyxOEffIgfU2XiYCs6uIhYP2FSUqAAvt76
 85UOiqNxGplqCNgS/JrTJ7gzt41YQZSceF0LdPV9llXGED7G4N0vxKs5IfFk1l1iAS2BlkQRR
 NI6r2cOrC9LDV8eHr654eXF05yAvb/ZIspfLz3pVlCjU+7eESct7+eAgumATkqY+pX62Iv3aI
 4uXxeK1IlL0hfOfgPbpCkqF1+JEEbfHckGyUcRvDsiqz0laBt3IcKiVUF+Sv6g+qVxq3zIFlp
 8YLZzDbeGZXkcZ7TvEpFNV/srwGw9ftzcPZQ3eXq9jhzV5tFZzQ0GTahsG2TuGXRF3ZjLE18P
 aUVIqP37WPjK17y7VP943rQVyUIy6eZCn9mG+fIQb4IOB3H5efY8qrdIr3+sluVxyLkKV6T08
 hJAY0FfBP0aez8QNqj7EkFx3QcQDFvaqSdcAjxHWGlMZuL1EXcQlL+lkfDHIxiaxMSypM07wN
 2BWAxGhRy/odUAdPgMgbwUWxIVQf5DcYM049O1vBTUbNgt6b5QXrf/mVUpRIgOwSl8+nVNb20
 3Xmni/TuGe+eYGKBV8RYu0I6BbUJgalcCkXoguJAePZeczirUpz3ww4z7buWU7j7LjT0WtZ5R
 F/qITn8bHeXBfvInEZPkIe2eMScqVjukNDPwBJQ0/G/0Yfpst0+un3aufV+ghtNHENAU+KfVV
 5JDTeZfIO+E6VozOEQbp+8DhD2kDLSiB+KnQ7ZR6Knu86PWeRp+sf9iuko1JjuyyjgA2JKWGq
 OwPC0389UqIC9qQeBedsXB4w1/iALhrVKrOeFZXqMrLBSGt+ZgNGr73PJhR5LfMGEHS8zi8GR
 vTgQG5QZmlYSvSlPj82X0/pQDZexzle9FRUaQNqOPDX4L1p98+Bo4pmy8QX90bnuqBfj5rSCf
 8lh9x/rCqAwao6EAwQJEGz4xw3Uj5VzMwCMgeHH35T3kab8VqFpoFV8/GZcuyGVWQNKmDTZZ+
 jus2Q0Vq1oqbx4Im8sIhfZCqcqune+m44J0c6yYbVeZU3sSMCUnkGeHudnCoFXEq967hli4N9
 hsbrkr40J48tS9NQWbp+TEPO1BlNxHVTZEif6pPU1RO4KelI/+8nRQjr1Ddc6X7Lq/VX4DtiR
 iNZSzuymS8wbE56oNefGUE3beHBcUZDthW082A8kAG2ge0wroCSXMQBrHbj4lC7d8OIGPAMmb
 GQoGZtdH+I5tia74YRrKAcg9E+VSQaKjmiZ0DBQk+KcFDfrFKevc0URRDYDzcYd8buNAYp725
 /troPewkzVRPEkEWDVFHaWsa8nK1hPp6cVjL/auNKHMF7/R64UQHnovXiTclXlDWH8CeXHPuu
 lfRczB10s+uhxxTV4dcxhkIO2+wHKAyUXkyA/k0b2YQJZNyJLP/ceiYoHP2UoAoS0IIbHOZTJ
 UhmHJ3YrRCIzvyakdU//IWGOjxNQL/EOsjh/mQgXw/OrEFcigfZU86Zey8TbkK5DacNlVCNDZ
 8jmqzCZXpiiBxpxvEiEKxchzV1B9Gl3JLQSGOTKM6nSAoJNWNOT2F83ykdZ8rA06CrxHlK9z1
 yakN2r6bHRi9ZiIAzHPmV3HCgHi+W2haduJi5QHs3FyRwplXZGO2Yorf7z+YmoZ6uMBX9dOFt
 tpsnHs224tFyxj5B5EXkoR1WcXLgNk/2vxa7x4yLxjWyaAKOLVYHXZK6Cp9g1MaYAVWErkQlB
 E6IDBPh8cZDX/O0Zam5X5o7SrxTGWNFJAvx77F/wXYKAnPvpyUmHVZWsE5M44xCY48pjNuxYg
 0wtdek67yfUXxfCzoDeG4CuCv60wnkG+KjnctUXFheqcvWW9FVUjyJ8Xf/F9Qa1/xCen9lg5G
 nb+aBKREJI7XoLvbc9gKl/ft13GWUFMdSdeueHDqd4nXWdYDBCBZrAueF9922374sbmnCxnIm
 DJGchYNZurHPJ4ueXG7swro/CyAMP8JF0dkdYRjiGXnPfGuzT8GQzOkbFxVs3X2Mg8y0Rj22r
 rp64oCNxrq+2TThqVZWoREyNBWngJBgF4GfI+OKNTfI10NbklUwI7FZR874XhySqrd4vMcMA3
 tqCL9dxMF/kf2+oLC8DMJVjuwIMyyvlgDTM+HtoDcIAVB91Fxpo5Q0VS5DPvc7xozc1z123eU
 drPD8e12NqbANNSsG1gZEFqEe0oH3J1GqmHHyybCxtor0qJu9lUa9bL3ZZd4LOiGsFOZ9Kiyt
 N08WUt65SK6NmYD5fDEMsJ98WD1FvP7ZkCBe5lBbRVCvdY52ghVTAWFDqw2dzzA5OZO5DR/mt
 YC+eZBqoVUjJeKABDT9oKGqR4D6lEpE27HIPiiwiyoElToYdK4G3MA5NS2OySmCCWIHiY/eG/
 om8Fv3vHQU0naDtVFgwcehhU8LDSi75mJfaA01Vn33/XAl51Wx/khYenxDXX/J3eWa5RMlcIs
 lV6HUqI5EQRa89aG303JM0iix+8IQ1OJq2u4wOP3Vh3shsX0F5ALXbIEKtGuw2domTrLIGRIs
 za8HvIbTHw4/t+tT2fY6ZciLeSHMZJJgHlenECrNO+/DuRzVPHN1FfdZvG+VgUyopPKKNBzl1
 3EuPr41vsRoAW3qAMBJlXpIm82YkqlgXao69lp2i/OA+Ci4nP14btdIdRxnglbiWM8A4+7pe6
 Z7v8U/rjGYPiAB0suf7LVOQVZOoBDtbdoNb+Ca/sNxocIXUCH3p+DApgjyqGTaLnG+kF5NPvz
 sFuNbp9YcWTugwGAs0M1ossb0Lp1qm62GIFa2N8kN9lsMBZIq+AHstcPv22PuC5L7/dsnCLC+
 Qw4jcr8rOQUwyFRWc5qYnj/Fq1iaEaMnOJ1x+zYbFQ0qpEGusDtiUewcsQ71MIZjyQMPIPaCH
 3tE8aTb5TgsghomBoH2dMVXl/3l2T0SAfq3cwo9lWvA3Vx7mE5QhT1mC5pwSXs3derS6ECJPr
 b8VfWMRfdBAeRn1tmlfR6PjgklInxIpODALCF4qL9pbrQIlQTFW3JiZGiGtBVzu3n+Ewe7bZv
 JAkGDwzAbRpNJ7zFan0Wn7kecLwREAnWehrHv29hq4/NuqmjPGfoVD2pu/EGN+ozJiRDouqhY
 bhPI8AlL9+lA8Pb4rgXiloRKLZ5n2lf1O9XZZ49TCZhFAKNrQDWEFbc5qD8RV2QP7UzQKeUC5
 0BWRVKVV39G4fdaNBr48D/CJvKmlQzL3/334GO5ZZCcRxy/cHMp636+RBo7YA/APIvJX7ke6B
 BU1mjVSK4oTXaoew8xoO0KAhC4CuBylbcGKxShyVqWH4kNY6eXkepLMmHe8X30BTrwevSHr3C
 SRDGo0SZVHkaTroQcRfwErqd65MMUJOdq8DBAcmEUUyIHmPUcYWGbBeDCCOFl9G77ynuPAvVT
 9rYGVqakRuuF9hN6Nb6uAnJqfii4Hyi3sYw5kQxHcRW2oO65Cpw0I1GH2Yfb4kYW8y3z8tm8t
 NUN1+9Tr224Rb3r+kVvWiVDATcNZvQCyZQgYeE6+wT3Ob3FBfQlVaxbg6PmuPbz7mykE2fesx
 P7xed2b3xK6Duid64oASp9TfBK8zYaPPEXtGoXCcSy4q4UXTJR519W6lR+5HMxgksBr9gSQOX
 Q6n2cKax2Mj5QlaC0aNPIvoX19p1mj1FnvDhH0by1MqyB90OtRRNWHkcPtkNaamxFIxWG9mt8
 uyWKQtfESmxFmN3WFX4B/K4BAmPzG9zU84m8rPX151h2LnzsY/2UqUCw9+xgm+4Cgvp4IQT0q
 6Yjzy5QWo5iAffwyV0v2AfNCt3HZJ33HZXRd8wpVgEm12vZU+QZ/dTeKv+rEcPYzN90v/vRjr
 mkslYTRrZUR2xCNTPS7f1CEOkFhgcv/+l1mCji2ZVOUwCMqNXr/ZmFdhl+hfygu0CI2+a+jY7
 dyDIoGzKuk2wYnVFP7JDdW0chvueycvBFtymTssARUZjxWpR82cRzhBSJ9Y93K693/IADOyNV
 AujeLi1tIs1RKxEU6cuvsAaKYEl3RQd/oAyJ+nBzfs6XDDuhQSYy109DQGdppYjGMFmpT5Jxb
 pgcNOFxk5kvYnWjdLUczVbvBLs5HZl1zmGHEbwwuLViwBcVuYsli1Fzqa9ET04uLG5me83/Au
 wtWlwZ2N3DDbRDg2uOq5NI+rn75HUVwTdGgFWVhuNcvg+EDYfGkn3G8+7jve/iZYAfXBO6kYv
 DJatGzBZZMZZGO4SnfiEsMYBs02gMg1rHE5PQaOHJQBvVxN5RUYaYck0SH6pgKNtwDkbKXY2A
 QN+/8rKN6VdLwLQ3Q81k6f+CNj+TZU73wVoM7e9lWUVPcii6UDN7XcD4ZxUzSsX25F78mkIKR
 inA5z2Hbm62upZvz9DHBrEtMwiQnwRNDobIvg8KAmHkRPcwXKmfTu8Be3MW8QlYQgDLo7777n
 o4UVdp7DTduLbc5BPvRDcGR/DZODoOG5nNHIqgTKamr27NmAu5la5TxCbRq6pZwtzJlHglQRh
 gTs7JmzEmEItN1x4evh04oyyI5014lI6nHWSPh6sZbYcQ8JzJfPgPjfFslTXiaSwVzb477fNO
 wFYjcIaVWMcTTrO5+UEHEeldxhAGNF1d9qewVCPSbEQHVVA5KDxstPhHIKRNzkmFLpFAgVIvi
 FdVVsON7OKGF81WJmf4a3vgr/t6zreZ4gxatILbcv8BEEtlZwP0EJbrJMCd60gJwhIPKigfqv
 touvkQj36NctgPdffglfF8mS+KAc0SGKijKKQCRuODai6vI0h9I8yqVwVMhkcumcr0yfGkAuj
 Qhc0JvYyqTqIINbySjQzql4yyfN7m/Yj2QZxzehv2K2wQsQjPhLx1whLrYTAo2atvHKdqUYuv
 Qdmg7fdI2ywBqfQl6f+rFFzG0HZ5bz1J3/s3avDTfmdDJQxKXGzXcmcxgMMf453joIkLOE0ok
 VWgA1FxIicXF2hbpTrN/pO7OUN7H+NCoJNYtbtV5mPC+9ZPT2E39Gdz9VrbhZ4CgUEwBAzbpu
 8fhsgwheWos9CXGyvHCY15eJ2EpGgWv5XwAdRy6B/WTD+BpqJMY5Lo4FhOB8DVu24vdJ1l7Hs
 I2e3zjmKwzYGVr14Eet+xDrFPcwr5f3N7+yVSPac9Y09P061xk+ZxFuXzYllgwFj8zTok+iu8
 6rfesCLnNvZwVd5g9D7QhAn22c+SimYAaD+peR
Archived-At: 
 <https://www.postgresql.org/message-id/4FEE2E28-5A05-4423-B49E-CA23E2B78420%40gmx.net>
Precedence: bulk

On 24 Feb 2026, at 20:20, Andrei Lepikhov <lepihov@gmail.com> wrote:
>=20
> 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.
>=20
> 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.
>=20

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 =3D =
kal.rti_id ... AND gdt_2.datum >=3D ('now'::cstring)::date) .. and =
gauf_2....
				))
		) spaet
		select
		...
		from view
		left join spaet on spaet.rti_id::text =3D =
akd.dp_rti_id::text
		left join lateral ( select from where )
		left join lateral ( select from where )
		...
	=09
		somewhere around this line:
		"Filter: ((ext_dd.table_d_id IS NULL) OR =
((ext_dd.table_d_id)::text =3D 'schema1'::text) OR (NOT =
COALESCE(ext_dd_dpe.enabled, false)))"

	) table_k_dly
=09

Thank you.

Regards,
Attila