New Features in pg_stat_statements PostgreSQL 17
1. Tracking of Nested Statements
PostgreSQL 17 adds the ability to track nested statements executed within functions, procedures, and DO blocks:
- Previously, only top-level statements were tracked
- However, statements executed within PL/pgSQL functions are captured
- Consequently, this provides visibility into previously “hidden” query performance issues
Configuration parameter:
-- Enable nested statement tracking (default: off)
SET pg_stat_statements.track_nested = on;
-- Enable nested statement tracking (default: off)
SET pg_stat_statements.track_nested = on;
-- Enable nested statement tracking (default: off) SET pg_stat_statements.track_nested = on;
2. Improved Memory Usage Tracking in pg_stat_statements
Enhanced memory usage statistics:
- New columns track temporary file usage for statements
- Moreover, this provides better visibility into queries causing excessive disk spills
- As a result, it identify memory-intensive operations more precisely
New columns include:
temp_blks_readtemp_blks_written
3. Enhanced Plan Statistics
More detailed execution plan information:
- Plan-level statistics are now available
- Additionally, there is better correlation between query text and actual execution plans
- Hence, it helps identify plan regression issues more effectively
4. Improved Aggregation Options
New options for how statements are aggregated:
- More flexible grouping of similar queries
- Furthermore, it offers better handling of parameterized queries
- Consequently, reduced cardinality in the statistics view for easier analysis
5. Integration with Extended Query Protocol
Better support for prepared statements and the extended query protocol:
- More accurate tracking of parameterized queries
- In addition, it improves statistics for application frameworks that use prepared statements
- Thus, better visibility into real-world application performance
6. Configuration Improvements in pg_stat_statements
New configuration parameters for finer control:
-- Control maximum number of tracked statements
SET pg_stat_statements.max = 10000; -- Increased default
-- Control tracking level
SET pg_stat_statements.track = 'all'; -- Options: none, top, all, nested
-- Control maximum number of tracked statements
SET pg_stat_statements.max = 10000; -- Increased default
-- Control tracking level
SET pg_stat_statements.track = 'all'; -- Options: none, top, all, nested
-- Control maximum number of tracked statements SET pg_stat_statements.max = 10000; -- Increased default -- Control tracking level SET pg_stat_statements.track = 'all'; -- Options: none, top, all, nested
Usage Example
To take advantage of these new features:
-- Enable the extension (if not already enabled)
CREATE EXTENSION pg_stat_statements;
-- Enable nested statement tracking
ALTER SYSTEM SET pg_stat_statements.track_nested = on;
-- View the enhanced statistics
SELECT query, calls, total_exec_time, mean_exec_time,
temp_blks_read, temp_blks_written
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;
-- Enable the extension (if not already enabled)
CREATE EXTENSION pg_stat_statements;
-- Enable nested statement tracking
ALTER SYSTEM SET pg_stat_statements.track_nested = on;
-- View the enhanced statistics
SELECT query, calls, total_exec_time, mean_exec_time,
temp_blks_read, temp_blks_written
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;
-- Enable the extension (if not already enabled)
CREATE EXTENSION pg_stat_statements;
-- Enable nested statement tracking
ALTER SYSTEM SET pg_stat_statements.track_nested = on;
-- View the enhanced statistics
SELECT query, calls, total_exec_time, mean_exec_time,
temp_blks_read, temp_blks_written
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;
These enhancements make pg_stat_statements significantly more powerful for performance monitoring and troubleshooting in PostgreSQL 17, providing deeper insights into query performance across all levels of your database application.
