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:

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
-- 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_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:

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
-- 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:

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
-- 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.

 

 

 

About MinervaDB Corporation 143 Articles
Full-stack Database Infrastructure Architecture, Engineering and Operations Consultative Support(24*7) Provider for PostgreSQL, MySQL, MariaDB, MongoDB, ClickHouse, Trino, SQL Server, Cassandra, CockroachDB, Yugabyte, Couchbase, Redis, Valkey, NoSQL, NewSQL, Databricks, Amazon Resdhift, Amazon Aurora, CloudSQL, Snowflake and AzureSQL with core expertize in Performance, Scalability, High Availability, Database Reliability Engineering, Database Upgrades/Migration, and Data Security.