pg_stat_statements v7.4

pg_stat_statements is a core PostgreSQL module that provides a means to track planning and execution statistics of all SQL statements executed by the server.

In WarehousePG (WHPG) distributed architecture, query execution is parallelized across the coordinator and multiple segments. The WHPG implementation of this module provides cluster-wide visibility, collecting performance metrics from every segment node. This allows administrators to analyze buffer usage, I/O operations, and execution patterns across the entire distributed cluster rather than just the coordinator.

By extending standard functionality into the parallel execution layer, pg_stat_statements enables:

• Comprehensive distributed tracking across all query executors.
• Unified visibility through specialized system views for both raw and aggregated data.
• Centralized management for resetting performance metrics across the entire cluster simultaneously.

Loading the module

pg_stat_statements is shipped with WarehousePG 7.4 and later. You must load the extension as a shared library before you can use it within your databases.

1. Load the extension as a shared library before you can use it within your databases. Check for existing shared libraries:

   → WrapCopy

   gpconfig -s shared_preload_libraries

2. Use the output of the previous command to enable `pg_stat_statements1, along any other shared libraries, and restart WHPG:

   → WrapCopy

   gpconfig -c shared_preload_libraries -v '<other_libraries>, pg_stat_statements'
   gpstop -ar

3. Create the extension in your database:

   → WrapCopy

   CREATE EXTENSION pg_stat_statements;

Analyzing performance with distributed views

The statistics gathered by the module are made available via two specialized views:

whpg_stat_statements

This view displays raw statistics from the coordinator and all segments in a single table.

SELECT * FROM whpg_stat_statements;

whpg_stat_statements_aggregated

This view provides a unified summary of total resource consumption per query across the entire cluster.

SELECT * FROM whpg_stat_statements_aggregated;

To provide a consolidated view of cluster performance, the metrics in this view are processed as follows:

• Buffer and I/O statistics (shared_blks_*, local_blks_*, temp_blks_*, blk_*_time) are summed across all nodes to show total cluster effort.
• Timing statistics (total_time, min_time, max_time, mean_time) reflect the values recorded at the coordinator level, representing the actual duration experienced by the user.

Managing statistics globally

The pg_stat_statements module includes functions to manage and clear gathered performance data.

whpg_stat_statements_reset

Resets all collected statistics on the coordinator and all segments simultaneously.

-- Global reset for all users and databases
SELECT whpg_stat_statements_reset();

-- Targeted reset for specific user, database, or query
SELECT whpg_stat_statements_reset(userid, dbid, queryid);

Examples

View per-segment buffer usage

Check if specific segments are performing significantly more physical reads than others, which often indicates poor data distribution.

SELECT
    gp_segment_id,
    query,
    shared_blks_read,
    shared_blks_hit,
    blk_read_time
FROM whpg_stat_statements
WHERE query LIKE '%my_table%'
ORDER BY gp_segment_id;

Get aggregated statistics

Identify queries consuming the most total I/O time summed across the entire cluster.

SELECT
    query,
    calls,
    rows,
    shared_blks_hit,
    blk_read_time + blk_write_time AS total_io_time_ms
FROM whpg_stat_statements_aggregated
ORDER BY total_io_time_ms DESC
LIMIT 10;

Reset statistics before benchmarking

To ensure accurate results for a new workload, reset all cluster statistics first.

-- 1. Clear cluster-wide history
SELECT whpg_stat_statements_reset();

-- 2. Run workload
-- [Queries]

-- 3. Analyze aggregated cluster costs
SELECT * FROM whpg_stat_statements_aggregated;