如何优化PostgreSQL中含数组比较的SELECT COUNT查询？

问题背景 #

现有一张1000万条记录的表，包含contained_special_ids数组类型列，表结构示例如下：

id |  content |  contained_special_ids 
----------------------------------------
1  |  abc     |  { 1, 2 }
2  |  abd     |  { 1, 3 }
3  |  abe     |  { 1, 4 }
4  |  abf     |  { 3 }
5  |  abg     |  { 2 }
6  |  abh     |  { 3 }

需要统计contained_special_ids中包含3的记录数，使用SQL语句：

select count(*) from my_table where contained_special_ids @> array[3]

数据量较小时查询正常，但1000万条记录下耗时超30秒。已为该列创建GIN索引：

"index_my_table_on_contained_special_ids" gin (contained_special_ids)

EXPLAIN执行计划（中文翻译） #

Finalize Aggregate  (成本=1049019.17..1049019.18 行数=1 宽度=8) (实际时间=44343.230..44362.224 行数=1 循环=1)
  输出: count(*)
  ->  Gather  (成本=1049018.95..1049019.16 行数=2 宽度=8) (实际时间=44340.332..44362.217 行数=3 循环=1)
        输出: (PARTIAL count(*))
        计划的工作进程数: 2
        启动的工作进程数: 2
        ->  Partial Aggregate  (成本=1048018.95..1048018.96 行数=1 宽度=8) (实际时间=44337.615..44337.615 行数=1 循环=3)
              输出: PARTIAL count(*)
              工作进程0: 实际时间=44336.442..44336.442 行数=1 循环=1
              工作进程1: 实际时间=44336.564..44336.564 行数=1 循环=1
              ->  并行位图堆扫描 public.my_table  (成本=9116.31..1046912.22 行数=442694 宽度=0) (实际时间=330.602..44304.221 行数=391431 循环=3)
                    重新检查条件: (my_table.contained_special_ids @> '{12511}'::bigint[])
                    索引重新检查过滤的行数: 501077
                    堆块: exact=67496 lossy=109789
                    工作进程0: 实际时间=329.547..44301.513 行数=409272 循环=1
                    工作进程1: 实际时间=329.794..44304.582 行数=378538 循环=1
                    ->  位图索引扫描 index_my_table_on_contained_special_ids  (成本=0.00..8850.69 行数=1062465 宽度=0) (实际时间=278.413..278.414 行数=1176563 循环=1)
                          索引条件: (my_table.contained_special_ids @> '{12511}'::bigint[])
规划时间: 1.041 ms
执行时间: 44362.262 ms

关键性能瓶颈分析 #

从执行计划可以看出：

• 位图索引扫描阶段耗时仅278ms，效率很高
• 耗时主要集中在并行位图堆扫描阶段（约44秒），核心原因是：
  1. lossy=109789：大量堆块以lossy模式存储，说明work_mem内存不足，位图无法保存精确的行指针，只能存储块地址，导致回表后必须逐个检查块内所有行是否符合条件
  2. Rows Removed by Index Recheck: 501077：回表后有大量行被过滤，进一步增加了无效的IO和计算开销

优化方案 #

1. 临时调大work_mem参数 #

直接解决lossy位图问题，让位图能存储精确的行指针，避免回表后的全块检查：

-- 临时设置会话级别的work_mem，可根据服务器内存调整（如64MB/128MB）
SET work_mem = '64MB';
-- 重新执行查询
select count(*) from my_table where contained_special_ids @> array[3];

如果该查询是高频操作，可修改postgresql.conf中的work_mem参数（需重启生效），或针对特定用户/表设置：

-- 针对用户设置
ALTER USER your_user SET work_mem = '64MB';
-- 针对表设置
ALTER TABLE my_table SET (work_mem = '64MB');

2. 优化表的可见性映射，尝试索引-only扫描 #

COUNT(*)需要确认行的MVCC可见性，若表的可见性映射（VM）未及时更新，无法使用索引-only扫描。执行VACUUM更新可见性映射：

VACUUM ANALYZE my_table;

更新后，查询可能直接通过GIN索引完成计数，无需回表。

3. 拆分数组为行结构，使用B树索引 #

如果经常需要按单个元素统计记录数，可将数组列拆分为独立的行表，用B树索引替代GIN索引，提升计数效率：

-- 创建拆分后的表
CREATE TABLE my_table_special_ids AS
SELECT id, unnest(contained_special_ids) AS special_id
FROM my_table;

-- 创建B树索引
CREATE INDEX idx_my_table_special_id ON my_table_special_ids(special_id);

-- 执行统计查询
SELECT COUNT(DISTINCT id) FROM my_table_special_ids WHERE special_id = 3;

若原表数据频繁更新，可创建触发器维护拆分表的同步：

-- 插入触发器
CREATE OR REPLACE FUNCTION sync_special_ids_insert()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO my_table_special_ids(id, special_id)
    SELECT NEW.id, unnest(NEW.contained_special_ids);
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_my_table_insert
AFTER INSERT ON my_table
FOR EACH ROW EXECUTE FUNCTION sync_special_ids_insert();

-- 更新触发器
CREATE OR REPLACE FUNCTION sync_special_ids_update()
RETURNS TRIGGER AS $$
BEGIN
    DELETE FROM my_table_special_ids WHERE id = OLD.id;
    INSERT INTO my_table_special_ids(id, special_id)
    SELECT NEW.id, unnest(NEW.contained_special_ids);
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_my_table_update
AFTER UPDATE ON my_table
FOR EACH ROW EXECUTE FUNCTION sync_special_ids_update();

-- 删除触发器
CREATE OR REPLACE FUNCTION sync_special_ids_delete()
RETURNS TRIGGER AS $$
BEGIN
    DELETE FROM my_table_special_ids WHERE id = OLD.id;
    RETURN OLD;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_my_table_delete
AFTER DELETE ON my_table
FOR EACH ROW EXECUTE FUNCTION sync_special_ids_delete();

4. 调整并行扫描参数 #

从执行计划看，当前使用2个工作进程，可根据服务器CPU核心数调整max_parallel_workers_per_gather参数，提升并行处理能力：

-- 临时设置
SET max_parallel_workers_per_gather = 4;
-- 永久设置需修改postgresql.conf

内容的提问来源于Stack Exchange，提问作者Siwei