pgsql-general
❮
Top -N Query performance issue and high CPU usage
- Jump to comment-1yudhi s<learnerdatabase99@gmail.com>Jan 31, 2026, 1:30 PM UTCHello Experts,
We have a "Select" query which is using three to five main transaction
tables (txntbl, txnstatus, txndecision, txnsale, 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- Jump to comment-1Adrian Klaver<adrian.klaver@aklaver.com>Jan 31, 2026, 4:14 PM UTCOn 1/31/26 05:30, yudhi s wrote:
Hello Experts,
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.
1) Without even looking at the plan I'm going to say 2-VCPU and 16GB RAM and is insufficient resources for what you want to do.
2) You will need to provide the schema definitions for the tables involved.
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?
You already have the beginnings of a chart:
1 session 2-3 secs
10-15 sessions 50-60% usage
40-50 sessions 90% usageRegards
Yudhi
--
Adrian Klaver
adrian.klaver@aklaver.com- Jump to comment-1yudhi s<learnerdatabase99@gmail.com>Jan 31, 2026, 7:47 PM UTCThank you.
Can you please explain a bit in detail, how much minimum VCPU and RAM will
1) Without even looking at the plan I'm going to say 2-VCPU and 16GB RAM
and is insufficient resources for what you want to do.
be enough resources to suffice this requirement? and you normally do that
calculation?2) You will need to provide the schema definitions for the tables involved.
help?
Do you mean table DDL or just the index definitions on the tables should
Also i was trying to understand , by just looking into the "explain
analyze" output, is there any way we can tie the specific step in the plan
, which is the major contributor of the cpu resources? Such that we can
then try to fix that part rather than looking throughout the query as its
big query?
And if any suggestion to improve the TOP-N queries where the base table may
have many rows in it.- Jump to comment-1Ron Johnson<ronljohnsonjr@gmail.com>Jan 31, 2026, 9:05 PM UTCOn Sat, Jan 31, 2026 at 2:47 PM yudhi s <learnerdatabase99@gmail.com> wrote:
Thank you.
1) Without even looking at the plan I'm going to say 2-VCPU and 16GB RAM
and is insufficient resources for what you want to do.Can you please explain a bit in detail, how much minimum VCPU and RAM will
be enough resources to suffice this requirement? and you normally do that
calculation?2) You will need to provide the schema definitions for the tables
involved.
Do you mean table DDL or just the index definitions on the tables shouldhelp?
It looks like 71% (748ms of a total 1056ms) of elapsed time is taken by the
Also i was trying to understand , by just looking into the "explain
analyze" output, is there any way we can tie the specific step in the plan
, which is the major contributor of the cpu resources? Such that we can
then try to fix that part rather than looking throughout the query as its
big query?
c1.trandate external sort on line 150.
That, obviously, is what you should work on.
1. You say you increased work_mem. From what, to what?
2. But that it did not reduce execution time. Please post the EXPLAIN from
after increasing work_mem.
3. Did you remember to run SELECT pgreloadconf(); after increasing
work_mem?
4. Is there an index on APPschema.txntbl.tran_date?
And if any suggestion to improve the TOP-N queries where the base table mayhave many rows in it.
--
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!
- Jump to comment-1David Mullineux<dmullx@gmail.com>Jan 31, 2026, 2:41 PM UTCOn Sat, 31 Jan 2026, 13:30 yudhi s, <learnerdatabase99@gmail.com> wrote:
Hello Experts,
Plan says it's using temp files for sorting....I would suggest you increase
We have a "Select" query which is using three to five main transaction
tables (txntbl, txnstatus, txndecision, txnsale, 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
work_mem for this to avoid temp.fike creation...Although not the answer to
all your problems, it would be a good start .- Jump to comment-1yudhi s<learnerdatabase99@gmail.com>Jan 31, 2026, 7:40 PM UTC
Plan says it's using temp files for sorting....I would suggest you
Even setting work_mem to 64MB remove all the "temp read" and showig all
increase work_mem for this to avoid temp.fike creation...Although not the
answer to all your problems, it would be a good start .
memory reads, but still we are seeing similar cpu spike when executing this
query from multiple sessions and also the response time is staying same.