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 1vnIoz-004rMd-0i for pgsql-general@arkaria.postgresql.org; Tue, 03 Feb 2026 16:00:09 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.96) (envelope-from ) id 1vnIox-005YIY-17 for pgsql-general@arkaria.postgresql.org; Tue, 03 Feb 2026 16:00:07 +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 1vnIow-005YIP-2R for pgsql-general@lists.postgresql.org; Tue, 03 Feb 2026 16:00:06 +0000 Received: from mail-oi1-x22e.google.com ([2607:f8b0:4864:20::22e]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.98.2) (envelope-from ) id 1vnIou-00000000Nfu-1J7n for pgsql-general@lists.postgresql.org; Tue, 03 Feb 2026 16:00:05 +0000 Received: by mail-oi1-x22e.google.com with SMTP id 5614622812f47-45c7c841904so3871312b6e.3 for ; Tue, 03 Feb 2026 08:00:04 -0800 (PST) ARC-Seal: i=1; a=rsa-sha256; t=1770134403; cv=none; d=google.com; s=arc-20240605; b=E8LWe47nwS0idg7pSNd8mQrzlhkb8/Uy4GQMmehbZLnYvEgW+xYTn0HHV8arxf/p1L XTOHXVfuEPWa90b155ARHsoN5jmpFzOTaSXFLYaYLIA6UhF5Mk6ANtFG4R1pfJa/Lpr0 luT4YPcmLd9G1Sxnlm0YZFRx/6ChyjzmIkzkjoGRrF07z/JCBl88Lew8oVHajgACF5lJ /TeR6uRNBHas3WbVzd98jyF3fZTt+dLpwTWJjP2uDvN0hFUU1lnalCWeQOTdzx8xysgF ScDmxN8njNLinzmbU0bDi16GrsYjg1bslLtnCiuJVwo3iSOSd5QIwVFHANAo2UTZcaFC cIpg== ARC-Message-Signature: i=1; a=rsa-sha256; c=relaxed/relaxed; d=google.com; s=arc-20240605; h=to:subject:message-id:date:from:in-reply-to:references:mime-version :dkim-signature; bh=aT+eFYsJmC0+5P04mM5QgwFZqC4vcUAcTSkZ8r0aS6U=; fh=2DoGC78b0mi8uZ4UY+9ocPF86GSVzeaJWqou/x907js=; b=ZXXIFYgaQJU/u5c4x1hh+wM/C069DAPavC0ol1rEv5OgRMKqsMO3uq4FuoGu74kDU3 SLm0FEAlbJx0m1Md01sifzzspX9hkKWX1pZJjefRaW+iP8c/YFzyiClT70QJXuKbEBM6 S9atbwbEjly58KaG8R3GssDLvIOjL1VG7kwhJlRp5iWVotyHZhBPeOHNmjxXQtdx5bF7 zLlATGNsZ7CXqOi0yiM+iHEGQ/7cecQvuOuItkGakCi7FXh4V21AJDbV8quChPhtHM9i IpLhe1t6AJ8mnkWY9FNXP13f9KF3BCzypKtcWXZLuhkfrS6g0ByvlODXnPaNuLLmehOt qBKg==; 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=1770134403; x=1770739203; darn=lists.postgresql.org; h=to:subject:message-id:date:from:in-reply-to:references:mime-version :from:to:cc:subject:date:message-id:reply-to; bh=aT+eFYsJmC0+5P04mM5QgwFZqC4vcUAcTSkZ8r0aS6U=; b=mTLt15GMdspma2eYVz7b+4mNlHBbwoWqXExjYODZEILgHc7jcL7JLrac7rWdlA+jYY oQa6L0EEIfHoiVSgqUh9B8wXB1fyzWa760EZYrH9dzh2wgBeenJzHlkIgYzCXxY0XZqQ qDvvhZuorniEmARJQUlbCQo0eW5G8B6HH21v37WKP7Zwk7HYlT1wkoMwExeO7UdFphWj hcRz3uJKKwkLUIyncGd9N34QwOT2sre2XiQB7vqXqz3CQ0nI7d64laKwxM/OOFqpR/8G TQLG7HuberWqyuDkgYsPQSkQXI8r1yp1r8uOFs+R0b5iPTQ9R0IQkW7XkXtRy5XXmTBN TTHQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1770134403; x=1770739203; h=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=aT+eFYsJmC0+5P04mM5QgwFZqC4vcUAcTSkZ8r0aS6U=; b=BtHG7VLi2sIs7RcL0KnfzkjnF76+b1aJs1FmeEu3IjPnbfW9HNU3znt0kYmqgPdNwn 8bfkymx3NyB/4bO0LrzXfgnRCFeGrlzhv6H7JkUrdjEbQbRgX/unasna0IL0HS5pdrKp FPq5EGnAP5oalqGrS4QhIBeuTPCGiWVR3JVj7viyReRttbyY6CcbGVigNSZZtEJgZCxs TLat19yij2a2830UCa3bl42IPNBBC/qT0rbZLH4XR1cpg4ndbrXyuI9H7kGFnoVeP6BU KxjM0TrEN1N1jl5V3SOU4UnDTUsNOi/9Ku88sQ+M2mcmU2OrfnrLmXOjGdIDN0q4sd55 wuxg== X-Gm-Message-State: AOJu0Yxeriel0Ry4gxK039hmUqbb5s2dJvSXiKK1MXQD++Rz3/WyN6Ma fPZsYSW3AenLEfOvDIV1Qx2ik1A47suPHhPcLuj4J6X9W6nED0ssd6LlScSt4VVaZz50lSgCgtA RxICElTy6Qr7whifO8OLqzXKAiwgiXtPB2ifH X-Gm-Gg: AZuq6aJCeALJSZtvUHunWza+zpB8CKIXmeSkRrhV65yeIv0HCOWH3o1+mzO5xsZtmF+ UlWPOMpLVXSCtC9oTWGZXN9RRrG7HjXRLp8rkYDyA3RjPwX5bblshdABafRErv1ElKEq9oet++O ia72Ah4PZd+0pdJYB6S/xxSNlrAZQXk762InpUVKmqTG415OZpiRn75/M3UXhfaU1q2yTHVewl+ yMyd2VaEZ9vGZzq2dWPfOn0kgpPDGjyfz8XEDAvzeIli+q0prqEr6iBZC4zuhblQ28LVpd+ X-Received: by 2002:a05:6820:150f:b0:662:c63e:986e with SMTP id 006d021491bc7-66a2323ed9fmr36230eaf.58.1770134402715; Tue, 03 Feb 2026 08:00:02 -0800 (PST) MIME-Version: 1.0 References: In-Reply-To: From: Ron Johnson Date: Tue, 3 Feb 2026 10:59:51 -0500 X-Gm-Features: AZwV_Qi8wUYB6Q_SAmuZOVgmsxmEtJSYaefP08WPW5Z09gOgASjgaH9UBmcuAGc Message-ID: Subject: Re: Top -N Query performance issue and high CPU usage To: "pgsql-generallists.postgresql.org" Content-Type: multipart/alternative; boundary="0000000000004754610649ed8891" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000004754610649ed8891 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Tue, Feb 3, 2026 at 4:26=E2=80=AFAM yudhi s wrote: > 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 perform= ing the >>>>>>> below nested loop join. So my question was , is there any possibili= ty to >>>>>>> reduce the resource consumption or response time further here? Hop= e 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 = 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 th= e "(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 hav= e >>> fetched the DDL for the three tables and their selected columns, used i= n >>> the smaller version of the query and its plan , which I recently update= d. >>> >>> https://gist.github.com/databasetech0073/e4290b085f8f974e315fb41bdc47a1= f3 >>> >>> https://gist.github.com/databasetech0073/344df46c328e02b98961fab0cd2214= 92 >>> >> >> 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 th= e > plan now looks as below. The costing function sinow removed. So it must b= e > 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) (= *actual > 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 > Hmm. What does pg_stat_user_tables say about when you last analyzed and vacuumed APP_schema.txn_tbl and APP_schema.ent? Beyond "aggressively keep those two tables analyzed, via reducing autovacuum_analyze_scale_factor to something like 0.05, and adding 'vacuumdb -d mumble -j2 --analyze-only -t APP_schema.txn_tbl -t APP_schema.ent' to crontab", I'm out of ideas. An 85% speed improvement is nothing to sneeze at, though. > 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 d= o > 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.= .20.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 > There is no VARCHAR or CHAR; there is only TEXT. Thus, this is 100% expected and normal. --=20 Death to , and butter sauce. Don't boil me, I'm still alive. lobster! --0000000000004754610649ed8891 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
On Tue, Feb 3, 2026 at 4:26=E2=80=AFAM yu= dhi s <learnerdatabase99@= gmail.com> wrote:
On Tue, Feb 3, 2026 at 4:50=E2=80=AFAM Ron Johnson <ronljohnsonjr@gmail.c= om> wrote:
On Mon, Feb 2, 2= 026 at 3:43=E2=80=AFPM yudhi s <learnerdatabase99@gmail.com> wrote:
=

On Tue, Feb 3, 2026 at 1:01=E2=80=AFAM Ro= n Johnson <= ronljohnsonjr@gmail.com> wrote:
On Mon, Feb 2, 202= 6 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@gmail.com&g= t; wrote:

My apologies if i misunderstand=C2=A0the plan, But If I see,=C2=A0 =C2=A0= it's spending ~140ms(140ms-6ms) i.e. almost all the time now, in perfor= ming the below nested loop join. So my question was , is there any possibil= ity to reduce the resource consumption or response time further=C2=A0here?= =C2=A0 Hope my understanding is correct here.

= <= /tr>
-> Nested Loop (cost=3D266.53..1548099.38 rows=3D411215 w= idth=3D20) (actual time=3D6.009..147.695 rows=3D1049 loops=3D1)
Jo= in Filter: ((df.ent_id)::numeric =3D m.ent_id)
= Rows Removed by Join Filter: 513436
Buffers: shared hit=3D1939

I don'= 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 ability = to use an index.


Than= k you.
Actually i tried executing the first two CTE where the que= ry was spending most of the time=C2=A0 and teh alias has changed.

We need to see everything, not jus= t what you think is relevant.
=C2=A0
Also here i have changed the real table names before putting it here, hop= e that is fine.=C2=A0
However , i verified the data type of the e= nt_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 addition= al burden. Also, even i tried creating an index on the "(df.ent_id)::numeric" its still reulting in= to 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 types equal on bot= h tables for the column ent_id the plan now looks as below. The costing fun= ction 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? Or it's the = best time we can get here.

=C2=A0 -> =C2=A0Nested Loop =C2=A0(cos= t=3D262.77..1342550.91 rows=3D579149 width=3D20) (actual time=3D6.406..1= 07.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 Filter: 514648
= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 Buffers: shared hit=3D1972=

Hmm.=C2=A0 Wha= t does=C2=A0pg_stat_user_tables=C2=A0say about when you last analyzed and vacuumed=C2=A0APP_schema.txn_tbl=C2=A0and=C2=A0APP_schema.ent?

Beyond "agg= ressively keep those two tables analyzed, via reducing autovacuum_analyze_s= cale_factor to something like 0.05, and adding 'vacuumdb -d mumble -j2 = --analyze-only -t=C2=A0APP_schema.txn_tbl=C2=A0-t=C2=A0APP_schema.ent' t= o crontab", I'm out of ideas.=C2=A0 An 85% speed improvement is no= thing to sneeze at, though.
=C2=A0
Also I do see in some othe= r 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?

=C2=A0 =C2=A0 AND txn_t= bl_dcsn.txn_tbl_txn_sts_tx NOT IN ('STATUS_A','STATUS_B')=C2=A0 =C2=A0 WHERE txn_tbl.txn_tbl_type_nm IN ('TYPE1','TYPE= 2','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=3D1419= 195 width=3D20) (actual time=3D0.019..20.377 rows=3D43727 loops=3D1)
F= ilter: ((txn_tbl_type_nm)::text =3D ANY ('{TYPE1,TYPE2,TYPE3}'::= text[]))
Rows Removed by Filter: 17
Buffers: shared hit=3D183= 9

There is no V= ARCHAR or CHAR; there is only TEXT.=C2=A0 Thus, this is 100% expected and n= ormal.

-- =
Deat= h to <Redacted>, and butter sauce.
Don't boil me, I'm sti= ll alive.
<Redacted> lobster!
--0000000000004754610649ed8891--