Thread: Include a timestamp in future versions of pg_stat_statements when when a query entered the cache?
Include a timestamp in future versions of pg_stat_statements when when a query entered the cache?
From
Jerry Brenner
Date:
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
Re: Include a timestamp in future versions of pg_stat_statements when when a query entered the cache?
From
Julien Rouhaud
Date:
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