Message-ID: <298af5cb-5547-4c95-b988-7be0617b17b5@vondra.me>
Date: Thu, 16 Apr 2026 20:52:53 +0200
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: BUG #19449: Massive performance degradation for complex query on
 Postgres 16+ (few seconds -> multiple hours)
To: Thomas Munro <thomas.munro@gmail.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>, =?UTF-8?Q?Adrian_M=C3=B6nnich?=
 <adrian.moennich@cern.ch>, Andres Freund <andres@anarazel.de>,
 pgsql-bugs@lists.postgresql.org, Tomas Vondra <tv@fuzzy.cz>
References: <19449-4fac687c06cc7def@postgresql.org>
 <dihw6lynx3p75sv5fbgqjlsu3kfeagcnm4px2r7mgsvf4w2sf5@53udqm4e5wid>
 <43225458.20260402160627@cern.ch>
 <jivwllcuyvd7m4ceydwwpjptmadfe3cfbw47hqnej7yjfkleej@2q33rbrfybm4>
 <94712944.20260402164957@cern.ch>
 <2747373b-d188-43b1-8e49-66f9e23e3c24@vondra.me>
 <e43f543b-fac2-46da-9a4c-951c038ac0bc@vondra.me>
 <bbaf15a4-d743-47df-92fe-a1c5e94165ba@vondra.me>
 <3675338.1775169816@sss.pgh.pa.us>
 <9f6b7a6d-62db-4a63-9fb7-5deee702a24f@vondra.me>
 <CA+hUKGL=tHLCbVHMEno2AQentOYL0jTS=iLE0BEgdWPaUDASiA@mail.gmail.com>
Content-Language: en-US
From: Tomas Vondra <tomas@vondra.me>
In-Reply-To: <CA+hUKGL=tHLCbVHMEno2AQentOYL0jTS=iLE0BEgdWPaUDASiA@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit
Archived-At: <https://www.postgresql.org/message-id/298af5cb-5547-4c95-b988-7be0617b17b5%40vondra.me>
Precedence: bulk


On 4/16/26 07:25, Thomas Munro wrote:
> On Sun, Apr 5, 2026 at 2:45 AM Tomas Vondra <tomas@vondra.me> wrote:
>> At this point I was suspecting the data distributions for the join
>> columns may be somewhat weird, causing issues for the hashjoin batching.
>> For events.contributions.id it's perfectly fine - it's entirely unique,
>> with each ID having 1 entry. Unsurprisingly, because it's the PK. But
>> for attachments.folders.contribution_id I see this:
>>
>> SELECT contribution_id, count(*) FROM attachments.folders
>>  GROUP BY contribution_id ORDER BY 2 DESC;
>>
>>  contribution_id | count
>> -----------------+--------
>>                  | 464515
>>          5492978 |     67
>>          4117499 |     42
>>          4045045 |     41
>>              ...
>>
>> So there's ~500k entries with NULL, that can't possibly match to
>> anything (right)? I assume we still add them to the hash, though.
> 
> That's also the conditions required to prevent the
> "stop-partitioning-it's-not-working" logic from triggering.  That
> thing where we know we need to pick a better lower than 100%.  But
> what?
> 
> Did this commit help?
> 
> commit 1811f1af98fb237fdd5adb588cd4b57c433b75f8
> Author: Tom Lane <tgl@sss.pgh.pa.us>
> Date:   Thu Mar 19 15:21:36 2026 -0400
> 
>     Improve hash join's handling of tuples with null join keys.

Possibly. With the original (simplified) query, I get no failures with
current master. And it starts failing after I revert 1811f1af98.

With the alternative queries (with IS NOT NULL), it seems to work OK
even after the revert. So maybe the queries are not failing for the same
reason?

regards

-- 
Tomas Vondra