Message-ID: <bbaf15a4-d743-47df-92fe-a1c5e94165ba@vondra.me>
Date: Fri, 3 Apr 2026 00:34:25 +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)
From: Tomas Vondra <tomas@vondra.me>
To: =?UTF-8?Q?Adrian_M=C3=B6nnich?= <adrian.moennich@cern.ch>,
 Andres Freund <andres@anarazel.de>
Cc: pgsql-bugs@lists.postgresql.org, Tomas Vondra <tv@fuzzy.cz>,
 Thomas Munro <thomas.munro@gmail.com>
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>
Content-Language: en-US
In-Reply-To: <e43f543b-fac2-46da-9a4c-951c038ac0bc@vondra.me>
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 7bit
Archived-At: <https://www.postgresql.org/message-id/bbaf15a4-d743-47df-92fe-a1c5e94165ba%40vondra.me>
Precedence: bulk

On 4/2/26 21:00, Tomas Vondra wrote:
> ...
> FWIW I think that's what's happening. If I add an elog(WARNING) into
> ExecParallelHashJoinSetUpBatches, I see this:
> 
>     WARNING:  0x55dbe375a5e8 initializing 16 batches
>     WARNING:  0x7f3868a3a978 initializing 32 batches
>     WARNING:  0x7f3868a3ab80 initializing 4 batches
>     WARNING:  0x55dbe36148c0 initializing 4 batches
>     WARNING:  0x7f3868a3b230 initializing 16 batches
>     WARNING:  0x7f3868a3a978 initializing 64 batches
>     WARNING:  0x55dbe36144b0 initializing 128 batches
>     WARNING:  0x55dbe36144b0 initializing 256 batches
>     WARNING:  0x55dbe36144b0 initializing 512 batches
>     WARNING:  0x55dbe36144b0 initializing 1024 batches
>     WARNING:  0x7f3868a3a978 initializing 2048 batches
>     WARNING:  0x7f3868a3a978 initializing 4096 batches
>     WARNING:  0x55dbe36144b0 initializing 8192 batches
>     WARNING:  0x55dbe36144b0 initializing 16384 batches
>     WARNING:  0x55dbe36144b0 initializing 32768 batches
>     WARNING:  0x7f3868a3a978 initializing 65536 batches
>     WARNING:  0x55dbe36144b0 initializing 131072 batches
>     WARNING:  0x7f3868a3a978 initializing 262144 batches
> 
> so we're ending with 256k batches, for this one join. I'm not sure how
> exactly this maps to the 2M files from pg_stat_database, but it means
> ~0.5M tuplestores and ~10GB virtual memory (at lest per top).
> 
> I don't know what triggers the batch increase, but I still suspect it's
> similar to the explosion we fixed (or mitigated) in PG18, but only for
> serial (non-parallel) joins.
> 

An interesting question is "What changed in PG16?" causing the query to
fail, when it worked OK on earlier versions. I guess the main suspect is
this item from release notes

  Allow parallelization of FULL and internal right OUTER hash joins

So I guess it might be interesting to flip the joins to inner, see if it
still fails like that, and then see if that crashes on PG15 too.

Although the query has only inner and left outer joins, which seems
unrelated to the change. It might be simply a consequence of the planner
picking a different join tree (due to some general optimizer changes).

It might be interesting to try forcing the same join tree (which might
be possible with join_collapse_limit=1) on PG15. Maybe it'll crash the
same way?

Maybe it'd be easier to try reducing the query first, before doing any
of this. Start removing the joins one by one from the "top" (per the
explain), until it stops failing. That might leave a much smaller query.


regards

-- 
Tomas Vondra