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: yudhi s <learnerdatabase99@gmail.com>
Date: Mon, 2 Feb 2026 09:47:06 +0530
Message-ID: <CAEzWdqf+b+7JDRNzJVqWKyF8N8Aa2CZC5B_o1xZLDWozqV-RUA@mail.gmail.com>
Subject: Re: Top -N Query performance issue and high CPU usage
To: pgsql-general@lists.postgresql.org, Ron Johnson <ronljohnsonjr@gmail.com>, 
	hjp-pgsql@hjp.at
Content-Type: multipart/alternative; boundary="0000000000002f20490649cf99a3"
Archived-At: <https://www.postgresql.org/message-id/CAEzWdqf%2Bb%2B7JDRNzJVqWKyF8N8Aa2CZC5B_o1xZLDWozqV-RUA%40mail.gmail.com>
Precedence: bulk

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

On Mon, Feb 2, 2026 at 3:17=E2=80=AFAM Peter J. Holzer <hjp-pgsql@hjp.at> w=
rote:

>
> 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.
>
>
Thank you so much. I need to get back on the exact number of such queries
which can hit the database. However, as 1000 of users will be there, so the
possibility of all logging into the system on the same page at same time
needs to be found out. Will double check on this.

However,  when you said caching :- The results on the base tables are going
to be ~30-50 million. This landing page has filters on it so it may be of
30+ different combinations based on the user's choice. So do you suggest ,
we will populate the base data in a materialized view(named like "landing
page data") which we can refresh (maybe once in ~5 minutes behind the
scenes) and then that can be queried in the landing page directly. And we
can have suitable indexes created on the materialized view based on the
dynamic filter criteria?

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

<div dir=3D"ltr"><div><br></div><br><div class=3D"gmail_quote gmail_quote_c=
ontainer"><div dir=3D"ltr" class=3D"gmail_attr">On Mon, Feb 2, 2026 at 3:17=
=E2=80=AFAM Peter J. Holzer &lt;<a href=3D"mailto:hjp-pgsql@hjp.at">hjp-pgs=
ql@hjp.at</a>&gt; wrote:<br></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"><br>
However, maybe you didn&#39;t mean that. There are relatively few<br>
applications where thousands of users log in within a second. Maybe you<br>
just meant that there would be thousands of users logged in in total. If<br=
>
so, how many simultaneus queries do you really expect?<br>
<br>
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><br>=
</blockquote><div><br></div><div>Thank you so much. I need to get back on t=
he exact number of such queries which can hit the database. However, as 100=
0 of users will be there, so the possibility of all logging into the system=
 on the same page at same time needs to be found out. Will double check on =
this.</div><div><br></div><div>However,=C2=A0 when you said caching :- The =
results on the base tables are going to be ~30-50 million. This landing pag=
e has filters on it so it may be of 30+ different combinations based on the=
 user&#39;s choice. So do you suggest , we will populate the base data in a=
 materialized view(named like &quot;landing page data&quot;) which we can r=
efresh (maybe once in ~5 minutes behind the scenes) and then that can=C2=A0=
be queried in the landing page directly. And we can have suitable indexes c=
reated on the materialized view based on the dynamic filter criteria?</div>=
</div></div>

--0000000000002f20490649cf99a3--