Re: timestamp for query in pg_stat_statements

On Sun, Mar 06, 2022 at 12:37:00PM -0800, Zhihong Yu wrote:
> The current design of pg_stat_statements doesn't have the concept of
> observation.
>
> By observation I mean scenarios where pg_stat_statements is read by people
> doing performance tuning.
>
> Here is one example (same query, q, is concerned).
> At t1, q is performed, leaving one row in pg_stat_statements with mean_time
> of 10.
> At t2, operator examines pg_stat_statements and provides some suggestion
> for tuning q (which is carried out).
> At t3, q is run again leaving the row with mean_time of 9.
> Now with two rows for q, how do we know whether the row written at t3 is
> prior to or after implementing the suggestion made at t2 ?

Well, if pg_stat_statements is read by people doing performance tuning
shouldn't they be able to distinguish which query text is the one they just
rewrote?

> Using other tools, a lot of the information in pg_stat_statements would be
> duplicated to distinguish the counters recorded w.r.t. tuning operation.

Yes, which is good. Your example was about rewriting a query, but what about
other possibilities like creating an index, changing hash_mem_multiplier...?
You won't get a new record and the mean_time will mostly be useless.

If you take regular snapshot, then you will be able to compute the mean_time
for each interval, and that will answer bot this scenario and the one in your
example (since the 2nd row won't exist in the earlier snapshots).