我喜欢这样来查找丢失的索引：

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_statements可以帮助…