PostgreSQL为何使用UNIQUE索引而非FTS索引？

我有一张超过1000万行的表，其中OfficialEnterprise_vatNumber列需要唯一且支持全文检索。已创建以下索引：

"uq_officialenterprise_vatnumber"   "CREATE UNIQUE INDEX uq_officialenterprise_vatnumber ON commonservices.""OfficialEnterprise"" USING btree (""OfficialEnterprise_vatNumber"")"
"ix_officialenterprise_vatnumber"   "CREATE INDEX ix_officialenterprise_vatnumber ON commonservices.""OfficialEnterprise"" USING gin (to_tsvector('commonservices.unaccent_dictionary'::regconfig, (""OfficialEnterprise_vatNumber"")::text))"

但执行以下本应使用全文检索（FTS）索引的查询，并通过EXPLAIN分析时：

SELECT * FROM commonservices."OfficialEnterprise" 
WHERE 
  to_tsvector('commonservices.unaccent_dictionary', "OfficialEnterprise_vatNumber") @@ to_tsquery('FR:* | IE:*')
ORDER BY "OfficialEnterprise_vatNumber" ASC 
LIMIT 100

发现实际使用的是uq_officialenterprise_vatnumber（BTREE唯一索引）而非ix_officialenterprise_vatnumber（GIN全文索引）。

补充信息 #

原查询的EXPLAIN ANALYZE结果 #

"Limit  (cost=0.43..1460.27 rows=100 width=238) (actual time=6996.976..6997.057 rows=15 loops=1)"
"  ->  Index Scan using uq_officialenterprise_vatnumber on ""OfficialEnterprise""  (cost=0.43..1067861.32 rows=73149 width=238) (actual time=6996.975..6997.054 rows=15 loops=1)"
"        Filter: (to_tsvector('commonservices.unaccent_dictionary'::regconfig, (""OfficialEnterprise_vatNumber"")::text) @@ to_tsquery('FR:* | IE:*'::text))"
"        Rows Removed by Filter: 1847197"
"Planning Time: 0.185 ms"
"Execution Time: 6997.081 ms"

修改ORDER BY后（添加|| '0'）的EXPLAIN ANALYZE结果 #

"Limit  (cost=55558.82..55570.49 rows=100 width=270) (actual time=7.069..9.827 rows=15 loops=1)"
"  ->  Gather Merge  (cost=55558.82..62671.09 rows=60958 width=270) (actual time=7.068..9.823 rows=15 loops=1)"
"        Workers Planned: 2"
"        Workers Launched: 2"
"        ->  Sort  (cost=54558.80..54635.00 rows=30479 width=270) (actual time=0.235..0.238 rows=5 loops=3)"
"              Sort Key: (((""OfficialEnterprise_vatNumber"")::text || '0'::text))"
"              Sort Method: quicksort  Memory: 28kB"
"              Worker 0:  Sort Method: quicksort  Memory: 25kB"
"              Worker 1:  Sort Method: quicksort  Memory: 25kB"
"              ->  Parallel Bitmap Heap Scan on ""OfficialEnterprise""  (cost=719.16..53393.91 rows=30479 width=270) (actual time=0.157..0.166 rows=5 loops=3)"
"                    Recheck Cond: (to_tsvector('commonservices.unaccent_dictionary'::regconfig, (""OfficialEnterprise_vatNumber"")::text) @@ to_tsquery('FR:* | IE:*'::text))"
"                    Heap Blocks: exact=6"
"                    ->  Bitmap Index Scan on ix_officialenterprise_vatnumber  (cost=0.00..700.87 rows=73149 width=0) (actual time=0.356..0.358 rows=15 loops=1)"
"                          Index Cond: (to_tsvector('commonservices.unaccent_dictionary'::regconfig, (""OfficialEnterprise_vatNumber"")::text) @@ to_tsquery('FR:* | IE:*'::text))"
"Planning Time: 0.108 ms"
"Execution Time: 9.886 ms"

请问我忽略了什么导致索引选择异常？

这是PostgreSQL查询优化器的成本估算逻辑导致的，核心是ORDER BY + LIMIT组合让优化器误判了路径成本：

1. BTREE索引的有序性“诱惑”
   uq_officialenterprise_vatnumber是BTREE索引，数据本身按OfficialEnterprise_vatNumber有序存储。优化器觉得：走这个索引顺序扫描，找到100条符合条件的记录就能直接返回，不用额外排序，节省了排序开销——这个思路本身没错，但它低估了需要扫描的行数。

2. 估算偏差踩坑
   从执行计划能看到，实际扫描了184万多行才找到15条符合条件的记录，但优化器原本以为只要扫少量行就能凑够LIMIT的100条，所以选了BTREE索引的“顺序扫描+过滤”路径，结果反而慢得离谱。

3. 修改ORDER BY后的逻辑反转
   加了|| '0'后，原BTREE索引的有序性用不上了——排序字段变成了拼接后的字符串，优化器必须先把所有符合条件的记录找出来再排序。这时候走GIN索引筛选的成本明显更低，所以它就选了正确的全文索引。

解决办法 #

• 强制指定索引：PostgreSQL 11及以上版本支持索引提示，直接告诉优化器用GIN索引：

  SELECT * FROM commonservices."OfficialEnterprise" 
  WHERE 
    to_tsvector('commonservices.unaccent_dictionary', "OfficialEnterprise_vatNumber") @@ to_tsquery('FR:* | IE:*')
  ORDER BY "OfficialEnterprise_vatNumber" ASC 
  LIMIT 100
  INDEX ix_officialenterprise_vatnumber;
  

  或者加个OFFSET 0，有时候能让优化器重新评估路径：

  SELECT * FROM commonservices."OfficialEnterprise" 
  WHERE 
    to_tsvector('commonservices.unaccent_dictionary', "OfficialEnterprise_vatNumber") @@ to_tsquery('FR:* | IE:*')
  ORDER BY "OfficialEnterprise_vatNumber" ASC 
  LIMIT 100 OFFSET 0;
  

• 更新统计信息：执行ANALYZE commonservices."OfficialEnterprise";，让优化器拿到更准确的表数据分布，修正成本估算。

• 拆分查询逻辑：用子查询先筛选再排序，阻断优化器的“有序扫描”思路：

  SELECT * FROM (
    SELECT * FROM commonservices."OfficialEnterprise" 
    WHERE to_tsvector('commonservices.unaccent_dictionary', "OfficialEnterprise_vatNumber") @@ to_tsquery('FR:* | IE:*')
  ) AS filtered
  ORDER BY "OfficialEnterprise_vatNumber" ASC 
  LIMIT 100;
  

  子查询会优先用GIN索引筛选出符合条件的记录，外层再用BTREE索引排序，兼顾了筛选效率和排序需求。

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