Message-ID: <6199d929-711e-4657-bcf9-7d285cbafca6@gmail.com>
Date: Fri, 27 Feb 2026 09:15:33 +0100
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
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>
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>
 <4FEE2E28-5A05-4423-B49E-CA23E2B78420@gmx.net>
Content-Language: en-US
From: Andrei Lepikhov <lepihov@gmail.com>
In-Reply-To: <4FEE2E28-5A05-4423-B49E-CA23E2B78420@gmx.net>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: 
 <https://www.postgresql.org/message-id/6199d929-711e-4657-bcf9-7d285cbafca6%40gmail.com>
Precedence: bulk

On 26/2/26 17:22, Attila Soki wrote:
> 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.
No problem. Issues with your query plan starts in exactly the following 
line:

->  Hash Right Join  (cost=210369.25..210370.30 rows=8 width=99)
	(actual time=150.790..150.853 rows=44.56 loops=21798)

Schema of this part of the query tree is as the following:

Hash Right Join  (loops=21798)
   │
   ├─ [Left/Probe] GroupAggregate (loops=14426)
   │    └─ Merge Right Anti Join
   │         └─ Merge Join
   │              └─ Index Only Scan on table_k gkal_2  (loops=14426)
   │
   └─ [Right/Build = Hash] Nested Loop (loops=21798)
        ├─ Index Scan on table_o goftr_1 (loops=21798)
        │    Index Cond: goftr_1.au_id = gauf_1.id
        └─ Index Scan on table_k gkal_1
             Index Cond: gkal_1.oo_id = goftr_1.id

So, the hash table is rebuilt each rescan based on the changed 
'gauf_1.id' external parameter.
Without the query, it is hard to say exactly what the trigger of this 
problem is. Having a reproduction, we could use planner advising 
extensions and see how additional knowledge of true cardinalities 
rebuilds the query plan. Sometimes, additional LATERAL restriction, 
added by the planner to pull-up subplan, restricts the join search scope 
badly, but I doubt if we have this type of problem here.

-- 
regards, Andrei Lepikhov,
pgEdge