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