What is new with pg_stat_statements in PostgreSQL 17?

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;

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_read
• temp_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

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;

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.

Effective Methods for Tracking PostgreSQL Parameter Changes

Understanding How Plan Cache Based Execution Statistics implemented in PostgreSQL?

How Long-Running Queries Negatively Influence PostgreSQL Execution Plans?