What is the timeframe for pg_stat_statements

Question

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.

Topping

Answer 1

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.

Deem

Thank you so much @LukasFittl I will take a look – Topping

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? – Ainu

@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. – Deem

Thanks @LukasFittl , Its really helpful. – Ainu

Anyone knows if it is working with postgres 15.2? my lineage is not working. i've created the pg_stat_statements and my user have the required permissions. – Amphitryon

@JonatasDelatorre Whilst pg_stat_statements has been improved over the years (with additional data being tracked), the core mechanism still works the same in Postgres 15. Did you forget to restart the server after adding "pg_stat_statements" to your "shared_preload_libraries" setting? That can cause it to not work. Additionally check that the "pg_stat_statements.track" setting is set to either "top" or "all" (and not to "none", which turns it off). – Deem

Answer 2

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.

Venturous

Thanks but then does it shows queries executed over the last X hours ? whats value of from and to timeframe? – Topping

Answer 3

PostgreSQL 14 in 2020 adds pg_stat_statements_info view.

https://www.depesz.com/2020/12/21/waiting-for-postgresql-14-pg_stat_statements-track-time-at-which-all-statistics-were-last-reset/

PostgreSQL 14+

SELECT stats_reset FROM pg_stat_statements_info;

stats_reset          
 2020-12-20 12:06:02.099943+01
(1 ROW)

This will show the last reset time for the stats when using SELECT pg_stat_statements_reset();

All other answers are applicable and useful advice. Statements will get evicted depending on pg_stat_statements.max. Doing regular resets, or using a monitoring tool can allow you to analyze more recent activity over a set timeframe - like the deployment of a new version of application code.

Enate