MIME-Version: 1.0
References: 
 <CAJyV5AZ=PdsWJtNfFy6tZ+WuvLd76muH2qfKLpMOW+qGDp0Ckw@mail.gmail.com>
In-Reply-To: 
 <CAJyV5AZ=PdsWJtNfFy6tZ+WuvLd76muH2qfKLpMOW+qGDp0Ckw@mail.gmail.com>
From: Miroslav Mladenov Ivanov <miroslavmladenovivanov@gmail.com>
Date: Thu, 10 Oct 2024 14:58:47 +0300
Message-ID: 
 <CAH7wMTNKV7Eo=Htzg_hPXYrFvZyRXujo0u=6Z9n1kJJksoFfjw@mail.gmail.com>
Subject: Re: Google Cloud PostgreSQL Upgrade v11 to 15
To: SOzcn <selahattinozcnma@gmail.com>
Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="0000000000007e15d206241e180e"
Archived-At: 
 <https://www.postgresql.org/message-id/CAH7wMTNKV7Eo%3DHtzg_hPXYrFvZyRXujo0u%3D6Z9n1kJJksoFfjw%40mail.gmail.com>
Precedence: bulk

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

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=E2=80=AFPM SOzcn <selahattinozcnma@gmail.com> =
wrote:

> Hello All,
> I hope everything is well with you. I have some issues after upgrading th=
e
> 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 takin=
g
> 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 wa=
s
> 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 te=
sted by
> disabling the subqueries and using temporary tables, but that also did no=
t
> lead to a solution.
>
> I look forward to your comments on this matter.
>

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

<div dir=3D"ltr">Hello ,<div><br></div><div>Maybe you should try pg_hint_pl=
an to control the execution plan for your problematic queries. You should c=
heck for execution plans for queries before the upgrade and try to generate=
 similar plans to these .</div></div><br><div class=3D"gmail_quote"><div di=
r=3D"ltr" class=3D"gmail_attr">On Thu, Oct 10, 2024 at 1:07=E2=80=AFPM SOzc=
n &lt;<a href=3D"mailto:selahattinozcnma@gmail.com">selahattinozcnma@gmail.=
com</a>&gt; wrote:<br></div><blockquote class=3D"gmail_quote" style=3D"marg=
in:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1e=
x"><div dir=3D"ltr"><div>Hello All,<br>I hope everything is well with you. =
I have some issues after upgrading the GCP.<br><br>We performed an upgrade =
on Google Cloud PostgreSQL from version 11 to 15. Initially, we executed th=
is in our test environment, and there were no major issues observed.<br><br=
>However, we unfortunately encountered a performance issue with one particu=
lar query after the upgrade of Production. Below is my detailed analysis, a=
nd I would appreciate your comments on the matter.<br><br>When we carried o=
ut the upgrade in the Production environment, we faced several major proble=
ms. First and foremost, there was a relentless disk issue and high CPU cons=
umption. We realized that the root cause of these two problems was the acti=
vation of the log_duration parameter, which was generating thousands of log=
s per second, and we resolved it.<br><br>We also discovered that the execut=
ion plans had changed after the upgrade, which was expected. Since it was p=
erforming a table scan, retrieving a total of 200 records through paging to=
ok minutes. By setting the random_page_cost parameter to 4, I managed to di=
rect it towards an Index Scan. As a result, the 200 records were fetched wi=
thin 3-5 seconds.<br><br>At this point, the query is running consistently o=
n both version 11 and version 15, taking 3-5 seconds to retrieve 200 record=
s. However, while version 11 processed 20,000 records in 15-20 seconds, ver=
sion 15 is taking 1.5-2 minutes for the same amount.<br><br>The query conta=
ins many joins and subqueries. Based on this, I maximized the values of the=
 work_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 b=
y disabling the jit parameter, but it didn=E2=80=99t help in my case.<br><b=
r>I also tried standard analyze and index maintenance, including rebuilding=
 the indexes. Additionally, I took the problematic database to my local env=
ironment and conducted tests on versions 15 and 16. However, there was no c=
hange in performance.<br><br>After analyzing the query, I tried certain ind=
exing methods on the identified costly parts, but they didn=E2=80=99t help.=
 Additionally, I tested by disabling the subqueries and using temporary tab=
les, but that also did not lead to a solution.<br><br>I look forward to you=
r comments on this matter.<br></div></div>
</blockquote></div>

--0000000000007e15d206241e180e--