Re: Performance issue after migration from 9.4 to 15

On Tue, 11 Apr 2023 at 23:03, Christian Schröder
<Christian(dot)Schroeder(at)solvians(dot)com> wrote:
> 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. ?

Using pg_upgrade? Did you run ANALYZE? If not then you may be
suffering from lack of statistics leading to bad plans.

> Old: https://explain.dalibo.com/plan/b7d7gab73f6c7274#plan/node/21
> New: https://explain.dalibo.com/plan/84h16cg1f19266be#plan/node/34

I'm not well versed in looking at JSON output plans. You might get a
better overall response posting to https://explain.depesz.com/.
Personally, I always look for raw output, and at least with depesz,
that's text-based.

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

Perhaps your 15 server is under more load than 9.4 due to all
concurrent plans being slower from bad statistics? Load averages might
be a good indicator. (I assume the server is busy due to the "Workers
Launched": 0)

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

Looks that way to me too.

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

Probably just run ANALYZE on the database in question.

David