PostgreSQL为何忽略字符串数组列的GIN索引？如何优化？

问题背景 #

我在students表中有字符串类型的数组列read_allow_sids和read_deny_sids，列中通常包含数百个元素。使用&&运算符查询该列与目标字符串数组的交集，SQL语句如下：

SELECT students.*
FROM students
WHERE (students.read_allow_sids && ARRAY['long', 'list', 'of', 'strings'])
AND NOT (students.read_deny_sids && ARRAY['long', 'list', 'of', 'strings'])

测试库中该表约有1万条记录，顺序扫描耗时约4.5秒。我通过Rails迁移为这两个数组列添加了GIN索引：

add_index :students, :read_allow_sids, using: 'gin'
add_index :students, :read_deny_sids, using: 'gin'

但通过EXPLAIN发现查询规划器仍选择顺序扫描，忽略GIN索引。手动执行SET enable_seqscan TO OFF;强制使用索引后，查询耗时降至200ms，性能提升约20倍。已执行ANALYZE更新表统计信息，但问题依旧。

提出问题 #

1. 索引性能更优，为何查询规划器偏好顺序扫描？
2. 如何在生产环境中让查询规划器优先选用GIN索引？

附：EXPLAIN(ANALYZE, VERBOSE, BUFFERS)输出 #

顺序扫描开启时： #

Seq Scan on public.students (cost=0.00..970.57 rows=786 width=522) (actual time=3.429..4319.502 rows=1698 loops=1)
  Output: id, classroom_id, created_at, updated_at, user_id, read_allow_sids, read_deny_sids, write_allow_sids, write_deny_sids
  Filter: ((students.read_allow_sids && '{long, list, of, strings}'::character varying[]) AND (NOT (students.read_deny_sids && '{long, list, of, strings}'::character varying[])))
  Rows Removed by Filter: 9554
  Buffers: shared hit=803
Planning Time: 20.932 ms
Execution Time: 4320.698 ms

顺序扫描关闭时： #

Bitmap Heap Scan on public.students  (cost=1734.59..2634.39 rows=786 width=522) (actual time=127.219..137.151 rows=1698 loops=1)
  Output: id, classroom_id, created_at, updated_at, user_id, read_allow_sids, read_deny_sids, write_allow_sids, write_deny_sids
  Recheck Cond: (students.read_allow_sids && '{long, list, of, strings}'::character varying[])
  Filter: (NOT (students.read_deny_sids && '{long, list, of, strings}'::character varying[]))
  Heap Blocks: exact=549
  Buffers: shared hit=1390
  ->  Bitmap Index Scan on index_students_on_read_allow_sids  (cost=0.00..1734.40 rows=6453 width=0) (actual time=126.842..126.843 rows=1698 loops=1)
        Index Cond: (students.read_allow_sids && '{long, list, of, strings}'::character varying[])
        Buffers: shared hit=841
Planning Time: 21.460 ms
Execution Time: 137.890 ms

问题解答 #

1. 查询规划器偏好顺序扫描的原因 #

从执行计划的成本估算来看，规划器认为顺序扫描的成本（cost=0.00..970.57）远低于索引扫描的成本（cost=1734.59..2634.39），这是核心原因，具体偏差来源包括：

• 数组列统计信息不足：虽然执行了ANALYZE，但PostgreSQL默认对数组类型的统计粒度有限，无法准确评估数百元素数组的&&运算开销，导致规划器低估了顺序扫描的计算成本。
• 索引成本参数偏差：默认的random_page_cost（值为4）是针对机械硬盘设置的，若使用SSD存储，实际随机读性能远优于估算值，规划器会高估索引扫描的IO成本。
• 返回行数估算错误：规划器预估索引扫描会返回6453行，但实际仅返回1698行，说明它对read_allow_sids与目标数组的匹配率估算严重偏高，进而错误计算了索引扫描的总成本。

2. 生产环境中引导规划器选用GIN索引的方法 #

可以通过以下几种方式调整，让规划器做出合理选择：

• 提升数组列统计粒度：增加统计目标值，让PostgreSQL收集更详细的数组元素分布信息，执行后重新分析表：

  ALTER TABLE students ALTER COLUMN read_allow_sids SET STATISTICS 1000;
  ALTER TABLE students ALTER COLUMN read_deny_sids SET STATISTICS 1000;
  ANALYZE students;
  

  注：1000为较高统计目标，最大值为10000，可根据实际情况调整。
• 调整存储成本参数：若使用SSD，降低random_page_cost以准确评估索引IO成本：

  -- 会话级临时测试
  SET random_page_cost = 1.1;
  -- 全局永久生效，需修改postgresql.conf后重启数据库
  # random_page_cost = 1.1
  

• 查询中添加索引提示：若上述方法无效，可在查询中显式指定使用索引：

  SELECT students.*
  FROM students
  WHERE (students.read_allow_sids && ARRAY['long', 'list', 'of', 'strings'])
  AND NOT (students.read_deny_sids && ARRAY['long', 'list', 'of', 'strings'])
  INDEX index_students_on_read_allow_sids;
  

• 重构查询语句：通过子查询先筛选read_allow_sids的候选集，再过滤read_deny_sids，引导规划器使用索引：

  SELECT s.*
  FROM (
    SELECT * FROM students
    WHERE read_allow_sids && ARRAY['long', 'list', 'of', 'strings']
  ) s
  WHERE NOT (s.read_deny_sids && ARRAY['long', 'list', 'of', 'strings']);
  

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