Content-Type: text/plain; charset="utf-8"
MIME-Version: 1.0
Content-Transfer-Encoding: quoted-printable
Subject: BUG #19449: Massive performance degradation for complex query on Postgres 16+ (few seconds -> multiple hours)
To: pgsql-bugs@lists.postgresql.org
From: PG Bug reporting form <noreply@postgresql.org>
Cc: adrian.moennich@cern.ch
Reply-To: adrian.moennich@cern.ch, pgsql-bugs@lists.postgresql.org
Date: Thu, 02 Apr 2026 13:04:46 +0000
Message-ID: <19449-4fac687c06cc7def@postgresql.org>
Auto-Submitted: auto-generated
Archived-At: <https://www.postgresql.org/message-id/19449-4fac687c06cc7def%40postgresql.org>
Precedence: bulk

The following bug has been logged on the website:

Bug reference:      19449
Logged by:          Adrian
Email address:      adrian.moennich@cern.ch
PostgreSQL version: 18.3
Operating system:   Linux
Description:       =20

In Indico (an open source conference mgmt tool which I maintain and develop)
I noticed that a
certain query to gather statistics became extremely slow on newer Postgres
version on our production
database. And with extremely slow I mean 3 hours instead of a few seconds.

To replicate:

$ podman run -it --rm -p 65432:5432 -e POSTGRES_HOST_AUTH_METHOD=3Dtrust
--shm-size 8G docker.io/postgres:XX-alpine
$ createdb -h 127.0.0.1 -p 65432 -U postgres test
$ psql -h 127.0.0.1 -p 65432 -U postgres test -f data.sql
$ psql -h 127.0.0.1 -p 65432 -U postgres test -f stats.sql

Likely works fine with Docker as well, or with a non-containerized setup.
I just used podman/containers because of the convenience to run different
Postgres versions.

XX=3D14: Works fine, even w/o the increased shm-size of the container
XX=3D15: Works fine but only with the increased shm-size of the container
XX=3D{16,17,17}: Massive CPU and disk usage (tens of gigabytes)

On these simple reproducers I did not keep the query running on 16+.
However, I ran it on a postgres 16.11 instance on our production setup (with
our real database),
and there the query finished only after over 3 hours(!).

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

For the reproducer above I created a dumbed down version of my real data
which basically just has
the relevant columns, FKs and indexes but no actual data. I'm sharing a link
to the data.sql file
since it's 250 MB uncompressed and still 50 MB compressed.

Structure + dummy data: https://fd.aeum.net/pgperf/data.sql.bz2
Problematic query: https://fd.aeum.net/pgperf/stats.sql

For the sake of having the query here and not just in an external file:

```
EXPLAIN ANALYZE SELECT count(attachments.attachments.id) AS count_1
FROM attachments.attachments
JOIN attachments.folders ON attachments.folders.id =3D
attachments.attachments.folder_id
JOIN events.events ON events.events.id =3D attachments.folders.event_id
LEFT OUTER JOIN events.sessions ON events.sessions.id =3D
attachments.folders.session_id
LEFT OUTER JOIN events.contributions ON events.contributions.id =3D
attachments.folders.contribution_id
LEFT OUTER JOIN events.subcontributions ON events.subcontributions.id =3D
attachments.folders.subcontribution_id
LEFT OUTER JOIN events.contributions AS contributions_1 ON
contributions_1.id =3D events.subcontributions.contribution_id
WHERE attachments.folders.link_type !=3D 1
  AND NOT attachments.attachments.is_deleted
  AND NOT attachments.folders.is_deleted
  AND NOT events.events.is_deleted
  AND NOT coalesce(events.sessions.is_deleted,
events.contributions.is_deleted, events.subcontributions.is_deleted, false)
  AND (contributions_1.is_deleted IS NULL
        OR NOT contributions_1.is_deleted)
```