Re: Top -N Query performance issue and high CPU usage

On Sat, 31 Jan 2026, 13:30 yudhi s, <learnerdatabase99(at)gmail(dot)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 .