Re: Top -N Query performance issue and high CPU usage

On Mon, Feb 2, 2026 at 7:04 PM Ron Johnson <ronljohnsonjr(at)gmail(dot)com> wrote:

> On Mon, Feb 2, 2026 at 6:39 AM yudhi s <learnerdatabase99(at)gmail(dot)com>
> wrote:
>
>>
>>
>> On Mon, Feb 2, 2026 at 3:17 AM Peter J. Holzer <hjp-pgsql(at)hjp(dot)at> 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.
>
>
>
My apologies if i misunderstand the plan, But If I see, it's spending
~140ms(140ms-6ms) i.e. almost all the time now, in performing the below
nested loop join. So my question was , is there any possibility to reduce
the resource consumption or response time further here? Hope my
understanding is correct here.

-> Nested Loop (cost=266.53..1548099.38 rows=411215 width=20) (actual time=
*6.009..147.695* rows=1049 loops=1)
Join Filter: ((df.ent_id)::numeric = m.ent_id)
Rows Removed by Join Filter: 513436
Buffers: shared hit=1939