SQL performance analysis
Use this guide when slow SQL statements occur or SQL statements consume excessive memory. The typical diagnostic workflow is:
Identify slow SQL statements — both historical and currently running.
Analyze execution plans with EXPLAIN to understand resource consumption.
Examine
pg_stat_statementsandpolar_stat_sqlfor deeper resource and timing breakdowns.
Check slow SQL statements
View historical slow SQL statements
Query pg_stat_statements to find the top 10 slowest statements by average execution time:
SELECT total_exec_time / calls AS avg, query
FROM pg_stat_statements
ORDER BY avg DESC -- highest average execution time first
LIMIT 10; -- return the 10 slowest statementsView SQL statements currently running
Query pg_stat_activity to find statements that have been running for more than 1 second:
SELECT *
FROM pg_stat_activity
WHERE state <> 'idle' -- exclude idle connections
AND now() - query_start > interval '1 s' -- running longer than 1 second
ORDER BY query_start;Analyze execution plans with EXPLAIN
EXPLAIN shows how PostgreSQL plans to execute a query, including estimated costs, row counts, and resource usage.
Syntax
EXPLAIN [ ( option [, ...] ) ] statement
EXPLAIN [ ANALYZE ] [ VERBOSE ] statementParameters
| Parameter | Default | Description |
|---|---|---|
ANALYZE | FALSE | Executes the statement and returns the actual execution plan. To analyze a data-modifying statement (INSERT, UPDATE, DELETE) without committing changes, wrap it in a transaction and roll back after analysis. |
VERBOSE | FALSE | Displays additional plan details: output column list per node, table and function schemas, column-to-table aliases, and trigger names. |
COSTS | TRUE | Shows the estimated startup cost (cost to find the first record that meets the specified conditions) and total cost for each plan node, along with estimated row count and row width. |
BUFFERS | FALSE | Shows buffer usage: the number of hit blocks, updated blocks, and removed blocks among shared blocks, local blocks, and temporary blocks. Requires ANALYZE TRUE. |
FORMAT | TEXT | Output format. TEXT is human-readable. XML, JSON, and YAML are easier for programs to parse. |
Buffer block types:
Shared blocks — data from regular tables and indexes
Local blocks — data from temporary tables and indexes
Temporary blocks — short-term working data used in sorts, hashes, and similar operations
Example: analyze a data-modifying statement safely
BEGIN;
EXPLAIN ANALYZE UPDATE orders SET status = 'shipped' WHERE id = 12345;
ROLLBACK;Analyze resource usage with views
The pg_stat_statements and polar_stat_sql views provide detailed resource and timing statistics at the SQL level.
Before you begin, ensure that you have superuser permissions. Superuser permissions are required to create the polar_stat_sql extension. Contact Alibaba Cloud support if needed.
Enable the extensions
Run the following commands to create and enable both extensions:
CREATE EXTENSION pg_stat_statements;
CREATE EXTENSION polar_stat_sql;
ALTER SYSTEM SET polar_stat_sql.enable_stat = on;
ALTER SYSTEM SET polar_stat_sql.enable_qps_monitor = on;
SELECT pg_reload_conf();Expected output:
pg_reload_conf
----------------
t
(1 row)pg_stat_statements
pg_stat_statements tracks execution statistics for all SQL statements. For details on available columns and usage, see pg_stat_statements.
polar_stat_sql
polar_stat_sql provides PolarDB-specific monitoring data across four categories:
| Category | What it tracks | When to use |
|---|---|---|
| Execution plan node statistics | Nodes where scans, joins, aggregations, sorts, and hashes are performed | Use when EXPLAIN shows an unexpected plan node (for example, a sequential scan instead of an index scan) |
| Resource usage | CPU time (system and user mode), I/O (read/write bytes), memory (pages requested and swapped), the number of received and requested Inter-process communication (IPC) messages, and voluntary and involuntary CPU context switches | Use when a query is slow but the execution plan looks reasonable — high I/O bytes or CPU time pinpoints the bottleneck |
| Execution phase timing | Time spent in parsing, analyzing, rewriting, plan generation, and execution | Use when overall latency is high but per-node costs in EXPLAIN look low — identifies overhead outside of execution |
| Latch and lock statistics | Latch and lock wait events | Use when concurrent queries are slower than expected — high lock waits indicate contention |
Reset accumulated data before analysis
polar_stat_sql records cumulative data. To get accurate measurements for a specific statement, clear existing data first:
SELECT polar_stat_sql_reset();