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:-
Regards
Yudhi
