Subject: Re: BUG #19449: Massive performance degradation for complex query on Postgres 16+ (few seconds -> multiple hours)

From: Andres Freund <[email-masked]>
Date: Thu, 2 Apr 2026 09:54:52 -0400

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

---

From: Adrian Mönnich <[email-masked]>
Date: Thu, 2 Apr 2026 16:06:27 +0200

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

>                                  ->  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

---

From: Andres Freund <[email-masked]>
Date: Thu, 2 Apr 2026 10:27:12 -0400

Hi,

> https://explain.depesz.com/s/SaVI

That's good.

> our production DB. But I guess there's still a but somewhere that results to the

I don't even know if it's a misestimate that didn't happen in the earlier
versions - the join order is different in 14 than it's in the later ones.  I
don't know why that is at this point.

This means that we don't know if 14 would have had the same misestimation if
the same join order had been chosen.

There also seem to be some data differences:

14: https://explain.depesz.com/s/17Fp#source
  ->  Parallel Seq Scan on contributions contributions_1  (cost=0.00..164891.13 rows=2687413 width=5) (actual time=0.013..454.721 rows=2143186 loops=3)

16: https://explain.depesz.com/s/7Zan
  ->  Parallel Seq Scan on contributions contributions_1  (cost=0.00..37776.28 rows=1643228 width=5) (actual time=0.081..78.499 rows=1314582.00 loops=3)

That's a pretty substantial difference in the number of rows.

Greetings,

Andres Freund