Hello ,
Maybe you should try pg_hint_plan to control the execution plan for your problematic queries. You should check for execution plans for queries before the upgrade and try to generate similar plans to these .
On Thu, Oct 10, 2024 at 1:07 PM SOzcn <selahattinozcnma@xxxxxxxxx> wrote:
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.
