Include a timestamp in future versions of pg_stat_statements when when a query entered the cache?

From: Jerry Brenner <jbrenner(at)guidewire(dot)com>
To: pgsql-performance(at)lists(dot)postgresql(dot)org
Subject: Include a timestamp in future versions of pg_stat_statements when when a query entered the cache?
Date: 2023-12-05 14:28:54
Message-ID: CACoKFYQ6DBsKbVLSA9BOGsWsP3U17Le0Cu_QrwGOEk2kPOoyMw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

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

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Julien Rouhaud 2023-12-06 06:45:07 Re: Include a timestamp in future versions of pg_stat_statements when when a query entered the cache?
Previous Message Jerry Brenner 2023-12-05 14:03:24 Re: Does Postgres have consistent identifiers (plan hash value) for explain plans?