PostgreSQL满足条件却未选用部分索引ea_index的问题排查

问题背景 #

triples表结构如下：

Column   |  Type   | Collation | Nullable | Default
-----------+---------+-----------+----------+---------
 app_id    | uuid    |           | not null |
 entity_id | uuid    |           | not null |
 attr_id   | uuid    |           | not null |
 value     | text    |           | not null |
 ea_index  | boolean |           |          |
Indexes:
    "triples_pkey" PRIMARY KEY, btree (app_id, entity_id, attr_id, value)
    "ea_index" UNIQUE, btree (app_id, entity_id, attr_id) WHERE ea_index
    "triples_app_id" btree (app_id)
    "triples_attr_id" btree (attr_id)
Foreign-key constraints:
    "triples_app_id_fkey" FOREIGN KEY (app_id) REFERENCES apps(id) ON DELETE CASCADE
    "triples_attr_id_fkey" FOREIGN KEY (attr_id) REFERENCES attrs(id) ON DELETE CASCADE

已创建仅对ea_index = true的行生效的部分唯一索引ea_index。执行以下查询：

EXPLAIN (
  SELECT 
    * 
  FROM triples 
  WHERE 
    app_id = '6b1ca162-0175-4188-9265-849f671d56cc' AND
    entity_id = '6b1ca162-0175-4188-9265-849f671d56cc' AND 
    ea_index
);

得到的执行计划显示，查询使用了triples_app_id索引而非预期的ea_index：

Index Scan using triples_app_id on triples  (cost=0.28..4.30 rows=1 width=221)
   Index Cond: (app_id = '6b1ca162-0175-4188-9265-849f671d56cc'::uuid)
   Filter: (ea_index AND (entity_id = '6b1ca162-0175-4188-9265-849f671d56cc'::uuid))
(3 rows)

未使用部分索引的可能原因 #

• 统计信息过时：PostgreSQL优化器依赖表的统计数据估算执行成本。如果统计信息未及时更新，优化器可能误判ea_index的收益，转而选择triples_app_id索引。
• 返回行占比过高：如果符合app_id条件的行中，多数同时满足ea_index = true和entity_id条件，优化器会认为基于app_id索引扫描后过滤的成本更低——因为部分索引的额外查找开销超过了过滤收益。
• 索引成本估算偏差：优化器会计算满足部分索引WHERE ea_index的行占比，若认为该占比过高，会判定使用全app_id索引更高效。
• 索引列匹配不完整：ea_index的索引列是(app_id, entity_id, attr_id)，但查询未指定attr_id条件。虽然这不是无法使用索引的绝对障碍，但优化器可能认为仅匹配前两列的索引扫描收益不如单列的app_id索引。

调试与解决方法 #

• 更新统计信息：执行ANALYZE triples;强制刷新表的统计数据，之后重新执行EXPLAIN查看执行计划是否变化。
• 强制使用索引测试：通过索引提示强制优化器使用ea_index，命令如下：

  EXPLAIN SELECT * FROM triples INDEX (ea_index) 
  WHERE app_id = '6b1ca162-0175-4188-9265-849f671d56cc' 
    AND entity_id = '6b1ca162-0175-4188-9265-849f671d56cc' 
    AND ea_index;
  

  对比两种索引的执行成本，判断优化器的选择是否合理。也可临时禁用其他扫描方式，比如SET enable_seqscan = off;再执行查询测试。
• 查看统计与索引使用情况：
  • 查看表统计：SELECT * FROM pg_stat_user_tables WHERE relname = 'triples';
  • 查看索引使用统计：SELECT * FROM pg_stat_user_indexes WHERE relname = 'triples';
    确认ea_index是否从未被使用，以及表的行分布数据是否准确。
• 验证部分索引有效性：在psql中执行\d triples，再次确认ea_index的定义是否正确（确保WHERE ea_index指向的是表的ea_index字段）。
• 手动计算行占比：执行以下查询计算满足条件的行占比：

  SELECT 
    COUNT(*) FILTER (WHERE ea_index = true AND entity_id = '6b1ca162-0175-4188-9265-849f671d56cc') AS target_rows,
    COUNT(*) AS total_app_rows
  FROM triples 
  WHERE app_id = '6b1ca162-0175-4188-9265-849f671d56cc';
  

  如果target_rows占total_app_rows的比例超过30%左右，优化器选择triples_app_id索引是合理的，此时部分索引的优势不明显。

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