PostgreSQL未按预期使用覆盖索引的问题排查与优化咨询

使用PostgreSQL 12.3，表结构如下：

create table records
(
    id                           serial                                           primary key,
    number                       varchar(20)                                      not null,
    owner_id                     integer                                          not null,
    state                        varchar(16)    default 'open'::character varying not null,
    created_at                   date,
    updated_at                   date,
    finished_at                  date
);

执行分页查询：

EXPLAIN (ANALYSE, BUFFERS)
SELECT "records".*
FROM "records"
WHERE "records"."trashed_at" IS NULL
  AND "records"."owner_id" = 11
  AND "records"."state" IN ('fresh', 'processing')
ORDER BY "records"."created_at" DESC, "records"."number" DESC
LIMIT 20 OFFSET 0;

遇到的问题：

• 执行计划选用旧索引index_records_on_owner_id_and_created_at_and_number，但因大量过滤导致缓存命中时仍耗时约300ms，且规划器估算偏差严重（已执行VACUUM ANALYZE）。
• 创建优化索引index_records_optimize_sort_on_created_at_and_number_in期望避免过滤，但规划器未选用：

  create index index_records_optimize_sort_on_created_at_and_number_in
      on records (owner_id asc, created_at desc, number desc)
      include (state)
      where (trashed_at IS NULL);
  

• 若创建带特定state条件的索引可适配当前查询，但UI支持多状态组合选择，该索引仅适用于单一场景。

需求：确认是否遗漏文档要点，能否通过修改单个索引适配多状态组合查询，并让规划器选用。

一、新索引未被选用的可能原因 #

1. 统计信息偏差：尽管执行了VACUUM ANALYZE，新索引的统计信息可能未被正确采集，或规划器对state过滤后的行数估算错误，导致认为旧索引成本更低。
2. 旧索引优先级：规划器可能因旧索引存在时间更久、统计信息更完善或结构更简单，优先选择它。
3. 成本参数设置：PostgreSQL默认的random_page_cost（默认值4）可能更倾向于顺序扫描或旧索引，导致新索引的成本估算偏高。

二、验证与调整方案 #

1. 验证新索引有效性
   使用索引提示强制规划器使用新索引，测试性能是否提升：

   EXPLAIN (ANALYSE, BUFFERS)
   SELECT "records".*
   FROM "records" INDEX USING index_records_optimize_sort_on_created_at_and_number_in
   WHERE "records"."trashed_at" IS NULL
     AND "records"."owner_id" = 11
     AND "records"."state" IN ('fresh', 'processing')
   ORDER BY "records"."created_at" DESC, "records"."number" DESC
   LIMIT 20 OFFSET 0;
   

   若性能明显提升，说明索引本身有效，问题出在规划器的选择逻辑上。

2. 引导规划器选用新索引

   • 更新统计信息：执行ANALYZE records;确保表和新索引的统计信息完全更新。
   • 调整成本参数：临时降低random_page_cost，让规划器更倾向于索引扫描：

     SET random_page_cost = 2;
     

     重新执行查询，若规划器选用新索引且性能达标，可考虑在数据库配置中永久调整该参数，或针对特定会话设置。
   • 清理旧索引：若旧索引无其他查询依赖，可删除它，消除规划器的选择干扰。

3. 优化索引结构适配多状态
   当前的新索引已支持多状态组合查询（未在WHERE中限制state），若需进一步提升性能，可调整索引结构：

   • 将state加入索引键，增强过滤效率（不破坏排序顺序）：

     CREATE INDEX index_records_optimize_multi_state ON records 
     (owner_id asc, created_at desc, number desc, state)
     WHERE (trashed_at IS NULL);
     

     该索引可高效过滤任意state组合，同时保持排序顺序，但会增加索引体积。
   • 创建全覆盖索引，避免回表开销：

     CREATE INDEX index_records_covering ON records 
     (owner_id asc, created_at desc, number desc)
     INCLUDE (id, number, state, updated_at, finished_at)
     WHERE (trashed_at IS NULL);
     

     查询可完全从索引获取数据，无需回表，性能大幅提升，但索引体积会显著增大，需权衡空间与性能。

内容的提问来源于stack exchange，提问作者arthurwozniak