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>
In-Reply-To: <CAEzWdqd6LAHs+FiFeJLqDTS-QBLq6+foE1-mgBC9AXVpFmVnZg@mail.gmail.com>
From: Ron Johnson <ronljohnsonjr@gmail.com>
Date: Sat, 31 Jan 2026 16:05:17 -0500
Message-ID: <CANzqJaD5gfNufOXe0LMuxhN_goHRSTt2MJo3oQNUbaiO=cph_w@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="0000000000000e85430649b57327"
Archived-At: <https://www.postgresql.org/message-id/CANzqJaD5gfNufOXe0LMuxhN_goHRSTt2MJo3oQNUbaiO%3Dcph_w%40mail.gmail.com>
Precedence: bulk

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

On Sat, Jan 31, 2026 at 2:47=E2=80=AFPM yudhi s <learnerdatabase99@gmail.co=
m> wrote:

> Thank you.
>
>>
>> 1) Without even looking at the plan I'm going to say 2-VCPU and 16GB RAM
>> and is insufficient resources for what you want to do.
>>
>>
> Can you please explain a bit in detail, how much minimum VCPU and RAM wil=
l
> be enough resources to suffice this requirement? and you normally do 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 should
> 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 pla=
n
> , 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 its
> 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.
>

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

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

<div dir=3D"ltr"><div dir=3D"ltr">On Sat, Jan 31, 2026 at 2:47=E2=80=AFPM y=
udhi s &lt;<a href=3D"mailto:learnerdatabase99@gmail.com">learnerdatabase99=
@gmail.com</a>&gt; wrote:</div><div class=3D"gmail_quote gmail_quote_contai=
ner"><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;bo=
rder-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir=3D"ltr"><di=
v>Thank you.=C2=A0</div><div class=3D"gmail_quote"><blockquote class=3D"gma=
il_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,2=
04,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">c_1.tran_date</span>=C2=A0 external sort on line 150.</d=
iv><div><br></div><div>That, obviously, is what you should work on.</div><d=
iv><br></div><div>1. You say you increased work_mem.=C2=A0 From what, to wh=
at?</div><div>2. But that it did not reduce execution time.=C2=A0 Please po=
st the EXPLAIN from after increasing work_mem.</div><div>3. Did you remembe=
r to run SELECT pg_reload_conf(); after increasing work_mem?</div><div>4. I=
s there an index on=C2=A0<span style=3D"color:rgb(31,35,40);font-family:ui-=
monospace,SFMono-Regular,&quot;SF Mono&quot;,Menlo,Consolas,&quot;Liberatio=
n Mono&quot;,monospace;font-size:12px;white-space:pre">APP_schema.txn_tbl.<=
/span><span style=3D"color:rgb(31,35,40);font-family:ui-monospace,SFMono-Re=
gular,&quot;SF Mono&quot;,Menlo,Consolas,&quot;Liberation Mono&quot;,monosp=
ace;font-size:12px;white-space:pre">tran_date</span>?</div><div><br></div><=
blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-l=
eft:1px solid rgb(204,204,204);padding-left:1ex"><div dir=3D"ltr"><div clas=
s=3D"gmail_quote"><div>And if any suggestion to improve the TOP-N queries w=
here the base table may have many rows in it.</div></div></div>
</blockquote></div><div><br></div><span class=3D"gmail_signature_prefix">--=
 </span><br><div dir=3D"ltr" class=3D"gmail_signature"><div dir=3D"ltr">Dea=
th to &lt;Redacted&gt;, and butter sauce.<div>Don&#39;t boil me, I&#39;m st=
ill alive.<br><div><div>&lt;Redacted&gt; lobster!</div></div></div></div></=
div></div>

--0000000000000e85430649b57327--