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
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 |