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 .
