MIME-Version: 1.0
References: <CAEzWdqd0SPkZMYNaAbERdgczkfQqLmNV5JBMmF-F9s7KjxJ0gw@mail.gmail.com>
 <fde67103-e00e-460e-b2ec-eb539809e998@aklaver.com> <CAEzWdqd6LAHs+FiFeJLqDTS-QBLq6+foE1-mgBC9AXVpFmVnZg@mail.gmail.com>
 <CANzqJaD5gfNufOXe0LMuxhN_goHRSTt2MJo3oQNUbaiO=cph_w@mail.gmail.com>
In-Reply-To: <CANzqJaD5gfNufOXe0LMuxhN_goHRSTt2MJo3oQNUbaiO=cph_w@mail.gmail.com>
From: Luigi Nardi <luigi@dbtune.com>
Date: Sun, 1 Feb 2026 13:54:54 +0100
Message-ID: <CADpjEK8TVVUC70N8RZomcC5wni2RgnFh2JvT+_ag4b6idYLpWg@mail.gmail.com>
Subject: Re: Top -N Query performance issue and high CPU usage
To: pgsql-general <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="00000000000015f4520649c2b821"
Archived-At: <https://www.postgresql.org/message-id/CADpjEK8TVVUC70N8RZomcC5wni2RgnFh2JvT%2B_ag4b6idYLpWg%40mail.gmail.com>
Precedence: bulk

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

On Sat, Jan 31, 2026 at 10:05=E2=80=AFPM Ron Johnson <ronljohnsonjr@gmail.c=
om>
wrote:

> On Sat, Jan 31, 2026 at 2:47=E2=80=AFPM yudhi s <learnerdatabase99@gmail.=
com>
> wrote:
>
>> Thank you.
>>
>>>
>>> 1) Without even looking at the plan I'm going to say 2-VCPU and 16GB RA=
M
>>> and is insufficient resources for what you want to do.
>>>
>>>
>> Can you please explain a bit in detail, how much minimum VCPU and RAM
>> will be enough resources to suffice this requirement? and you normally d=
o
>> that calculation?
>>
>>
>>> 2) You will need to provide the schema definitions for the tables
>>> involved.
>>>
>>> Do you mean table DDL or just the index definitions on the tables shoul=
d
>> help?
>>
>> Also i was trying to understand , by just looking into the "explain
>> analyze" output, is there any way we can tie the specific step in the pl=
an
>> , which is the major contributor of the cpu resources? Such that we can
>> then try to fix that part rather than looking throughout the query as it=
s
>> big query?
>>
>
> It looks like 71% (748ms of a total 1056ms) of elapsed time is taken by
> the c_1.tran_date  external sort on line 150.
>
> That, obviously, is what you should work on.
>
> 1. You say you increased work_mem.  From what, to what?
> 2. But that it did not reduce execution time.  Please post the EXPLAIN
> from after increasing work_mem.
> 3. Did you remember to run SELECT pg_reload_conf(); after increasing
> work_mem?
> 4. Is there an index on APP_schema.txn_tbl.tran_date?
>
> And if any suggestion to improve the TOP-N queries where the base table
>> may have many rows in it.
>>
>
>
The DBtune Free Edition <http://app.dbtune.com> can help you find the
correct adjustments for work_mem and other server parameters
<https://dbtune.com/blog/dbtunes-multi-dimensional-performance-tuning-space=
>.
It's designed to help optimize your PostgreSQL runtime for your current
hardware setup.


> --
> Death to <Redacted>, and butter sauce.
> Don't boil me, I'm still alive.
> <Redacted> lobster!
>

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

<div dir=3D"ltr"><div dir=3D"ltr"><div dir=3D"ltr"><br></div><br><div class=
=3D"gmail_quote"><div dir=3D"ltr" class=3D"gmail_attr">On Sat, Jan 31, 2026=
 at 10:05=E2=80=AFPM Ron Johnson &lt;<a href=3D"mailto:ronljohnsonjr@gmail.=
com" target=3D"_blank">ronljohnsonjr@gmail.com</a>&gt; wrote:<br></div><blo=
ckquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left=
:1px solid rgb(204,204,204);padding-left:1ex"><div dir=3D"ltr"><div dir=3D"=
ltr">On Sat, Jan 31, 2026 at 2:47=E2=80=AFPM yudhi s &lt;<a href=3D"mailto:=
learnerdatabase99@gmail.com" target=3D"_blank">learnerdatabase99@gmail.com<=
/a>&gt; wrote:</div><div class=3D"gmail_quote"><blockquote class=3D"gmail_q=
uote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,2=
04);padding-left:1ex"><div dir=3D"ltr"><div>Thank you.=C2=A0</div><div clas=
s=3D"gmail_quote"><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px=
 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><br>
1) Without even looking at the plan I&#39;m going to say 2-VCPU and 16GB RA=
M <br>
and is insufficient resources for what you want to do.<br>
<br></blockquote><div><br></div><div>Can you please explain a bit in detail=
, how much minimum VCPU and RAM will be enough resources to suffice this re=
quirement? and you normally do that calculation?</div><div>=C2=A0</div><blo=
ckquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left=
:1px solid rgb(204,204,204);padding-left:1ex">
2) You will need to provide the schema definitions for the tables involved.=
<br><br></blockquote><div>Do you mean table DDL or just the index definitio=
ns=C2=A0on the tables should help?</div><div><br></div><div>Also i was tryi=
ng to understand , by just looking into the &quot;explain analyze&quot; out=
put, is there any way we can tie the specific step in the plan , which is t=
he major contributor of the cpu resources? Such that we can then try to fix=
 that part rather than looking throughout the query as its big query?=C2=A0=
=C2=A0</div></div></div></blockquote><div><br></div><div>It looks like 71% =
(748ms of a total 1056ms) of elapsed time is taken by the=C2=A0<span style=
=3D"color:rgb(31,35,40);font-family:ui-monospace,SFMono-Regular,&quot;SF Mo=
no&quot;,Menlo,Consolas,&quot;Liberation Mono&quot;,monospace;font-size:12p=
x;white-space:pre-wrap">c_1.tran_date</span>=C2=A0 external sort on line 15=
0.</div><div><br></div><div>That, obviously, is what you should work on.</d=
iv><div><br></div><div>1. You say you increased work_mem.=C2=A0 From what, =
to what?</div><div>2. But that it did not reduce execution time.=C2=A0 Plea=
se post the EXPLAIN from after increasing work_mem.</div><div>3. Did you re=
member to run SELECT pg_reload_conf(); after increasing work_mem?</div><div=
>4. Is there an index on=C2=A0<span style=3D"color:rgb(31,35,40);font-famil=
y:ui-monospace,SFMono-Regular,&quot;SF Mono&quot;,Menlo,Consolas,&quot;Libe=
ration Mono&quot;,monospace;font-size:12px;white-space:pre-wrap">APP_schema=
.txn_tbl.</span><span style=3D"color:rgb(31,35,40);font-family:ui-monospace=
,SFMono-Regular,&quot;SF Mono&quot;,Menlo,Consolas,&quot;Liberation Mono&qu=
ot;,monospace;font-size:12px;white-space:pre-wrap">tran_date</span>?</div><=
div><br></div><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px=
 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir=3D=
"ltr"><div class=3D"gmail_quote"><div>And if any suggestion to improve the =
TOP-N queries where the base table may have many rows in it.</div></div></d=
iv>
</blockquote></div><div><br></div><span class=3D"gmail_signature_prefix"></=
span></div></blockquote><div><br></div><div><span style=3D"color:rgb(31,31,=
31);font-family:&quot;Google Sans&quot;,Roboto,Arial,sans-serif;font-varian=
t-ligatures:no-contextual">The=C2=A0</span><a rel=3D"noopener noreferrer" h=
ref=3D"http://app.dbtune.com" style=3D"color:rgb(11,87,208);font-family:&qu=
ot;Google Sans&quot;,Roboto,Arial,sans-serif;font-variant-ligatures:no-cont=
extual" target=3D"_blank"><span>DBtune Free Edition</span></a><span style=
=3D"color:rgb(31,31,31);font-family:&quot;Google Sans&quot;,Roboto,Arial,sa=
ns-serif;font-variant-ligatures:no-contextual">=C2=A0can help you find the =
correct adjustments for work_mem and=C2=A0</span><a rel=3D"noopener norefer=
rer" href=3D"https://dbtune.com/blog/dbtunes-multi-dimensional-performance-=
tuning-space" style=3D"color:rgb(11,87,208);font-family:&quot;Google Sans&q=
uot;,Roboto,Arial,sans-serif;font-variant-ligatures:no-contextual" target=
=3D"_blank"><span>other server parameters</span></a><span style=3D"color:rg=
b(31,31,31);font-family:&quot;Google Sans&quot;,Roboto,Arial,sans-serif;fon=
t-variant-ligatures:no-contextual">. It&#39;s designed to help optimize you=
r PostgreSQL runtime for your current hardware setup.</span><br><div><br></=
div></div><div>=C2=A0</div><blockquote class=3D"gmail_quote" style=3D"margi=
n:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex=
"><div dir=3D"ltr"><span class=3D"gmail_signature_prefix">-- </span><br><di=
v dir=3D"ltr" class=3D"gmail_signature"><div dir=3D"ltr">Death to &lt;Redac=
ted&gt;, and butter sauce.<div>Don&#39;t boil me, I&#39;m still alive.<br><=
div><div>&lt;Redacted&gt; lobster!</div></div></div></div></div></div>
</blockquote></div></div>
</div>

--00000000000015f4520649c2b821--