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 1vn0mt-001I5x-05 for pgsql-general@arkaria.postgresql.org; Mon, 02 Feb 2026 20:44:47 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.96) (envelope-from ) id 1vn0ms-001QlE-0G for pgsql-general@arkaria.postgresql.org; Mon, 02 Feb 2026 20:44:45 +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 1vn0lQ-001E5j-15 for pgsql-general@lists.postgresql.org; Mon, 02 Feb 2026 20:43:16 +0000 Received: from mail-ed1-x531.google.com ([2a00:1450:4864:20::531]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.98.2) (envelope-from ) id 1vn0lO-00000000Epo-1AZy for pgsql-general@lists.postgresql.org; Mon, 02 Feb 2026 20:43:15 +0000 Received: by mail-ed1-x531.google.com with SMTP id 4fb4d7f45d1cf-658b6757f7fso261511a12.1 for ; Mon, 02 Feb 2026 12:43:14 -0800 (PST) ARC-Seal: i=1; a=rsa-sha256; t=1770064993; cv=none; d=google.com; s=arc-20240605; b=LfYMI4FCVQort7ZeBU0RL5hljHj32poxmgOC1hncRC9szFAcd0B5QPZO9SwjYZfHgV uHorbfNPt6N456ig2quXkPYL1HnZHrJfKCByqb9FQlk4ARAjgvVxwd/D+Dsgtw5yp4Ij cw0CX45MMlk67MV2HJ0pn+Grty2BO5ErTqpbh9RUtsCt359S4AZCJZfyzZn8/dz9rHGa PZveTy6wsrNTAV/14R5e7nFRmdmbGNN6PCReV4I/exch2Gv3Em8m3rEXdtkOx3nCqFgW 9k4EerVnpBYMLDOrQVadduQxMpiXKjS29xSG7ar0foju4EnNya7EPpywL7UTFpWMXqML H7yg== 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=gVtYvKKvYHkcFlzEQK3B41/+HgxJP2zN0bSkvI//Mcs=; fh=M5d6ZpcaQVHTXkkOH6MmskvUc9TU9yzFINFTBAvu35o=; b=Es5Q1JYqP2xz617HV/TpCnv8HJL6yGe06LjAH7BQDS904ogmzRKIlAqnz6tosZE8/i gnIudEzn5RWFJuPXmCMMk33JpRybEctDKd7/iL/xQ6F0S45c78WOjBPLbPJ23SjeYe3e 1aIWDEp3aFVxPm1OunN+e15m3nzsKKNCSgmcv3/qhf3L/yUHfoIlZZ1huMfXuCE3JNO4 V1UJjBEsXfwIB4kHNvk6yB4D2rum7Bbk1yfiF5qdAxllC+e2WCKIfYFN9WCQSS/b9wsg WilOjE5ZNE/VHnNw5858zYH3SNsYByuuBx3ynTYCIKh5dzixbtIfvMhRLNVxHSZWfWh+ dwxQ==; 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=1770064993; x=1770669793; 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=gVtYvKKvYHkcFlzEQK3B41/+HgxJP2zN0bSkvI//Mcs=; b=aNJ2UjCHmt++wNPklW4BKVF1somrBcDE4qaHrHRN94ecmBsEviePGLo9yW97vGRDPp myR3R8v4WNN1b5PTgQcOeIn1vMxULHKVsDalDtR8ZW/Md/NVMB55fPIW9v/8FlfuDaM2 rvGzfyKpcWsiGXyAON8pXloxqdoUVoFkMBROdQy7tHkNR+YYaqMKW/sihWOv70gxp79f Lvd1W2a7O2UYyIWJdTViucZ9XEYjvxfXEHMgLje/+RHbgj92Wb0fzw1o51QtVSDVOdDg ULOdopb6noiG4JlXM/7mt76YDyE+P49WodvR9f/+jTymO14zJkdIOwcMQrrtkmMFNWBV aQGw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1770064993; x=1770669793; 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=gVtYvKKvYHkcFlzEQK3B41/+HgxJP2zN0bSkvI//Mcs=; b=Egg11tZgrZJW6jdBexazFDgLPJIz9f4E+kFOJIUwJD5BVY4MQiIAoqW88VNelpqenF sWwhKm8KljlXfVX9GKggDEFtQ12+ywehnj7poFLwLiUG2knL7khBaWhm5woXScjawPpK iRiiCJfNuYj2xt6neUx1m8+M0/4kni+dZtuBD48dSTsSSyNjaAT0PF3egUEenFIoNkjU HHY+0qg74zswEly+IOJqJ0BAW43zcSXoO+fkFaNOUj3A9WJAeghsSTVff1m2zmvi6BKK pWamiB+YwGdQm5uPAREbwlpHDnXKPKWfH0981FHosTigeFRmu3lTTfesxkaRcythxVGA laxQ== X-Gm-Message-State: AOJu0YyDlDj6uvgrX4soBDRSUTuNzRK6tNX59TT1X/FXcyF3422/l/FS z/ER3hZkmvDYUUy3waIk1YxqEEcZOP5/uF22W5411mKnk0hVn/4DSDleGuVL/6vgVTZm8hbXkFX oY131RAjlmC1Uf3cyjW15c+gBql7Irpk= X-Gm-Gg: AZuq6aIaRbPokD/IIac0cPl5Y9s/xFvvygHlY10o0TTy68v2g8fIQCg1BQpKohkJGJT dSsec72BxBAhEn3PeNm4lwFtBgEiExbpNrY4/2JGzXNox0iAq0V6xcNDPbL/kwfEAKzVKg7sWyk K64VH/a2GWYXd7FYDJZ8D7RjKoxT6Z/h+db5ez3Pl92E1c9P1BrTvBk2uHj8rfkBWXC4Pv0PGhT 6VXtHN/FN9WmH8/p7K6P2tGu1zpzD50Y0cKbQh08InJoimOXosG9qzQ8PA+0XpNZlhBV7VomV53 /LXjivUWbrvXKWz/GAwyQheF3w== X-Received: by 2002:a05:6402:52d8:b0:64b:58df:cf24 with SMTP id 4fb4d7f45d1cf-6593281c924mr367959a12.11.1770064992456; Mon, 02 Feb 2026 12:43:12 -0800 (PST) MIME-Version: 1.0 References: In-Reply-To: From: yudhi s Date: Tue, 3 Feb 2026 02:13:01 +0530 X-Gm-Features: AZwV_QgY0FE2lvNRldz_H0SQq21Dg-uPbvmIOXZgSvssEPXwtml7uNjlEYNgeLE 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="0000000000001ad4690649dd5f60" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000001ad4690649dd5f60 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable 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 spendin= g >>>> ~140ms(140ms-6ms) i.e. almost all the time now, in performing the belo= w >>>> nested loop join. So my question was , is there any possibility to red= uce >>>> 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) (a= ctual >>>> 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 inde= x. >>> >>> >>> Thank you. >> Actually i tried executing the first two CTE where the query was spendin= g >> 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 thi= s >> difference in the data type is causing this high response time during th= e >> 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 Regards Yudhi --0000000000001ad4690649dd5f60 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable

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@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 po= ssibility to reduce the resource consumption or response time further=C2=A0= here?=C2=A0 Hope my understanding is correct here.

-> Nested Loop (cost=3D266.53..1548099.3= 8 rows=3D411215 width=3D20) (actual time=3D6.009..147.695 rows=3D104= 9 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?

Als= o, casting in a JOIN typically brutalizes the ability to use an index.


=

We need to see everything, not just what you think is r= elevant.
=C2=A0
Also here i have ch= anged 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 "n= umeric 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 understandi= ng 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 resp= onse time.=C2=A0



--0000000000001ad4690649dd5f60--