10

I would like to know for "pg_stat_statements" view in postgres. What is the timeframe for the data? Does it shows query executed in last 24 hours or overall queries executed? As the table doesn't contain any timestamp.

Walery Strauch
  • 6,792
  • 8
  • 50
  • 57
Madhu
  • 367
  • 2
  • 7
  • 20

2 Answers2

11

The timeframe of the view provided by pg_stat_statements is from either the last reset (pg_stat_statements_reset) or the time the extension was created, which may be a very long time.

There is logic to expire infrequent statements if the max threshold is reached (5000 on recent Postgres versions), which means that you may not see the full activity if you query the view infrequently.

To work better with this data, you essentially have two options:

1) Call pg_stat_statements_reset() every 24 hours, which means that the query data will only reflect recent activity (ideally you'd keep track of when the reset happened, so you can figure out number of calls per minute, etc)

2) Use a separate monitoring tool that takes snapshots and can keep historic pg_stat_statements statistics

Which one you go with depends on your requirements, though I would usually go with (2) for production systems.

Disclaimer: I'm the author of pganalyze (https://pganalyze.com), a hosted Postgres monitoring tool that also provides historic pg_stat_statement statistics.

Lukas Fittl
  • 321
  • 1
  • 4
  • Thank you so much @LukasFittl I will take a look – Madhu Jun 14 '18 at 20:52
  • Does pg_stat_statements_reset() impact performance? As query stats are used by query planner and in this case it has to build stats again. or PostgreSQL uses stats from somewhere else? – YogeshR Oct 29 '20 at 10:58
  • 1
    @Yogi Not significantly. pg_stat_statements data is not used by the planner (there are other tables for that). The one downside of frequent resets is that it would increase I/O to the query text file that is used by pg_stat_statements, since it has to write the query texts more often. Its probably a bad idea to reset once a second for that reason, but reasonable to do something like once an hour. – Lukas Fittl Oct 30 '20 at 15:46
  • Thanks @LukasFittl , Its really helpful. – YogeshR Nov 02 '20 at 08:25
5

There is no timeframe only a maximum number of statements tracked.

F.28.3. Configuration Parameters

pg_stat_statements.max (integer)

pg_stat_statements.max is the maximum number of statements tracked by the module (i.e., the maximum number of rows in the pg_stat_statements view). If more distinct statements than that are observed, information about the least-executed statements is discarded. The default value is 1000. This parameter can only be set at server start.

sticky bit
  • 36,626
  • 12
  • 31
  • 42
  • Thanks but then does it shows queries executed over the last X hours ? whats value of from and to timeframe? – Madhu Jun 12 '18 at 23:17