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 1vmu4l-00HaMp-0Z for pgsql-general@arkaria.postgresql.org; Mon, 02 Feb 2026 13:34: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 1vmu4i-00DvBi-3C for pgsql-general@arkaria.postgresql.org; Mon, 02 Feb 2026 13:34: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 1vmu4i-00DvBD-1i for pgsql-general@lists.postgresql.org; Mon, 02 Feb 2026 13:34:45 +0000 Received: from mail-oi1-x229.google.com ([2607:f8b0:4864:20::229]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.98.2) (envelope-from ) id 1vmu4h-00000000CIH-2U2i for pgsql-general@lists.postgresql.org; Mon, 02 Feb 2026 13:34:44 +0000 Received: by mail-oi1-x229.google.com with SMTP id 5614622812f47-45e934bb51dso1648286b6e.1 for ; Mon, 02 Feb 2026 05:34:43 -0800 (PST) ARC-Seal: i=1; a=rsa-sha256; t=1770039282; cv=none; d=google.com; s=arc-20240605; b=YZGRRmoqWbVEjy0LWhwgPWneLKybrqp0xLHMaeWj6boEcw7kTECRD8RZxjA0w+Jlf9 Yakd2rueGU1MG0irS+krcQbtpFfE7TOE1rz0rnu9bM2hKhiHgHHMEzYYNC4q2CeS8X83 1i2zq183lFidmE/+eYkFmyPpbcTRc5O2kuY16gjCPwXDzu/FP4zImNn2OOtsU63ZLn1J br9kIQ/jBw68S3T5L46qlsDk24Qa81+IhZLvaBcDvhh6UHScClQeXoWrcWoRSbQhMiyj vDPYDX1vDlxbXc31DPBrC2QpZ6qs0GVerCyOftWD91yxTj13JHb/709WGrUK1Vhj5326 l8vw== 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=RC07OLkwe5WmI8+HrGTxBaDl0IvggRhH/fuUzo7zXKw=; fh=2DoGC78b0mi8uZ4UY+9ocPF86GSVzeaJWqou/x907js=; b=a3pjevQ+n3V6NLHUGXVekxwdePXt2NxmGdwQEO3OlaXGLC0JX3sZPTt6mEHK66boGK PfjS0aVv7pbnvoYy+ypNeJe1VoVTC9cg4UVqebxQbkvDYW7gderPWk1z/gmmGNNlyS9Y 31tBbCjbqMqTYf7o3KltjaEHvLPDhAV8HpzIUI7Bw0xtm1S9vTMYsNSqDy7yKTziUchu Fy9ycMBYo5HMjcYVCdx45NLxrPQl5ERjJxw8F5oOob8qXWyE2pkBmMKeN6LA/eD5OGQo mmFSz5poCa1aJEQ0CQAbx+ZA/6rwdaff+MMcR2gmlJOB5DUl1nijxozNdy8gFhlJiAGv 5yzw==; 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=1770039282; x=1770644082; 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=RC07OLkwe5WmI8+HrGTxBaDl0IvggRhH/fuUzo7zXKw=; b=OfGlS08/zdslKRx4Ne35hl8zCezVm/HP8xqysgslaJVUwUSFyiVoAJm7EOxuH99JzI llznGYRyRmu8/dbT6S/UhO480SePeLiHqrrnoLVqf8vOva1DunS5rbstFNe972L9n2o5 YwyVe9zXT9HxOJhkeF1YGaFVvGBWtih+oH+OxDplelsf8UC/0mFUPsQz2mlrIl3efwea YFI5WoMzKdc/4fTvTShBYRyU2G0F8d9pHX61fioCKi7eNayH0ZUC77u/34kXKbABAGs5 XEC96EXevCc0ydlwIPmNPWGdZnV2jO5m7RmlPiouEeZj4hh/DVOITeEVmfoMAooBy5ff kklA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1770039282; x=1770644082; 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=RC07OLkwe5WmI8+HrGTxBaDl0IvggRhH/fuUzo7zXKw=; b=sb8MpX608eV2k6W/WiT00CKtBWrn/9GX5W0OQA+ybX/YaRyAPtHDXrQsaWqrv2mlOe ysqUfxc6gl+kISDeYeryqXiZAIy6VgqGb3dVQJZ0v6a0a9UwnQxtRelNhHdJguehRUgN ZmBiYCLEO+7gPXeUT88QmlIkSbTrsoj+PrMcEe7WH0PfC9aW3I4si2YV/UXXXh1jHLU+ MYTymgDhLHTUDKA+vPefnXl+vlloAeyDBSXdAOtcekGvm+PWKrLZp5eUY3Id5k98Rg6L JDWYahSK7AbDB+l+KZhdK7aESv3Mq6VMvFLvV1cKZ1u4nZtLjy0889+qFU2dzE/qoavt wmKQ== X-Gm-Message-State: AOJu0YyIqP+9/pfT8VK7UMIvU5kngE68rFuhvZpGXvFkvXs10JT/XIUs jdhAy5UZHXWeOiyjt0nDqTezTfRdH8qSTggAJxurOvCqPUVXiWNcScOZTI/8Mn6Dw60xPb+ZIIA X4YdV4JO5o6ZquuDbDBQTawnl3rJaz1ucBYRr X-Gm-Gg: AZuq6aKlI/BGX80bnUrYJ1LVY6Xd1dx4kDU++/JzQ4WEuIGbWPx7TEqihJM9wkNRbza m6QFu2p3L1iEWVWbNiPYwTT2cXRi9J/l53bZiKC+Pfmdz+YdHTFhh6c5S5mgAW2yUsIpdgFky97 whSTF72rTJTBziMCkhoKb2CXsc6Zm51qSG5enwZm0aWx276+YbpjstQXLg59eYhFOQIj9XJt1jZ GVtPK39NZIZrRpYoOJlxFxTE8VZIVtwAbVRpyEN+obLIPi3J1aF3lFNYOdHAAOFzK8awowb X-Received: by 2002:a05:6808:2201:b0:45e:b6a5:c6d with SMTP id 5614622812f47-45f34d33382mr6265748b6e.49.1770039282356; Mon, 02 Feb 2026 05:34:42 -0800 (PST) MIME-Version: 1.0 References: In-Reply-To: From: Ron Johnson Date: Mon, 2 Feb 2026 08:34:31 -0500 X-Gm-Features: AZwV_Qg0cQlqGvVXsG0Zna173dEP3250D4WsrjGgTYp5OwSYdfb3SLGRHtm5px8 Message-ID: Subject: Re: Top -N Query performance issue and high CPU usage To: "pgsql-generallists.postgresql.org" Content-Type: multipart/alternative; boundary="000000000000a9d5350649d762d2" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000a9d5350649d762d2 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable 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, and >> 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 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. >> >> >> > > 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. Below is the > portion of the query and its 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 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 o= f > 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 getting > 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. --=20 Death to , and butter sauce. Don't boil me, I'm still alive. lobster! --000000000000a9d5350649d762d2 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
On Mon, Feb 2, 2026 at 6:39=E2=80=AFAM yu= dhi 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 f= ind row 53, which is the "deepest" (most nested) row with the hig= hest=C2=A0actual time.

<= /div>
That tells you where the time is now spent, and what it's doi= ng.

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