PostgreSQL Statistics Settings Query Guide

PostgreSQL Statistics Settings Query Guide

This PostgreSQL query retrieves all statistics collection configuration settings from the pg_settings system view. These parameters control what runtime data PostgreSQL collects about query execution, table activity, and performance, feeding into the pg_stat_* monitoring views.

Purpose and Overview

PostgreSQL's statistics collector gathers data about database activity and makes it available through system views like pg_stat_activity, pg_stat_user_tables, and pg_stat_statements. If the underlying collection settings are disabled, those views return incomplete or empty data.

This query surfaces all statistics settings so administrators can verify that monitoring is properly enabled and understand the performance cost of each collection option.

SQL Script

 1SELECT
 2    name,
 3    setting,
 4    -- category,
 5    vartype,
 6    short_desc,
 7    -- enumvals,
 8    source,
 9    min_val,
10    max_val,
11    boot_val,
12    reset_val,
13    -- not available on PostgreSQL < 9.5
14    pending_restart
15FROM
16    pg_settings
17WHERE
18    -- Statistics / Query and Index Statistics Collector
19    -- Statistics / Monitoring
20    category ILIKE '%Statistics%';

Code Breakdown

Filter Condition

1WHERE category ILIKE '%Statistics%'

Matches two subcategory groups:

• Statistics / Query and Index Statistics Collector — settings that control what the statistics collector tracks
• Statistics / Monitoring — settings related to server process monitoring output

Selected Columns

name: The configuration parameter name such as track_activities or track_counts.

setting: The current active value (on/off, none/pl/all, etc.).

vartype: The data type (bool, integer, enum).

short_desc: A brief description of what the parameter controls.

source: Where the value originates — default, configuration file, or session-level override.

min_val / max_val: Allowed range for numeric parameters.

boot_val: Compiled-in default value.

reset_val: Value used if the parameter is reset.

pending_restart: Whether a server restart is needed for a change to take effect.

Key Statistics Parameters

Query and Index Statistics Collector

track_activities — Enables tracking of the current command being executed by each backend, along with its start time. Required for pg_stat_activity to show query text and state. Disabling saves a small amount of overhead but blinds all session monitoring.

track_activity_query_size — Maximum number of bytes stored for the current query text per backend in pg_stat_activity. Increase this if query text is being truncated in monitoring output.

track_counts — Enables counting of rows inserted, updated, deleted, and fetched per table. Required for autovacuum to determine when tables need vacuuming and for pg_stat_user_tables row activity columns.

track_io_timing — Enables timing of I/O read and write calls. Provides data for the blk_read_time and blk_write_time columns in pg_stat_database and pg_stat_statements. Has a measurable overhead on systems where the OS clock is slow to query.

track_functions — Controls function call tracking. Options are none (disabled), pl (procedural languages only), or all (including SQL and C functions). Populates pg_stat_user_functions.

track_wal_io_timing — Enables timing of WAL I/O operations (PostgreSQL 14+). Feeds WAL timing data into pg_stat_wal.

Monitoring (Planner Statistics Logging)

log_executor_stats — Logs executor performance statistics to the server log for each query. High overhead — for diagnostics only.

log_parser_stats — Logs parser performance statistics to the server log for each query. High overhead — for diagnostics only.

log_planner_stats — Logs planner performance statistics to the server log for each query. High overhead — for diagnostics only.

log_statement_stats — Logs combined total statement statistics to the server log. Cannot be enabled simultaneously with any of the individual component stats settings above.

Monitoring Views Fed by These Settings

Practical Applications

Verify Monitoring Prerequisites

Before relying on pg_stat_activity or autovacuum statistics, confirm track_activities and track_counts are both on. If either is off, monitoring dashboards and autovacuum decisions will be based on incomplete data.

pg_stat_statements Setup

pg_stat_statements requires the extension to be loaded, but also benefits from track_io_timing = on to populate I/O timing columns. This query confirms whether I/O timing is enabled.

Function Performance Analysis

Enable track_functions = 'all' to identify expensive stored procedures via pg_stat_user_functions. Return it to none after analysis if overhead is a concern.

Diagnostic Logging

The log_planner_stats and log_executor_stats settings are useful for deep-dive query analysis in a test environment. Always confirm they are off in production as the log volume and overhead can be significant.

Version Compatibility

Requires PostgreSQL 9.5 or later due to the pending_restart column. track_wal_io_timing requires PostgreSQL 14 or later. Tested on PostgreSQL 9.5, 9.6, 10.x, 11.x, 12.x, and 13.0.

Best Practices

Keep track_activities and track_counts on: The overhead is minimal and the monitoring value is high. Disabling these settings is rarely worth the tradeoff.

Enable track_io_timing on fast storage: On SSDs where the clock call overhead is low, enabling track_io_timing provides valuable query I/O breakdown data. On spinning disk servers, evaluate the overhead first.

Never run log_*_stats in production: These settings can flood the log and significantly degrade performance. Use them only in isolated test sessions.

Increase track_activity_query_size for long queries: If monitoring tools show truncated SQL in pg_stat_activity, increase this setting to capture full query text. It requires a server restart.

References

• PostgreSQL Documentation - Run-Time Statistics - Official reference for all statistics collection configuration parameters
• PostgreSQL Documentation - pg_stat_activity - Reference for the pg_stat_activity monitoring view
• PostgreSQL Documentation - pg_settings View - Detailed reference for the pg_settings catalog view
• Source SQL Script - postgres_settings_statistics.sql - Original script by Hari Sekhon