Date: Sun, 1 Feb 2026 22:47:11 +0100
From: "Peter J. Holzer" <hjp-pgsql@hjp.at>
To: pgsql-general@lists.postgresql.org
Subject: Re: Top -N Query performance issue and high CPU usage
Message-ID: <vecavrvgzoxkks66nw2gvt3vot5lwbcm7f65iopgjbw72v2lc6@qd5leh3coj7g>
Mail-Followup-To: pgsql-general@lists.postgresql.org
References: <CAEzWdqd0SPkZMYNaAbERdgczkfQqLmNV5JBMmF-F9s7KjxJ0gw@mail.gmail.com>
 <fde67103-e00e-460e-b2ec-eb539809e998@aklaver.com>
 <CAEzWdqd6LAHs+FiFeJLqDTS-QBLq6+foE1-mgBC9AXVpFmVnZg@mail.gmail.com>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha512;
	protocol="application/pgp-signature"; boundary="5ecko43p7imale2p"
Content-Disposition: inline
In-Reply-To: <CAEzWdqd6LAHs+FiFeJLqDTS-QBLq6+foE1-mgBC9AXVpFmVnZg@mail.gmail.com>
Archived-At: <https://www.postgresql.org/message-id/vecavrvgzoxkks66nw2gvt3vot5lwbcm7f65iopgjbw72v2lc6%40qd5leh3coj7g>
Precedence: bulk


--5ecko43p7imale2p
Content-Type: text/plain; protected-headers=v1; charset=iso-8859-1
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable
Subject: Re: Top -N Query performance issue and high CPU usage
MIME-Version: 1.0

On 2026-02-01 01:16:56 +0530, yudhi s wrote:
> Thank you.=A0
>=20
>=20
>     1) Without even looking at the plan I'm going to say 2-VCPU and 16GB =
RAM
>     and is insufficient resources for what you want to do.
>=20
>=20
>=20
> Can you please explain a bit in detail, how much minimum VCPU and RAM wil=
l be
> enough resources to suffice this requirement? and you normally do that
> calculation?

You wrote:

| This query is supposed to allow thousands of users to hit this same
| query at the first landing page at the same time.

If you meant that literally, you would need thousands of cores to handle
those thousands of simultaneous queries and enough RAM for thousands of
sessions, each performing a rather complex query. So possibly hundreds
of maybe even thousands of gigabytes, not 16.

However, maybe you didn't mean that. There are relatively few
applications where thousands of users log in within a second. Maybe you
just meant that there would be thousands of users logged in in total. If
so, how many simultaneus queries do you really expect?

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.

        hjp

--=20
   _  | Peter J. Holzer    | Story must make more sense than reality.
|_|_) |                    |
| |   | hjp@hjp.at         |    -- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |       challenge!"

--5ecko43p7imale2p
Content-Type: application/pgp-signature; name="signature.asc"

-----BEGIN PGP SIGNATURE-----

iQIzBAABCgAdFiEETtJbRjyPwVTYGJ5k8g5IURL+KF0FAml/ydgACgkQ8g5IURL+
KF3hsw//ex201AG4G0ifNXfqv/CplwVWkbXbHxQCojXNXtWYgnZV6Xsx/tm7wG25
ftUNQIyhgw0draG6USfDCGisITIAFnumSjaTy2E4JPhLciErgyozS228ElBQBxEu
ACQohbUPuN1dGBd9DxfMh19nzjIBCPbwYn12C93IufAUJJtj1EMUdehjoNDFtm6C
+2ijsJqS0vcgO7kBcUOFXqrBpfBqTC8i+pB+DYPNDuDl9tPQu7J9zkYb4vQuC49P
qEr72WjiwZGRPQYnOWdw6Nmmb736cjJ0Zl84pktK7klU8le6rg77AwX1IOZeczLc
qlqdldLLjC6jbbes6DkJPKEUYJ/Z2vWP1/PUjgCo82QNEbBY0oNLhPuRubGPxNwN
rKEA6f9zbMXOov1dXHU/9a22O/Lr+zhG7bbU6BM6f2DT2J6gO0fSgxnh8FrXvlYY
7oBU7bVETWNzeQH/xlwM2SD/eoep6xrvAzN3n1P5ML6JUm+IcODx7ugEsEOme+pU
C02QkR/j74HT7yobFyeWo+GRUJSTJTrNdq2vWhhvyi9sJpppn/KZJPxHtSBexl9F
ghZn/wBJsl+KarfUTzU+6r7vmZm+0/v8kzw70bU05pP+OQREWbAMBsAeaLTWaHcy
NNRqt38dUAnqRWCDbqVD5BsMs2d83Gh9xybh1I8OJAgI+neZ0wQ=
=WyhA
-----END PGP SIGNATURE-----

--5ecko43p7imale2p--