Postgres多索引场景下为何未选用更高效的company_id_personal_mobile_index？

背景信息 #

表定义 #

CREATE TABLE IF NOT EXISTS employeedb.employee_tbl
(
    id                         integer                  NOT NULL GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1 ),
    dm_company_id              integer                  NOT NULL,
    customer_employee_id       text                     NOT NULL,
    personal_mobile            text,
    CONSTRAINT employee_pkey PRIMARY KEY (id),
    CONSTRAINT employee_id_company_constraint UNIQUE (dm_company_id, customer_employee_id)
);

创建的索引 #

针对personal_mobile创建的复合索引：

CREATE INDEX company_id_personal_mobile_index ON employeedb.employee_tbl (dm_company_id, personal_mobile COLLATE "C");

查询语句及执行计划 #

执行的查询：

explain analyse select * from employeedb.employee_tbl where dm_company_id = 2011 and employee_tbl.personal_mobile like '+1%';

PostgreSQL实际选用的执行计划：

Index Scan using employee_id_company_constraint on employee_tbl  (cost=0.14..8.16 rows=1 width=641) (actual time=0.056..0.093 rows=2 loops=1)
  Index Cond: (dm_company_id = 2011)
  Filter: (personal_mobile ~~ '+1%'::text)
  Rows Removed by Filter: 28
Planning Time: 0.951 ms
Execution Time: 0.173 ms

原因分析 #

PostgreSQL未选用company_id_personal_mobile_index主要有以下几点原因：

• 排序规则不匹配：你创建的索引中personal_mobile指定了COLLATE "C"排序规则，但查询语句里的personal_mobile like '+1%'使用的是列默认的排序规则（非C）。排序规则不匹配时，索引内的字符串排序逻辑和查询过滤的逻辑不一致，数据库无法通过该索引直接定位+1%的范围条件，导致索引无法发挥过滤LIKE条件的作用。

• 数据量极小，成本估算倾向简单路径：从执行计划可见，dm_company_id=2011的总记录仅30条（返回2条，过滤28条）。PostgreSQL认为，先用唯一索引employee_id_company_constraint定位到目标公司的所有记录，再在内存中过滤LIKE条件的开销，比使用另一索引的成本更低——小数据量下两种路径的性能差距微乎其微，优化器会选择它判断更“廉价”的执行方式。

• 唯一索引的特性优势：employee_id_company_constraint是唯一约束对应的索引，这类索引在PostgreSQL中会被做针对性优化，结构上同样能快速定位dm_company_id=2011的所有行。对于极小数据量的查询，优化器更倾向于选择已存在的、它认为更可靠的唯一索引。

解决办法 #

如果希望优化器选用company_id_personal_mobile_index，可以尝试：

• 让查询排序规则与索引匹配：将查询语句修改为personal_mobile COLLATE "C" like '+1%'
• 更新表统计信息：执行ANALYZE employeedb.employee_tbl;，让优化器获取更准确的数据分布情况
• 强制使用索引（仅用于测试，不推荐生产环境）：使用SELECT * FROM employeedb.employee_tbl INDEX USING company_id_personal_mobile_index WHERE dm_company_id=2011 AND personal_mobile like '+1%';

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