如何在 PostgreSQL 中查找并修复缺失索引

Source: Dev.to

问题：隐藏的顺序扫描

你发布了一个功能，测试通过，预发布环境看起来正常，但随后生产环境开始对一个两天前还能完美运行的查询抛出超时：

SELECT ... WHERE customer_email = ?

该表现在有 1000 万行，PostgreSQL 正在读取其中的每一行。
没有索引 → 完全顺序扫描 → 每次查询约 200 毫秒，而该表每小时被访问数千次。

在开发环境中，表只有几百行，因此顺序扫描只需微秒级，问题在真实数据量出现之前是不可见的。

查找被顺序扫描的表

PostgreSQL 在 pg_stat_user_tables 中记录扫描统计信息。查询该视图以发现有问题的表：

SELECT
    schemaname,
    relname AS table_name,
    seq_scan,
    seq_tup_read,
    idx_scan,
    CASE WHEN (seq_scan + idx_scan) > 0
        THEN round(100.0 * seq_scan / (seq_scan + idx_scan), 1)
        ELSE 0
    END AS sequential_scan_percentage,
    pg_size_pretty(pg_relation_size(schemaname || '.' || relname)) AS table_size
FROM pg_stat_user_tables
WHERE seq_scan > 0
ORDER BY seq_tup_read DESC
LIMIT 20;

需要关注的点

• 高 seq_tup_read（大量行被顺序扫描获取）。
• sequential_scan_percentage > 90 %（几乎所有访问都是顺序扫描）。
• idx_scan = 0（该表上没有查询使用索引）。

大的 seq_tup_read 表明缺少或未使用索引。

确认特定查询的扫描方式

对慢查询运行 EXPLAIN (ANALYZE, BUFFERS)：

EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM sim_customers
WHERE customer_email = 'test@example.com';

如果输出中包含 Seq Scan，且实际时间很高并读取了大量缓冲区，则说明规划器没有可用的索引。

在创建索引（见下一节）后，再次运行相同的命令。你应该会看到 Index Scan 或 Index Only Scan，并且缓冲区的使用量会显著减少。

添加缺失的索引

创建索引 concurrently 以避免阻塞读取和写入：

CREATE INDEX CONCURRENTLY idx_sim_customers_email
    ON sim_customers (customer_email);

CONCURRENTLY 只获取短暂的锁，在后台构建索引，并防止停机。它耗时更长并会扫描表两次，但对生产表是安全的。

使用与之前相同的 EXPLAIN 验证索引是否被使用；之前读取 6 400 个缓冲区（≈ 50 MB）的顺序扫描现在可能只读取少量缓冲区。

Source: …

多列索引

当查询在多个列上进行过滤时，复合索引通常比单列索引更高效：

CREATE INDEX CONCURRENTLY idx_orders_customer_date
    ON orders (customer_id, order_date);

列顺序很重要

1. 最具选择性的列放在前面 – 能最大程度减少行集合的列应放在位置 1。
2. 范围列放在最后 – 使用 <、> 或 BETWEEN 的列应放在末尾。
3. 前缀规则 – 规划器只能在查询仅过滤前导列（或前导列的组合）时使用该索引。如果查询只过滤后面的列（例如仅 order_date），则无法使用该索引。

如果列顺序错误，规划器会悄悄忽略该索引，回退到顺序扫描，同时仍会产生维护未使用索引的写入开销。

为什么缺失索引的危害比单个查询更大

• 顺序扫描读取表的大部分数据，会把缓冲区缓存中的有用页面驱逐出去。
• 这种缓存压力会削弱之前受益于缓存数据的其他不相关查询。
• 随着表的增长，影响呈非线性增长，使得原本“几个月都运行良好”的查询成为瓶颈。

最佳实践检查清单