public inbox for pgsql-bugs@postgresql.org  

help / color / mirror / Atom feed

From: Andres Freund <andres@anarazel.de>
To: Adrian Mönnich <adrian.moennich@cern.ch>
Cc: pgsql-bugs@lists.postgresql.org
Cc: Tomas Vondra <tv@fuzzy.cz>
Cc: Thomas Munro <thomas.munro@gmail.com>
Subject: Re: BUG #19449: Massive performance degradation for complex query on Postgres 16+ (few seconds -> multiple hours)
Date: Thu, 2 Apr 2026 10:27:12 -0400
Message-ID: <jivwllcuyvd7m4ceydwwpjptmadfe3cfbw47hqnej7yjfkleej@2q33rbrfybm4> (raw)
In-Reply-To: <43225458.20260402160627@cern.ch>
References: <19449-4fac687c06cc7def@postgresql.org>
	<dihw6lynx3p75sv5fbgqjlsu3kfeagcnm4px2r7mgsvf4w2sf5@53udqm4e5wid>
	<43225458.20260402160627@cern.ch>

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

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, tv@fuzzy.cz, thomas.munro@gmail.com
  Subject: Re: BUG #19449: Massive performance degradation for complex query on Postgres 16+ (few seconds -> multiple hours)
  In-Reply-To: <jivwllcuyvd7m4ceydwwpjptmadfe3cfbw47hqnej7yjfkleej@2q33rbrfybm4>

* 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