Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1vnCg5-003iBc-1l for pgsql-general@arkaria.postgresql.org; Tue, 03 Feb 2026 09:26:33 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.96) (envelope-from ) id 1vnCg3-0049Vk-0m for pgsql-general@arkaria.postgresql.org; Tue, 03 Feb 2026 09:26:31 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1vnCg2-0049Vc-26 for pgsql-general@lists.postgresql.org; Tue, 03 Feb 2026 09:26:30 +0000 Received: from mail-ed1-x52e.google.com ([2a00:1450:4864:20::52e]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.98.2) (envelope-from ) id 1vnCg0-00000000KPx-0hfk for pgsql-general@lists.postgresql.org; Tue, 03 Feb 2026 09:26:29 +0000 Received: by mail-ed1-x52e.google.com with SMTP id 4fb4d7f45d1cf-658ad86082dso9400000a12.0 for ; Tue, 03 Feb 2026 01:26:27 -0800 (PST) ARC-Seal: i=1; a=rsa-sha256; t=1770110785; cv=none; d=google.com; s=arc-20240605; b=j95GfUQSFsbetS3zpWMwvN2j45BrW/42pisQNH3wIzfxX/ZCJkw4s1KPve5fIz32Br Wq6OXdEwoB7mMNqju3Ux0YMCRD1ywE6Q1UUsDCExOhuTtBjgIc6R3GQWs8Zlu8Mg5jwT NbteJ+b+MoXPqV3LJK+bYaZibDwyFYsqNh07BJq3yojPgrOlBE39SayAuop2F08CeoiN WvMXuGMkfCQgr4i3UHPRw+rLvKMZbaXFvLhVMzeU3PhMexDmG+OFR7Ecu9W6jiMGWt5m ksjrCpTKnV9Q68TEMNprz4d+dR3QZpwx7IaiJd2fBS0v/VF8H0MRUXP2HleCmktob+Zc uq0w== ARC-Message-Signature: i=1; a=rsa-sha256; c=relaxed/relaxed; d=google.com; s=arc-20240605; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:dkim-signature; bh=gjCxWoVCoQMxTPuLFHofGds1aOH8bxs/xARtzIyg76g=; fh=M5d6ZpcaQVHTXkkOH6MmskvUc9TU9yzFINFTBAvu35o=; b=XNwurPz3ViXdDidKKY3TdXU423oR8IH0JTixbi/TXXudwChBiGZYpJoyJzQpFXYH6r 1dbgRRYxMpzhVD3o6PHkPlYOf9aEc7O94N7/LkY5CSli0Oxt0+IVTTSZODVfFg2CVgSv Ws/ShePX5YNSsbNn8WD7ZwdoPCBTH4Efmm/rPBJhPCBvfiw8rAePIqG5OCbiLEszFU+v y/eRNkqsUx5IBBBiUiwhlNN8uTbokf/G4cOuufKAK0g5tHEkIUIVR38Etienx1ZztryF k5wvIz1W3mpOj5tnxQobmwe2gNsCXKV6VOVUhiV7/KLvCw9vavef83J5Jn4A19mSFPz2 Ygyw==; darn=lists.postgresql.org ARC-Authentication-Results: i=1; mx.google.com; arc=none DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1770110785; x=1770715585; darn=lists.postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=gjCxWoVCoQMxTPuLFHofGds1aOH8bxs/xARtzIyg76g=; b=BPaP15Z5zTpeecKIRRnBUGPik+LeaF3xg92xEZoMl9jQuH3eATldFivmBbMBuHodFq tQZUaozhau8fDZnyxI83zXAAgrd0bTlnjMTnwsBj/A6mtLdk7Z8W8IiVGcxd5Z5ZSmEI z3rtmVe1DdG4fH+RQOEZ2n430wjFT9/AqMN/YWWf8b5A8ZOMn1P2kvhD4WGyEzg7RpzN pBGf9RlGI0PkhOeLWpKTT9oXioNhxxLjF6tvF6wPWAOqmYaeG+EZJNJyCRGeN7Z428vA rKpnfvVfRT0dIZoBQtlIpAMgFiLB4b8cvK1iQFxgkNKnziB9esU3cgtiNIbxvM3nsSNz Pvvg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1770110785; x=1770715585; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-gg:x-gm-message-state:from:to:cc:subject:date :message-id:reply-to; bh=gjCxWoVCoQMxTPuLFHofGds1aOH8bxs/xARtzIyg76g=; b=g4sVHYBg+c9eZAvkJ+lKXNeI/BWRwFWuyKcHGjvujEl2roOP+T8Ya3Zr9s+HpOlJfn WV5Nd+mIrU7TAt395WN45B5yZLe5FI+F33P7LCcKw9aJSW4soZWukck1H9hWzC3SCJu9 RXHd8fgyKFpzvczjC6rf81xIcAfFgk0oVY2cAFcC9enMqPdBs546RVK3L4nYqJ4e4nOf 0YzXQ11Zbavi2ywp8X729cTD0iPHZ3O/lBKyOujk6IEraUc0f2NE1y5ekl2Hm44yogA6 Y5vmCIVNwSV76hL+49sCCW0XBYjHRePCNWUsJPZT76xy9i8eoRHHsq8A+7FX8rgWV04I LHkQ== X-Gm-Message-State: AOJu0Yx9lX8HmOct1HuUQM2TS+xTHrBIkIPhqJPx3aJOIk8HbBU2QnW4 KVVW8paYN+6BJWYy0G8nudbjNv6o9IzBTUTXuqLsgzer9Xv1kIhUGFBUIt/s5PwCDtZqobD2JXj CMiVIxpF0ABKRPYLuNMDvuPv03DNtvp8= X-Gm-Gg: AZuq6aKJ5WTO1KRws0p7RgXhbJGMAxD0BQSP74mKEia6jtHvH495t8fNAdpbJivNimK PKSkkF1WLNZTt8XQOKirrPOW93eLyuStFjgkFae/94qaIVla6RBUWHRUrAO+S89LhgXo2rP//oZ cSnA2mNcfYerYciBtwqvj7gZh2CD2t1mG8U2dzrrM+/HUdFoMCtIXrLlgun/L5RiBbKVCijnjc8 fuddNOaARxDTe7EsCbFipaipZf1mt18Hjh5sEDrvevwDMB9y3lng31CbR6jRzL1Zzf09mAV4uF3 pOVCBHOjSmQkY/BnyAXq0OP25U/H X-Received: by 2002:a05:6402:438d:b0:659:31af:b989 with SMTP id 4fb4d7f45d1cf-65931afd6damr1578646a12.34.1770110784855; Tue, 03 Feb 2026 01:26:24 -0800 (PST) MIME-Version: 1.0 References: In-Reply-To: From: yudhi s Date: Tue, 3 Feb 2026 14:56:14 +0530 X-Gm-Features: AZwV_Qj_3RcWGvQ69aOvsAB9BTHkM_oPlMx2Lg0WFnQdf3DOwRKb2XSwd-XEJzk Message-ID: Subject: Re: Top -N Query performance issue and high CPU usage To: Ron Johnson Cc: "pgsql-generallists.postgresql.org" Content-Type: multipart/alternative; boundary="0000000000008b60bb0649e808e1" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000008b60bb0649e808e1 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Tue, Feb 3, 2026 at 4:50=E2=80=AFAM Ron Johnson wrote: > On Mon, Feb 2, 2026 at 3:43=E2=80=AFPM yudhi s > wrote: > >> >> On Tue, Feb 3, 2026 at 1:01=E2=80=AFAM Ron Johnson >> wrote: >> >>> On Mon, Feb 2, 2026 at 1:39=E2=80=AFPM yudhi s >>> wrote: >>> >>>> On Mon, Feb 2, 2026 at 8:57=E2=80=AFPM Ron Johnson >>>> 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 performi= ng the >>>>>> below nested loop join. So my question was , is there any possibilit= y to >>>>>> reduce the resource consumption or response time further here? Hope= my >>>>>> understanding is correct here. >>>>>> >>>>>> -> Nested Loop (cost=3D266.53..1548099.38 rows=3D411215 width=3D20) = (actual >>>>>> time=3D*6.009..147.695* rows=3D1049 loops=3D1) >>>>>> Join Filter: ((df.ent_id)::numeric =3D m.ent_id) >>>>>> Rows Removed by Join Filter: 513436 >>>>>> Buffers: shared hit=3D1939 >>>>>> >>>>> >>>>> 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 t= his >>>> 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/e4290b085f8f974e315fb41bdc47a1f= 3 >> >> https://gist.github.com/databasetech0073/344df46c328e02b98961fab0cd22149= 2 >> > > 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. > > -- > Thank you so much. After making the data types equal on both tables for the column ent_id the plan now looks as below. The costing function sinow removed. So it must be helping reduce CPU cycle consumption to some extent, But, I still see ~100ms is spent in this step. Is there anything we can do to further drop the response time here? Or it's the best time we can get here. -> Nested Loop (cost=3D262.77..1342550.91 rows=3D579149 width=3D20) (*a= ctual time=3D6.406..107.946* rows=3D1049 loops=3D1) Join Filter: (*df.ent_id =3D m.ent_id*) Rows Removed by Join Filter: 514648 Buffers: shared hit=3D1972 Also I do see in some other steps in the plan , the casting function is getting used. For example in the below filter. Here txn_tbl_type_nm is defined as Varchar(25) and still it's trying to cast it to Text. Can we do anything to avoid these force casts as these must consume the CPU cycles? AND txn_tbl_dcsn.txn_tbl_txn_sts_tx NOT IN ('STATUS_A','STATUS_B') WHERE txn_tbl.txn_tbl_type_nm IN ('TYPE1','TYPE2','TYPE3') -> Index Scan Backward using txn_tbl_due_dt_idx on txn_tbl df (cost=3D0.43..115879.87 rows=3D1419195 width=3D20) (actual time=3D0.019..2= 0.377 rows=3D43727 loops=3D1) Filter: *((txn_tbl_type_nm)::text =3D ANY ('{TYPE1,TYPE2,TYPE3}'::text[])*) Rows Removed by Filter: 17 Buffers: shared hit=3D1839 The plan is as below. https://gist.github.com/databasetech0073/558377c1939a9291e7b72b1cbac7c9f9 Regards Yudhi --0000000000008b60bb0649e808e1 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable


On Tue, Feb 3, = 2026 at 4:50=E2=80=AFAM Ron Johnson <ronljohnsonjr@gmail.com> wrote:
On Mon, = Feb 2, 2026 at 3:43=E2=80=AFPM yudhi s <learnerdatabase99@gmail.com> wrote:=

On Tue, Feb 3, 2026 at 1:01=E2=80= =AFAM Ron Johnson <ronljohnsonjr@gmail.com> wrote:
On Mon, = Feb 2, 2026 at 1:39=E2=80=AFPM yudhi s <learnerdatabase99@gmail.com> wrote:=
On Mon, Feb 2, 2026 at 8:57=E2=80=AFPM Ron Johnson <ronljohnsonjr@gmai= l.com> wrote:
My apologies if i misunderstand=C2=A0the plan, But If I see,= =C2=A0 =C2=A0it's spending ~140ms(140ms-6ms) i.e. almost all the time n= ow, in performing the below nested loop join. So my question was , is there= any possibility to reduce the resource consumption or response time furthe= r=C2=A0here?=C2=A0 Hope my understanding is correct here.

<= tr style=3D"box-sizing:border-box">
= -> Nested Loop (cost=3D266.53..1548099.38 rows=3D411215 width=3D20= ) (actual time=3D6.009..147.695 rows=3D1049 loops=3D1)
Join Filter: ((df.ent_id)::numer= ic =3D m.ent_id)
Rows Removed by Join Filter: 513436
Buffers: shared hit=3D1939

I don&#= 39;t see=C2=A0m.ent_id=C2= =A0in the actual query.=C2=A0 Did you only paste a portion of the query?

Also, casting in a JOIN typically brutalizes the abi= lity to use an index.


Thank you.
Actually i tried executing the first two CTE where th= e query was spending most of the time=C2=A0 and teh alias has changed.

We need to see everything, no= t just what you think is relevant.
=C2=A0
Also here i have changed the real table names before putting it her= e, hope that is fine.=C2=A0
However , i verified the data type of= the ent_id column in "ent" its "int8" and in table &qu= ot;txn_tbl" is "numeric 12", so do you mean to say this diff= erence in the data type is causing this high response time during the neste= d loop join? My understanding was it will be internally castable without ad= ditional burden. Also, even i tried creating an index on the "(df.ent_id)::numeric" its still reult= ing into same plan and response time.=C2=A0

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 dr= ibs and drabs of information.

<= br>
I am unable to run "\d" from the dbeaver sql worksh= eet. However,=C2=A0 I have fetched the DDL for the three tables and their s= elected columns, used in the smaller version of the query and its plan , wh= ich I recently=C2=A0updated.=C2=A0



Lines 30-32 are where most= of the time and effort are taken.

I can't be = certain, but changing=C2=A0APP_schema.en= t.ent_id=C2=A0from NUMERIC= to int8 (with a CHECK constraint to, well, constrain it to 12 digits, if r= eally necessary) is something I'd test.

--


Thank=C2=A0you so much.

After making the data typ= es equal on both tables for the column ent_id the plan now looks as below. = The costing function sinow removed. So it must be helping reduce CPU cycle = consumption to some extent, But, =C2=A0I still see ~100ms is spent in this = step. Is there anything we can do to further drop the response time here? O= r it's the best time we can get here.

=C2=A0 -> =C2=A0Nested = Loop =C2=A0(cost=3D262.77..1342550.91 rows=3D579149 width=3D20) (actual = time=3D6.406..107.946 rows=3D1049 loops=3D1)
=C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 Join Filter: (df.ent_id =3D m.ent_id)=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 Rows Removed by Join Filt= er: 514648
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 Buffers: sha= red hit=3D1972


Also I do see in some other steps in the plan , t= he casting function is getting used. For example in the below filter. Here = txn_tbl_type_nm is defined as Varchar(25) and still it's trying to cast= it to Text. Can we do anything to avoid these force casts as these must co= nsume the CPU cycles?

=C2=A0 =C2=A0 AND txn_tbl_dcsn.txn_tbl_txn_sts= _tx NOT IN ('STATUS_A','STATUS_B')
=C2=A0 =C2=A0 WHERE t= xn_tbl.txn_tbl_type_nm IN ('TYPE1','TYPE2','TYPE3')=

=C2=A0 -> =C2=A0Index Scan Backward using txn_tbl_due_dt_idx on = txn_tbl df =C2=A0(cost=3D0.43..115879.87 rows=3D1419195 width=3D20) (actual= time=3D0.019..20.377 rows=3D43727 loops=3D1)
Filter: ((txn_tbl_typ= e_nm)::text =3D ANY ('{TYPE1,TYPE2,TYPE3}'::text[]))
Rows = Removed by Filter: 17
Buffers: shared hit=3D1839

The plan is as= below.

https://gist.github.com/databasetech0073/558377c1= 939a9291e7b72b1cbac7c9f9

Regards
Yudhi=C2=A0
--0000000000008b60bb0649e808e1--