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>
 <vecavrvgzoxkks66nw2gvt3vot5lwbcm7f65iopgjbw72v2lc6@qd5leh3coj7g>
In-Reply-To: <vecavrvgzoxkks66nw2gvt3vot5lwbcm7f65iopgjbw72v2lc6@qd5leh3coj7g>
From: yudhi s <learnerdatabase99@gmail.com>
Date: Mon, 2 Feb 2026 17:09:21 +0530
Message-ID: <CAEzWdqcAbi0GYp_K64oZTeUeN3YN7-eFQ2m2fZDRvmnJx5Lb5w@mail.gmail.com>
Subject: Re: Top -N Query performance issue and high CPU usage
To: pgsql-general@lists.postgresql.org
Content-Type: multipart/alternative; boundary="000000000000d4a5b10649d5c635"
Archived-At: <https://www.postgresql.org/message-id/CAEzWdqcAbi0GYp_K64oZTeUeN3YN7-eFQ2m2fZDRvmnJx5Lb5w%40mail.gmail.com>
Precedence: bulk

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

On Mon, Feb 2, 2026 at 3:17=E2=80=AFAM Peter J. Holzer <hjp-pgsql@hjp.at> w=
rote:

>
> If you do have that many simultaneous accesses to the landing page, and
> you can't speed up the query significantly (I take it you've seen the
> suggestion to check whether there's an index on
> APP_schema.txn_tbl.tran_date), then maybe you don't need to perform it
> for every user? I don't know what the query is supposed to do, but
> unless the "ent_id" is really a user id, it doesn't seem to be specific
> to the user. So maybe you can cache the result for a minute or an hour
> and show the same result to everybody who logs in during that time.
>
>
>

There was no index on column  tran_date  , I created one and it's making
the query finish in  ~200ms, a lot faster than in the past. Below is the
portion of the query and its plan which actually consumes most of the
resource and time post the new index creation.

https://gist.github.com/databasetech0073/344df46c328e02b98961fab0cd221492

1) Now the part  which takes time is the "nested loop" join on the
"ent_id"  column. Can we do anything to make it much better/faster?

2) Also another question I had was,  with this new index the table scan of
txn_tbl is now fully eliminated by the "Index Scan Backward" even i have
other columns from that table projected in the query, so how its getting
all those column values without visiting table but just that index scan
backward operation?

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

<div dir=3D"ltr"><div dir=3D"ltr"><br></div><br><div class=3D"gmail_quote g=
mail_quote_container"><div dir=3D"ltr" class=3D"gmail_attr">On Mon, Feb 2, =
2026 at 3:17=E2=80=AFAM Peter J. Holzer &lt;<a href=3D"mailto:hjp-pgsql@hjp=
.at">hjp-pgsql@hjp.at</a>&gt; wrote:<br></div><blockquote class=3D"gmail_qu=
ote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,20=
4);padding-left:1ex"><br>
If you do have that many simultaneous accesses to the landing page, and<br>
you can&#39;t speed up the query significantly (I take it you&#39;ve seen t=
he<br>
suggestion to check whether there&#39;s an index on<br>
APP_schema.txn_tbl.tran_date), then maybe you don&#39;t need to perform it<=
br>
for every user? I don&#39;t know what the query is supposed to do, but<br>
unless the &quot;ent_id&quot; is really a user id, it doesn&#39;t seem to b=
e specific<br>
to the user. So maybe you can cache the result for a minute or an hour<br>
and show the same result to everybody who logs in during that time.<br>
<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0<br></blockquote><div><br></div><div>There w=
as no index on column=C2=A0

<span style=3D"color:rgb(31,35,40);font-family:&quot;Monaspace Neon&quot;,u=
i-monospace,SFMono-Regular,&quot;SF Mono&quot;,Menlo,Consolas,&quot;Liberat=
ion Mono&quot;,monospace;font-size:12px;white-space:pre">tran_date </span>=
=C2=A0, I created one and it&#39;s making the query finish in=C2=A0 ~200ms,=
 a lot faster than in the past. Below is the portion of the query and its p=
lan which actually consumes most of the resource and time post the new inde=
x creation.</div><div><br></div><div><a href=3D"https://gist.github.com/dat=
abasetech0073/344df46c328e02b98961fab0cd221492">https://gist.github.com/dat=
abasetech0073/344df46c328e02b98961fab0cd221492</a></div><div><br></div><div=
>1) Now the part=C2=A0 which takes time is the &quot;nested loop&quot; join=
 on the &quot;ent_id&quot;=C2=A0 column. Can we do anything to make it much=
 better/faster?</div><div><br></div><div>2) Also another question I had was=
,=C2=A0=C2=A0with this new index the table scan of txn_tbl is now fully eli=
minated by the &quot;Index Scan Backward&quot; even i have other columns fr=
om that table projected in the query, so how its getting all those column v=
alues without visiting table but just that index scan backward operation?</=
div><div><br></div><div><br></div></div></div>

--000000000000d4a5b10649d5c635--