PostgreSQL14主键列pg_trgm索引致连接极慢 如何指定使用btree索引

不需要删除现有pg_trgm索引，以下方案都可以实现「等值连接走btree索引、ILIKE模糊查询保留pg_trgm索引能力」的效果：

方案1：为order_id列补充创建btree索引（优先推荐） #

• pg_trgm索引的设计目标是服务模糊匹配场景，等值查询、等值连接场景下btree索引的执行效率比pg_trgm索引高1~2个数量级，两类索引可以在同一列共存，不会互相冲突。
• 线上环境创建索引用CONCURRENTLY模式避免锁表阻塞业务：

-- 注意order是SQL关键字，表名需要加双引号
CREATE INDEX CONCURRENTLY idx_order_order_id_btree ON "order" (order_id);
CREATE INDEX CONCURRENTLY idx_order_rows_order_id_btree ON order_rows (order_id);

• 索引创建完成后执行以下命令刷新统计信息：

ANALYZE "order";
ANALYZE order_rows;

• 之后优化器会自动在等值连接、等值过滤场景选择btree索引，ILIKE、正则匹配等模糊查询场景仍然会使用原有的pg_trgm索引，不需要修改任何业务SQL。

方案2：调整pg_trgm代价参数引导优化器选择 #

如果暂时不方便创建新索引，或是已经存在btree索引但优化器仍然误选pg_trgm索引，可以直接调整pg_trgm扩展自带的等值查找代价参数，从代价估算层面避免优化器选择pg_trgm索引做等值操作：

• PostgreSQL 14 随pg_trgm新增了pg_trgm.equality_search_cost参数，专门控制优化器估算pg_trgm索引执行等值查找的成本，默认值为100，将该值调大到1000及以上，就会让优化器判定pg_trgm做等值查找的成本远高于btree索引：

-- 全局永久生效，需要超级用户权限
ALTER SYSTEM SET pg_trgm.equality_search_cost = 1000;
SELECT pg_reload_conf();

-- 仅当前会话临时生效，可用于效果验证
SET pg_trgm.equality_search_cost = 1000;

• 参数调整后同样执行ANALYZE刷新表统计信息，再通过EXPLAIN查看连接语句的执行计划，确认索引选择符合预期即可。

方案3：通过pg_hint_plan强制指定索引（仅应急使用） #

如果既不能加索引也不能调整全局参数，可以安装pg_hint_plan扩展，对出现性能问题的SQL单独加hint，强制执行计划走btree索引。该方案需要逐句修改问题SQL，维护成本极高，仅适合临时止血场景，不推荐长期使用。

注意事项 #

• 不要直接删除pg_trgm索引，否则所有依赖该索引的模糊查询会退化为全表扫描，引发更严重的性能问题。
• 所有变更请先在测试环境验证执行计划和性能表现，确认无问题再上线到生产环境。

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