跳过正文
  1. PostgreSQL大法师/

找出没用过的索引

·546 字·2 分钟· ·
PostgreSQL PG管理
冯若航
作者
冯若航
Pigsty 创始人, @Vonng
目录

索引很有用, 但不是免费的。没用到的索引是一种浪费,使用以下SQL找出未使用的索引:

  • 首先要排除用于实现约束的索引(删不得)
  • 表达式索引(pg_index.indkey中含有0号字段)
  • 然后找出走索引扫描的次数为0的索引(也可以换个更宽松的条件,比如扫描小于1000次的)

找出没有使用的索引
#

  • 视图名称:monitor.v_bloat_indexes
  • 计算时长:1秒,适合每天检查/手工检查,不适合频繁拉取。
  • 验证版本:9.3 ~ 10
  • 功能:显示当前数据库索引膨胀情况。

在版本9.3与10.4上工作良好。视图形式

-- CREATE SCHEMA IF NOT EXISTS monitor;
-- DROP VIEW IF EXISTS monitor.pg_stat_dummy_indexes;

CREATE OR REPLACE VIEW monitor.pg_stat_dummy_indexes AS
SELECT s.schemaname,
       s.relname AS tablename,
       s.indexrelname AS indexname,
       pg_relation_size(s.indexrelid) AS index_size
FROM pg_catalog.pg_stat_user_indexes s
   JOIN pg_catalog.pg_index i ON s.indexrelid = i.indexrelid
WHERE s.idx_scan = 0      -- has never been scanned
  AND 0 <>ALL (i.indkey)  -- no index column is an expression
  AND NOT EXISTS          -- does not enforce a constraint
         (SELECT 1 FROM pg_catalog.pg_constraint c
          WHERE c.conindid = s.indexrelid)
ORDER BY pg_relation_size(s.indexrelid) DESC;

COMMENT ON VIEW monitor.pg_stat_dummy_indexes IS 'monitor unused indexes'
-- 人类可读的手工查询
SELECT s.schemaname,
       s.relname AS tablename,
       s.indexrelname AS indexname,
       pg_size_pretty(pg_relation_size(s.indexrelid)) AS index_size
FROM pg_catalog.pg_stat_user_indexes s
   JOIN pg_catalog.pg_index i ON s.indexrelid = i.indexrelid
WHERE s.idx_scan = 0      -- has never been scanned
  AND 0 <>ALL (i.indkey)  -- no index column is an expression
  AND NOT EXISTS          -- does not enforce a constraint
         (SELECT 1 FROM pg_catalog.pg_constraint c
          WHERE c.conindid = s.indexrelid)
ORDER BY pg_relation_size(s.indexrelid) DESC;

批量生成删除索引的命令
#

SELECT 'DROP INDEX CONCURRENTLY IF EXISTS "' 
	|| s.schemaname || '"."' || s.indexrelname || '";'
FROM pg_catalog.pg_stat_user_indexes s
   JOIN pg_catalog.pg_index i ON s.indexrelid = i.indexrelid
WHERE s.idx_scan = 0      -- has never been scanned
  AND 0 <>ALL (i.indkey)  -- no index column is an expression
  AND NOT EXISTS          -- does not enforce a constraint
         (SELECT 1 FROM pg_catalog.pg_constraint c
          WHERE c.conindid = s.indexrelid)
ORDER BY pg_relation_size(s.indexrelid) DESC;

找出重复的索引
#

检查是否有索引工作在相同的表的相同列上,但要注意条件索引。

SELECT
  indrelid :: regclass              AS table_name,
  array_agg(indexrelid :: regclass) AS indexes
FROM pg_index
GROUP BY
  indrelid, indkey
HAVING COUNT(*) > 1;

相关文章

批量配置SSH免密登录
·539 字·2 分钟
PostgreSQL PG管理
快速配置所有机器的免密登陆
Wireshark抓包分析协议
·2014 字·5 分钟
PostgreSQL PG管理 工具
Wireshark是一个很有用的工具,特别适合用来分析网络协议,这里简单介绍使用Wireshark抓包分析PostgreSQL协议的方法。
file_fdw妙用无穷——从数据库读取系统信息
·1457 字·3 分钟
PostgreSQL PG管理 扩展
通过file_fdw,轻松查看操作系统信息,拉取网络数据,把各种各样的数据源轻松喂进数据库里统一查看管理。
Linux 常用统计 CLI 工具
·4377 字·9 分钟
PostgreSQL PG管理 工具
top, free, vmstat, iostat:四大常用 CLI 工具命令速查
源码编译安装 PostGIS
·1527 字·4 分钟
PostgreSQL PG管理 扩展
PostGIS是PG的杀手锏插件,但编译安装可不容易。
PostgreSQL MongoFDW安装部署
·1634 字·4 分钟
PostgreSQL PG管理 扩展
最近有业务要求通过PostgreSQL FDW去访问MongoDB,但是,MongoDB FDW 编译起来真是要人命啊。