pg_stat_statements displays only useless 9 queries despite tens of queries are executed per second
2
votes
1
answer
123
views
In PostgreSQL v15.10 I have installed pg_stat_statement extension and \dx shows it is at version 1.10. I have restarted the postgres cluster after install and extension settings setup.
I reset statistics with:
SELECT pg_stat_statements_reset();
Looking into active transactions: SELECT query FROM pg_stat_activity WHERE state = 'active';
I see few tens of SQLs (SELECT, UPDATE...) are executed per second.
But when I check executed SQLs: SELECT query FROM pg_stat_statements;
all I get is only 9 rows:
query
-------------------------------------------------
SET extra_float_digits = 3
COMMIT
SET application_name = 'backend-prod'
ROLLBACK
SET application_name = 'PostgreSQL JDBC Driver'
BEGIN
SET application_name = 'black-prod'
show all
SHOW TRANSACTION ISOLATION LEVEL
It looks like SELECT, UPDATE, DELETE and INSERT statements are not recorded at all.
QUESTION: Why are SQLs not recorded?
Little diagnosing:
1. Display extensions \dx
List of installed extensions
Name | Version | Schema | Description
--------------------+---------+------------+---------------------------------------------------------------------------------------
pg_stat_statements | 1.10 | public | track planning and execution statistics of all SQL statements executed
pgstattuple | 1.5 | public | show tuple-level statistics
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
timescaledb | 2.18.0 | public | Enables scalable inserts and complex queries for time-series data (Community Edition)
2. List extension settings: psql -c "show all" | grep pg_stat_statements
pg_stat_statements.max | 5000
pg_stat_statements.save | on
pg_stat_statements.track | top
pg_stat_statements.track_planning | off
pg_stat_statements.track_utility | on
shared_preload_libraries | timescaledb,pg_stat_statements
Asked by folow
(523 rep)
Feb 27, 2025, 08:32 AM
Last activity: Mar 5, 2025, 07:11 AM
Last activity: Mar 5, 2025, 07:11 AM