PostgreSQL 查询使用了索引，为什么执行仍然较慢？

2026-11-06数据小陈

结论

索引参与了这条查询的执行，但不能因此认为整个查询都会变快。计划显示，equipment 通过索引扫描读取了约 700,000 行，auditory 的查找则由 Memoize 缓存。与此同时，查询返回约 500,008 行，department 使用并行顺序扫描，结果还需要排序。处理和排序大量行的成本可能高于使用索引，因此执行计划选择顺序扫描并不反常。

计划中的 Sort Method: external merge Disk 表明排序使用了磁盘临时空间，可以进一步调查。不过，仅凭现有计划还不能判断排序是否是全部耗时的来源。

从计划中能看出什么

BadDepartments 的部分索引扫描实际返回 0 行，因此后续针对 equipment 和 auditory 的嵌套循环没有执行。这次运行中，所有 department 都通过了反连接条件。
DeptStats 扫描了约 700,000 条 equipment 记录，并用 Memoize 缓存重复的 number_auditory 查找。计划显示约 700 次缓存未命中、699,300 次命中，说明索引查找被重复利用。
department 输出约 500,008 行。即使有 (name, ID_department) 索引，读取全部部门并按 name 排序，也未必比并行顺序扫描后再排序便宜。计划显示，每个工作进程的 Sort 都写入了约 10 MB 的磁盘临时数据。
索引适合帮助查询定位数据或按特定顺序读取数据。若查询需要读取表中的大部分数据，索引扫描产生的随机访问成本可能高于顺序扫描。

先确认排序是否值得优化

用完整执行计划查看实际 I/O 和各节点耗时：

EXPLAIN (ANALYZE, BUFFERS)
WITH BadDepartments AS (
    SELECT DISTINCT a."ID_department"
    FROM auditory a
    JOIN equipment e ON a."number_auditory" = e."number_auditory"
    JOIN inventorylist il ON e."inventory_number" = il."inventory_number"
    WHERE il."ID_total" IN (2, 3)
),
DeptStats AS (
    SELECT a."ID_department", COUNT(e."inventory_number") AS cnt
    FROM auditory a
    JOIN equipment e ON a."number_auditory" = e."number_auditory"
    GROUP BY a."ID_department"
)
SELECT
    d."name" AS "division",
    COALESCE(ds.cnt, 0) AS "num of equip"
FROM department d
LEFT JOIN BadDepartments bd ON d."ID_department" = bd."ID_department"
LEFT JOIN DeptStats ds ON d."ID_department" = ds."ID_department"
WHERE bd."ID_department" IS NULL
ORDER BY d."name" ASC;

检查 BUFFERS 中共享块和临时块的读写量，并留意各节点的实际行数和耗时。当前计划估算 BadDepartments 有 1 行，实际却返回 0 行。如果这种估算偏差持续出现，可以先更新相关表的统计信息：

ANALYZE department;
ANALYZE auditory;
ANALYZE equipment;
ANALYZE inventorylist;

更新统计信息后，再检查执行计划。ANALYZE 可以改善规划器对数据分布的估算，但不保证计划一定改变或运行时间一定缩短。

检查磁盘排序

可以在单次事务中谨慎提高 work_mem，观察排序是否转为内存排序，以及总耗时和临时块读写量是否下降：

BEGIN;
SET LOCAL work_mem = '64MB';

EXPLAIN (ANALYZE, BUFFERS)
-- 在这里执行完整查询

ROLLBACK;

64MB 是用于比较的示例值，实际设置应结合并发查询数和服务器可用内存调整。一个查询中的多个排序或哈希操作都可能分别使用 work_mem，因此不适合未经评估就全局调高。即使排序不再落盘，查询仍需扫描、聚合并输出大量行，总耗时也不一定会明显下降。

可比较的查询写法

BadDepartments 会先去重，再通过 LEFT JOIN ... IS NULL 排除部门。也可以改写为 NOT EXISTS，表达相同的反连接意图。哪种写法更快，需要结合实际数据和执行计划验证：

WITH DeptStats AS (
    SELECT a."ID_department", COUNT(e."inventory_number") AS cnt
    FROM auditory a
    JOIN equipment e ON a."number_auditory" = e."number_auditory"
    GROUP BY a."ID_department"
)
SELECT
    d."name" AS "division",
    COALESCE(ds.cnt, 0) AS "num of equip"
FROM department d
LEFT JOIN DeptStats ds ON d."ID_department" = ds."ID_department"
WHERE NOT EXISTS (
    SELECT 1
    FROM auditory a
    JOIN equipment e ON a."number_auditory" = e."number_auditory"
    JOIN inventorylist il ON e."inventory_number" = il."inventory_number"
    WHERE a."ID_department" = d."ID_department"
      AND il."ID_total" IN (2, 3)
)
ORDER BY d."name" ASC;

如果业务不要求按名称排序，移除 ORDER BY 可以省去排序步骤。若结果必须排序，就需要保留它。如果调用方只需要分页或前几条记录，可以根据业务需求添加 LIMIT，但返回的结果范围也会随之改变。

还可以分别检查有无 (name, ID_department) 索引时的完整计划。计划没有选择该索引，并不代表索引失效。计划中的连接条件还出现了将 number_auditory 转成 text 的表达式，建议确认两侧列的数据类型是否一致。当前计划仍使用了 auditory 上的索引；统一连接列类型也有助于避免不必要的转换及潜在的索引匹配问题。先根据实际耗时、BUFFERS 和数据分布确认瓶颈，再针对瓶颈调整一项并复查，避免盲目增加索引。