MIME-Version: 1.0
References: <CAG-eXHJ+KbQ8_k-jKSGZU9V6HkLKU2Jqz7nYMYGhHuC-Zqm7qQ@mail.gmail.com>
In-Reply-To: <CAG-eXHJ+KbQ8_k-jKSGZU9V6HkLKU2Jqz7nYMYGhHuC-Zqm7qQ@mail.gmail.com>
From: David Mullineux <dmullx@gmail.com>
Date: Wed, 6 Nov 2024 22:11:00 +0000
Message-ID: <CAGsyd8WqPEgoAkNO0Q7rpQpOWOZ-Z6wCM7xh5d6nXCxLH_GM_A@mail.gmail.com>
Subject: Re: Performance Issue with Hash Partition Query Execution in
 PostgreSQL 16
To: Ramakrishna m <ram.pgdb@gmail.com>
Cc: pgsql-general <pgsql-general@lists.postgresql.org>, ravisql09@gmail.com
Content-Type: multipart/alternative; boundary="0000000000009e6ca7062645cb04"
Archived-At: <https://www.postgresql.org/message-id/CAGsyd8WqPEgoAkNO0Q7rpQpOWOZ-Z6wCM7xh5d6nXCxLH_GM_A%40mail.gmail.com>
Precedence: bulk

--0000000000009e6ca7062645cb04
Content-Type: text/plain; charset="UTF-8"

It might be worth eliminating the use of cached plans here. Is your app
using prepared statements at all?
Point is that if the optimizer sees the same prepared query , 5 times, the
it locks the plan that it found at that time. This is a good trade off as
it avoids costly planning-time for repetitive queries. But if you are
manually querying, the  a custom plan will be generated  anew.
A quick analyze of the table should reset the stats and invalidate any
cached plans.
This may not be your problem  just worth eliminating it from the list of
potential causes.

On Wed, 6 Nov 2024, 17:14 Ramakrishna m, <ram.pgdb@gmail.com> wrote:

> Hi Team,
>
> One of the queries, which retrieves a single record from a table with 16
> hash partitions, is taking more than 10 seconds to execute. In contrast,
> when we run the same query manually, it completes within milliseconds. This
> issue is causing exhaustion of the application pools. Do we have any bugs
> in postgrs16 hash partitions? Please find the attached log, table, and
> execution plan.
>
> size of the each partitions : 300GB
> Index Size : 12GB
>
> Postgres Version : 16.x
> Shared Buffers : 75 GB
> Effective_cache :  175 GB
> Work _mem : 4MB
> Max_connections : 3000
>
> OS  : Ubuntu 22.04
> Ram : 384 GB
> CPU : 64
>
> Please let us know if you need any further information or if there are
> additional details required.
>
>
> Regards,
> Ram.
>

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

<div dir=3D"auto">It might be worth eliminating the use of cached plans her=
e. Is your app using prepared statements at all?=C2=A0=C2=A0<div dir=3D"aut=
o">Point is that if the optimizer sees the same prepared query , 5 times, t=
he=C2=A0 it locks the plan that it found at that time. This is a good trade=
 off as it avoids costly planning-time for repetitive queries. But if you a=
re manually querying, the=C2=A0 a custom plan will be generated=C2=A0 anew.=
</div><div dir=3D"auto">A quick analyze of the table should reset the stats=
 and invalidate any cached plans.</div><div dir=3D"auto">This may not be yo=
ur problem=C2=A0 just worth eliminating it from the list of potential cause=
s.</div></div><br><div class=3D"gmail_quote"><div dir=3D"ltr" class=3D"gmai=
l_attr">On Wed, 6 Nov 2024, 17:14 Ramakrishna m, &lt;<a href=3D"mailto:ram.=
pgdb@gmail.com">ram.pgdb@gmail.com</a>&gt; wrote:<br></div><blockquote clas=
s=3D"gmail_quote" style=3D"margin:0 0 0 .8ex;border-left:1px #ccc solid;pad=
ding-left:1ex"><div dir=3D"ltr"><div>Hi Team,</div><div><div><div><div><div=
><div></div></div></div></div></div><div><div><div><div dir=3D"auto"><div><=
div><p>One of the queries, which retrieves a single record from a table wit=
h 16 hash partitions, is taking more than 10 seconds to execute. In contras=
t, when we run the same query manually, it completes within milliseconds. T=
his issue is causing exhaustion of the application pools.=C2=A0Do we have a=
ny bugs in postgrs16 hash partitions? Please find the attached log, table, =
and execution plan.=C2=A0</p><p><font face=3D"arial, sans-serif">size of th=
e each partitions : 300GB=C2=A0<br>Index Size : 12GB</font></p><p><span sty=
le=3D"font-family:arial,sans-serif">Postgres Version : 16.x</span><font fac=
e=3D"arial, sans-serif"><br></font><span style=3D"font-family:arial,sans-se=
rif">Shared Buffers : 75 GB</span><font face=3D"arial, sans-serif"><br></fo=
nt><span style=3D"font-family:arial,sans-serif">Effective_cache :=C2=A0 175=
 GB</span><font face=3D"arial, sans-serif"><br></font><span style=3D"font-f=
amily:arial,sans-serif">Work _mem : 4MB</span><font face=3D"arial, sans-ser=
if"><br></font><span style=3D"font-family:arial,sans-serif">Max_connections=
 : 3000</span><font face=3D"arial, sans-serif"></font></p><p><span style=3D=
"font-family:arial,sans-serif">OS=C2=A0 :=C2=A0Ubuntu 22.04</span><br style=
=3D"font-family:arial,sans-serif"><span style=3D"font-family:arial,sans-ser=
if">Ram : 384 GB</span><br style=3D"font-family:arial,sans-serif"><span sty=
le=3D"font-family:arial,sans-serif">CPU : 64</span><font face=3D"arial, san=
s-serif"></font></p><p>Please let us know if you need any further informati=
on or if there are additional details required.=C2=A0=C2=A0</p><p><br></p><=
/div></div></div></div></div></div></div><div>Regards,</div><div dir=3D"ltr=
" class=3D"gmail_signature" data-smartmail=3D"gmail_signature"><div dir=3D"=
ltr"><div>Ram.<br></div></div></div></div>
</blockquote></div>

--0000000000009e6ca7062645cb04--