public inbox for [email protected]
help / color / mirror / Atom feedRe: BUG #19449: Massive performance degradation for complex query on Postgres 16+ (few seconds -> multiple hours)
3+ messages / 2 participants
[nested] [flat]
* Re: BUG #19449: Massive performance degradation for complex query on Postgres 16+ (few seconds -> multiple hours)
@ 2026-04-02 13:54 Andres Freund <[email protected]>
0 siblings, 1 reply; 3+ messages in thread
From: Andres Freund @ 2026-04-02 13:54 UTC (permalink / raw)
To: [email protected]; [email protected]
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: BUG #19449: Massive performance degradation for complex query on Postgres 16+ (few seconds -> multiple hours)
@ 2026-04-02 14:06 =?windows-1250?Q?Adrian_M=F6nnich?= <[email protected]>
parent: Andres Freund <[email protected]>
0 siblings, 1 reply; 3+ messages in thread
From: =?windows-1250?Q?Adrian_M=F6nnich?= @ 2026-04-02 14:06 UTC (permalink / raw)
To: Andres Freund <[email protected]>; [email protected]
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: BUG #19449: Massive performance degradation for complex query on Postgres 16+ (few seconds -> multiple hours)
@ 2026-04-02 14:27 Andres Freund <[email protected]>
parent: =?windows-1250?Q?Adrian_M=F6nnich?= <[email protected]>
0 siblings, 0 replies; 3+ messages in thread
From: Andres Freund @ 2026-04-02 14:27 UTC (permalink / raw)
To: Adrian Mönnich <[email protected]>; +Cc: [email protected]; Tomas Vondra <[email protected]>; Thomas Munro <[email protected]>
Hi,
On 2026-04-02 16:06:27 +0200, Adrian Mönnich wrote:
> 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
That's good.
> 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?
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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2026-04-02 14:27 UTC | newest]
Thread overview: 3+ messages (download: mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-04-02 13:54 Re: BUG #19449: Massive performance degradation for complex query on Postgres 16+ (few seconds -> multiple hours) Andres Freund <[email protected]>
2026-04-02 14:06 ` =?windows-1250?Q?Adrian_M=F6nnich?= <[email protected]>
2026-04-02 14:27 ` Andres Freund <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox