postgresql - 用于查找和创建模式缺失索引的脚本

Question

我使用下面的脚本来查找丢失的索引，但是对其进行了任何修改以查找和创建索引创建语句或查找我们可以索引的列？

数据库：Postgres

SELECT 
relname AS TableName
,seq_scan-idx_scan AS TotalSeqScan
,CASE WHEN seq_scan-idx_scan > 0 
    THEN 'Missing Index Found' 
    ELSE 'Missing Index Not Found' 
END AS MissingIndex
,pg_size_pretty(pg_relation_size(relname::regclass)) AS TableSize
,idx_scan AS TotalIndexScan
FROM pg_stat_all_tables
WHERE schemaname='public'
AND pg_relation_size(relname::regclass)>100000 
ORDER BY 2 DESC;

标签： postgresqlpostgresql-9.5

Answer 1

我认为该查询不是很有用。

更好的是

SELECT relname,
       seq_scan,
       seq_tup_read / seq_scan AS tup_per_scan
FROM pg_stat_user_tables
WHERE seq_scan > 0;

如果第二列和第三列都很大，则缺少索引。