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.
Asfar as I understand, an outdated/non-optimized index would not be relevant in a sequential scan, and outdated
statisticscould cause a non-optimal plan to be chosen, but they should not affect the performance of a single
sequentialscan. 

Best,
Christian


From: Inzamam Shafiq <inzamam.shafiq@hotmail.com>
Sent: Tuesday, April 11, 2023 1:25 PM
To: Christian Schröder <Christian.Schroeder@solvians.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.Schroeder@solvians.com>
Sent: Tuesday, April 11, 2023 3:58 PM
To: mailto:pgsql-general@lists.postgresql.org <mailto:pgsql-general@lists.postgresql.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
moreresources, we see a considerable decrease in the performance of some of our heavier queries, and I have no idea
whereI 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
ofthe time goes into actual I/O. In the new plan, the corresponding node is #34. It took 55 seconds, but less than 1
secondwas 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
#21in 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,
eventhough 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
Ican tell, none of these changes is performance related. 

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

Thanks,
Christian