PostgreSQL如何查询IO消耗最高的SQL及优化，pg_stat_statements插件安装及使用
PostgreSQL如何查询IO消耗最高的SQL及优化，pg_stat_statements
- 一、安装pg_stat_statements插件
- 二、加载pg_stat_statements模块
- 三、配置pg_stat_statements采样参数
- 四、创建pg_stat_statements extension
- 五、分析SQL
- 六、重置统计信息
- 七、优化方案
摘要：一、安装pg_stat_statements插件此插件用于统计数据库的资源消耗，分析SQL语句安装流程：
一、安装pg_stat_statements插件
shared_preload_libraries = 'timescaledb,pg_stat_statements'
此插件用于统计数据库的资源消耗，分析SQL语句
安装流程：
cd */postgresql-11.1/contrib/pg_stat_statements
make
make install
二、加载pg_stat_statements模块
–编辑postgresql.conf文件
vi $PGDATA/postgresql.conf–修改配置
shared_preload_libraries='pg_stat_statements'
track_io_timing = on用于跟踪IO消耗的时间
track_activity_query_size = 1024设置单条SQL的最长长度，超过被截断显示（可选）
三、配置pg_stat_statements采样参数
–在postgresql.conf文件添加
pg_stat_statements.max = 10000 # 在pg_stat_statements中最多保留多少条统计信息
pg_stat_statements.track = all # all - (所有SQL包括函数内嵌套的SQL), top - 直接执行的SQL(函数内的sql不被跟踪), none - (不跟踪)
pg_stat_statements.track_utility = off # 是否跟踪非DML语句 (例如DDL，DCL)， on表示跟踪, off表示不跟踪
pg_stat_statements.save = on # 重启后是否保留统计信息
# data/postgresql.conf中，进行配置：
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.max = 1000
pg_stat_statements.track = all
shared_preload_libraries = 'pg_stat_statements'，表示要在启动时导入pg_stat_statements 动态库。
pg_stat_statements.max = 1000，表示监控的语句最多为1000句。
pg_stat_statements.track = top，表示不监控嵌套的sql语句。
pg_stat_statements.track_utility = true，表示对 INSERT/UPDATE/DELETE/SELECT 之外的sql动作也作监控。
pg_stat_statements.save = true，表示当postgresql停止时，把信息存入磁盘文件以备下次启动时再使用。
修改完postgresql.conf文件后，重启数据库restart
pg_ctl —D $PGDATA restart
四、创建pg_stat_statements extension
postgres=# create extension pg_stat_statements;
CREATE EXTENSION
postgres=#五、分析SQL
可以通过查询pg_stat_statements视图，获取统计信息
postgres=# select * from pg_stat_statements;
userid | dbid | queryid | query | calls | total_time | min_time | max_time | mean_time | stddev_time | rows | shared_blks_hit | shared_blks_read | shared_blks_dirtied | shared_blks_written | local_blks_hit | local_blks_read | local_blks_dirtied | local_blks_written | temp_blks_read | temp_blks_written | blk_read_time | blk_write_time
--------+------+---------+-------+-------+------------+----------+----------+-----------+-------------+------+-----------------+------------------+---------------------+---------------------+----------------+-----------------+--------------------+--------------------+----------------+-------------------+---------------+----------------
(0 rows)
查询哪些sql语句执行效率慢：
-- 查询哪些sql语句执行效率慢：
SELECT query, calls, total_time, (total_time/calls) as average ,rows,
100.0 * shared_blks_hit /nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent
FROM pg_stat_statements
ORDER BY average DESC LIMIT 10;
重置统计信息
-- 统计结果一直都在，重启也不会清零，那么统计结果如何清零重新统计呢？
-- 执行下面SQL即可：
select pg_stat_statements_reset() ;
普通用户需执行授权（permission denied for function pg_stat_statements_reset）
permission denied for function pg_stat_statements_reset
-- 给数据库用户授权
GRANT EXECUTE ON FUNCTION pg_stat_statements_reset() TO pmusername;
2.如何获取不同情况的SQL
(1)最耗IO的SQL
单次消耗IO的前五条SQL，降序
select userid::regrole, dbid, query
from pg_stat_statements
order by (blk_read_time+blk_write_time)/calls desc limit 5;
总消耗IO的前五条SQL，降序
select userid::regrole, dbid, query
from pg_stat_statements
order by (blk_read_time+blk_write_time) desc limit 5;
(2)最耗时的SQL
单次调用耗时前五条SQL，降序
select userid::regrole, dbid, query
from pg_stat_statements
order by mean_time desc limit 5;
总耗时前五条SQL，降序（最关注的）
select userid::regrole, dbid, query
from pg_stat_statements
order by total_time desc limit 5;
(3)响应时间抖动最严重的SQL
select userid::regrole, dbid, query
from pg_stat_statements
order by stddev_time desc limit 5;
(4)最耗共享内存的SQL
select userid::regrole, dbid, query
from pg_stat_statements
order by (shared_blks_hit+shared_blks_dirtied) desc limit 5;
(5)最耗临时空间的SQL
select userid::regrole, dbid, query
from pg_stat_statements
order by temp_blks_written desc limit 5;
六、重置统计信息
可以定期清理历史的统计信息，使用如下SQL
postgres=# select pg_stat_statements_reset();
pg_stat_statements_reset
--------------------------
(1 row)
七、优化方案
1.进行有针对的查询，避免使用select * ？
如果是要使用count(*)计算，要使用计算行数的子查询
select count(*) from
(
select
id
from users
where preferred_language = 'zh_CN'
and private_profile = True
) as temp;
2.避免使用NOT IN
避免使用IN或者NOT IN，因为这个操作会进行全表扫描。可以使用EXCEPT或NOT EXISTS来代替
3.模糊查询的优化可以参考《PostgreSQL模糊查询优化》这篇文章
4.添加适当的索引
对表中经常进行查询、排序、分组的字段添加索引
（1）b-tree索引
b-tree适合所有的数据类型，支持排序，支持大于、小于、等于、大于或等于、小于或等于的搜索。
postgres=# create index idx_t_btree_1 on t_btree using btree (id);
2）hash索引
hash索引存储的是被索引字段VALUE的哈希值，只支持等值查询。hash索引特别适用于字段VALUE非常长（不适合b-tree索引，因为b-tree一个PAGE至少要存储3个ENTRY，所以不支持特别长的VALUE）的情况。
如果只需要等值搜索，可以使用hash索引
postgres=# create index idx_t_hash_1 on t_hash using hash (info);
（3）gin倒排索引
当需要搜索多值类型内的VALUE时，适合多值类型，例如数组、全文检索、TOKEN。
–arr是数组类型
postgres=# create index idx_t_gin1_1 on t_gin1 using gin (arr);
（4）gist索引
它是一种平衡树结构的访问方法,在系统中作为一个基本模版,可以使用它实现任意索引模式。B-trees, R-trees和许多其它的索引模式都可以用GiST实现。但Gist索引创建耗时较长，占用空间也比较大。
postgres=# create index idx_t_gist_1 on t_gist using gist (pos);
（5）sp-gist索引
SP-GiST类似GiST，是一个通用的索引接口，但是SP-GIST使用了空间分区的方法，使得SP-GiST可以更好的支持非平衡数据结构，支持位置搜索。
postgres=# create index idx_t_spgist_1 on t_spgist using spgist (rg);
5.UNION ALL 比 UNION 快
UNION在进行表链接后会筛选掉重复的记录，UNION ALL不会去除重复记录UNION将会按照字段的顺序进行排序，UNION ALL只是简单的将两个结果合并后就返回
F.28.1. pg_stat_statements视图
该模块收集的统计信息通过一个名为 pg_stat_statements的系统视图提供。 这个视图为每个不同的数据库 ID、用户 ID 和查询 ID （最多到该模块可以追踪的不同语句的最大数量）包含一行。该视图的列显示在 表 F-20中。
表 F-20. pg_stat_statements列
1.修改配置参数
vi $PGDATA/postgresql.conf
------
shared_preload_libraries='pg_stat_statements' #加载pg_stat_statements模块
track_io_timing = on #如果要跟踪IO消耗的时间，需要打开如上参数
track_activity_query_size = 2048 #设置单条SQL的最长长度，超过被截断显示（可选）
#以下配置pg_stat_statements采样参数
pg_stat_statements.max = 10000 # 在pg_stat_statements中最多保留多少条统计信息，通过LRU算法，覆盖老的记录。
pg_stat_statements.track = all # all - (所有SQL包括函数内嵌套的SQL), top - 直接执行的SQL(函数内的sql不被跟踪), none - (不跟踪)
pg_stat_statements.track_utility = off # 是否跟踪非DML语句 (例如DDL，DCL)，on表示跟踪, off表示不跟踪
pg_stat_statements.save = on # 重启后是否保留统计信息
