MIME-Version: 1.0
References: <CAEzWdqd0SPkZMYNaAbERdgczkfQqLmNV5JBMmF-F9s7KjxJ0gw@mail.gmail.com>
In-Reply-To: <CAEzWdqd0SPkZMYNaAbERdgczkfQqLmNV5JBMmF-F9s7KjxJ0gw@mail.gmail.com>
From: David Mullineux <dmullx@gmail.com>
Date: Sat, 31 Jan 2026 14:41:18 +0000
Message-ID: <CAGsyd8Wc0Oa-863xWE6rcWk4zR=m3tCzhnGdEjvV+UytXjdvrA@mail.gmail.com>
Subject: Re: Top -N Query performance issue and high CPU usage
To: yudhi s <learnerdatabase99@gmail.com>
Cc: pgsql-general <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000ce67790649b01552"
Archived-At: <https://www.postgresql.org/message-id/CAGsyd8Wc0Oa-863xWE6rcWk4zR%3Dm3tCzhnGdEjvV%2BUytXjdvrA%40mail.gmail.com>
Precedence: bulk

--000000000000ce67790649b01552
Content-Type: text/plain; charset="UTF-8"

On Sat, 31 Jan 2026, 13:30 yudhi s, <learnerdatabase99@gmail.com> wrote:

> 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
>

Plan says it's using temp files for sorting....I would suggest you increase
work_mem for this to avoid temp.fike creation...Although not the answer to
all your problems, it would be a good start .

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

<div dir=3D"auto"><div><br><br><div class=3D"gmail_quote gmail_quote_contai=
ner"><div dir=3D"ltr" class=3D"gmail_attr">On Sat, 31 Jan 2026, 13:30 yudhi=
 s, &lt;<a href=3D"mailto:learnerdatabase99@gmail.com">learnerdatabase99@gm=
ail.com</a>&gt; wrote:<br></div><blockquote class=3D"gmail_quote" style=3D"=
margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div dir=3D"=
ltr">Hello Experts,<br>=C2=A0We have a &quot;Select&quot; query which is us=
ing three to five main transaction tables (txn_tbl, txn_status, txn_decisio=
n, 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 wh=
ich some are master and some other small tables. <br><br>When we are runnin=
g this query , and it&#39;s taking ~2-3seconds , however when we hit this q=
uery from 10-15 session at same time its causing CPU spike up to ~50-60% fo=
r the DB instance and this is increasing and touching 90% when we are incre=
asing the hits further to 40-50 times concurrently. <br><br>This query is g=
oing 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 su=
pposed to allow thousands of users to hit this same query at the first land=
ing 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" target=3D"_blank" rel=3D"noreferrer">https://gist.github.com/databasete=
ch0073/6688701431dc4bf4eaab8d345c1dc65f</a></div><div><br></div><div>Regard=
s</div><div>Yudhi=C2=A0<br></div></div></blockquote></div></div><div dir=3D=
"auto"><br></div><div dir=3D"auto">Plan says it&#39;s using temp files for =
sorting....I would suggest you increase work_mem for this to avoid temp.fik=
e creation...Although not the answer to all your problems, it would be a go=
od start .</div><div dir=3D"auto"><br></div><div dir=3D"auto"><div class=3D=
"gmail_quote gmail_quote_container"><blockquote class=3D"gmail_quote" style=
=3D"margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div dir=
=3D"ltr"><div></div></div>
</blockquote></div></div></div>

--000000000000ce67790649b01552--