PostgreSQL分区裁剪与btree_gin索引未按预期生效问题

这个执行计划异常不是GIN索引统计信息缺失导致的，核心是两个问题叠加触发了PostgreSQL 14优化器的成本估算偏差：

1. 索引定义和查询不匹配：你之前创建的父表GIN索引存在两个冗余/不匹配问题：
   • 表本身是按species做LIST分区，带species='sparrow'过滤时会直接裁剪掉另外两个分区，索引中存储species列完全没有必要，反而会增大索引体积
   • 索引中的to_tsvector(description)没有指定english分词配置，和查询中使用的to_tsvector('english', description)不是严格一致的表达式，优化器无法直接判定索引可以匹配该查询条件
2. LIMIT子句触发的路径成本误判：当查询同时满足「分区裁剪后仅访问单分区」「按主键倒序排序」「小LIMIT值」三个特征时，优化器会优先选择主键索引倒序扫描、边扫边过滤的执行路径——它的估算逻辑是“只需扫描少量主键条目就能凑够10条符合条件的记录”，但实际符合全文检索条件的记录占比极低，这个估算和真实数据分布偏差极大，最终选了低效路径。

你测试的两个能命中索引的场景刚好都避开了这个误判逻辑：去掉LIMIT时，优化器判定需要返回所有匹配记录，主键全扫的成本远高于GIN位图扫描；去掉species过滤时，需要跨3个分区扫描，跨分区主键扫描的合并成本高于GIN扫描，因此都能选对计划。

按优先级从高到低选择：

方案1：创建与查询严格匹配的分区级GIN部分索引（推荐） #

分区裁剪后查询只会访问对应分区，不需要在父表创建带冗余列的索引，直接在各分区创建和查询表达式、过滤条件完全对齐的索引即可，索引体积更小、命中逻辑更稳定：

-- 先删除之前创建的冗余父表索引
DROP INDEX bird_species_description_index;

-- 为sparrow分区创建匹配查询的GIN部分索引
CREATE INDEX bird_sparrow_desc_fts_idx ON bird_sparrow USING GIN(to_tsvector('english', description))
WHERE description IS NOT NULL;

-- 其余两个分区如果也需要跑同类查询，可同步创建相同索引
CREATE INDEX bird_chicken_desc_fts_idx ON bird_chicken USING GIN(to_tsvector('english', description))
WHERE description IS NOT NULL;
CREATE INDEX bird_hawk_desc_fts_idx ON bird_hawk USING GIN(to_tsvector('english', description))
WHERE description IS NOT NULL;

-- 建完后执行统计信息更新
ANALYZE bird_sparrow, bird_chicken, bird_hawk;

创建完成后原查询无需任何修改，即可自动命中GIN索引走位图扫描，执行性能符合预期。

方案2：用CTE物化屏障绕开LIMIT路径误判 #

如果不想调整索引，可以通过MATERIALIZED CTE强制优化器先完成全文检索匹配，再做排序和LIMIT截断，避免LIMIT下推触发错误的路径选择：

WITH matched_records AS MATERIALIZED (
  SELECT * FROM bird
  WHERE
    species = 'sparrow'
    AND description IS NOT NULL
    AND to_tsvector('english', description) @@ plainto_tsquery('english', $1)
)
SELECT * FROM matched_records
ORDER BY id DESC
LIMIT 10;

方案3：临时参数调整（仅用于调试验证，不推荐线上使用） #

可以在会话级临时关闭普通索引扫描的优先级，强制优化器选择位图扫描路径：

-- 会话级设置，仅对当前连接生效
SET enable_indexscan = off;
-- 执行原查询
-- 执行完成后恢复参数默认值
RESET enable_indexscan;

该方式会影响当前连接所有查询的路径选择，不适合作为长期方案。

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