Google Cloud PostgreSQL Upgrade v11 to 15

Hello All,
I hope everything is well with you. I have some issues after upgrading the
GCP.

We performed an upgrade on Google Cloud PostgreSQL from version 11 to 15.
Initially, we executed this in our test environment, and there were no
major issues observed.

However, we unfortunately encountered a performance issue with one
particular query after the upgrade of Production. Below is my detailed
analysis, and I would appreciate your comments on the matter.

When we carried out the upgrade in the Production environment, we faced
several major problems. First and foremost, there was a relentless disk
issue and high CPU consumption. We realized that the root cause of these
two problems was the activation of the log_duration parameter, which was
generating thousands of logs per second, and we resolved it.

We also discovered that the execution plans had changed after the upgrade,
which was expected. Since it was performing a table scan, retrieving a
total of 200 records through paging took minutes. By setting the
random_page_cost parameter to 4, I managed to direct it towards an Index
Scan. As a result, the 200 records were fetched within 3-5 seconds.

At this point, the query is running consistently on both version 11 and
version 15, taking 3-5 seconds to retrieve 200 records. However, while
version 11 processed 20,000 records in 15-20 seconds, version 15 is taking
1.5-2 minutes for the same amount.

The query contains many joins and subqueries. Based on this, I maximized
the values of the work_mem parameter and ran tests, but no improvement was
observed.

In my research on similar cases, I found that some cases reported success
by disabling the jit parameter, but it didn’t help in my case.

I also tried standard analyze and index maintenance, including rebuilding
the indexes. Additionally, I took the problematic database to my local
environment and conducted tests on versions 15 and 16. However, there was
no change in performance.

After analyzing the query, I tried certain indexing methods on the
identified costly parts, but they didn’t help. Additionally, I tested by
disabling the subqueries and using temporary tables, but that also did not
lead to a solution.

I look forward to your comments on this matter.