public inbox for [email protected]
help / color / mirror / Atom feedFrom: David Mullineux <[email protected]>
To: yudhi s <[email protected]>
Cc: pgsql-general <[email protected]>
Subject: Re: Top -N Query performance issue and high CPU usage
Date: Sat, 31 Jan 2026 14:41:18 +0000
Message-ID: <CAGsyd8Wc0Oa-863xWE6rcWk4zR=m3tCzhnGdEjvV+UytXjdvrA@mail.gmail.com> (raw)
In-Reply-To: <CAEzWdqd0SPkZMYNaAbERdgczkfQqLmNV5JBMmF-F9s7KjxJ0gw@mail.gmail.com>
References: <CAEzWdqd0SPkZMYNaAbERdgczkfQqLmNV5JBMmF-F9s7KjxJ0gw@mail.gmail.com>
On Sat, 31 Jan 2026, 13:30 yudhi s, <[email protected]> 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 .
view thread (24+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected]
Subject: Re: Top -N Query performance issue and high CPU usage
In-Reply-To: <CAGsyd8Wc0Oa-863xWE6rcWk4zR=m3tCzhnGdEjvV+UytXjdvrA@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox