RE: Performance issue after migration from 9.4 to 15

Hi,
I will definitely give it a try; however, I do not understand how this could explain the observed performance changes. As far as I understand, an outdated/non-optimized index would not be relevant in a sequential scan, and outdated statistics could cause a non-optimal plan to be chosen, but they should not affect the performance of a single sequential scan.

Best,
Christian

From: Inzamam Shafiq <inzamam(dot)shafiq(at)hotmail(dot)com>
Sent: Tuesday, April 11, 2023 1:25 PM
To: Christian Schröder <Christian(dot)Schroeder(at)solvians(dot)com>
Subject: Re: Performance issue after migration from 9.4 to 15

Hi,

How did you upgrade the databse? if it is done by pg_upgrade then I think you have to do the following,

Try re-indexing the database
Update/analyze statistics

Regards,

Inzamam Shafiq
Sr. DBA
________________________________________
From: Christian Schröder <mailto:Christian(dot)Schroeder(at)solvians(dot)com>
Sent: Tuesday, April 11, 2023 3:58 PM
To: mailto:pgsql-general(at)lists(dot)postgresql(dot)org <mailto:pgsql-general(at)lists(dot)postgresql(dot)org>
Subject: Performance issue after migration from 9.4 to 15

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