SQL-PolarDB(PolarDB)-阿里云帮助中心

PolarDB for PostgreSQL (Compatible with Oracle) provides three views for monitoring SQL performance. Query these views to identify slow queries, high-resource statements, and execution bottlenecks.

View	Purpose	Requires
`pg_stat_statements`	Tracks execution time, row counts, and block I/O per SQL statement	`pg_stat_statements` plug-in
`polar_stat_sql`	Extends `pg_stat_statements` with CPU usage, lock waits, query plan node stats, and storage I/O metrics	`polar_stat_sql` plug-in
`polar_stat_query_count`	Aggregates execution counts by SQL type (DQL, DML, DDL, DCL)	`polar_stat_sql` plug-in

Start with pg_stat_statements for general query performance analysis. Use polar_stat_sql when you need deeper diagnostics—CPU time, lock contention, or I/O latency at the statement level. Use polar_stat_query_count for a high-level workload overview.

pg_stat_statements

pg_stat_statements records execution statistics for every distinct SQL statement, normalized by query structure. Use it to find slow queries and high-frequency statements.

Prerequisites

Before querying this view, create the plug-in:

CREATE EXTENSION pg_stat_statements;

Parameters

Parameter	Type	Description
`userid`	oid	Object identifier (OID) of the user who ran the statement
`dbid`	oid	OID of the database where the statement ran
`queryid`	bigint	Internal hash code derived from the statement's parse tree
`query`	text	Normalized text of the SQL statement
`calls`	bigint	Number of times the statement was executed
`total_time`	double precision	Total execution time. Unit: milliseconds
`min_time`	double precision	Shortest single execution time. Unit: milliseconds
`max_time`	double precision	Longest single execution time. Unit: milliseconds
`mean_time`	double precision	Average execution time. Unit: milliseconds
`stddev_time`	double precision	Population standard deviation of execution time. Unit: milliseconds
`rows`	bigint	Total rows retrieved or affected
`shared_blks_hit`	bigint	Total shared-block cache hits
`shared_blks_read`	bigint	Total shared blocks read by the statement
`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 by the statement
`local_blks_dirtied`	bigint	Total local blocks dirtied
`local_blks_written`	bigint	Total local blocks written
`temp_blks_read`	bigint	Total temporary blocks read
`temp_blks_written`	bigint	Total temporary blocks written
`blk_read_time`	double precision	Total time spent reading blocks. Unit: milliseconds. Non-zero only when `track_io_timing` is set to `on`
`blk_write_time`	double precision	Total time spent writing blocks. Unit: milliseconds. Non-zero only when `track_io_timing` is set to `on`

Query examples

Find the slowest queries by average execution time:

SELECT
    query,
    calls,
    mean_time AS avg_ms,
    max_time AS max_ms
FROM pg_stat_statements
ORDER BY mean_time DESC
LIMIT 10;

Find the most frequently executed queries:

SELECT
    query,
    calls,
    total_time,
    mean_time AS avg_ms
FROM pg_stat_statements
ORDER BY calls DESC
LIMIT 10;

Find queries with the highest disk read volume (potential index candidates):

SELECT
    query,
    calls,
    shared_blks_read,
    shared_blks_hit,
    100.0 * shared_blks_hit / NULLIF(shared_blks_hit + shared_blks_read, 0) AS cache_hit_pct
FROM pg_stat_statements
ORDER BY shared_blks_read DESC
LIMIT 10;

polar_stat_sql

polar_stat_sql extends pg_stat_statements with additional metrics: CPU usage, memory, OS-level I/O, query plan node statistics, lock wait times, and storage layer I/O. Use it when pg_stat_statements points to a problem but you need to pinpoint the root cause.

Prerequisites

Before querying this view, create the plug-in:

CREATE EXTENSION polar_stat_sql;

Parameters

Identity

Parameter	Type	Description
`queryid`	bigint	Query ID, matches `queryid` in `pg_stat_statements`
`datname`	name	Database name
`rolname`	name	Username

CPU and memory

Parameter	Type	Description
`user_time`	double	Time spent in user mode
`system_time`	double	Time spent in system (kernel) mode
`minflts`	bigint	Number of recycled pages or minor faults
`majflts`	bigint	Number of major page faults
`nswaps`	bigint	Number of page swaps

OS-level I/O

Parameter	Type	Description
`reads`	bigint	Bytes read from disk
`reads_blks`	bigint	Blocks read from disk
`writes`	bigint	Bytes written to disk
`writes_blks`	bigint	Blocks written to disk
`io_open_num`	bigint	Number of file open operations
`io_seek_count`	bigint	Number of file seek operations
`io_open_time`	double	Time spent on file open operations. Unit: microseconds
`io_seek_time`	double	Time spent on file seek operations. Unit: microseconds

IPC and context switches

Parameter	Type	Description
`msgsnds`	bigint	IPC messages sent
`msgrcvs`	bigint	IPC messages received
`nsignals`	bigint	Semaphores received
`nvcsws`	bigint	Voluntary context switches
`nivcsws`	bigint	Involuntary context switches

Query plan node statistics

All rows, time, and count metrics below correspond to operations in the query execution plan.

Parameter	Type	Description
`scan_rows`	double	Rows read by scan node operations
`scan_time`	double	Time spent on scan node operations
`scan_count`	bigint	Number of scan node operations
`join_rows`	double	Rows read by join node operations
`join_time`	double	Time spent on join node operations
`join_count`	bigint	Number of join node operations
`sort_rows`	double	Rows read by sort node operations
`sort_time`	double	Time spent on sort node operations
`sort_count`	bigint	Number of sort node operations
`group_rows`	double	Rows read by group node operations
`group_time`	double	Time spent on group node operations
`group_count`	bigint	Number of group node operations
`hash_rows`	double	Rows read by hash node operations
`hash_memory`	bigint	Memory used by hash node operations. Unit: bytes
`hash_count`	bigint	Number of hash node operations

Parsing and planning time

Parameter	Type	Description
`parse_time`	double	Time spent parsing the SQL statement
`analyze_time`	double	Time spent analyzing the SQL statement
`rewrite_time`	double	Time spent rewriting the SQL statement
`plan_time`	double	Time spent generating the execution plan
`execute_time`	double	Time at which the statement was executed

Lock waits

Parameter	Type	Description
`lwlock_wait`	double	Lightweight lock (lwlock) wait time
`rel_lock_wait`	double	Table lock wait time
`xact_lock_wait`	double	Transaction lock wait time
`page_lock_wait`	double	Page lock wait time
`tuple_lock_wait`	double	Row lock wait time

Storage I/O

Parameter	Type	Description
`shared_read_ps`	bigint	Read IOPS
`shared_write_ps`	bigint	Write IOPS
`shared_read_throughput`	bigint	Read throughput. Unit: bytes
`shared_write_throughput`	bigint	Write throughput. Unit: bytes
`shared_read_latency`	double	Read latency. Unit: microseconds
`shared_write_latency`	double	Write latency. Unit: microseconds

Diagnosing performance issues

Use the following observations and queries to investigate specific performance problems.

Observation	Likely cause	Action
High `lwlock_wait` or `rel_lock_wait`	Lock contention between concurrent sessions	Identify conflicting queries by joining with `pg_stat_statements` on `queryid`; review transaction isolation levels or reduce lock scope
High `shared_read_latency` or large `reads_blks`	Excessive disk reads; missing index or low cache hit rate	Check `pg_stat_statements.shared_blks_read` for the same `queryid`; consider adding an index or increasing `shared_buffers`
High `majflts`	Memory pressure causing paging	Review memory allocation for the workload; check `hash_memory` for hash-heavy queries
High `sort_time` or `hash_memory`	Spill to disk during sort or hash operations	Increase `work_mem` for affected sessions
High `plan_time` relative to `execute_time`	Frequent re-planning of the same query	Use prepared statements to cache query plans

polar_stat_query_count

polar_stat_query_count provides an aggregate count of executed statements grouped by SQL type and command type. Use it for a quick workload overview—for example, to see whether your database is read-heavy or write-heavy.

Prerequisites

Before querying this view, create the polar_stat_sql plug-in:

CREATE EXTENSION polar_stat_sql;

Parameters

Parameter	Type	Description
`sqltype`	text	SQL language category. Valid values: `DQL`, `DML`, `DDL`, `DCL`
`cmdtype`	text	Specific command type. Examples: `SELECT`, `INSERT`, `UPDATE`
`count`	bigint	Total number of executions

Query example

Get an overview of workload distribution by SQL type:

SELECT sqltype, cmdtype, count
FROM polar_stat_query_count
ORDER BY count DESC;