public inbox for [email protected]
help / color / mirror / Atom feedFrom: Ron Johnson <[email protected]>
To: yudhi s <[email protected]>
Cc: pgsql-generallists.postgresql.org <[email protected]>
Subject: Re: Top -N Query performance issue and high CPU usage
Date: Mon, 2 Feb 2026 18:19:50 -0500
Message-ID: <CANzqJaDxOsp=QNqa5bK4JXKOt7uHfxNpcTPZGWuVUMpGjNUB4Q@mail.gmail.com> (raw)
In-Reply-To: <CAEzWdqddHgqsZCachpQMgZmRRRuTQ9HxvdP6=Lhr+TYtPL=w-A@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>
<CANzqJaBucLq65V9OH_Ruah7S=g+5s-L8yFkjELdAerZszzcOXA@mail.gmail.com>
<CAEzWdqd3dcJoDDaG8t1nsgrdy7Tw-EvD1zMCXHy_uOgkLFAZdQ@mail.gmail.com>
<CANzqJaDHtROOVSEB_i6Lo+wFki-vNuQ6wDTNGT-hCWRsMBpvZQ@mail.gmail.com>
<CAEzWdqddHgqsZCachpQMgZmRRRuTQ9HxvdP6=Lhr+TYtPL=w-A@mail.gmail.com>
On Mon, Feb 2, 2026 at 3:43 PM yudhi s <[email protected]> wrote:
>
> On Tue, Feb 3, 2026 at 1:01 AM Ron Johnson <[email protected]>
> wrote:
>
>> On Mon, Feb 2, 2026 at 1:39 PM yudhi s <[email protected]>
>> wrote:
>>
>>> On Mon, Feb 2, 2026 at 8:57 PM Ron Johnson <[email protected]>
>>> wrote:
>>>
>>>>
>>>>> 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.
>>>>
>>>>
>>>> Thank you.
>>> Actually i tried executing the first two CTE where the query was
>>> spending most of the time and teh alias has changed.
>>>
>>
>> We need to see everything, not just what you think is relevant.
>>
>>
>>> Also here i have changed the real table names before putting it here,
>>> hope that is fine.
>>> However , i verified the data type of the ent_id column in "ent" its
>>> "int8" and in table "txn_tbl" is "numeric 12", so do you mean to say this
>>> difference in the data type is causing this high response time during the
>>> nested loop join? My understanding was it will be internally castable
>>> without additional burden. Also, even i tried creating an index on the "(df.ent_id)::numeric"
>>> its still reulting into same plan and response time.
>>>
>>
>> If you'd shown the "\d" table definitions like Adrian asked two days ago,
>> we'd know what indexes are on each table, and not have to beg you to
>> dispense dribs and drabs of information.
>>
>>
> I am unable to run "\d" from the dbeaver sql worksheet. However, I have
> fetched the DDL for the three tables and their selected columns, used in
> the smaller version of the query and its plan , which I recently updated.
>
> https://gist.github.com/databasetech0073/e4290b085f8f974e315fb41bdc47a1f3
>
> https://gist.github.com/databasetech0073/344df46c328e02b98961fab0cd221492
>
Lines 30-32 are where most of the time and effort are taken.
I can't be certain, but changing APP_schema.ent.ent_id from NUMERIC to int8
(with a CHECK constraint to, well, constrain it to 12 digits, if really
necessary) is something I'd test.
--
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], [email protected]
Subject: Re: Top -N Query performance issue and high CPU usage
In-Reply-To: <CANzqJaDxOsp=QNqa5bK4JXKOt7uHfxNpcTPZGWuVUMpGjNUB4Q@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