PostgreSQL含三个及以上字段时GIN索引未命中问题求助

在PostgreSQL 14.2（Docker环境，宿主为macOS 13.1）中，创建包含id、name、created_at三个字段的user_test1表，并基于name字段创建使用gin_trgm_ops的GIN索引。执行模糊查询select name from user_test1 where name like '%123456%';时，执行计划显示为全表扫描（Seq Scan），未命中GIN索引；而创建仅含id、name两个字段的user_test2表，创建相同的GIN索引后执行相同查询，执行计划显示命中索引（Bitmap Index Scan）。尝试删除并重建表后问题依旧，特此求助。

PostgreSQL版本信息：PostgreSQL 14.2 (Debian 14.2-1.pgdg110+1) on aarch64-unknown-linux-gnu, compiled by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit
环境信息：macOS 13.1，Docker镜像postgres:14.2（IMAGE ID: 8b547b8bf0d7）

测试案例1：含created_at字段的表 #

测试SQL #

CREATE TABLE user_test1 (
    id bigserial PRIMARY KEY,
    name text NOT NULL,
    created_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX idx_user_name_test1 ON user_test1 using gin (name gin_trgm_ops);
explain analyse
select name from user_test1 where name like '%123456%';

执行结果 #

Seq Scan on user_test1  (cost=0.00..23.38 rows=1 width=32) (actual time=0.006..0.007 rows=0 loops=1)
   Filter: (name ~~ '%123456%'::text)
 Planning Time: 0.110 ms
 Execution Time: 0.030 ms
(4 rows)

测试案例2：仅含id、name字段的表 #

测试SQL #

CREATE TABLE user_test2 (
    id bigserial PRIMARY KEY,
    name text NOT NULL
);
CREATE INDEX idx_user_name_test2 ON user_test2 using gin (name gin_trgm_ops);
explain analyse
select name from user_test2 where name like '%123456%';

执行结果 #

Bitmap Heap Scan on user_test2  (cost=20.00..24.02 rows=1 width=32)
   Recheck Cond: (name ~~ '%123456%'::text)
   ->  Bitmap Index Scan on idx_user_name_test2  (cost=0.00..20.00 rows=1 width=0)
         Index Cond: (name ~~ '%123456%'::text)
(4 rows)

原因分析 #

PostgreSQL查询优化器会根据表元组大小、IO成本预估、索引启动成本等因素选择执行计划：

• 元组大小差异：user_test1多了created_at字段，单条记录存储空间更大，Bitmap Heap Scan回表读取数据的IO开销高于user_test2。
• 小表成本权衡：空表或数据量极小时，GIN索引的启动成本（索引扫描+回表总开销）高于全表扫描，优化器优先选择全表扫描。
• 统计信息不足：空表或数据量过少时，PostgreSQL统计信息不充分，成本预估倾向于全表扫描。

验证方法 #

1. 强制禁用全表扫描：会话级别执行set enable_seqscan = off;，再执行查询，验证索引本身是否有效。
2. 插入大量测试数据：向user_test1插入数千条数据后重新执行explain analyse，此时全表扫描IO成本上升，优化器会自动选择索引扫描。

解决建议 #

• 临时强制索引：会话级别设置set enable_seqscan = off;，不建议全局修改该参数。
• 调整成本参数：会话级别降低random_page_cost（如set random_page_cost = 2;）或提高seq_page_cost，引导优化器倾向索引扫描。
• 等待数据增长：表数据量达到一定规模后，优化器会自动选择索引扫描，无需手动干预。

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