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.sha...@hotmail.com> Sent: Tuesday, April 11, 2023 1:25 PM To: Christian Schröder <christian.schroe...@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.schroe...@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 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
