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

public inbox for [email protected]  
help / color / mirror / Atom feed

From: yudhi s <[email protected]>
To: Ron Johnson <[email protected]>
Cc: pgsql-generallists.postgresql.org <[email protected]>
Subject: Re: Top -N Query performance issue and high CPU usage
Date: Tue, 3 Feb 2026 02:13:01 +0530
Message-ID: <CAEzWdqddHgqsZCachpQMgZmRRRuTQ9HxvdP6=Lhr+TYtPL=w-A@mail.gmail.com> (raw)
In-Reply-To: <CANzqJaDHtROOVSEB_i6Lo+wFki-vNuQ6wDTNGT-hCWRsMBpvZQ@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>

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

Regards
Yudhi

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: <CAEzWdqddHgqsZCachpQMgZmRRRuTQ9HxvdP6=Lhr+TYtPL=w-A@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