public inbox for [email protected]
help / color / mirror / Atom feedFrom: Ron Johnson <[email protected]>
To: pgsql-generallists.postgresql.org <[email protected]>
Subject: Re: Top -N Query performance issue and high CPU usage
Date: Mon, 2 Feb 2026 08:34:31 -0500
Message-ID: <CANzqJaDf4kKc89e_9YGZ+BorPbViYgPZomo1ssQO9utOHeStCg@mail.gmail.com> (raw)
In-Reply-To: <CAEzWdqcAbi0GYp_K64oZTeUeN3YN7-eFQ2m2fZDRvmnJx5Lb5w@mail.gmail.com>
References: <CAEzWdqd0SPkZMYNaAbERdgczkfQqLmNV5JBMmF-F9s7KjxJ0gw@mail.gmail.com>
<[email protected]>
<CAEzWdqd6LAHs+FiFeJLqDTS-QBLq6+foE1-mgBC9AXVpFmVnZg@mail.gmail.com>
<vecavrvgzoxkks66nw2gvt3vot5lwbcm7f65iopgjbw72v2lc6@qd5leh3coj7g>
<CAEzWdqcAbi0GYp_K64oZTeUeN3YN7-eFQ2m2fZDRvmnJx5Lb5w@mail.gmail.com>
On Mon, Feb 2, 2026 at 6:39 AM yudhi s <[email protected]> wrote:
>
>
> On Mon, Feb 2, 2026 at 3:17 AM Peter J. Holzer <[email protected]> wrote:
>
>>
>> If you do have that many simultaneous accesses to the landing page, and
>> you can't speed up the query significantly (I take it you've seen the
>> suggestion to check whether there's an index on
>> APP_schema.txn_tbl.tran_date), then maybe you don't need to perform it
>> for every user? I don't know what the query is supposed to do, but
>> unless the "ent_id" is really a user id, it doesn't seem to be specific
>> to the user. So maybe you can cache the result for a minute or an hour
>> and show the same result to everybody who logs in during that time.
>>
>>
>>
>
> There was no index on column tran_date , I created one and it's making
> the query finish in ~200ms, a lot faster than in the past. Below is the
> portion of the query and its plan which actually consumes most of the
> resource and time post the new index creation.
>
> https://gist.github.com/databasetech0073/344df46c328e02b98961fab0cd221492
>
> 1) Now the part which takes time is the "nested loop" join on the
> "ent_id" column. Can we do anything to make it much better/faster?
>
> 2) Also another question I had was, with this new index the table scan of
> txn_tbl is now fully eliminated by the "Index Scan Backward" even i have
> other columns from that table projected in the query, so how its getting
> all those column values without visiting table but just that index scan
> backward operation?
>
Reading through EXPLAIN output isn't always a mystery.
Search for "actual time" and you'll find row 53, which is the "deepest"
(most nested) row with the highest actual time.
That tells you where the time is now spent, and what it's doing.
--
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!
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]
Subject: Re: Top -N Query performance issue and high CPU usage
In-Reply-To: <CANzqJaDf4kKc89e_9YGZ+BorPbViYgPZomo1ssQO9utOHeStCg@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