PG SQL Statistics

Info

PG SQL Statistics will be renamed to SQL Statistics (DB) in the future.

This is DB's own SQL statistics provided by PostgreSQL's pg_stat_statements view. You can check delta values of execution count, execution time, number of rows processed per SQL, block I/O, etc. on the screen without querying the DB directly.

The agent calculates and collects delta values at 1-hour intervals.
Collects up to statements_row_limit based on the statements_order_by agent option (default total_time). (Default: 5,000 records)
DBX agent version 1.6.10 or later is required.

Note

Data collection begins approximately 1 hour after configuration is complete.

Prerequisites

Both DB configuration and Agent configuration are required to display data on the screen.

DB configuration

Install the pg_stat_statements extension

pg_stat_statements is included in PostgreSQL's contrib module. Install the package if it is not already installed.
```
# Red Hat / CentOS
yum install postgresql-contrib

# Debian / Ubuntu
apt-get install postgresql-contrib
```
Configure postgresql.conf

Add pg_stat_statements to shared_preload_libraries. PostgreSQL restart is required after changes.
```
shared_preload_libraries = 'pg_stat_statements'
```
Create the extension module

Create the extension in the target monitoring database.
```
CREATE EXTENSION pg_stat_statements;
```

Verify installation

SELECT * FROM pg_stat_statements LIMIT 1;

Agent configuration

In PostgreSQL, the default value of the statements option is true, so SQL statistics are collected by default without any additional configuration. To disable collection, set statements=false.

Option	Type	Default	Description
`statements`	boolean	`true`	Configure whether to collect SQL statistics.

Advanced options

Option	Type	Default	Description
`statements_interval`	int	`1`	SQL statistics collection interval (in hours). If set to `statements_interval=1`, collection occurs every 1 hour.
`statements_interval_min`	int	`0`	SQL statistics collection interval (in minutes). If set to `statements_interval_min=10`, collection occurs every 10 minutes. This option takes priority over `statements_interval` when set.
`statements_min_row`	int	`10000`	Threshold for collecting SQL statistics data. See collection criteria below.
`statements_row_limit`	int	`5000`	Maximum number of records to collect. Collects up to the top count based on `statements_order_by`.
`statements_order_by`	String	`"total_time"`	Specifies the sort criteria when collecting SQL statistics. (e.g., `statements_order_by=total_time,temp_blks_read`)
`statements_schema`	String	`""`	If the `pg_stat_statements` extension is not installed on the DB specified in the `db` option of whatap.conf, specify the DB name where the extension is installed.

Collection criteria (statements_min_row)

Collects the SQL data if the rows value in pg_stat_statements (total number of rows retrieved or affected by the query) exceeds statements_min_row.

Note

If the amount of SQL collected is too much or too little depending on the DB workload, you can control the collection volume by adjusting the statements_min_row and statements_row_limit values.

Analyzing SQL Statistics (DB)

Basic options

In the top filter area, you can set the query period, target instance, filter conditions, and more.

Time: Select the date and time to query
Instance: Select the target DB instance
Filter: Filter data by condition (AND logic applied for multiple conditions)
Sort order: Select the column to sort data by (e.g., rows, total_time, calls, etc.)
Query count: Set the number of items to display in the table
Search in results: Search by query keyword within the retrieved results
(Column selection): Add/remove columns to display, change order by drag
(Download): Export in CSV format

Column guide

Basic information

Column	Description
`query`	Normalized SQL statement (literal values replaced with `$1`, `$2`, etc.)
`queryid`	Hash code identifying the same normalized query
`usename`	Name of the user who executed the query
`db`	Name of the database where the query was executed
`instance`	DB instance name where the SQL was executed

Execution and time

Column	Description
`calls`	SQL execution count
`total_time`	Total execution time (unit: milliseconds)
`rows`	Total number of rows retrieved or affected by the query

Block I/O (shared buffer)

Column	Description
`shared_blks_hit`	Number of blocks read from shared buffer cache (cache hit)
`shared_blks_read`	Number of shared blocks read from disk (cache miss)
`shared_blks_dirtied`	Number of modified shared blocks
`shared_blks_written`	Number of shared blocks written to disk

Block I/O (local buffer)

Column	Description
`local_blks_hit`	Number of blocks read from local buffer cache
`local_blks_read`	Number of local blocks read from disk
`local_blks_dirtied`	Number of modified local blocks
`local_blks_written`	Number of local blocks written to disk

Temporary blocks

Column	Description
`temp_blks_read`	Number of temporary blocks read
`temp_blks_written`	Number of temporary blocks written

I/O time

Column	Description
`blk_read_time`	Time spent reading blocks (unit: milliseconds). Requires `track_io_timing` to be enabled.
`blk_write_time`	Time spent writing blocks (unit: milliseconds). Requires `track_io_timing` to be enabled.

Table fetch

Column	Description
`table fetch by rowid`	Number of times table rows were directly fetched via index
`table fetch continued row`	Number of times additional blocks were read when a row spans multiple blocks

Data interpretation guide

Identifying SQL to tune

SQL with high total_time and high calls

Frequently executed and slow → Top priority tuning target
SQL with high rows

Processing a large number of rows → Check whether the query is unnecessary
SQL with high total_time / calls (average execution time)

Individual executions are slow → SQL tuning, execution plan check

Cache efficiency check

Cache hit rate = shared_blks_hit / (shared_blks_hit + shared_blks_read)
- If below 90%, consider increasing shared_buffers or checking inefficient queries
SQL with high shared_blks_read: Causing a lot of disk I/O → Index check, consider increasing shared_buffers
SQL with high shared_blks_dirtied: Modifying a lot of data → May cause checkpoint load

Temporary block check

If temp_blks_read and temp_blks_written are high, memory is insufficient during sorting or hash joins, causing disk usage
Consider increasing work_mem

I/O time check

SQL with high blk_read_time: Large disk read wait → Check storage performance or cache efficiency
track_io_timing = on must be set in postgresql.conf to check blk_read_time

Practical usage scenarios

Scenario 1. Finding slow SQL

Sort in descending order by total_time
Check calls of top SQL → SQL that is frequently executed and slow is the top priority tuning target

Scenario 2. Finding SQL with low cache efficiency

Check SQL with high shared_blks_read
Compare with shared_blks_hit to calculate cache hit rate
If cache hit rate is low, check the Plan (execution plan) for the SQL to decide whether to add an index

Scenario 3. Finding SQL with excessive disk I/O

Check SQL with high blk_read_time (requires track_io_timing = on)
→ SQL spending a lot of time on disk reads → Index check, remove unnecessary sequential scans

Additional features

SQL details

Click the SQL column to open the details window. Refer to the following for descriptions of the SQL details window.

DB reference

`pg_stat_statements`

Since the data on this screen is retrieved from the DB's pg_stat_statements view, changes to DB settings or statistics reset directly affect the collected data.

`pg_stat_statements.max`

This parameter determines how many different SQL patterns (queryid) can be stored. The default value is 5000.

-- Example: The following two SQLs are normalized to the same queryid.
SELECT * FROM users WHERE id = 10;
SELECT * FROM users WHERE id = 20;
→ SELECT * FROM users WHERE id = $1

When the number of storable SQL patterns exceeds the limit, SQL with the fewest executions is removed first. In this case, statistics for some SQL may be missing.

-- Check current limit
SHOW pg_stat_statements.max;

-- Check current number of stored SQL patterns
SELECT COUNT(*) FROM pg_stat_statements;

If the number of SQL patterns is approaching the limit, increase the pg_stat_statements.max value. PostgreSQL restart is required after changes.

`pg_stat_statements_reset()` initialization

Resetting statistics with the pg_stat_statements_reset() function deletes all existing data and records new data. Note that the delta values collected by the agent are also reset.

SELECT pg_stat_statements_reset();

Prerequisites​

DB configuration​

Agent configuration​

Analyzing SQL Statistics (DB)​

Basic options​

Column guide​

Basic information​

Execution and time​

Block I/O (shared buffer)​

Block I/O (local buffer)​

Temporary blocks​

I/O time​

Table fetch​

Data interpretation guide​

Identifying SQL to tune​

Cache efficiency check​

Temporary block check​

I/O time check​

Practical usage scenarios​

Scenario 1. Finding slow SQL​

Scenario 2. Finding SQL with low cache efficiency​

Scenario 3. Finding SQL with excessive disk I/O​

Additional features​

SQL details​

DB reference​

pg_stat_statements​

pg_stat_statements.max​

pg_stat_statements_reset() initialization​