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 1vn3DD-001mrb-1c for pgsql-general@arkaria.postgresql.org; Mon, 02 Feb 2026 23:20:07 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.96) (envelope-from ) id 1vn3DB-001z2v-1m for pgsql-general@arkaria.postgresql.org; Mon, 02 Feb 2026 23:20:05 +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 1vn3DB-001z2Z-0C for pgsql-general@lists.postgresql.org; Mon, 02 Feb 2026 23:20:05 +0000 Received: from mail-oi1-x235.google.com ([2607:f8b0:4864:20::235]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.98.2) (envelope-from ) id 1vn3D8-00000000Fvx-2p05 for pgsql-general@lists.postgresql.org; Mon, 02 Feb 2026 23:20:04 +0000 Received: by mail-oi1-x235.google.com with SMTP id 5614622812f47-45efd53148eso1804365b6e.0 for ; Mon, 02 Feb 2026 15:20:02 -0800 (PST) ARC-Seal: i=1; a=rsa-sha256; t=1770074402; cv=none; d=google.com; s=arc-20240605; b=h4IM6XtEibteuu0cL7k0JHAAq72wLWR2aK5X6jDOb288CMa8KnCLPqpfpDuuQCDsom NnONOaWekX2VwUGyQjILy6L5Q9A5legYfRzkoiQtTMUjFIcey+uZ8nvx76wuf8RV+w4P uWn98Ye6WeATiXao3kT+AoDvdnqWBMz/Xlg5chncllkQF0fPbTX8UCRx/zo95zqZM9c7 tNbdMsdVNfI0qK2TyizWTs9VqUAX2ka+7URN0tvF32Nq9OIwul5T8Mmp/WgwnIti9d2y NI74wwSSy8FDFlCwu4GS7MnGyhh6n2WcWctdSFtYFIkeZeq4x+EXvgQwv2lilJPjCTfe vuqA== 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=mo2OVyVGaS8zKczZMmEpOBO/wVGVgBCPyJKxJDozpyM=; fh=Ljd5nf0MJ6vbeCLZpYIZLWlcv/0NOGHL3saVjs89nDo=; b=YZiPiULZQL5Wmdo4aJdskEP1lZLVaVjPNx5pIRpak1/5E81oJ6v8VE2HvpvpMSAsfm 1Wi1HQbnBP9L+RgkVnQAyXns/XVwJj3LXV5kpDYG/33znv1El1j+vhgTwRJKR1Nfzhuo EM30IDqI4kz3We4kT7g60FSaUbeodP+3hcFLGUXFMkQCX1STPb2u0dyvVkys+O5Mc4V1 pCwNxKFmZd6Redg1eUOTmSkswhT53L4AnfneyMrYuD9hTZQDkOYsvmRl5AmimSLxHNQX igafVz9CO/6lNZFSUARXAo2+nW8k1UFopu+vLymOe4dM1ROokPwi15F/OeXWOYFZ/VEL Hzcg==; 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=1770074402; x=1770679202; 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=mo2OVyVGaS8zKczZMmEpOBO/wVGVgBCPyJKxJDozpyM=; b=JacahqsnjQ/o49VeRJI3IhwANnHWVNewfE7YO3SaF70TSy9ZO/F99t9E49iloqPgJl NkcKt01agLkTGWZXHFzg9lwOfIU2sHaoH8SB6N7f15OvMo9XB80hyMIRSwCFQ+XaLlK1 f1Tt7h0L9WNyCNLLgmf7FaT0s6uXJz8mkqrYWlGmG1FwVM1rqZpT7iVA3kB+ROfsz+97 p1QbhJsf/ZPdQXQtz6k7WtwPT22V3GcfRlRrWs+7+2YxnMA3pVefRPB5Vo0Rk5VFlvTU 6+O2lUps2zLiyDhJKguYoTsIqiLn7NHNJfxqqeMZzdv2m0UtUwsX8vzS9EwngeL3IvLa jNGg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1770074402; x=1770679202; 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=mo2OVyVGaS8zKczZMmEpOBO/wVGVgBCPyJKxJDozpyM=; b=PH0CRFrK7Z+Y/yrCH+4BZ5FYvXzMD7p/oStTyHG09NmpgkIf5u3d/gaBfgd8Cae+kA hgpUNa+BMomDZPvIfBXUL6Mg3q1KJN/iP5pdzmiliZEuPfUjqKx6ETnQq1r3SkIB3Wzb xaEGMdaqtel48gZjsNpPJtrf8IIqzw/VAfJeeBlK+rSEAPfAcQpZmnZSlbP+xOCPlze4 tAndTU+Z1+wCxR4uKL+XQ/P6SElwBbadIlxkxQN9lE/dNPXTcNcuA2WTpI7hiqZipGLK kShIc59FuZIltp4qkrKLv3rHicJ7HqlWmFBnCv4aMnwSMC1baqGxCzvTLKEb+1DAF7UJ DoIQ== X-Gm-Message-State: AOJu0YzFQyhZSkJQRaqPs2MIr+jIrGR79l1k6tpDfZax1E+aN7fErB1I PeF13FbTO0poLbBZ9cI5Sidqf5/WskAr3zfqh7SBdTpYXMMvIb2m9jq89ChcXB8AiqtcZJuytr6 SsEHE21bzcwx006OXwUXI+3iiczJTmi4= X-Gm-Gg: AZuq6aLIV0CH6hXPCXL198b6Y4ByZaa1CcVTuoegyG8g5nWpMng6EUdubmyozlWqZNQ VpavuS7CBo4fMLECWCedWyodkWNGeCZ6f5UsxkainWsZmV7pnwq8+zyP9HZbl+RwUJzJCJePuHo 99uOizaRq/ofPYUlND0M2q1o7BFBWxHEUkLvnIYiOHKg3dXGEhimXSJ8fZK2O70TXEIQrhufGeJ QXOEpDIGjoarAV9vUckLXYuxW1FasQdTEtEeWeIQQG9bpVUecX0BW27hxJmib0qIJ3f0yX/ZyAW 5rQkD6Y= X-Received: by 2002:a4a:e711:0:b0:664:86ce:df6d with SMTP id 006d021491bc7-66486cee34dmr3134826eaf.18.1770074402025; Mon, 02 Feb 2026 15:20:02 -0800 (PST) MIME-Version: 1.0 References: In-Reply-To: From: Ron Johnson Date: Mon, 2 Feb 2026 18:19:50 -0500 X-Gm-Features: AZwV_QiOrumBKcO702ZynYadw9_9A9Sm8L6zqNHd8bzkfTvQU2VI1C9fDrkZwIo Message-ID: Subject: Re: Top -N Query performance issue and high CPU usage To: yudhi s Cc: "pgsql-generallists.postgresql.org" Content-Type: multipart/alternative; boundary="000000000000f5776e0649df8f8f" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000f5776e0649df8f8f Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable 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 performin= g 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=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 th= is >>> difference in the data type is causing this high response time during t= he >>> 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 > 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. --=20 Death to , and butter sauce. Don't boil me, I'm still alive. lobster! --000000000000f5776e0649df8f8f Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
On Mon, Feb 2, 2026 at 3:43=E2=80=AFPM yu= dhi s <learnerdatabase99@= gmail.com> wrote:

On Tue, Feb 3, 2026 at 1:01=E2=80=AFAM Ron Johnson <ronljohnsonjr@gmail.c= om> wrote:
On Mon, Feb 2, 2026 at 1:39=E2=80=AFPM = yudhi s <learnerdatabase99@gmail.com> wrote:
On Mon, Feb 2, 2= 026 at 8:57=E2=80=AFPM Ron Johnson <ronljohnsonjr@gmail.com> wrote:

My apologies if i m= isunderstand=C2=A0the plan, But If I see,=C2=A0 =C2=A0it's spending ~14= 0ms(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 res= ource consumption or response time further=C2=A0here?=C2=A0 Hope my underst= anding is correct here.

-> Nested Loop (cost=3D266.53..1548099.38 rows= =3D411215 width=3D20) (actual time=3D6.009..147.695 rows=3D1049 loop= s=3D1)
Join Filter: ((df.ent_id)::numeri= c =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 pa= ste a portion of the query?

Also, casting in a JOI= N typically brutalizes the ability to use an index.


Thank you.
Actually i tried execut= ing the first two CTE where the query was spending most of the time=C2=A0 a= nd teh alias has changed.

We need to see everything, not just what you think is relevant.
= =C2=A0
Also here i have changed the real tabl= e 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 &quo= t;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 r= esponse time during the nested loop join? My understanding was it will be i= nternally castable without additional burden. Also, even i tried creating a= n index on the "(df.ent_id)::= numeric" its still 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.

<= 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.ent.ent_id=C2=A0from NUMERIC to int8 (= with a CHECK constraint to, well, constrain it to 12 digits, if really nece= ssary) is something I'd test.

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