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 1vmvq7-000Irt-07 for pgsql-general@arkaria.postgresql.org; Mon, 02 Feb 2026 15:27: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 1vmvq6-0005Qr-0F for pgsql-general@arkaria.postgresql.org; Mon, 02 Feb 2026 15:27:45 +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 1vmvq5-0005Qe-2E for pgsql-general@lists.postgresql.org; Mon, 02 Feb 2026 15:27:45 +0000 Received: from mail-oo1-xc2a.google.com ([2607:f8b0:4864:20::c2a]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.98.2) (envelope-from ) id 1vmvQC-00000000gjG-419h for pgsql-general@lists.postgresql.org; Mon, 02 Feb 2026 15:01:06 +0000 Received: by mail-oo1-xc2a.google.com with SMTP id 006d021491bc7-662f65c7d8cso2520336eaf.2 for ; Mon, 02 Feb 2026 07:01:00 -0800 (PST) ARC-Seal: i=1; a=rsa-sha256; t=1770044459; cv=none; d=google.com; s=arc-20240605; b=bxhLvH5eQUJ0hbhI61x2syosmfn1dp1cjUTiBXuMLdV4/IUeI+H5rmROe5g4Bdfi8D WgqvJGhfq1f6MlD56KFOVwS7ktxzQami+sDW9w39Lb2t5p1CbE/vnYT98RlxdDS6jtHm oj+c8E8ACU/IBO7HouHIobc7NY6/gSSL1wfkkfYrfRUkWktrHzfhWxoNJ94gcScXtsDB jhKhGC4y6JoNrOP/B74cdvanfpYPJAOtYX1F91s4HkIWF5kVVnAie7IS5LaejCvnnTox 7liDE8yKbu19zn1JWCiRJprkN36oadGAVHgcYhOfMx8hEtajBHhq483gbuRjDGcTPRRQ XF+w== 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=bpOXZ6FCYOZ54WRigPYxSHIliGN0O2jJPe13nEntBAE=; fh=2DoGC78b0mi8uZ4UY+9ocPF86GSVzeaJWqou/x907js=; b=OB2dvdVtlsnVV+7hb9e0s4XKhQ6jxkf71BTL4g0Ht29heUKhD8bMTvmDIM0fH3NsIn 7VRXmhxwr9/Po7SIFeGBCf+qaHB5Rqllh0ro1K4vClzTst+/DfcE+BQ0iw+vsCzC8me0 J1z5crVI6b5cUgz8KwrePKKl8cO8ADuELrn+phjZNJ1ubrM2p9x0RGLspuWz+tdZzobT iENoBMO8yowBUVesfcFZ/EvjOy+D2JD11p3kM+6i5VLMPYccxl2qQF7+uPzOd1QuMeFt UeYNRaW66GP+cil06XY23IwDVWCJMQecfGjB9rDhvKjkqc/h8Mrjs4yup0D2NSpKavZY ilnQ==; 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=1770044459; x=1770649259; 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=bpOXZ6FCYOZ54WRigPYxSHIliGN0O2jJPe13nEntBAE=; b=RNJbsEouIJQm419tSiZe/2dcN5adZZQtfy+uK3yhi+zP/bZLwVfwesBk9NjG1vBSnW aicFWBtAVqT9DmetcUYaW/jSYr7NSr54YWFsMd05Dp1tONH3YOyDdyJTyP4eKIJpphfv lEkMae6dS0TrcnpHgI7HfnReUM8iRtJynlq3ZQ0uNFKwZTpboNjPgfEENaGBy2TjNImR 1EO+ZZVtHMOiPtrVuXmkl7+pqWW8wM+pRY2CN4vaB0uEaJo7qjZwBXNQebbRx4T+Bx/U Q0Cbw22Ch/9mV4vlIJ3rHoMbEuwuf3EDDncxUviXXvcQXI4lvu3loFLOGEgHQ24oHu1N 7p9Q== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1770044459; x=1770649259; 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=bpOXZ6FCYOZ54WRigPYxSHIliGN0O2jJPe13nEntBAE=; b=CkgNK6TW6hYWWxy6vpRjMY7GAk/S3u12GO1q1zP3/03VmGmvlYMph2LEU+qpFpW2pj b/7Hwi7Z5AZ7FcuIuomlS73/H+DuWS40tj2m5QXwOf2NhloH87siQMML0Xoo5W3UuG6z N+uDQ3SgeAFt6yybcHtaWk5gjhyOlCM/plPY0eLKf36mRpQumESFdGGTT1Nt8abTAkY/ aSTFKSQyyytDnx+Mgsx49Qvt0sS/VEuC6Z7SAFFp1DakrdtPmlaMIZxOQ1F91d/QUE7s mzNGewFC+jfUPPP+0r88UMGXABTsFpX1q0nHpScIos2lhTBhiAE/FYvwA0znsApgKYR4 75fQ== X-Gm-Message-State: AOJu0YwEycBTzqRl3lazjPJ4aBioubZwep0BBqBD5eTX8CMFk7rk9Gqv TgnC6m4yfdMRck2NfClnwvLmp/WnLn8hBpVXlq/HGx6RTqbH3JjisfCABvb5UfWmJnLvV3JfLt3 jUmCDMnKfq0NdZ6ZMm3DvU4TNHjY0QOHw4udm X-Gm-Gg: AZuq6aLVhnvbsDUhwkZgaJ6QbmHUNlkhryf3FA/h5mtSRRbVp2y5qhK8oQNmMbAsX+R hayMAJECK7OD9s/nWE6ybZbwsl2zO9T0Krh1gw21KeaThPmB4s9f7imGn24Ba+uz1aQ7VxGTayS 7kMOUJIg37I4rubsfnpK5gxxKhrtqFydqUHwTj1zC4Xafx3RQr5f0vPpzHVUhZUR53RUUmqwAnH tnb2Iqe/xPgHyu4aoMm0/W9X9rxitbjDm2O3lR/lTYorjuSXDm9+fqKBY6esA0+rZR9LAeg X-Received: by 2002:a4a:edcc:0:b0:65f:54b1:a9af with SMTP id 006d021491bc7-6630f361628mr4268655eaf.42.1770044458190; Mon, 02 Feb 2026 07:00:58 -0800 (PST) MIME-Version: 1.0 References: In-Reply-To: From: Ron Johnson Date: Mon, 2 Feb 2026 10:00:47 -0500 X-Gm-Features: AZwV_Qj4WKqDFWmssmb1F5ZEmVV_rzaNwzf1PWJZkMFHNULTE2gvjKH65X_3D9o Message-ID: Subject: Re: Top -N Query performance issue and high CPU usage To: "pgsql-generallists.postgresql.org" Content-Type: multipart/alternative; boundary="0000000000002acb5a0649d8971c" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000002acb5a0649d8971c Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Mon, Feb 2, 2026 at 8:53=E2=80=AFAM yudhi s wrote: > > > On Mon, Feb 2, 2026 at 7:04=E2=80=AFPM Ron Johnson > wrote: > >> On Mon, Feb 2, 2026 at 6:39=E2=80=AFAM yudhi s >> wrote: >> >>> >>> >>> On Mon, Feb 2, 2026 at 3:17=E2=80=AFAM Peter J. Holzer wrote: >>> >>>> >>>> If you do have that many simultaneous accesses to the landing page, an= d >>>> you can't speed up the query significantly (I take it you've seen the >>>> suggestion to check whether there's an index on >>>> APP_schema.txn_tbl.tran_date), then maybe you don't need to perform it >>>> for every user? I don't know what the query is supposed to do, but >>>> unless the "ent_id" is really a user id, it doesn't seem to be specifi= c >>>> to the user. So maybe you can cache the result for a minute or an hour >>>> and show the same result to everybody who logs in during that time. >>>> >>>> >>>> >>> >>> There was no index on column tran_date , I created one and it's >>> making the query finish in ~200ms, a lot faster than in the past. Belo= w is >>> the portion of the query and its plan which actually consumes most of t= he >>> resource and time post the new index creation. >>> >>> https://gist.github.com/databasetech0073/344df46c328e02b98961fab0cd2214= 92 >>> >>> 1) Now the part which takes time is the "nested loop" join on the >>> "ent_id" column. Can we do anything to make it much better/faster? >>> >>> 2) Also another question I had was, with this new index the table scan >>> of txn_tbl is now fully eliminated by the "Index Scan Backward" even i = have >>> other columns from that table projected in the query, so how its gettin= g >>> all those column values without visiting table but just that index scan >>> backward operation? >>> >> >> Reading through EXPLAIN output isn't always a mystery. >> >> Search for "actual time" and you'll find row 53, which is the "deepest" >> (most nested) row with the highest actual time. >> >> That tells you where the time is now spent, and what it's doing. >> >> >> > 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 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) (actu= al 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. --=20 Death to , and butter sauce. Don't boil me, I'm still alive. lobster! --0000000000002acb5a0649d8971c Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
On Mon, Feb 2, 2026 at 8:53=E2=80=AFAM yu= dhi s <learnerdatabase99@= gmail.com> wrote:


On Mon, Feb 2, 2026 at 7:04=E2=80=AFPM Ron Johnson <ronljohnsonjr@gma= il.com> wrote:
On Mon, Feb 2, 2026 at 6:39=E2=80= =AFAM yudhi s <learnerdatabase99@gmail.com> wrote:
=


On Mon, Feb 2, 2026 at 3:17=E2=80=AFAM Peter J. Holzer= <hjp-pgsql@hjp.at= > wrote:
=
If you do have that many simultaneous accesses to the landing page, and
you can't speed up the query significantly (I take it you've seen t= he
suggestion to check whether there's an index on
APP_schema.txn_tbl.tran_date), then maybe you don't need to perform it<= br> for every user? I don't know what the query is supposed to do, but
unless the "ent_id" is really a user id, it doesn't seem to b= e specific
to the user. So maybe you can cache the result for a minute or an hour
and show the same result to everybody who logs in during that time.

=C2=A0 =C2=A0 =C2=A0 =C2=A0

There w= as no index on column=C2=A0 tran_date =C2=A0, I created one and it's making the query finish in=C2=A0 ~20= 0ms, a lot faster than in the past. Below is the portion of the query and i= ts plan which actually consumes most of the resource and time post the new = index creation.

https= ://gist.github.com/databasetech0073/344df46c328e02b98961fab0cd221492

1) Now the part=C2=A0 which takes time is the "= nested loop" join on the "ent_id"=C2=A0 column. Can we do an= ything to make it much better/faster?

2) Also anot= her question I had was,=C2=A0=C2=A0with this new index the table scan of tx= n_tbl is now fully eliminated by the "Index Scan Backward" even i= have other columns from that table projected in the query, so how its gett= ing all those column values without visiting table but just that index scan= backward operation?

Reading through EXPLAI= N output isn't always a mystery.

Search for &q= uot;actual time" and you'= ;ll find row 53, which is the "deepest" (most nested) row with th= e highest=C2=A0actual time.
=

That tells you where the time is now spent, and what it= 's doing.



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.

-> Nested Loop (cost=3D266.53..154= 8099.38 rows=3D411215 width=3D20) (actual time=3D6.009..147.695 rows= =3D1049 loops=3D1)
Join Filter: ((df.ent_id)::nume= ric =3D m.ent_id)
R= ows Removed by Join Filter: 513436
Buffers: shar= ed hit=3D1939

I don't see=C2=A0m.en= t_id=C2=A0in the actual query.=C2=A0 Did you only paste a portion of= the query?

Also, casting in a JOIN typically brut= alizes the ability to use an index.

--
Death to <Redacted>, and butter sauce.Don't boil me, I'm still alive.
<Redacted> lobs= ter!
--0000000000002acb5a0649d8971c--