Madhu
Madhu

Reputation: 429

What is the timeframe for pg_stat_statements

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.

Upvotes: 13

Views: 11312

Answers (3)

Justin Lowen
Justin Lowen

Reputation: 41

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.

Upvotes: 4

Lukas Fittl
Lukas Fittl

Reputation: 361

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.

Upvotes: 14

sticky bit
sticky bit

Reputation: 37472

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.

Upvotes: 5

Related Questions