public inbox for pgsql-bugs@postgresql.org  

help / color / mirror / Atom feed

From: Andres Freund <andres@anarazel.de>
To: adrian.moennich@cern.ch
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 09:54:52 -0400
Message-ID: <dihw6lynx3p75sv5fbgqjlsu3kfeagcnm4px2r7mgsvf4w2sf5@53udqm4e5wid> (raw)
In-Reply-To: <19449-4fac687c06cc7def@postgresql.org>
References: <19449-4fac687c06cc7def@postgresql.org>

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: andres@anarazel.de, adrian.moennich@cern.ch, 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: <dihw6lynx3p75sv5fbgqjlsu3kfeagcnm4px2r7mgsvf4w2sf5@53udqm4e5wid>

* 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