public inbox for [email protected]
help / color / mirror / Atom feedFrom: Andrei Lepikhov <[email protected]>
To: Attila Soki <[email protected]>
Cc: [email protected]
Cc: Laurenz Albe <[email protected]>
Subject: Re: unstable query plan on pg 16,17,18
Date: Fri, 27 Feb 2026 09:15:33 +0100
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
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>
<[email protected]>
On 26/2/26 17:22, Attila Soki wrote:
> 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.
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
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