Performance issue after migration from 9.4 to 15

From: Christian Schröder <Christian(dot)Schroeder(at)solvians(dot)com>
To: "pgsql-general(at)lists(dot)postgresql(dot)org" <pgsql-general(at)lists(dot)postgresql(dot)org>
Subject: Performance issue after migration from 9.4 to 15
Date: 2023-04-11 10:58:37
Message-ID: FR2P281MB334131052A52EB875A8A6DAF8A9A9@FR2P281MB3341.DEUP281.PROD.OUTLOOK.COM
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi list,
We have recently migrated from our old PostgreSQL 9.4 server to a PostgreSQL 15 server. Even though the new machine has more resources, we see a considerable decrease in the performance of some of our heavier queries, and I have no idea where I should start tuning. ?

Old database: PostgreSQL 9.4.26 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-17), 64-bit
New database: PostgreSQL 15.0 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit

I tested the same query against the old and the new database.

Old: https://explain.dalibo.com/plan/b7d7gab73f6c7274#plan/node/21
New: https://explain.dalibo.com/plan/84h16cg1f19266be#plan/node/34

Particularly interesting are the sequential scans. In the old plan, we have node #21, which took 32 seconds. Almost all of the time goes into actual I/O. In the new plan, the corresponding node is #34. It took 55 seconds, but less than 1 second was actual I/O (because most of the data was already in memory). Why did this step still take about twice the time?
There is another Seq Scan of the same table. Node #10 in the old plan took 3 seconds, whereas the corresponding node #21 in the new plan took more than 2 minutes (of which less than 2 seconds was actual I/O).

Am I misreading the plans? If not, I have no idea why the sequential scans take so much longer in the new database, even though the I/O is even faster than before.

The configuration was left almost unchanged, with only some adjustments due to changes between the versions. As far as I can tell, none of these changes is performance related.

Can anybody give me a hint into which direction I should investigate further?

Thanks,
Christian

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Christian Schröder 2023-04-11 12:24:59 RE: Performance issue after migration from 9.4 to 15
Previous Message Amit Kapila 2023-04-11 09:02:23 Re: Support logical replication of DDLs