Stack Overflow
Stack Overflow

Questions tagged [pg-stat-statements]

The pg_stat_statements PostgreSQL module provides a means for tracking execution statistics of all SQL statements executed by a server.

The module must be loaded by adding pg_stat_statements to shared_preload_libraries in postgresql.conf, because it requires additional shared memory. This means that a server restart is needed to add or remove the module.

The pg_stat_statements View
The statistics gathered by the module are made available via a system view named pg_stat_statements. This view contains one row for each distinct query, database ID, and user ID (up to the maximum number of distinct statements that the module can track).

Functions
pg_stat_statements_reset discards all statistics gathered so far by pg_stat_statements. By default, this function can only be executed by superusers.

Full documentation

35 questions
54
votes
4 answers

pg_stat_statements enabled, but the table does not exist

I've postgresql-9.4 up and running, and I've enabled pg_stat_statements module lately by the help of official documentation. But I'm getting following error upon usage: postgres=# SELECT * FROM pg_stat_statements; ERROR: relation…
Hett
  • 2,023
  • 3
  • 16
  • 19
42
votes
6 answers

DOCKER container with postgres, WARNING: could not open statistics file "pg_stat_tmp/global.stat": Operation not permitted

I have a DOCKER container built from a few different images using a .yml, Dockerfile(s), etc. Everything builds and runs fine so far, except for this one issue that I am seeing mentioned in the title: index-db_1 | 2021-02-22 23:18:33.388 UTC [31]…
SScotti
  • 2,158
  • 4
  • 23
  • 41
36
votes
2 answers

How to remove Postgres extension

I enabled extension pg_stat_statements for PostgreSQL create EXTENSION pg_stat_statements; How can I stop using extension pg_stat_statements in PostgreSQL 9.x? Even original documentation does not contain any examples.
Sergiy Seletskyy
  • 16,236
  • 7
  • 69
  • 80
11
votes
1 answer

Heroku + Apartment PG::Error: ERROR: function pg_stat_statements_reset() does not exist

I use Apartment gem in Rails 4 to support multi-tenancy in Postgres 9.3.3 on Heroku. An error is occurred when Apartment gem creates a new tenant. Deep investigation showed that a schema was created, but no tables inside. Heroku logs showed an…
Sergiy Seletskyy
  • 16,236
  • 7
  • 69
  • 80
10
votes
2 answers

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.
Madhu
  • 367
  • 2
  • 7
  • 20
4
votes
3 answers

Explicitly distinguish similar queries in pg_stat_statements?

pg_stat_statements is (thankfully) pretty smart at normalizing queries to aggregate stats regardless of the arguments you pass. However, I'm facing a situation where a particular query is called from many locations across our application and I would…
rafbm
  • 225
  • 1
  • 8
3
votes
1 answer

how to interpret stddev_time and hit_percent

I launched the following command, showed in this documentation: https://www.postgresql.org/docs/current/pgstatstatements.html SELECT query, calls, total_time, rows, 100.0 * shared_blks_hit / nullif(shared_blks_hit +…
Adrien Ruffie
  • 195
  • 4
  • 16
2
votes
1 answer

In Postgres pg_stat_statements huge difference between max and mean time

In Postgres 14 in pg_stat_statements, why is there a huge time difference between max_exec_time and mean_exec_time? Could you please help?
PravinMahandule
  • 21
  • 2
2
votes
1 answer

Join pg_stat_activity with pg_stat_statements?

I am researching heavy loads within the database when a user runs heavy queries. I got to pg_stat_statements, which shows me the id of the user, the query, and the time it takes to run. On the other hand I found pg_stat_activity that shows me the…
Im X
  • 21
  • 2
2
votes
1 answer

Efficiently inserting a variable number of rows into Postgres with a query that pg_stat_statements can aggregate

To reduce some of the query load on my database, I currently batch multiple queries together into one INSERT query with a variable number of rows. This produces queries like: INSERT INTO example_table (column_1, column_2, ...) VALUES ($1, $2, ...),…
ide
  • 19,942
  • 5
  • 64
  • 106
2
votes
1 answer

Incorrect stats_reset value in pg_stat_bgwriter

I am checking the stats of background process by below command: select * from pg_stat_bgwriter ; But after resetting the stats by command: select pg_stat_reset() ; I am expecting the column stats_reset return the time at which stats reset, but it…
Aman Aggarwal
  • 17,619
  • 9
  • 53
  • 81
1
vote
0 answers

pg_stat_statements same query different queryid

I am setting up a monitoring system for a PostgreSQL database and I am checking pg_stat_statements, however I have noticed this: SELECT queryid, query FROM pg_stat_statements WHERE query LIKE '%UPDATE pgbench_tellers%'; results…
Matteo
  • 2,256
  • 26
  • 42
1
vote
0 answers

How to make pg_stat_statement merge queries with variable number of parameters in a IN close?

I your application have many queries with variable number of elements in a IN like this : SELECT * FROM my_table WHERE id IN ($1, $2, $3, $4, ...) -- number of parameters varies from one to thousands Then pg_stat_statements consider those queries…
bokan
  • 3,601
  • 2
  • 23
  • 38
1
vote
1 answer

How to get pg_stat_user_tables n_tup_ins for timescale's compressed table?

We have a Prometheus Postgres Exporter set up and expect we can get stats of rows inserted into table pg_stat_user_tables: query: | SELECT current_database() datname, schemaname, relname, seq_scan, …
Eljah
  • 4,188
  • 4
  • 41
  • 85
1
vote
1 answer

Details Query in pg_stat_statements

In pg_stat_statements output ("query" field): SELECT * FROM users WHERE id = ? How can I get the details of "?" from query in pg_stat_statements? Can anybody tell me about this, because I want to track if anyone execute some query. Can we also know…
bakekoq
  • 11
  • 2
1
2 3