PostgreSQL中JSONB索引已启用但查询仍缓慢的优化求助

问题描述 #

已创建表达式索引：

create index name_idx on transactions((details->>'name'));

执行以下查询时，表中数百万条数据耗时2-3分钟，执行计划确认已使用索引，但性能不佳：

select count(*) from transactions where (details->>'name') in ('Tom', 'Ken', 'Cherry');

执行计划（中文翻译） #

最终聚合  (成本=500691.85..500691.86 行数=1 宽度=8) (实际时间=158421.546..158723.501 行数=1 循环=1)
  输出: count(*)
  缓冲区: 共享命中=19078917 读取=1643001
  ->  收集  (成本=500691.64..500691.85 行数=2 宽度=8) (实际时间=158389.403..158721.346 行数=3 循环=1)
        输出: (部分 count(*))
        计划工作进程数: 2
        启动工作进程数: 2
        缓冲区: 共享命中=19078917 读取=1643001
        ->  部分聚合  (成本=499691.64..499691.65 行数=1 宽度=8) (实际时间=158034.608..158034.946 行数=1 循环=3)
              输出: 部分 count(*)
              缓冲区: 共享命中=19078917 读取=1643001
              工作进程0: 实际时间=157861.445..157861.673 行数=1 循环=1
                JIT:
                  函数数: 5
                  选项: 内联=true, 优化=true, 表达式=true, 变形=true
                  耗时: 生成2.171毫秒, 内联231.577毫秒, 优化35.146毫秒, 发射43.585毫秒, 总计312.479毫秒
                缓冲区: 共享命中=6376436 读取=547834
              工作进程1: 实际时间=157861.810..157862.080 行数=1 循环=1
                JIT:
                  函数数: 5
                  选项: 内联=true, 优化=true, 表达式=true, 变形=true
                  耗时: 生成1.931毫秒, 内联230.618毫秒, 优化36.129毫秒, 发射44.178毫秒, 总计312.856毫秒
                缓冲区: 共享命中=6375452 读取=547682
              ->  并行位图堆扫描 on public.transactions t  (成本=2000.04..499504.94 行数=74679 宽度=0) (实际时间=903.630..157497.724 行数=1706863 循环=3)
                    重检查条件: ((t.details ->> 'name'::text) = ANY ('{Tom, Cherry, Ken}'::text[]))
                    索引重检查移除行数: 88
                    堆块: 精确=12348 有损=327329
                    缓冲区: 共享命中=19078917 读取=1643001
                    工作进程0: 实际时间=749.550..157337.900 行数=1711896 循环=1
                      缓冲区: 共享命中=6376436 读取=547834
                    工作进程1: 实际时间=749.524..157373.287 行数=1709957 循环=1
                      缓冲区: 共享命中=6375452 读取=547682
                    ->  位图索引扫描 on test1_idx  (成本=0.00..1955.24 行数=179230 宽度=0) (实际时间=956.688..956.689 行数=5120588 循环=1)
                          索引条件: ((t.details ->> 'name'::text) = ANY ('{Tom, Cherry, Ken}'::text[]))
                          缓冲区: 共享命中=17 读取=4313
设置: search_path = 'public, public, "$user"'
规划时间: 6.219毫秒
JIT:
  函数数: 17
  选项: 内联=true, 优化=true, 表达式=true, 变形=true
  耗时: 生成38.523毫秒, 内联504.706毫秒, 优化167.494毫秒, 发射203.509毫秒, 总计914.232毫秒
执行时间: 158769.716毫秒

关键性能瓶颈分析 #

从执行计划可定位核心问题：

• 位图索引扫描仅耗时约956ms，效率正常；
• 并行位图堆扫描耗时超157秒，是主要性能瓶颈；
• 堆块中存在大量有损=327329记录，说明当前work_mem不足，PostgreSQL无法存储完整位图，只能采用有损压缩模式，导致需重新读取数据块验证条件，IO开销剧增；
• 查询匹配行数达512万，需扫描大量堆数据。

优化方案 #

1. 调整work_mem参数 #

增大work_mem以存储完整位图，避免有损扫描：

-- 会话级临时设置
SET work_mem = '64MB';
-- 永久设置（修改postgresql.conf后重启服务）
work_mem = 64MB

可根据服务器内存情况调整至128MB或更高，直到执行计划中Heap Blocks的lossy值消失。

2. 创建覆盖索引 #

利用覆盖索引直接完成统计，无需访问堆表：

-- PostgreSQL 11+支持include子句，包含主键即可实现覆盖
create index name_idx_covering on transactions((details->>'name')) include (id);

3. 提取JSON字段为普通存储列 #

若频繁按name查询，将JSON字段提取为普通列，性能优于表达式索引：

-- 添加自动同步的生成列
alter table transactions add column name text generated always as (details->>'name') stored;
-- 在普通列上创建索引
create index name_col_idx on transactions(name);

优化后查询语句：

select count(*) from transactions where name in ('Tom', 'Ken', 'Cherry');

4. 关闭不必要的JIT编译 #

执行计划中JIT总耗时约914ms，若无需即时编译可临时关闭：

SET jit = off;

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