Re: timestamp for query in pg_stat_statements

On Sun, Mar 6, 2022 at 6:23 PM Julien Rouhaud <rjuju123(at)gmail(dot)com> wrote:

> 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?
>
Did I mention rewriting ?
As you said below, adding index is one way of tuning which doesn't involve
rewriting.

Please also note that the person tuning the query may be different from the
person writing the query.
So some information in pg_stat_statements (or related table) is needed to
disambiguate.

> > 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).
>