PREPARE语句多次调用后弃用索引致查询变慢的原因排查

操作背景 #

我为products表的Title字段创建了如下GIN索引：

CREATE INDEX products_title_trgm_idx 
ON products USING gin(title gin_trgm_ops)

随后创建了一条预编译语句：

PREPARE my_test AS SELECT "products"."id","products"."active","products"."title","products"."subtitle","products"."description","products"."isbn","products"."ean","products"."bznr","products"."cover_picture","products"."publication_date","products"."edition","products"."publisher","products"."stock","products"."delivery_time","products"."selling_price","products"."width","products"."height","products"."length","products"."weight" FROM "products" WHERE products.title ILIKE $1 LIMIT 10;

连续5次执行该语句：

EXECUTE my_test('%Warum Frauen alles besser wissen - und trotzdem alles falsch machen%'); 

执行现象：前4次查询使用索引，耗时约20ms；第5次及之后的查询改用顺序扫描（seq scan），耗时3-4秒。禁用顺序扫描后查询始终保持20ms速度，但这并非合理方案。

原因分析 #

这是PostgreSQL中预编译语句执行计划缓存与统计信息交互导致的典型问题，核心原因如下：

• 预编译语句的计划生成与复用逻辑
  预编译语句的初始执行计划在首次执行时生成，前4次执行时，优化器结合LIMIT 10的限制，判断通过GIN索引能快速定位匹配行，因此选择索引扫描计划并缓存。

• 自动统计信息更新触发
  PostgreSQL会在表的查询/操作次数达到阈值时，通过autovacuum自动更新统计信息。第5次执行前，统计信息完成更新，优化器重新评估执行计划：由于gin_trgm_ops索引的统计信息对模糊匹配的行数预估存在偏差，优化器错误认为该ILIKE条件匹配的行数占比很高，判断顺序扫描的成本更低，因此切换执行计划。

• 通用计划的局限性
  预编译语句使用参数绑定后，默认会生成通用计划——该计划基于表的整体统计信息而非当前具体参数的匹配情况。即使本次查询的实际匹配行数极少，通用计划仍会按照“模糊查询普遍匹配行数多”的预设逻辑选择顺序扫描，忽略了LIMIT 10带来的索引扫描优势。

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