MIME-Version: 1.0
From: veem v <veema0000@gmail.com>
Date: Mon, 7 Oct 2024 00:41:24 +0530
Message-ID: <CAB+=1TWE48wbtY8Sa1T2vpWHkaQ7wcodBnx8WNyTRB5D=7j1Mw@mail.gmail.com>
Subject: Question on pg_stat* views
To: pgsql-general <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="0000000000004b26030623d3ac6a"
Archived-At: <https://www.postgresql.org/message-id/CAB%2B%3D1TWE48wbtY8Sa1T2vpWHkaQ7wcodBnx8WNyTRB5D%3D7j1Mw%40mail.gmail.com>
Precedence: bulk

--0000000000004b26030623d3ac6a
Content-Type: text/plain; charset="UTF-8"

Hi,
As per my understanding , the pg_stat_statements stores the aggregated
statistics of the query execution history(with almost near real time
executions) and the max number of statements or queries it can store
depends on the pg_stat_statement.max value(which I am seeing as ~5000 in
pg_settings). I have below questions,

1)Doing a count(*) on pg_stat_statements giving ~4818. But still
pg_stat_statements_info showing ~1990 as "dealloc" which means there are
more sql queries coming up and they are getting flushed out of the
pg_stat_statements, so does it mean that we should increase the
pg_stat_statement.max to further higher value?

2)The stats_reset column in pg_stat_statements_info view is showing as 16th
august , so does it mean that, whatever query stats are getting logged in
the pg_stat_statements, those are the aggregated stats for all the
executions that have happened since the day 16th august till today? Also as
we have not done any "stats reset" manually , so does it happen
automatically when the DB instance restarts or with any other database
events?

3)As pg_stat_statements holds the aggregated stats of all the execution for
a particular sql query ,so it's not easy to identify if in the past at some
point in time the same query suffered and thus went for high response time.
So to debug such performance  issues scenarios , is it advisable to insert
the records from this pg_stat* views to another table manually periodically
through a cron job?

Regards
Veem

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

<div dir=3D"ltr">Hi,<div>As per my understanding , the pg_stat_statements s=
tores the aggregated statistics of the query execution history(with almost =
near real time executions) and the max number of statements or queries it c=
an store depends on the pg_stat_statement.max value(which I am seeing as ~5=
000 in pg_settings). I have below questions,<br><br>1)Doing a count(*) on p=
g_stat_statements giving ~4818. But still pg_stat_statements_info showing ~=
1990 as &quot;dealloc&quot; which means there are more sql queries coming u=
p and they are getting flushed out of the pg_stat_statements, so does it me=
an that we should increase the pg_stat_statement.max to further higher valu=
e?<br><br>2)The stats_reset column in pg_stat_statements_info view is showi=
ng as 16th august , so does it mean that, whatever query stats are getting =
logged in the pg_stat_statements, those are the aggregated stats for all th=
e executions that have happened since the day 16th august till today? Also =
as we have not done any &quot;stats reset&quot; manually , so does it happe=
n automatically when the DB instance restarts or with any other database ev=
ents?</div><div><br>3)As pg_stat_statements holds the aggregated stats of a=
ll the execution for a particular sql query ,so it&#39;s not easy to identi=
fy if in the past at some point in time the same query suffered and thus we=
nt for high response time. So to=C2=A0debug such performance=C2=A0 issues s=
cenarios , is it advisable to insert the records from this pg_stat* views t=
o another table manually periodically through a cron job?<br><div><br></div=
><div>Regards</div><div>Veem</div></div></div>

--0000000000004b26030623d3ac6a--