Sample Header Ad - 728x90

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