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 1vmyqr-000tEu-34 for pgsql-general@arkaria.postgresql.org; Mon, 02 Feb 2026 18:40:46 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.96) (envelope-from ) id 1vmypp-000qFC-1X for pgsql-general@arkaria.postgresql.org; Mon, 02 Feb 2026 18:39:41 +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 1vmypo-000qF3-30 for pgsql-general@lists.postgresql.org; Mon, 02 Feb 2026 18:39:40 +0000 Received: from mail-ed1-x534.google.com ([2a00:1450:4864:20::534]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.98.2) (envelope-from ) id 1vmypm-00000000DvH-3e0Z for pgsql-general@lists.postgresql.org; Mon, 02 Feb 2026 18:39:39 +0000 Received: by mail-ed1-x534.google.com with SMTP id 4fb4d7f45d1cf-658d3d3ac37so8211413a12.1 for ; Mon, 02 Feb 2026 10:39:38 -0800 (PST) ARC-Seal: i=1; a=rsa-sha256; t=1770057576; cv=none; d=google.com; s=arc-20240605; b=Gkidwwl3dwlM3P6kJJCJUsGq52QQWixpJizz1UE5gRQXK2VNiy/lrAnb1NbhKnGIkg Wo+PJzyfQ5Q1t+gR4ZFcFs5/JC9PuzoyCjKdBvRx5GZTmEHw3L4px4P8/ABAr60pwRwG Z2MVC8tEgfu4XXmvm2LTIR3iLCP/MlAFRo6qohBIFW+nFPz/GiXmY6S6jbCOytJFFq1v xbWjLG9aJoX0QMEKQfREg6nIsL5YR1QiZKDZ9xZrjpQEn2TAE/ddXQRBZMKc7f4kl4Yd Gy1K6/w39Tb8xqfwjWCduUAnP85sLrN0x42YkhoXXoQDbWJ6iJgJ6CK2605Xq20R0cME StMg== 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=lWzWXr5NMhCeCRxLuk8E6DVdGeHx8+2W5NvsJNYGJM0=; fh=M5d6ZpcaQVHTXkkOH6MmskvUc9TU9yzFINFTBAvu35o=; b=IDQvZQy8AKhDcTUCAXE+G4SpkOWp/mRs7nZY3rir6Plk+nljCunk40aLNOMQvFeQpw OQh9ILhcVSQgASaJchNgo0cuifoa3hWzfjpW51sCwrTix6ai84Mbm4I0c8GrqdzpIWYV 2scecJeseksfDc1ydfWukP10t6EaclP3Kn2XQj8L1JtKOiS/LlwmNt7NadUGQSLYD0mz C4kx18rpHMaUoyOSClyJECCcwl9Fi9UU48B6KVPovYffj03ISqzHEgw9liZco/zAsVBF BfU8OZjnn+z1K1g3NrTsJ3kExs/ZNtwajuOii87kOUun8Y8arRg598cxOIgi/0g7Xh7B nsZg==; 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=1770057576; x=1770662376; 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=lWzWXr5NMhCeCRxLuk8E6DVdGeHx8+2W5NvsJNYGJM0=; b=CTmddo3Wu1P76hLmXaoax28CI0gOMX78waJKTBHcoxv78odzOlfK9SJnlacynyIgsK X7qJ1YCPvC2XYNas8b6cR05M4TJkKoxp6JCGSQyZCQwLRz1Mgpy65ccGtp/sukr5OW/s IyBfMhoDWkqd0/FhAzpvQcqWMpvEy2RKwPEt9rbb+swR7k4uREXymqonoRBjizKkzHEO QP+du9+ygMTr0HMVCdGSZfoJVFvEDJ2Vrq4jQotOdBNPgqWOpEj4LWUPMcDd0PmBjKAB hLr9+Iz+ptgiNqvTQh+p0Lu9hXqWMFBalWdkRPxo0wY+6p+UK9VPczlcVlbtkTO76pUp n8+A== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1770057576; x=1770662376; 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=lWzWXr5NMhCeCRxLuk8E6DVdGeHx8+2W5NvsJNYGJM0=; b=evYhZhaLVXG387GolZyxB8JyP3OQMXCFRRaG2DU0fCKYhJVvUjcRiJ/Yg+L8mQ8gXb 2kYoSXhXii0W7gy0AKHesc55G4gG4HoGE4SJsyp8KjmlCMnW8HIO6kHAutzBZkNECuLt PFfkGft/zWI9rhy7NqIqquEn0nhGLqP0zD2qASmJ2DbO8FkGLOOig92Ux6CdxPVVXupw KznTJVPL23lWbUHTjTkLsfkxWa10/E9XPkwwBxFnSDYNhCsNqrl3IXYf1ApnhAkVFU+/ LnPyOYiQs/pPHxwl5/yHQm1ktfSq/84CBrxGTGuK15ysSEwl4uTmoz+8j5dJEb0Ki6ar Wffw== X-Gm-Message-State: AOJu0YwxzhPboSx02GmqecJpJqeS5Wf+QWIe4BHLGkbyvAjrN1gAnJRC YYNWufu5MNzljs3aSs//e3ILCSNWVy1f4scu4SCnE/pTQbsgApDbaHbIQqVHH2wr13DZLTdepdM O2JWZqjgzUZ5Zr8plW09fZA039CLi4pBAKd47 X-Gm-Gg: AZuq6aLcFo/XGXdYFw6l/K8eu2KeGf+zWD8up2JE+dKM/hBuY5YP+d4Ki7zhj4DTNwl O1YQMVLz04fzsH9edKt6bIkqEMKLq8iRMFocil0Bdrc7gpZPv0CJqSoRgCbtPvbCiFoagabPi/6 ZEvdkSt/pswjKXxTHjQOBH0hrPGSXZzWGIBqKt7m0yEYZIvLwVc35v9iuQhhSj76yrRFjVX64SO ogqBvxRk19drMNkL6EYbILWMhDpuZeLZDJaRmCbkDwh1DnGBayFdZ+Kzq6+qNPz3fSB+F/J7Pas OMfnxg9hJNS/cee+r2QYq1Q1Uw== X-Received: by 2002:a05:6402:1454:b0:658:cbf0:6a08 with SMTP id 4fb4d7f45d1cf-658de58aea6mr8837880a12.17.1770057576008; Mon, 02 Feb 2026 10:39:36 -0800 (PST) MIME-Version: 1.0 References: In-Reply-To: From: yudhi s Date: Tue, 3 Feb 2026 00:09:24 +0530 X-Gm-Features: AZwV_QgjfI4mSho7mln0xbWUu7WGfg3HaxJYILwfM6m5v8D90b75UheOjqhPQdQ 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="0000000000000cceb80649dba53d" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000000cceb80649dba53d Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable 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 reduc= e >> 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) (act= ual >> 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. 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. --0000000000000cceb80649dba53d Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable


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 nested loop join. So = my question was , is there any possibility to reduce the resource consumpti= on or response time further=C2=A0here?=C2=A0 Hope my understanding is corre= ct here.

= <= /tbody>
-> Nested Loop (cost=3D266.53..1548099.38 rows=3D= 411215 width=3D20) (actual time=3D6.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= =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 us= e an index.


Thank you= .
Actually i tried executing the first two CTE where the query wa= s spending most of the time=C2=A0 and teh alias has changed. Also here i ha= ve changed the real table names before putting it here, hope that is fine.= =C2=A0
However , i verified the data type of the ent_id column in= "ent" its "int8" and in table "txn_tbl" is &= quot;numeric 12", so do you mean to say this difference in the data ty= pe is causing this high response time during the nested loop join? My under= standing 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 an= d response time.=C2=A0
--0000000000000cceb80649dba53d-- 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 1vmyqr-000tEu-34 for pgsql-general@arkaria.postgresql.org; Mon, 02 Feb 2026 18:40:46 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.96) (envelope-from ) id 1vmypp-000qFC-1X for pgsql-general@arkaria.postgresql.org; Mon, 02 Feb 2026 18:39:41 +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 1vmypo-000qF3-30 for pgsql-general@lists.postgresql.org; Mon, 02 Feb 2026 18:39:40 +0000 Received: from mail-ed1-x534.google.com ([2a00:1450:4864:20::534]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.98.2) (envelope-from ) id 1vmypm-00000000DvH-3e0Z for pgsql-general@lists.postgresql.org; Mon, 02 Feb 2026 18:39:39 +0000 Received: by mail-ed1-x534.google.com with SMTP id 4fb4d7f45d1cf-658d3d3ac37so8211413a12.1 for ; Mon, 02 Feb 2026 10:39:38 -0800 (PST) ARC-Seal: i=1; a=rsa-sha256; t=1770057576; cv=none; d=google.com; s=arc-20240605; b=Gkidwwl3dwlM3P6kJJCJUsGq52QQWixpJizz1UE5gRQXK2VNiy/lrAnb1NbhKnGIkg Wo+PJzyfQ5Q1t+gR4ZFcFs5/JC9PuzoyCjKdBvRx5GZTmEHw3L4px4P8/ABAr60pwRwG Z2MVC8tEgfu4XXmvm2LTIR3iLCP/MlAFRo6qohBIFW+nFPz/GiXmY6S6jbCOytJFFq1v xbWjLG9aJoX0QMEKQfREg6nIsL5YR1QiZKDZ9xZrjpQEn2TAE/ddXQRBZMKc7f4kl4Yd Gy1K6/w39Tb8xqfwjWCduUAnP85sLrN0x42YkhoXXoQDbWJ6iJgJ6CK2605Xq20R0cME StMg== 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=lWzWXr5NMhCeCRxLuk8E6DVdGeHx8+2W5NvsJNYGJM0=; fh=M5d6ZpcaQVHTXkkOH6MmskvUc9TU9yzFINFTBAvu35o=; b=IDQvZQy8AKhDcTUCAXE+G4SpkOWp/mRs7nZY3rir6Plk+nljCunk40aLNOMQvFeQpw OQh9ILhcVSQgASaJchNgo0cuifoa3hWzfjpW51sCwrTix6ai84Mbm4I0c8GrqdzpIWYV 2scecJeseksfDc1ydfWukP10t6EaclP3Kn2XQj8L1JtKOiS/LlwmNt7NadUGQSLYD0mz C4kx18rpHMaUoyOSClyJECCcwl9Fi9UU48B6KVPovYffj03ISqzHEgw9liZco/zAsVBF BfU8OZjnn+z1K1g3NrTsJ3kExs/ZNtwajuOii87kOUun8Y8arRg598cxOIgi/0g7Xh7B nsZg==; 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=1770057576; x=1770662376; 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=lWzWXr5NMhCeCRxLuk8E6DVdGeHx8+2W5NvsJNYGJM0=; b=CTmddo3Wu1P76hLmXaoax28CI0gOMX78waJKTBHcoxv78odzOlfK9SJnlacynyIgsK X7qJ1YCPvC2XYNas8b6cR05M4TJkKoxp6JCGSQyZCQwLRz1Mgpy65ccGtp/sukr5OW/s IyBfMhoDWkqd0/FhAzpvQcqWMpvEy2RKwPEt9rbb+swR7k4uREXymqonoRBjizKkzHEO QP+du9+ygMTr0HMVCdGSZfoJVFvEDJ2Vrq4jQotOdBNPgqWOpEj4LWUPMcDd0PmBjKAB hLr9+Iz+ptgiNqvTQh+p0Lu9hXqWMFBalWdkRPxo0wY+6p+UK9VPczlcVlbtkTO76pUp n8+A== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1770057576; x=1770662376; 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=lWzWXr5NMhCeCRxLuk8E6DVdGeHx8+2W5NvsJNYGJM0=; b=evYhZhaLVXG387GolZyxB8JyP3OQMXCFRRaG2DU0fCKYhJVvUjcRiJ/Yg+L8mQ8gXb 2kYoSXhXii0W7gy0AKHesc55G4gG4HoGE4SJsyp8KjmlCMnW8HIO6kHAutzBZkNECuLt PFfkGft/zWI9rhy7NqIqquEn0nhGLqP0zD2qASmJ2DbO8FkGLOOig92Ux6CdxPVVXupw KznTJVPL23lWbUHTjTkLsfkxWa10/E9XPkwwBxFnSDYNhCsNqrl3IXYf1ApnhAkVFU+/ LnPyOYiQs/pPHxwl5/yHQm1ktfSq/84CBrxGTGuK15ysSEwl4uTmoz+8j5dJEb0Ki6ar Wffw== X-Gm-Message-State: AOJu0YwxzhPboSx02GmqecJpJqeS5Wf+QWIe4BHLGkbyvAjrN1gAnJRC YYNWufu5MNzljs3aSs//e3ILCSNWVy1f4scu4SCnE/pTQbsgApDbaHbIQqVHH2wr13DZLTdepdM O2JWZqjgzUZ5Zr8plW09fZA039CLi4pBAKd47 X-Gm-Gg: AZuq6aLcFo/XGXdYFw6l/K8eu2KeGf+zWD8up2JE+dKM/hBuY5YP+d4Ki7zhj4DTNwl O1YQMVLz04fzsH9edKt6bIkqEMKLq8iRMFocil0Bdrc7gpZPv0CJqSoRgCbtPvbCiFoagabPi/6 ZEvdkSt/pswjKXxTHjQOBH0hrPGSXZzWGIBqKt7m0yEYZIvLwVc35v9iuQhhSj76yrRFjVX64SO ogqBvxRk19drMNkL6EYbILWMhDpuZeLZDJaRmCbkDwh1DnGBayFdZ+Kzq6+qNPz3fSB+F/J7Pas OMfnxg9hJNS/cee+r2QYq1Q1Uw== X-Received: by 2002:a05:6402:1454:b0:658:cbf0:6a08 with SMTP id 4fb4d7f45d1cf-658de58aea6mr8837880a12.17.1770057576008; Mon, 02 Feb 2026 10:39:36 -0800 (PST) MIME-Version: 1.0 References: In-Reply-To: From: yudhi s Date: Tue, 3 Feb 2026 00:09:24 +0530 X-Gm-Features: AZwV_QgjfI4mSho7mln0xbWUu7WGfg3HaxJYILwfM6m5v8D90b75UheOjqhPQdQ 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="0000000000000cceb80649dba53d" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000000cceb80649dba53d Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable 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 reduc= e >> 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) (act= ual >> 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. 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. --0000000000000cceb80649dba53d Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable


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 nested loop join. So = my question was , is there any possibility to reduce the resource consumpti= on or response time further=C2=A0here?=C2=A0 Hope my understanding is corre= ct here.

= <= /tbody>
-> Nested Loop (cost=3D266.53..1548099.38 rows=3D= 411215 width=3D20) (actual time=3D6.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= =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 us= e an index.


Thank you= .
Actually i tried executing the first two CTE where the query wa= s spending most of the time=C2=A0 and teh alias has changed. Also here i ha= ve changed the real table names before putting it here, hope that is fine.= =C2=A0
However , i verified the data type of the ent_id column in= "ent" its "int8" and in table "txn_tbl" is &= quot;numeric 12", so do you mean to say this difference in the data ty= pe is causing this high response time during the nested loop join? My under= standing 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 an= d response time.=C2=A0
--0000000000000cceb80649dba53d--