public inbox for pgsql-bugs@postgresql.org  

help / color / mirror / Atom feed

From: =?windows-1250?Q?Adrian_M=F6nnich?= <adrian.moennich@cern.ch>
To: Andres Freund <andres@anarazel.de>
To: pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #19449: Massive performance degradation for complex query on Postgres 16+ (few seconds -> multiple hours)
Date: Thu, 2 Apr 2026 16:06:27 +0200
Message-ID: <43225458.20260402160627@cern.ch> (raw)
In-Reply-To: <dihw6lynx3p75sv5fbgqjlsu3kfeagcnm4px2r7mgsvf4w2sf5@53udqm4e5wid>
References: <19449-4fac687c06cc7def@postgresql.org>
	<dihw6lynx3p75sv5fbgqjlsu3kfeagcnm4px2r7mgsvf4w2sf5@53udqm4e5wid>

Hi,

thanks a lot, I just tried with work_mem set to 128MB on PG16 and it worked fine:
https://explain.depesz.com/s/7Zan

Likewise on PG18:
https://explain.depesz.com/s/H15B

And with enable_memoize=0 (PG18, 128MB):
https://explain.depesz.com/s/SaVI

So increasing work_mem seems like a good workaround for when we upgrade
our production DB. But I guess there's still a but somewhere that results to the
wrong estimate?

Cheers,
Adrian

> Hi,

> On 2026-04-02 13:04:46 +0000, PG Bug reporting form wrote:
>> This is extreme both in general and compared to the performance we got on
>> 14/15, where the same
>> query took just a few seconds.
>> 
>> Here are EXPLAIN ANALYZE outputs from when I tested this a few weeks ago on
>> 14 and 16
>> using our real production database.
>> https://explain.depesz.com/s/17Fp
>> https://explain.depesz.com/s/0dHI

> A lot of time is wasted due to batching in the hash join in 16, seemingly due
> to a mis-estimate in how much batching we would need:

>                                  ->  Parallel Hash 
> (cost=323037.00..323037.00 rows=1075136 width=10) (actual
> time=3267572.432..3267575.016 rows=1023098 loops=3)
>                                        Buckets: 262144 (originally 262144) 
> Batches: 262144 (originally 32)  Memory Usage: 18912kB
> (note the 262144 batches, when 32 were originally assumed)

> I'd suggest trying to run the query with a larger work mem.  Not because
> that should be necessary to avoid regressions, but because it will be useful
> to narrow down whether that's related to the issue...

> However, even on 14, you do look to be loosing a fair bit of performance due
> to batching, so it might be also worth running the query on 14 with a larger
> work mem, to see what performance you get there.


> It also looks like that the choice of using memoize might not be working out
> entirely here. Although I don't think it's determinative for performance, it
> might still be worth checking what plan you get with
>   SET enable_memoize = 0;

> Greetings,

> Andres Freund

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: pgsql-bugs@postgresql.org
  Cc: adrian.moennich@cern.ch, andres@anarazel.de, pgsql-bugs@lists.postgresql.org
  Subject: Re: BUG #19449: Massive performance degradation for complex query on Postgres 16+ (few seconds -> multiple hours)
  In-Reply-To: <43225458.20260402160627@cern.ch>

* 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