MIME-Version: 1.0
From: SOzcn <selahattinozcnma@gmail.com>
Date: Thu, 10 Oct 2024 13:06:40 +0300
Message-ID: 
 <CAJyV5AZ=PdsWJtNfFy6tZ+WuvLd76muH2qfKLpMOW+qGDp0Ckw@mail.gmail.com>
Subject: Google Cloud PostgreSQL Upgrade v11 to 15
To: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000916a1a06241c872d"
Archived-At: 
 <https://www.postgresql.org/message-id/CAJyV5AZ%3DPdsWJtNfFy6tZ%2BWuvLd76muH2qfKLpMOW%2BqGDp0Ckw%40mail.gmail.com>
Precedence: bulk

--000000000000916a1a06241c872d
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

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=E2=80=99t 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=E2=80=99t help. Additionally, I test=
ed 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.

--000000000000916a1a06241c872d
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div>Hello All,<br>I hope everything is well with you. I h=
ave some issues after upgrading the GCP.<br><br>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.<br><br>Ho=
wever, 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.<br><br>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 consump=
tion. We realized that the root cause of these two problems was the activat=
ion of the log_duration parameter, which was generating thousands of logs p=
er second, and we resolved it.<br><br>We also discovered that the execution=
 plans had changed after the upgrade, which was expected. Since it was perf=
orming 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 direc=
t it towards an Index Scan. As a result, the 200 records were fetched withi=
n 3-5 seconds.<br><br>At this point, the query is running consistently on b=
oth 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, versio=
n 15 is taking 1.5-2 minutes for the same amount.<br><br>The query contains=
 many joins and subqueries. Based on this, I maximized the values of the wo=
rk_mem parameter and ran tests, but no improvement was observed.<br><br>In =
my research on similar cases, I found that some cases reported success by d=
isabling the jit parameter, but it didn=E2=80=99t help in my case.<br><br>I=
 also tried standard analyze and index maintenance, including rebuilding th=
e indexes. Additionally, I took the problematic database to my local enviro=
nment and conducted tests on versions 15 and 16. However, there was no chan=
ge in performance.<br><br>After analyzing the query, I tried certain indexi=
ng methods on the identified costly parts, but they didn=E2=80=99t help. Ad=
ditionally, I tested by disabling the subqueries and using temporary tables=
, but that also did not lead to a solution.<br><br>I look forward to your c=
omments on this matter.<br></div></div>

--000000000000916a1a06241c872d--