pg_stat_statements插件使用指南

为了帮助数据库管理员和开发者更好地监控和优化SQL查询，PostgreSQL提供了多种插件，其中pg_stat_statements是一个非常重要的插件。本文将详细讲解pg_stat_statements插件的用途、具体使用步骤，并在最后列出一些常用的PostgreSQL插件及其用途。

一、什么是pg_stat_statements？

pg_stat_statements是PostgreSQL的一个扩展插件，用于收集和记录SQL语句的执行统计信息。它可以帮助数据库管理员分析数据库中最常执行的查询、最耗时的查询以及其他可能影响性能的查询模式。通过这些信息，管理员可以对数据库性能进行深入分析，从而优化查询、调整索引策略、改进数据库配置等。

功能特点

• SQL统计信息：记录所有SQL语句的执行次数、总耗时、平均耗时、最大耗时和最小耗时等。
• 归一化处理：对SQL语句进行归一化处理，将类似但参数不同的查询合并统计，方便分析查询模式。
• 持久化存储：pg_stat_statements在数据库重启后仍保留统计信息，方便长期分析。

二、如何安装和配置pg_stat_statements？

1. 安装pg_stat_statements

pg_stat_statements插件通常默认包含在PostgreSQL的安装包中，因此无需单独安装。你可以通过以下命令检查是否已经安装：

psql -c "SELECT * FROM pg_available_extensions WHERE name = 'pg_stat_statements';"

如果输出中显示pg_stat_statements，说明插件已经包含在安装包中，可以直接使用。

2. 配置pg_stat_statements

在使用pg_stat_statements之前，需要在PostgreSQL配置文件中启用它。

1. 编辑配置文件
   打开PostgreSQL的配置文件postgresql.conf，找到或添加以下配置：

   shared_preload_libraries = 'pg_stat_statements'
   compute_query_id = on
   pg_stat_statements.max = 10000
   pg_stat_statements.track = all
   

以下为 pg_stat_statements 插件其他配置参数的说明：

1. pg_stat_statements.max (整数) 此参数定义了模块跟踪的最大SQL语句数量（即 pg_stat_statements
视图中的最大行数）。如果观察到的不同语句超过此数量，则最少执行的语句信息将被丢弃。默认值为5000。该参数只能在服务器启动时设置。

2.pg_stat_statements.track (枚举) 控制模块跟踪哪些语句。设置为 top 时，跟踪顶层语句（直接由客户端发出的语句）；设置为 all 时，还跟踪嵌套语句（如函数内调用的语句）；设置为 none
时禁用语句统计。默认值为 top。仅超级用户可以更改此设置。

3.pg_stat_statements.track_utility (布尔值) 控制模块是否跟踪实用程序命令。实用程序命令包括 SELECT、INSERT、UPDATE、DELETE 和 MERGE 之外的所有命令。默认值为
on。仅超级用户可以更改此设置。

4.pg_stat_statements.track_planning (布尔值) 控制模块是否跟踪查询计划操作及其持续时间。启用此参数可能会对性能造成显著影响，特别是在许多并发连接竞争更新少量
pg_stat_statements 条目时。默认值为 off。仅超级用户可以更改此设置。

5.pg_stat_statements.save (布尔值) 指定是否在服务器关闭时保存语句统计信息。如果关闭此参数，则不会在关机时保存统计信息，也不会在服务器启动时重新加载。默认值为
on。此参数只能在 postgresql.conf 文件中或通过服务器命令行进行设置。

2. 重启PostgreSQL服务
   保存配置文件后，重启PostgreSQL服务以应用配置更改：

   sudo systemctl restart postgresql
   

3. 创建扩展
   在需要使用pg_stat_statements的数据库中执行以下SQL命令，以创建扩展：

   CREATE EXTENSION pg_stat_statements;
   

   此命令将在当前数据库中启用pg_stat_statements插件。

3. 验证安装

可以通过以下命令验证插件是否安装成功：

SELECT * FROM pg_stat_statements LIMIT 10;

如果返回查询结果，说明pg_stat_statements已经成功启用并开始记录SQL统计信息。

三、pg_stat_statements的常用查询与分析

pg_stat_statements的统计信息存储在一个与插件同名的视图中，可以通过查询该视图来获取各种SQL统计数据。以下是pg_stat_statements视图的属性字段名称以及字段说明：

列名	类型	说明
userid	oid (参考 pg_authid.oid)	执行SQL语句的用户ID
dbid	oid (参考 pg_database.oid)	执行SQL语句的数据库ID
toplevel	bool	如果查询作为顶层语句执行则为true（如果 pg_stat_statements.track 设置为 top，则始终为true）
queryid	bigint	标识相同归一化查询的哈希码
query	text	代表性语句的文本
plans	bigint	语句被规划的次数（如果启用了 pg_stat_statements.track_planning，否则为0）
total_plan_time	double precision	语句规划总耗时，以毫秒为单位（如果启用了 pg_stat_statements.track_planning，否则为0）
min_plan_time	double precision	语句规划的最短时间，以毫秒为单位（如果启用了 pg_stat_statements.track_planning，否则为0）
max_plan_time	double precision	语句规划的最长时间，以毫秒为单位（如果启用了 pg_stat_statements.track_planning，否则为0）
mean_plan_time	double precision	语句规划的平均时间，以毫秒为单位（如果启用了 pg_stat_statements.track_planning，否则为0）
stddev_plan_time	double precision	语句规划时间的总体标准差，以毫秒为单位（如果启用了 pg_stat_statements.track_planning，否则为0）
calls	bigint	语句被执行的次数
total_exec_time	double precision	语句执行的总时间，以毫秒为单位
min_exec_time	double precision	语句执行的最短时间，以毫秒为单位
max_exec_time	double precision	语句执行的最长时间，以毫秒为单位
mean_exec_time	double precision	语句执行的平均时间，以毫秒为单位
stddev_exec_time	double precision	语句执行时间的总体标准差，以毫秒为单位
rows	bigint	语句检索或影响的总行数
shared_blks_hit	bigint	语句命中的共享块缓存总数
shared_blks_read	bigint	语句从磁盘读取到共享缓冲区的块数
shared_blks_dirtied	bigint	语句执行期间脏化的共享块数
shared_blks_written	bigint	语句执行期间写回磁盘的共享块数
local_blks_hit	bigint	语句命中的本地块缓存总数（仅适用于临时表）
local_blks_read	bigint	语句从磁盘读取到本地缓冲区的块数（仅适用于临时表）
local_blks_dirtied	bigint	语句执行期间脏化的本地块数（仅适用于临时表）
local_blks_written	bigint	语句执行期间写回磁盘的本地块数（仅适用于临时表）
temp_blks_read	bigint	语句执行期间从临时文件中读取的块数
temp_blks_written	bigint	语句执行期间写入临时文件的块数
blk_read_time	double precision	语句在读取数据文件块上花费的总时间，以毫秒为单位（如果启用了 track_io_timing，否则为0）
blk_write_time	double precision	语句在写入数据文件块上花费的总时间，以毫秒为单位（如果启用了 track_io_timing，否则为0）
temp_blk_read_time	double precision	语句在读取临时文件块上花费的总时间，以毫秒为单位（如果启用了 track_io_timing，否则为0）
temp_blk_write_time	double precision	语句在写入临时文件块上花费的总时间，以毫秒为单位（如果启用了 track_io_timing，否则为0）
wal_records	bigint	语句生成的WAL记录总数
wal_fpi	bigint	语句生成的WAL全页图像总数
wal_bytes	numeric	语句生成的WAL总量，以字节为单位
jit_functions	bigint	语句JIT编译的函数总数
jit_generation_time	double precision	语句生成JIT代码花费的总时间，以毫秒为单位
jit_inlining_count	bigint	语句内联函数的次数
jit_inlining_time	double precision	语句内联函数花费的总时间，以毫秒为单位
jit_optimization_count	bigint	语句优化的次数
jit_optimization_time	double precision	语句优化花费的总时间，以毫秒为单位
jit_emission_count	bigint	语句生成代码的次数
jit_emission_time	double precision	语句生成代码花费的总时间，以毫秒为单位

以上表格详细描述了 pg_stat_statements 视图中的各字段及其含义，用于帮助理解和分析PostgreSQL数据库的查询性能，以下为一些常用的查询示例。

1. 查看最耗时的查询

以下查询将返回执行时间最长的前10条SQL语句：

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

2. 查找执行次数最多的查询

要查看数据库中执行次数最多的前10条SQL语句，可以使用以下查询：

SELECT
    query,
    calls,
    mean_time,
    rows
FROM
    pg_stat_statements
ORDER BY
    calls DESC
LIMIT 10;

3. 识别最常见的查询模式

有时，优化数据库性能需要找到重复出现的查询模式。以下查询将帮助你识别这些模式：

SELECT
    query,
    calls,
    total_time,
    rows
FROM
    pg_stat_statements
ORDER BY
    calls DESC
LIMIT 10;

4. 清除统计数据

在某些情况下，你可能需要清除pg_stat_statements的统计数据，可以使用以下命令：

SELECT pg_stat_statements_reset();

此命令将清空统计信息，适用于需要重新收集数据的场景。

四、测试用例

以下是一个测试用例，展示如何使用pg_stat_statements插件收集和分析SQL统计信息。

1. 创建测试表和插入数据

CREATE TABLE test_table (
    id SERIAL PRIMARY KEY,
    data TEXT
);

INSERT INTO test_table (data)
SELECT 'Sample data ' || generate_series(1, 1000);

2. 执行一些查询

SELECT * FROM test_table WHERE id = 10;
SELECT * FROM test_table WHERE id > 500;
SELECT COUNT(*) FROM test_table;

3. 分析查询统计信息

运行以下查询，查看这些SQL语句的统计信息：

SELECT
    query,
    calls,
    total_time,
    mean_time
FROM
    pg_stat_statements
WHERE query LIKE '%test_table%'
ORDER BY calls DESC;

此查询将显示与test_table相关的所有SQL语句的执行统计信息，包括执行次数和总耗时。

五、常用PostgreSQL插件列表及用途

以下是一些常用的PostgreSQL插件及其用途，帮助你扩展数据库的功能：

1. hstore：用于存储键值对数据，适合处理非结构化数据。
2. uuid-ossp：生成全局唯一标识符（UUID）。
3. pgcrypto：提供数据加密和解密功能。
4. pgaudit：扩展审计功能，记录用户操作日志。

六、参考链接

1. PostgreSQL Documentation on pg_stat_statements