Using pg_stat_statements

Introduction

The pg_stat_statements plugin provides a means for tracking planning and execution statistics of all SQL statements executed by a server.

For more information, see official pg_stat_statements documentation.

Supported Versions

This plugin is available to the latest minor versions of RDS for PostgreSQL 10 and later versions.

You can run the following SQL statement to check whether your DB instance supports this plugin:

SELECT * FROM pg_available_extension_versions WHERE name  = 'pg_stat_statements';

If this plugin is not supported, upgrade the minor version of your DB instance or upgrade the major version using dump and restore.

To see more plugins supported by RDS for PostgreSQL, go to Supported Plugins.

Plugin Installation and Uninstallation

To check whether the pg_stat_statements plugin is installed in a database, run the following SQL statement:

select * from pg_extension  where extname = 'pg_stat_statements';

If the command output is empty, the plugin is not installed. If the plugin information is displayed, the plugin is installed.

By default, pg_stat_statements is preloaded in the shared_preload_libraries parameter. Perform the following steps to install or delete the plugin:

Installing the plugin

SELECT control_extension('create', 'pg_stat_statements');

Deleting the plugin

SELECT control_extension('drop', 'pg_stat_statements');

For more information, see Installing and Uninstalling a Plugin on the RDS Console and Installing and Uninstalling a Plugin Using SQL Commands.

Basic Usage

After the pg_stat_statements plugin is installed, enable the corresponding parameters. The following table lists the default parameter settings, which can be adjusted based on your workloads.

**Table 1** Parameter description
Parameter	Reboot Required	Value	Allowed Values	Description
pg_stat_statements.max	Yes	5000	100-5,000,000	Specifies the maximum number of statements tracked by pg_stat_statements.
pg_stat_statements.save	No	on	on, off	Specifies whether to save statement statistics across server shutdowns.
pg_stat_statements.track	No	top	top, all, none	Controls which statements are counted by pg_stat_statements.
pg_stat_statements.track_planning	No	off	on, off	Controls whether planning duration is tracked by pg_stat_statements.
pg_stat_statements.track_utility	No	on	on, off	Controls whether utility commands are tracked by pg_stat_statements.

Query the pg_stat_statements view to obtain the statistics.
```
select * from pg_stat_statements;
```

Query the SQL statements with high I/O consumption.

-- Top 5 SQL statements
select userid::regrole, dbid, query
from pg_stat_statements
order by (blk_read_time+blk_write_time) desc limit 5;

Query the SQL statements with high consumption of shared memory.

select userid::regrole, dbid, query
from pg_stat_statements
order by (shared_blks_hit+shared_blks_dirtied) desc limit 5;

Reset the statistics.
```
select pg_stat_statements_reset();
```

Advanced Usage

You can use pg_stat_statements to troubleshoot high CPU usage.

Reset the pg_stat_statements counter.
```
select pg_stat_statements_reset();
```
Leave enough time for pg_stat_statements to collect information.
Obtain the most time-consuming SQL statements.
```
select * from pg_stat_statements order by total_exec_time desc limit 10;
```
The obtained SQL statements have been occupying the user-mode CPU for a long time. Analyze these SQL statements.
Obtain the SQL statements that read the buffer for the most times.
```
select * from pg_stat_statements order by shared_blks_hit + shared_blks_read desc limit 10;
```
The obtained SQL statements may cause too many buffer reads due to a lack of indexes, consuming a large number of CPU resources.
Obtain the SQL statements that have been executed for the most times.
```
select * from pg_stat_statements order by calls desc limit 10;
```
It takes a short time to execute some simple SQL statements separately. However, in some cases (for example, cyclic executions in a transaction or concurrent executions), the CPU usage increases.