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 10:00:47 -0500
Message-ID: <CANzqJaBucLq65V9OH_Ruah7S=g+5s-L8yFkjELdAerZszzcOXA@mail.gmail.com> (raw)
In-Reply-To: <CAEzWdqc-2O8mWGdeDhnzKrp7-kwC99sqJ+ArWUS38WuHUKP-UQ@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>
<CANzqJaDf4kKc89e_9YGZ+BorPbViYgPZomo1ssQO9utOHeStCg@mail.gmail.com>
<CAEzWdqc-2O8mWGdeDhnzKrp7-kwC99sqJ+ArWUS38WuHUKP-UQ@mail.gmail.com>
On Mon, Feb 2, 2026 at 8:53 AM yudhi s <[email protected]> wrote:
>
>
> On Mon, Feb 2, 2026 at 7:04 PM Ron Johnson <[email protected]>
> wrote:
>
>> 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.
>>
>>
>>
> 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
>
I don't see m.ent_id in the actual query. Did you only paste a portion of
the query?
Also, casting in a JOIN typically brutalizes the ability to use an index.
--
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: <CANzqJaBucLq65V9OH_Ruah7S=g+5s-L8yFkjELdAerZszzcOXA@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