MIME-Version: 1.0
From: yudhi s <learnerdatabase99@gmail.com>
Date: Sat, 31 Jan 2026 19:00:03 +0530
Message-ID: <CAEzWdqd0SPkZMYNaAbERdgczkfQqLmNV5JBMmF-F9s7KjxJ0gw@mail.gmail.com>
Subject: Top -N Query performance issue and high CPU usage
To: pgsql-general <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="0000000000001f2c590649af1756"
Archived-At: <https://www.postgresql.org/message-id/CAEzWdqd0SPkZMYNaAbERdgczkfQqLmNV5JBMmF-F9s7KjxJ0gw%40mail.gmail.com>
Precedence: bulk

--0000000000001f2c590649af1756
Content-Type: text/plain; charset="UTF-8"

Hello Experts,
 We have a "Select" query which is using three to five main transaction
tables (txn_tbl, txn_status, txn_decision, txn_sale, ath) holding ~2million
rows in each of them(which is going to increase to have ~50-100million in
future) and others(6-7) tables out of which some are master and some other
small tables.

When we are running this query , and it's taking ~2-3seconds , however when
we hit this query from 10-15 session at same time its causing CPU spike up
to ~50-60% for the DB instance and this is increasing and touching 90% when
we are increasing the hits further to 40-50 times concurrently.

This query is going to be called in the first page of an UI screen and is
supposed to show the latest 1000 rows based on a certain transaction date.
This query is supposed to allow thousands of users to hit this same query
at the first landing page at the same time.  Its postgres version 17.  The
instance has 2-VCPU and 16GB RAM.

I have the following questions.

1)Why is this query causing a high cpu spike ,if there is any way in
postgres to understand what part/line of the query is contributing to the
high cpu time?
2)How can we tune this query to further reduce response time and mainly CPU
consumption ? Is any additional index or anything will make this plan
better further?
3) Is there any guidance or best practices exists , to create/design top
N-queries for such UI scenarios where performance is an important factor?
4)And based on the CPU core and memory , is there any calculation by using
which , we can say that this machine can support a maximum N number of
concurrent queries of such type beyond which we need more cpu cores
machines?

Below is the query and its current plan:-
https://gist.github.com/databasetech0073/6688701431dc4bf4eaab8d345c1dc65f

Regards
Yudhi

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

<div dir=3D"ltr">Hello Experts,<br>=C2=A0We have a &quot;Select&quot; query=
 which is using three to five main transaction tables (txn_tbl, txn_status,=
 txn_decision, txn_sale, ath) holding ~2million rows in each of them(which =
is going to increase to have ~50-100million in future) and others(6-7) tabl=
es out of which some are master and some other small tables. <br><br>When w=
e are running this query , and it&#39;s taking ~2-3seconds , however when w=
e hit this query from 10-15 session at same time its causing CPU spike up t=
o ~50-60% for the DB instance and this is increasing and touching 90% when =
we are increasing the hits further to 40-50 times concurrently. <br><br>Thi=
s query is going to be called in the first page of an UI screen and is supp=
osed to show the latest 1000 rows based on a certain transaction date. This=
 query is supposed to allow thousands of users to hit this same query at th=
e first landing page at the same time.=C2=A0

Its postgres version 17.=C2=A0 The instance has 2-VCPU and 16GB RAM.<br><br=
>I have the following=C2=A0questions.<br><br>1)Why is this query causing a =
high cpu spike ,if there is any way in postgres to understand what part/lin=
e of the query is contributing to the high cpu time?<br>2)How can we tune t=
his query to further reduce response time and mainly CPU consumption ? Is a=
ny additional index or anything will make this plan better further?<br>3) I=
s there any guidance or best practices exists , to create/design top N-quer=
ies for such UI scenarios where performance is an important factor?<br>4)An=
d based on the CPU core and memory , is there any calculation by using whic=
h , we can say that this machine can support a maximum N number of concurre=
nt queries of such type beyond which we need more cpu cores machines?<div><=
br></div><div>Below is the query and its current plan:-</div><div><a href=
=3D"https://gist.github.com/databasetech0073/6688701431dc4bf4eaab8d345c1dc6=
5f">https://gist.github.com/databasetech0073/6688701431dc4bf4eaab8d345c1dc6=
5f</a></div><div><br></div><div>Regards</div><div>Yudhi=C2=A0<br></div></di=
v>

--0000000000001f2c590649af1756--