Using pg_stat_statements to Track SQL Planning and Execution in PostgreSQL

The pg_stat_statements module in PostgreSQL provides a way to track the planning and execution statistics of all SQL statements executed by the server. It collects data across all databases on the server and offers insights via the views pg_stat_statements and pg_stat_statements_info, along with utility functions pg_stat_statements_reset and pg_stat_statements.

References:

• PostgreSQL Official Documentation
• Citus Data Blog
• Data Sentinel Blog
• Timescale Blog

Step 1: Enable the Extension

To start using pg_stat_statements, you need to enable the extension and configure the PostgreSQL settings:

# Edit the postgresql.conf file
vim postgresql.conf

# Add the following settings
shared_preload_libraries = 'pgaudit,pgauditlogtofile,pglogical,pg_stat_statements'
pg_stat_statements.max = 1000              
pg_stat_statements.track = 'all'
log_min_duration_statement = 30000    # 30 seconds or higher
log_duration = off       # Set to off or on. 'On' creates huge log files based on traffic

Step 2: Create the Extension

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

Step 3: Monitor SQL Performance

Viewing Query Execution Statistics

SELECT * FROM pg_stat_statements;

Resetting Statistics

SELECT pg_stat_statements_reset();

Analyzing Query Execution

SELECT query, calls, total_time, rows, shared_blks_hit, shared_blks_read, shared_blks_dirtied, shared_blks_written, temp_blks_read, temp_blks_written
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 10;

Usage Examples

1. Queries Based on Execution Time

   SELECT queryid, query, total_exec_time, rows
   FROM pg_stat_statements
   ORDER BY total_exec_time DESC
   LIMIT 10;
   

2. Queries Based on Number of Calls

   SELECT queryid, query, calls
   FROM pg_stat_statements
   ORDER BY calls DESC
   LIMIT 10;
   

3. Queries Based on Number of Rows Processed

   SELECT queryid, query, rows
   FROM pg_stat_statements
   ORDER BY rows DESC
   LIMIT 10;
   

4. Queries Based on Shared Blocks Hit

   SELECT queryid, query, shared_blks_hit, shared_blks_read
   FROM pg_stat_statements
   ORDER BY shared_blks_hit DESC
   LIMIT 10;
   

5. Queries Based on Temporary Blocks

   SELECT queryid, query, temp_blks_read, temp_blks_written
   FROM pg_stat_statements
   ORDER BY temp_blks_read DESC
   LIMIT 10;
   

6. Queries Based on Disk Writes

   SELECT queryid, query, blks_read, blks_written
   FROM pg_stat_statements
   ORDER BY blks_written DESC
   LIMIT 10;
   

7. Queries Based on Disk Reads

   SELECT queryid, query, blks_read
   FROM pg_stat_statements
   ORDER BY blks_read DESC
   LIMIT 10;
   

8. Queries Generating Most WAL Records (For Master Database)

   SELECT queryid, query, wal_records
   FROM pg_stat_statements
   ORDER BY wal_records DESC
   LIMIT 10;
   

9. Queries with High I/O Activity

   SELECT queryid, query, rows, blks_read, blks_written
   FROM pg_stat_statements
   ORDER BY (blks_read + blks_written) DESC
   LIMIT 10;
   

10. Queries Causing More Locks

    SELECT queryid, query, locks_hit, locks_missed
    FROM pg_stat_statements
    ORDER BY locks_missed DESC
    LIMIT 10;
    

Reset pg_stat_statements

SELECT pg_stat_statements_reset();

• This will reset the statistics collected by pg_stat_statements.

Conclusion

pg_stat_statements is a powerful tool for analyzing and optimizing SQL performance in PostgreSQL. By tracking query execution statistics, you can identify the most resource-intensive queries, understand their impact on the system, and make informed decisions to improve performance and resource usage.

Remember to regularly monitor these statistics and adjust settings as needed to ensure optimal performance and resource utilization.