pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedInclude a timestamp in future versions of pg_stat_statements when when a query entered the cache?
2+ messages / 2 participants
[nested] [flat]
* Include a timestamp in future versions of pg_stat_statements when when a query entered the cache?
@ 2023-12-05 14:28 Jerry Brenner <jbrenner@guidewire.com>
2023-12-06 06:45 ` Re: Include a timestamp in future versions of pg_stat_statements when when a query entered the cache? Julien Rouhaud <rjuju123@gmail.com>
0 siblings, 1 reply; 2+ messages in thread
From: Jerry Brenner @ 2023-12-05 14:28 UTC (permalink / raw)
To: pgsql-performance@lists.postgresql.org
It would be helpful if a timestamp column was added to pg_stat_statements
to denote when a query entered the view. This would make it easier to tell
how frequently a query is being executed (100,000 times since a specific
timestamp vs 100,000 times since the execution stats were last reset.)
I realize that Postgres is different from SQL Server. SQL Server has
timestamps for both the time that the query entered the cache and the last
execution. I assume that adding and maintaining a timestamp for the last
execution would be more difficult and expensive. Having that additional
information makes it possible for us to find queries that were executed
during a time range that corresponds to a batch process, queries executed
an abnormally high number of times in a short period of time, ...
We are taking hourly snapshot of pg_stat_statements and storing the
information in a database table so we can analyze the database activity in
a given interval. We are calculating and storing the deltas as part of
that process. We have to make certain simplifying assumptions due to the
lack of this type of timestamp. (We can live with these assumptions, but
having the additional timestamp(s) would increase the value of the
information.):
- If the number of executions increased since the last snapshot, then
use the difference as the delta. (We assume that the statement was not
flushed from the cache and then reloaded later in the interval.)
- If the number of executions remained the same since the last snapshot,
then the query was not executed in the interval. (We assume that the
statement was not flushed from the cache and then reloaded later in the
interval.)
- If the number of executions decreased since the last snapshot, then
the was flushed from the cache at some unknown point in the interval.
Thanks,
Jerry
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Include a timestamp in future versions of pg_stat_statements when when a query entered the cache?
2023-12-05 14:28 Include a timestamp in future versions of pg_stat_statements when when a query entered the cache? Jerry Brenner <jbrenner@guidewire.com>
@ 2023-12-06 06:45 ` Julien Rouhaud <rjuju123@gmail.com>
0 siblings, 0 replies; 2+ messages in thread
From: Julien Rouhaud @ 2023-12-06 06:45 UTC (permalink / raw)
To: Jerry Brenner <jbrenner@guidewire.com>; +Cc: pgsql-performance@lists.postgresql.org
Hi,
On Tue, Dec 05, 2023 at 06:28:54AM -0800, Jerry Brenner wrote:
> It would be helpful if a timestamp column was added to pg_stat_statements
> to denote when a query entered the view. This would make it easier to tell
> how frequently a query is being executed (100,000 times since a specific
> timestamp vs 100,000 times since the execution stats were last reset.)
This was actually done a few weeks ago, and will be available with pg 17. You
can see the 2 new timestamp counters (one for the whole record, one for the
minmax counters only) documentation at
https://www.postgresql.org/docs/devel/pgstatstatements.html
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2023-12-06 06:45 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-12-05 14:28 Include a timestamp in future versions of pg_stat_statements when when a query entered the cache? Jerry Brenner <jbrenner@guidewire.com>
2023-12-06 06:45 ` Julien Rouhaud <rjuju123@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox