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 1vmzdr-0012TZ-2O for pgsql-general@arkaria.postgresql.org; Mon, 02 Feb 2026 19:31:23 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.96) (envelope-from ) id 1vmzdq-000vRX-2m for pgsql-general@arkaria.postgresql.org; Mon, 02 Feb 2026 19:31:22 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1vmzdq-000vRP-1T for pgsql-general@lists.postgresql.org; Mon, 02 Feb 2026 19:31:22 +0000 Received: from mail-oi1-x22e.google.com ([2607:f8b0:4864:20::22e]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.98.2) (envelope-from ) id 1vmzdn-00000000iMT-3eLW for pgsql-general@lists.postgresql.org; Mon, 02 Feb 2026 19:31:21 +0000 Received: by mail-oi1-x22e.google.com with SMTP id 5614622812f47-45efe81556fso3271954b6e.2 for ; Mon, 02 Feb 2026 11:31:19 -0800 (PST) ARC-Seal: i=1; a=rsa-sha256; t=1770060678; cv=none; d=google.com; s=arc-20240605; b=jKBg/LYVLx5iR6DBYMd/bN5w6hmtov6xMiexdCjmzOsnCKp1RVV5g1QO/ch2Z7nk2l GI8Zkh+z6Q/bsPsSxrHFJLJQla1LNRlnqhDVhiea7Nqdh3THXp9+A5TUA2WWy1ZI9ru5 HJKuC1tT/Hy0WdNcScF2kXtCNkKaIcLjmpmUjNvKwUOfkrXLdmv/hw8WyJ21luqkSWlH 4UnSkvAFN9XJbi/3ExW9SNDT+crgrDub68O3E6SpwH15tdtKruaAgevZ69liMjOIIofy mr774mzdlQEGk9I5n6YIT8fDvzSldWieFljmBhzdZXXwmWoZ/fbgyQaRv7GjSwQGa1lE Htsw== 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=at2XSqGF8JZbxaE9moTraPzUbdn43qNb5IJKMjXCK3s=; fh=2DoGC78b0mi8uZ4UY+9ocPF86GSVzeaJWqou/x907js=; b=k0rqdWzghg1d7ttBrCY+aIE3rWhUw9kuZAjnEmJIdDYrnKp4GGBmNPDNtGrmC77l8/ 4Tp4VN3VZ8HbV0073ZUY8zo/3kcs4KOVDAzEYdo205+mWbe/U8O6HXCO6pd8msmuxwOO YstNO+sKZCz1oFrONd4QF5B2f5VIsVkf91O8tvFW5Q79RpeFgjcfP5wEleUPUt3Y0VZi Mp3/QG4YjhlYsAOXSB5NuC9Cby80CunFvnrLETG+3bUGb6czCiW0QPZRvUfoD1u7WtmA pk9COuT7tQTMK/CTJr847SY/1+uzdjQdkc4YMpIYB7WIg08UUUD7J1xagf4EppsO+Sj5 br8Q==; 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=1770060678; x=1770665478; 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=at2XSqGF8JZbxaE9moTraPzUbdn43qNb5IJKMjXCK3s=; b=c1jWo0oNzuzgAH4B1I2AJnY98rlGRFlsMQr7CddXNcksYAw6w/qHA9K2Gy+u778PJN pG9mzAIkUuq8DtbH8MBL1cAG+9qrv4NnRrfIVYRCwJIP+MRBz24o+lZEu1toiQMKC2Pb xIEFYPaOHt2wBuAlcSTV1tWAOQgvmSnZA40e+Fl+CB422Jd1T7c9uZBsD2DMiXKpB1sO 8oR2pzM0C/WB0ukN4mSkRa4Joaxtr7ZFvlI4mQpClObI+KBeYbuOAMoKkm/p4XrmJblR KSV9qgXukhusRVptCKKNPj1dbD7C0PqcYi2mwX2Gq55yEnU3IUUA5xOqKmotwnkARx8l xgGA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1770060678; x=1770665478; 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=at2XSqGF8JZbxaE9moTraPzUbdn43qNb5IJKMjXCK3s=; b=N9rZ9mSJuQ30ckHDb5AbCvRSSZAv07D2codiDtHg0zzFHgCACVg6YWZphY4b/GbpdZ bZyGS2+ZL9cMWLHB4KZ0FZdCr1A/YiglDgpzkR7NwFDgjHH8cAUN6vqblo8ums7BMfrY aRfhaDyoHYeTgAybpHHJFTDbyrk+jrjmwMJY01/+mcA1rBODcZUJRuSbj5Mxnl8syhQt etChZm6fOdjO4tFTJfvZhyvwE9IzlJW81tY1mlRocBPFm34Tpr8aET6w4oneNYuGFDbg hghhp0PTZ8Be9vjEhs+YqjVQipm08VxNfim4HcQ2urwN0izadBgW/FD/Jibijz9KupZO WaFQ== X-Gm-Message-State: AOJu0YysvHIuN5NZuu5TizgNOjpLZYknvvYPS3MARUJX/sXRYyjY0Wzp Vl0FYbphBpf3AKGmZ7oU73D5H+TWRGKwITHfgiEsiT8FIeqiMpODJByiydWM+GIY33pFvyWL8DH Z3ULhIxpDSxTlJcqispm1qS7oaxHLIDayEg== X-Gm-Gg: AZuq6aLedmp9GB3hFlRCeBhYd9OFMZztBftm+6qUcFRgr/SETx3lV5sLntTu266ZGm7 nIMXk1PPkqtZ8esXP8kC3VSKV1zx5ENOchEkTSWgMbfcKzPhOVuHoPdJQJIw7ZfiEfAlNd7Zgnd h82DasVv0l0ma4IazhkKOf4ukplq8Y7jW1A9khvqPV9fafeAeS+6R2nbA5vWT0trxacGpfqm/J/ 5KaSYCOIsZMMOGHOY12xPTNLL/52EWiA3YDReJunbJGoYu11PilA2cBSep0erOWjVgQOHcU X-Received: by 2002:a05:6808:16a9:b0:45c:a040:e53c with SMTP id 5614622812f47-45f34cd2704mr4785521b6e.32.1770060677679; Mon, 02 Feb 2026 11:31:17 -0800 (PST) MIME-Version: 1.0 References: In-Reply-To: From: Ron Johnson Date: Mon, 2 Feb 2026 14:31:06 -0500 X-Gm-Features: AZwV_QieQbunpLhDWqDPXcXsWS6QBkj-p8_BN2htUJM4RRDX_fjKjdPKUIDOf_s Message-ID: Subject: Re: Top -N Query performance issue and high CPU usage To: "pgsql-generallists.postgresql.org" Content-Type: multipart/alternative; boundary="000000000000ec91610649dc5d33" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000ec91610649dc5d33 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable 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 performing the below >>> nested loop join. So my question was , is there any possibility to redu= ce >>> 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) (ac= tual >>> 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, hop= e > 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. --=20 Death to , and butter sauce. Don't boil me, I'm still alive. lobster! --000000000000ec91610649dc5d33 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
On Mon, Feb 2, 2026 at 1:39=E2=80=AFPM yu= dhi s <learnerdatabase99@= gmail.com> wrote:
On Mon, Feb 2,= 2026 at 8:57=E2=80=AFPM Ron Johnson <ronljohnsonjr@gmail.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 now, in performing the below nest= ed loop join. So my question was , is there any possibility to reduce the r= esource consumption or response time further=C2=A0here?=C2=A0 Hope my under= standing is correct here.

=
-> Nested Loo= p (cost=3D266.53..1548099.38 rows=3D411215 width=3D20) (actual time=3D6= .009..147.695 rows=3D1049 loops=3D1)
Join Filter: ((df.ent_id)::numeric =3D m.e= nt_id)
Rows Removed by Join Filter: 513436
Buffers: shared h= it=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 brutaliz= es the ability to use an index.


Thank you.
Actually i tried executing the first two CT= E where the query was spending most of the time=C2=A0 and teh alias has cha= nged.

We need to see ever= ything, not just what you think is relevant.
=C2=A0
Also here i have changed the real table names before putti= ng it here, hope that is fine.=C2=A0
However , i verified the dat= a 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 w= ithout additional burden. Also, even i tried creating an index on the "= ;(df.ent_id)::numeric" its st= ill reulting 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.

--
Death to <Redacted>, and butter sauce.
Don't boi= l me, I'm still alive.
<Redacted> lobster!
--000000000000ec91610649dc5d33--