MIME-Version: 1.0
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>
In-Reply-To: <45AA83DB-A6B4-43A2-879B-290E4A7845AE@gmx.net>
From: Andrei Lepikhov <lepihov@gmail.com>
Date: Tue, 24 Feb 2026 20:20:29 +0100
Message-ID: 
 <CAMMNXXnWxgaYYn0xEH36do=UWG_3a47Qq9cyvQ6MucGHh9c=Qw@mail.gmail.com>
Subject: Re: unstable query plan on pg 16,17,18
To: Attila Soki <atiware@gmx.net>
Cc: pgsql-performance@postgresql.org, Laurenz Albe <laurenz.albe@cybertec.at>
Content-Type: multipart/mixed; boundary="0000000000008a959b064b96c85c"
Archived-At: 
 <https://www.postgresql.org/message-id/CAMMNXXnWxgaYYn0xEH36do%3DUWG_3a47Qq9cyvQ6MucGHh9c%3DQw%40mail.gmail.com>
Precedence: bulk

--0000000000008a959b064b96c85c
Content-Type: text/plain; charset="UTF-8"

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.


--
regards, Andrei Lepikhov,
pgEdge

--0000000000008a959b064b96c85c
Content-Type: application/sql; name="parameterized_hash_demo.sql"
Content-Disposition: attachment; filename="parameterized_hash_demo.sql"
Content-Transfer-Encoding: base64
Content-ID: <>
X-Attachment-Id: 

QkVHSU47CgpEUk9QIFRBQkxFIElGIEVYSVNUUyBkcml2ZXIsIGJpZ19zaWRlLCBzbWFsbF9zaWRl
OwoKLS0gRHJpdmVyOiBzbWFsbCDihpIgZm9yY2VzIE5lc3RlZCBMb29wCkNSRUFURSBUQUJMRSBk
cml2ZXIgKGlkIGludCBQUklNQVJZIEtFWSk7CklOU0VSVCBJTlRPIGRyaXZlciBTRUxFQ1QgZyBG
Uk9NIGdlbmVyYXRlX3NlcmllcygxLCA1KSBnOwoKLS0gTGVmdCAocHJvYmUpIHNpZGUgb2YgdGhl
IExFRlQgSk9JTjogbGFyZ2UsIE5PVCBwYXJhbWV0ZXJpemVkLgpDUkVBVEUgVEFCTEUgYmlnX3Np
ZGUgKAogICAgaWQgICBzZXJpYWwgUFJJTUFSWSBLRVksCiAgICBqa2V5IGludCBOT1QgTlVMTAop
OwpJTlNFUlQgSU5UTyBiaWdfc2lkZSAoamtleSkKICAgIFNFTEVDVCBnICUgMjAwIEZST00gZ2Vu
ZXJhdGVfc2VyaWVzKDEsIDEwMDAwMCkgZzsKCi0tIFJpZ2h0IChoYXNoIGJ1aWxkKSBzaWRlIG9m
IHRoZSBMRUZUIEpPSU46IHBhcmFtZXRlcml6ZWQgYnkgZHJpdmVyLmlkLgotLSBNYW55IHJvd3Mg
b3ZlcmFsbCwgYnV0IH41MCBtYXRjaGluZyBwZXIgZHJ2X2lkLgpDUkVBVEUgVEFCTEUgc21hbGxf
c2lkZSAoCiAgICBpZCAgICAgc2VyaWFsIFBSSU1BUlkgS0VZLAogICAgZHJ2X2lkIGludCBOT1Qg
TlVMTCwKICAgIGprZXkgICBpbnQgTk9UIE5VTEwKKTsKSU5TRVJUIElOVE8gc21hbGxfc2lkZSAo
ZHJ2X2lkLCBqa2V5KQogICAgU0VMRUNUIChnICUgNSkgKyAxLCBnICUgMjAwCiAgICBGUk9NIGdl
bmVyYXRlX3NlcmllcygxLCAyNTApIGc7CkNSRUFURSBJTkRFWCBPTiBzbWFsbF9zaWRlIChkcnZf
aWQpOwoKQU5BTFlaRSBkcml2ZXI7CkFOQUxZWkUgYmlnX3NpZGU7CkFOQUxZWkUgc21hbGxfc2lk
ZTsKClNFVCBlbmFibGVfbWVyZ2Vqb2luID0gb2ZmOwpTRVQgZW5hYmxlX25lc3Rsb29wICA9IG9u
OwoKRVhQTEFJTiAoQU5BTFlaRSwgVkVSQk9TRSkKU0VMRUNUIGQuaWQsIHN1Yi50b3RhbApGUk9N
IGRyaXZlciBkCkpPSU4gTEFURVJBTCAoCiAgICBTRUxFQ1QgY29hbGVzY2Uoc3VtKHMuamtleSks
IDApIEFTIHRvdGFsCiAgICBGUk9NIGJpZ19zaWRlIGIKICAgIExFRlQgSk9JTiBzbWFsbF9zaWRl
IHMgT04gcy5qa2V5ID0gYi5qa2V5IEFORCBzLmRydl9pZCA9IGQuaWQKKSBzdWIgT04gdHJ1ZTsK
CgpSRVNFVCBlbmFibGVfbWVyZ2Vqb2luOwpTRVQgZW5hYmxlX2hhc2hqb2luID0gb2ZmOwoKRVhQ
TEFJTiAoQU5BTFlaRSwgVkVSQk9TRSkKU0VMRUNUIGQuaWQsIHN1Yi50b3RhbApGUk9NIGRyaXZl
ciBkCkpPSU4gTEFURVJBTCAoCiAgICBTRUxFQ1QgY29hbGVzY2Uoc3VtKHMuamtleSksIDApIEFT
IHRvdGFsCiAgICBGUk9NIGJpZ19zaWRlIGIKICAgIExFRlQgSk9JTiBzbWFsbF9zaWRlIHMgT04g
cy5qa2V5ID0gYi5qa2V5IEFORCBzLmRydl9pZCA9IGQuaWQKKSBzdWIgT04gdHJ1ZTsKClJPTExC
QUNLOwoKLyoKICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg
ICAgICAgICAgICAgICAgIFFVRVJZIFBMQU4gICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg
ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAKLS0tLS0tLS0tLS0tLS0tLS0tLS0t
LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t
LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t
LS0tLS0tCiBOZXN0ZWQgTG9vcCAgKGNvc3Q9MjQ0Ny4xMi4uMTIyMzYuNzUgcm93cz01IHdpZHRo
PTEyKSAoYWN0dWFsIHRpbWU9MzIuMzE2Li4xNjEuNTA4IHJvd3M9NS4wMCBsb29wcz0xKQogICBP
dXRwdXQ6IGQuaWQsIChDT0FMRVNDRShzdW0ocy5qa2V5KSwgJzAnOjpiaWdpbnQpKQogICBCdWZm
ZXJzOiBzaGFyZWQgaGl0PTIyMjYKICAgLT4gIFNlcSBTY2FuIG9uIHB1YmxpYy5kcml2ZXIgZCAg
KGNvc3Q9MC4wMC4uMS4wNSByb3dzPTUgd2lkdGg9NCkgKGFjdHVhbCB0aW1lPTAuMDA0Li4wLjAw
NSByb3dzPTUuMDAgbG9vcHM9MSkKICAgICAgICAgT3V0cHV0OiBkLmlkCiAgICAgICAgIEJ1ZmZl
cnM6IHNoYXJlZCBoaXQ9MQogICAtPiAgQWdncmVnYXRlICAoY29zdD0yNDQ3LjEyLi4yNDQ3LjEz
IHJvd3M9MSB3aWR0aD04KSAoYWN0dWFsIHRpbWU9MzIuMjk5Li4zMi4yOTkgcm93cz0xLjAwIGxv
b3BzPTUpCiAgICAgICAgIE91dHB1dDogQ09BTEVTQ0Uoc3VtKHMuamtleSksICcwJzo6YmlnaW50
KQogICAgICAgICBCdWZmZXJzOiBzaGFyZWQgaGl0PTIyMjUKICAgICAgICAgLT4gIEhhc2ggTGVm
dCBKb2luICAoY29zdD01Ljc1Li4yMTk3LjEyIHJvd3M9MTAwMDAwIHdpZHRoPTQpIChhY3R1YWwg
dGltZT0wLjA1NS4uMjYuNjk4IHJvd3M9MTA1MDAwLjAwIGxvb3BzPTUpCiAgICAgICAgICAgICAg
IE91dHB1dDogcy5qa2V5CiAgICAgICAgICAgICAgIEhhc2ggQ29uZDogKGIuamtleSA9IHMuamtl
eSkKICAgICAgICAgICAgICAgQnVmZmVyczogc2hhcmVkIGhpdD0yMjI1CiAgICAgICAgICAgICAg
IC0+ICBTZXEgU2NhbiBvbiBwdWJsaWMuYmlnX3NpZGUgYiAgKGNvc3Q9MC4wMC4uMTQ0My4wMCBy
b3dzPTEwMDAwMCB3aWR0aD00KSAoYWN0dWFsIHRpbWU9MC4wMDUuLjcuNDQ2IHJvd3M9MTAwMDAw
LjAwIGxvb3BzPTUpCiAgICAgICAgICAgICAgICAgICAgIE91dHB1dDogYi5pZCwgYi5qa2V5CiAg
ICAgICAgICAgICAgICAgICAgIEJ1ZmZlcnM6IHNoYXJlZCBoaXQ9MjIxNQogICAgICAgICAgICAg
ICAtPiAgSGFzaCAgKGNvc3Q9NS4xMi4uNS4xMiByb3dzPTUwIHdpZHRoPTQpIChhY3R1YWwgdGlt
ZT0wLjA0Ny4uMC4wNDcgcm93cz01MC4wMCBsb29wcz01KQogICAgICAgICAgICAgICAgICAgICBP
dXRwdXQ6IHMuamtleQogICAgICAgICAgICAgICAgICAgICBCdWNrZXRzOiAxMDI0ICBCYXRjaGVz
OiAxICBNZW1vcnkgVXNhZ2U6IDEwa0IKICAgICAgICAgICAgICAgICAgICAgQnVmZmVyczogc2hh
cmVkIGhpdD0xMAogICAgICAgICAgICAgICAgICAgICAtPiAgU2VxIFNjYW4gb24gcHVibGljLnNt
YWxsX3NpZGUgcyAgKGNvc3Q9MC4wMC4uNS4xMiByb3dzPTUwIHdpZHRoPTQpIChhY3R1YWwgdGlt
ZT0wLjAwNS4uMC4wMzggcm93cz01MC4wMCBsb29wcz01KQogICAgICAgICAgICAgICAgICAgICAg
ICAgICBPdXRwdXQ6IHMuamtleQogICAgICAgICAgICAgICAgICAgICAgICAgICBGaWx0ZXI6IChz
LmRydl9pZCA9IGQuaWQpCiAgICAgICAgICAgICAgICAgICAgICAgICAgIFJvd3MgUmVtb3ZlZCBi
eSBGaWx0ZXI6IDIwMAogICAgICAgICAgICAgICAgICAgICAgICAgICBCdWZmZXJzOiBzaGFyZWQg
aGl0PTEwCiBQbGFubmluZzoKICAgQnVmZmVyczogc2hhcmVkIGhpdD03NiByZWFkPTEKIFBsYW5u
aW5nIFRpbWU6IDAuNDI2IG1zCiBFeGVjdXRpb24gVGltZTogMTYxLjU0NCBtcwooMjkgcm93cykK
ClJFU0VUClNFVAogICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg
ICAgICAgICAgICAgICAgICAgICAgICBRVUVSWSBQTEFOICAgICAgICAgICAgICAgICAgICAgICAg
ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgCi0tLS0tLS0tLS0t
LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t
LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t
LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0KIE5lc3RlZCBMb29wICAoY29zdD0xMDc1Mi45OC4uNTM3
NjYuMDMgcm93cz01IHdpZHRoPTEyKSAoYWN0dWFsIHRpbWU9MzkuNDc4Li4xMTIuMzE2IHJvd3M9
NS4wMCBsb29wcz0xKQogICBPdXRwdXQ6IGQuaWQsIChDT0FMRVNDRShzdW0ocy5qa2V5KSwgJzAn
OjpiaWdpbnQpKQogICBCdWZmZXJzOiBzaGFyZWQgaGl0PTQ1NAogICAtPiAgU2VxIFNjYW4gb24g
cHVibGljLmRyaXZlciBkICAoY29zdD0wLjAwLi4xLjA1IHJvd3M9NSB3aWR0aD00KSAoYWN0dWFs
IHRpbWU9MC4wMDMuLjAuMDA0IHJvd3M9NS4wMCBsb29wcz0xKQogICAgICAgICBPdXRwdXQ6IGQu
aWQKICAgICAgICAgQnVmZmVyczogc2hhcmVkIGhpdD0xCiAgIC0+ICBBZ2dyZWdhdGUgIChjb3N0
PTEwNzUyLjk4Li4xMDc1Mi45OSByb3dzPTEgd2lkdGg9OCkgKGFjdHVhbCB0aW1lPTIyLjQ2MS4u
MjIuNDYxIHJvd3M9MS4wMCBsb29wcz01KQogICAgICAgICBPdXRwdXQ6IENPQUxFU0NFKHN1bShz
LmprZXkpLCAnMCc6OmJpZ2ludCkKICAgICAgICAgQnVmZmVyczogc2hhcmVkIGhpdD00NTMKICAg
ICAgICAgLT4gIE1lcmdlIFJpZ2h0IEpvaW4gIChjb3N0PTk3NTQuMzYuLjEwNTAyLjk4IHJvd3M9
MTAwMDAwIHdpZHRoPTQpIChhY3R1YWwgdGltZT00LjI5Ni4uMTcuODIzIHJvd3M9MTA1MDAwLjAw
IGxvb3BzPTUpCiAgICAgICAgICAgICAgIE91dHB1dDogcy5qa2V5CiAgICAgICAgICAgICAgIE1l
cmdlIENvbmQ6IChzLmprZXkgPSBiLmprZXkpCiAgICAgICAgICAgICAgIEJ1ZmZlcnM6IHNoYXJl
ZCBoaXQ9NDUzCiAgICAgICAgICAgICAgIC0+ICBTb3J0ICAoY29zdD02LjU0Li42LjY2IHJvd3M9
NTAgd2lkdGg9NCkgKGFjdHVhbCB0aW1lPTAuMDQwLi4wLjA0MiByb3dzPTUwLjAwIGxvb3BzPTUp
CiAgICAgICAgICAgICAgICAgICAgIE91dHB1dDogcy5qa2V5CiAgICAgICAgICAgICAgICAgICAg
IFNvcnQgS2V5OiBzLmprZXkKICAgICAgICAgICAgICAgICAgICAgU29ydCBNZXRob2Q6IHF1aWNr
c29ydCAgTWVtb3J5OiAyNWtCCiAgICAgICAgICAgICAgICAgICAgIEJ1ZmZlcnM6IHNoYXJlZCBo
aXQ9MTAKICAgICAgICAgICAgICAgICAgICAgLT4gIFNlcSBTY2FuIG9uIHB1YmxpYy5zbWFsbF9z
aWRlIHMgIChjb3N0PTAuMDAuLjUuMTIgcm93cz01MCB3aWR0aD00KSAoYWN0dWFsIHRpbWU9MC4w
MDUuLjAuMDMzIHJvd3M9NTAuMDAgbG9vcHM9NSkKICAgICAgICAgICAgICAgICAgICAgICAgICAg
T3V0cHV0OiBzLmprZXkKICAgICAgICAgICAgICAgICAgICAgICAgICAgRmlsdGVyOiAocy5kcnZf
aWQgPSBkLmlkKQogICAgICAgICAgICAgICAgICAgICAgICAgICBSb3dzIFJlbW92ZWQgYnkgRmls
dGVyOiAyMDAKICAgICAgICAgICAgICAgICAgICAgICAgICAgQnVmZmVyczogc2hhcmVkIGhpdD0x
MAogICAgICAgICAgICAgICAtPiAgU29ydCAgKGNvc3Q9OTc0Ny44Mi4uOTk5Ny44MiByb3dzPTEw
MDAwMCB3aWR0aD00KSAoYWN0dWFsIHRpbWU9NC4yNTQuLjYuOTQ1IHJvd3M9MTA1MDAwLjAwIGxv
b3BzPTUpCiAgICAgICAgICAgICAgICAgICAgIE91dHB1dDogYi5qa2V5CiAgICAgICAgICAgICAg
ICAgICAgIFNvcnQgS2V5OiBiLmprZXkKICAgICAgICAgICAgICAgICAgICAgU29ydCBNZXRob2Q6
IHF1aWNrc29ydCAgTWVtb3J5OiAzMDcza0IKICAgICAgICAgICAgICAgICAgICAgQnVmZmVyczog
c2hhcmVkIGhpdD00NDMKICAgICAgICAgICAgICAgICAgICAgLT4gIFNlcSBTY2FuIG9uIHB1Ymxp
Yy5iaWdfc2lkZSBiICAoY29zdD0wLjAwLi4xNDQzLjAwIHJvd3M9MTAwMDAwIHdpZHRoPTQpIChh
Y3R1YWwgdGltZT0wLjAwNi4uMTIuMjA1IHJvd3M9MTAwMDAwLjAwIGxvb3BzPTEpCiAgICAgICAg
ICAgICAgICAgICAgICAgICAgIE91dHB1dDogYi5qa2V5CiAgICAgICAgICAgICAgICAgICAgICAg
ICAgIEJ1ZmZlcnM6IHNoYXJlZCBoaXQ9NDQzCiBQbGFubmluZzoKICAgQnVmZmVyczogc2hhcmVk
IGhpdD0xMgogUGxhbm5pbmcgVGltZTogMC4yNzkgbXMKIEV4ZWN1dGlvbiBUaW1lOiAxMTIuNDAw
IG1zCigzNSByb3dzKQoqLw==
--0000000000008a959b064b96c85c--