When your ApsaraDB RDS for PostgreSQL instance is under load, not all SQL statements contribute equally to resource consumption. The pg_stat_statements extension tracks execution statistics for every statement your instance runs, letting you rank queries by I/O time, execution time, response jitter, shared memory usage, or temporary space — so you can focus your optimization effort where it has the most impact.
This topic covers how to install the extension, run diagnostic queries for each resource dimension, and reset accumulated statistics.
The SQL Explorer and Audit feature provides an alternative way to analyze query execution. It records SQL statements at the database kernel level — including execution details, execution accounts, and IP addresses — without affecting instance performance. For details, see Use the SQL Explorer and Audit feature.
Install the extension
Run the following statement to enable pg_stat_statements on your instance:
CREATE EXTENSION pg_stat_statements;Once installed, the extension automatically begins collecting execution statistics for all SQL statements.
How pg_stat_statements works
pg_stat_statements normalizes SQL statements by replacing literal filter values with variables, then groups identical statement patterns together. For example, WHERE id = 1 and WHERE id = 2 map to the same entry. This prevents duplicate entries for queries that differ only in their parameter values.
The extension exposes its data through a view with the same name. The view records:
Execution statistics — call count, total/min/max/mean execution time (in milliseconds), and standard deviation of execution time. The standard deviation (
stddev_time) reflects response jitter.Shared buffer usage — cache hits, reads (misses), dirty blocks generated, and dirty blocks written.
Local buffer usage — cache hits, reads, dirty blocks generated, and dirty blocks written.
Temp buffer usage — temp blocks read and written.
Block I/O durations — time spent reading and writing blocks (requires
track_io_timingto be enabled; otherwise reported as zero).
View columns
The following table describes all columns in the pg_stat_statements view.
| Column | Type | Description |
|---|---|---|
userid | oid | Object identifier (OID) of the user who executed the statement. References pg_authid.oid. |
dbid | oid | OID of the database in which the statement was executed. References pg_database.oid. |
queryid | bigint | Internal hash code computed from the statement's parse tree. |
query | text | Text of a representative statement. |
calls | bigint | Number of times the statement was executed. |
total_time | double precision | Total execution time across all calls, in milliseconds. |
min_time | double precision | Minimum execution time for a single call, in milliseconds. |
max_time | double precision | Maximum execution time for a single call, in milliseconds. |
mean_time | double precision | Mean execution time per call, in milliseconds. |
stddev_time | double precision | Population standard deviation of execution time, in milliseconds. |
rows | bigint | Total number of rows retrieved or affected by the statement. |
shared_blks_hit | bigint | Total shared block cache hits. |
shared_blks_read | bigint | Total shared blocks read from disk. |
shared_blks_dirtied | bigint | Total shared blocks dirtied. |
shared_blks_written | bigint | Total shared blocks written. |
local_blks_hit | bigint | Total local block cache hits. |
local_blks_read | bigint | Total local blocks read from disk. |
local_blks_dirtied | bigint | Total local blocks dirtied. |
local_blks_written | bigint | Total local blocks written. |
temp_blks_read | bigint | Total temp blocks read. |
temp_blks_written | bigint | Total temp blocks written. |
blk_read_time | double precision | Total time spent reading blocks, in milliseconds. Requires track_io_timing. |
blk_write_time | double precision | Total time spent writing blocks, in milliseconds. Requires track_io_timing. |
Find top resource-consuming queries
Each query below selects from pg_stat_statements and returns the top five statements by a specific metric. Run these queries to identify candidates for optimization.
Highest I/O consumption
High I/O time often indicates missing indexes, full table scans, or queries that retrieve far more data than needed.
Top 5 by average I/O time per call (best for finding queries that are expensive each time they run):
SELECT userid::regrole, dbid, query
FROM pg_stat_statements
ORDER BY (blk_read_time + blk_write_time) / calls DESC
LIMIT 5;Top 5 by total I/O time across all calls (best for finding queries with the largest cumulative impact):
SELECT userid::regrole, dbid, query
FROM pg_stat_statements
ORDER BY (blk_read_time + blk_write_time) DESC
LIMIT 5;Slowest execution time
A high mean execution time points to queries that are individually slow. These are good candidates for query plan analysis with EXPLAIN ANALYZE.
Top 5 by mean execution time per call:
SELECT userid::regrole, dbid, query
FROM pg_stat_statements
ORDER BY mean_time DESC
LIMIT 5;Top 5 by total execution time across all calls (useful for identifying high-frequency queries that add up):
SELECT userid::regrole, dbid, query
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 5;Highest response jitter
A high standard deviation in execution time (stddev_time) indicates unpredictable response times. This often signals lock contention, cache eviction pressure, or intermittent resource competition.
SELECT userid::regrole, dbid, query
FROM pg_stat_statements
ORDER BY stddev_time DESC
LIMIT 5;Highest shared memory usage
Queries with high shared buffer consumption hold large portions of the buffer pool, which can crowd out other queries and increase cache misses across the instance.
SELECT userid::regrole, dbid, query
FROM pg_stat_statements
ORDER BY (shared_blks_hit + shared_blks_dirtied) DESC
LIMIT 5;Highest temporary space usage
Queries that write temporary blocks are performing sorts or hash joins that exceed work_mem. Increasing work_mem at the session level or rewriting the query to reduce result set size can eliminate the spill.
SELECT userid::regrole, dbid, query
FROM pg_stat_statements
ORDER BY temp_blks_written DESC
LIMIT 5;Reset statistics
pg_stat_statements accumulates statistics since the extension was installed or last reset. To establish a clean baseline — for example, after deploying a query optimization — reset the counters:
SELECT pg_stat_statements_reset();Run this periodically to prevent historical data from obscuring current performance patterns.