MIME-Version: 1.0
References: <CAEzWdqd0SPkZMYNaAbERdgczkfQqLmNV5JBMmF-F9s7KjxJ0gw@mail.gmail.com>
 <fde67103-e00e-460e-b2ec-eb539809e998@aklaver.com> <CAEzWdqd6LAHs+FiFeJLqDTS-QBLq6+foE1-mgBC9AXVpFmVnZg@mail.gmail.com>
 <vecavrvgzoxkks66nw2gvt3vot5lwbcm7f65iopgjbw72v2lc6@qd5leh3coj7g>
In-Reply-To: <vecavrvgzoxkks66nw2gvt3vot5lwbcm7f65iopgjbw72v2lc6@qd5leh3coj7g>
From: Ron Johnson <ronljohnsonjr@gmail.com>
Date: Sun, 1 Feb 2026 16:56:20 -0500
Message-ID: <CANzqJaBT6Uu6WDafuoRVXvpi7GgEZ9Jj2OJrN8sMZAstLNFjqQ@mail.gmail.com>
Subject: Re: Top -N Query performance issue and high CPU usage
To: pgsql-general@lists.postgresql.org
Content-Type: multipart/alternative; boundary="0000000000007b49520649ca47b8"
Archived-At: <https://www.postgresql.org/message-id/CANzqJaBT6Uu6WDafuoRVXvpi7GgEZ9Jj2OJrN8sMZAstLNFjqQ%40mail.gmail.com>
Precedence: bulk

--0000000000007b49520649ca47b8
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Sun, Feb 1, 2026 at 4:47=E2=80=AFPM Peter J. Holzer <hjp-pgsql@hjp.at> w=
rote:
[snip]

> 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.
>

That's what I was thinking, too: app server background process continually
runs that query in a loop, feeding the results to a shared cache; the end
user connections then read the latest version of the cached results.

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

--0000000000007b49520649ca47b8
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr">On Sun, Feb 1, 2026 at 4:47=E2=80=AFPM Pe=
ter J. Holzer &lt;<a href=3D"mailto:hjp-pgsql@hjp.at">hjp-pgsql@hjp.at</a>&=
gt; wrote:</div><div class=3D"gmail_quote gmail_quote_container"><div>[snip=
]=C2=A0</div><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px =
0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">
If you do have that many simultaneous accesses to the landing page, and<br>
you can&#39;t speed up the query significantly (I take it you&#39;ve seen t=
he<br>
suggestion to check whether there&#39;s an index on<br>
APP_schema.txn_tbl.tran_date), then maybe you don&#39;t need to perform it<=
br>
for every user? I don&#39;t know what the query is supposed to do, but<br>
unless the &quot;ent_id&quot; is really a user id, it doesn&#39;t seem to b=
e specific<br>
to the user. So maybe you can cache the result for a minute or an hour<br>
and show the same result to everybody who logs in during that time.<br></bl=
ockquote><div>=C2=A0</div><div>That&#39;s what I was thinking, too: app ser=
ver=C2=A0background=C2=A0process continually runs that query in a loop, fee=
ding the results to a shared cache; the end user connections then read the =
latest version of the cached results.</div></div><div><br></div><span class=
=3D"gmail_signature_prefix">-- </span><br><div dir=3D"ltr" class=3D"gmail_s=
ignature"><div dir=3D"ltr">Death to &lt;Redacted&gt;, and butter sauce.<div=
>Don&#39;t boil me, I&#39;m still alive.<br><div><div>&lt;Redacted&gt; lobs=
ter!</div></div></div></div></div></div>

--0000000000007b49520649ca47b8--