pg查看某个表的索引信息，优化sql语句

目录

1. 查询表的索引

2、安装pg_stat_statements插件此插件用于统计数据库的资源消耗，分析SQL语句

3、配置pg_stat_statements采样参数

4、分析SQL可以通过查询pg_stat_statements视图，获取统计信息

5.获取不同情况的SQ

1. 查询表的索引

1.  
   select * from pg_indexes where tablename='student';
2.  
   select * from pg_statio_all_indexes where relname='student';

2、安装pg_stat_statements插件
此插件用于统计数据库的资源消耗，分析SQL语句

3、配置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 # 重启后是否保留统计信息
修改完postgresql.conf文件后，重启数据库restart

4、分析SQL
可以通过查询pg_stat_statements视图，获取统计信息

1.  
   postgres=# select * from pg_stat_statements;
2.  
   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
3.  
   --------±-----±--------±------±------±-----------±---------±---------±----------±------------±-----±----------------±-----------------±--------------------±--------------------±---------------±----------------±-------------------±-------------------±---------------±------------------±--------------±---------------

1.字段分析
字段名 类型 引用 描述
userid oid pg_authid.oid ——执行该语句的用户的 OID
dbid oid pg_database.oid ——在其中执行该语句的数据库的 OID
queryid bigint——内部哈希码，从语句的解析树计算得来
query text——语句的文本形式
calls bigint——被执行的次数
total_time double precision——在该语句中花费的总时间，以毫秒计
min_time double precision——在该语句中花费的最小时间，以毫秒计
max_time double precision——在该语句中花费的最大时间，以毫秒计
mean_time double precision——在该语句中花费的平均时间，以毫秒计
stddev_time double precision——在该语句中花费时间的总体标准偏差，以毫秒计
rows bigint——该语句检索或影响的行总数
shared_blks_hit bigint——该语句造成的共享块缓冲命中总数
shared_blks_read bigint——该语句读取的共享块的总数
shared_blks_read 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被启用，否则为零）
blk_write_time double precision——该语句花在写入块上的总时间，以毫秒计（如果track_io_timing被启用，否则为零）

5.获取不同情况的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

这篇好文章是转载于：学新通技术网