Re: Google Cloud PostgreSQL Upgrade v11 to 15

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(at)gmail(dot)com> 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.
>