我喜欢这样来查找丢失的索引:
SELECT
relname AS TableName,
to_char(seq_scan, '999,999,999,999') AS TotalSeqScan,
to_char(idx_scan, '999,999,999,999') AS TotalIndexScan,
to_char(n_live_tup, '999,999,999,999') AS TableRows,
pg_size_pretty(pg_relation_size(relname :: regclass)) AS TableSize
FROM pg_stat_all_tables
WHERE schemaname = 'public'
AND 50 * seq_scan > idx_scan -- more than 2%
AND n_live_tup > 10000
AND pg_relation_size(relname :: regclass) > 5000000
ORDER BY relname ASC;
这会检查序列扫描是否多于索引扫描。如果表很小,它就会被忽略,因为 Postgres 似乎更喜欢对它们进行序列扫描。
上面的查询确实揭示了缺失的索引。
下一步是检测缺失的组合索引。我想这并不容易,但可行。也许分析缓慢的查询......我听说pg_stat_语句 https://www.postgresql.org/docs/current/static/pgstatstatements.html有帮助...