PostgreSQL含OR条件的JSONB数组查询慢及索引优化问询

表结构 #

data字段示例 #

["hex_address", 101, 1001, 51637660000, 51324528436, 6003, 235458709417729, 234683610487930] //交易方法
["hex_address", 200060013, 1, 250000000000, 3176359357709794, 6006, "0x00000000000000001431f1724a499fcd", 114440794831460] //添加方法
["hex_address", 200060013, 1, 42658905229340, 407285893749, "0x000000000000000000110204f76c06e2", 6006, 121017475390243, "0x000000000000000013bd9463821aedee"] //移除方法

现有索引 #

CREATE INDEX IDX_event_multicolumn_index ON public.events ("module", "method", "block_height") INCLUDE ("id", "idx", "data");
CREATE INDEX event_data_gin_index ON public.events USING gin (data);

慢查询详情 #

执行以下SQL时耗时约1分钟：

SELECT data FROM public.events 
WHERE module = 'amm' 
AND (
    (data::jsonb->6 IN ('6002', '6003') AND method = 'LiquidityRemoved') 
    OR 
    (data::jsonb->5 IN ('6002', '6003') AND method IN ('Traded', 'LiquidityAdded'))
) 
ORDER BY block_height DESC, idx DESC  
LIMIT 500;

带多列索引的执行计划（测试表） #

"限制  (cost=1909.21..1909.22 rows=5 width=334) (actual time=14019.484..14019.524 rows=100 loops=1)"
"  ->  排序  (cost=1909.21..1909.22 rows=5 width=334) (actual time=14019.477..14019.504 rows=100 loops=1)"
"        排序键: block_height DESC, idx DESC"
"        排序方法: top-N 堆排序  内存: 128kB"
"        ->  位图堆扫描 on events  (cost=114.28..1909.15 rows=5 width=334) (actual time=703.038..13957.503 rows=25625 loops=1)"
"              重检查条件: ((((module)::text = 'amm'::text) AND ((method)::text = 'LiquidityRemoved'::text)) OR (((module)::text = 'amm'::text) AND ((method)::text = ANY ('{Traded,LiquidityAdded}'::text[]))))"
"              过滤条件: ((((data -> 6) = ANY ('{5002,5003}'::jsonb[])) AND ((method)::text = 'LiquidityRemoved'::text)) OR (((data -> 5) = ANY ('{5002,5003}'::jsonb[])) AND ((method)::text = ANY ('{Traded,LiquidityAdded}'::text[]))))"
"              被过滤掉的行数: 9435"
"              堆块: exact=28532"
"              ->  位图或  (cost=114.28..114.28 rows=462 width=0) (actual time=696.569..696.580 rows=0 loops=1)"
"                    ->  位图索引扫描 on ""IDX_event_multicolumn_index""  (cost=0.00..4.59 rows=3 width=0) (actual time=24.375..24.382 rows=896 loops=1)"
"                          索引条件: (((module)::text = 'amm'::text) AND ((method)::text = 'LiquidityRemoved'::text))"
"                    ->  位图索引扫描 on ""IDX_event_multicolumn_index""  (cost=0.00..109.69 rows=459 width=0) (actual time=672.191..672.191 rows=34164 loops=1)"
"                          索引条件: (((module)::text = 'amm'::text) AND ((method)::text = ANY ('{Traded,LiquidityAdded}'::text[])))"

删除多列索引后的执行计划 #

"聚集合并  (cost=477713.00..477720.00 rows=60 width=130) (actual time=22151.357..22210.826 rows=79864 loops=1)"
"  计划的工作进程数: 2"
"  启动的工作进程数: 2"
"  ->  排序  (cost=476712.97..476713.05 rows=30 width=130) (actual time=22090.960..22097.308 rows=26621 loops=3)"
"        排序键: block_height"
"        排序方法: 外部合并  磁盘: 5400kB"
"        工作进程0:  排序方法: 外部合并  磁盘: 5264kB"
"        工作进程1:  排序方法: 外部合并  磁盘: 5416kB"
"        ->  并行顺序扫描 on events  (cost=0.00..476712.24 rows=30 width=130) (actual time=5.151..21985.878 rows=26621 loops=3)"
"              过滤条件: (((module)::text = 'amm'::text) AND ((((data -> 6) = ANY ('{6002,6003}'::jsonb[])) AND ((method)::text = 'LiquidityRemoved'::text)) OR (((data -> 5) = ANY ('{6002,6003}'::jsonb[])) AND ((method)::text = ANY ('{Traded,LiquidityAdded}'::text[])))))"
"              被过滤掉的行数: 2858160"
"规划时间: 0.559 ms"
"执行时间: 22217.351 ms"

尝试的优化 #

创建包含JSON字段的GIN索引，但效果与原多列索引一致：

create EXTENSION btree_gin;
CREATE INDEX extcondindex ON public.events USING gin (((data -> 5)), ((data -> 6)), module, method);

移除其中一个OR条件后，查询耗时约3秒，速度正常：

SELECT data FROM public.events 
WHERE module = 'amm' 
AND ((data::jsonb->6 IN ('6002', '6003') AND method = 'LiquidityRemoved')) 
ORDER BY block_height DESC, idx DESC  
LIMIT 500;

核心疑问 #

1. 为何多列索引会导致查询变慢？
2. 如何创建针对性索引优化该查询？

问题分析与解决方案 #

一、多列索引变慢的原因 #

从执行计划和IO时序可以看出核心问题：

1. 索引过滤能力不足：原多列索引仅基于module、method、block_height构建，未包含data字段的过滤条件。索引扫描会取出所有符合module='amm'且对应method的行（测试表中达3万+行），但近1/3的行最终会被data条件过滤，产生大量无效回表操作。
2. 随机IO开销过大：位图堆扫描需要从磁盘随机读取28000+个堆块，IO耗时占总执行时间的95%以上（read=13533ms）。而删除索引后的并行全表扫描是顺序IO，虽然扫描全表，但顺序IO效率远高于随机IO，因此总耗时更短。
3. OR条件的索引适配问题：原查询的OR条件对应两种不同的data字段位置（data->5和data->6），单一索引无法同时高效匹配两种过滤逻辑，导致索引只能做部分过滤，剩余逻辑依赖内存过滤。

二、针对性优化方案 #

将原查询拆分为两个独立子查询，分别创建适配的索引，再通过UNION ALL合并结果后排序，避免OR条件带来的索引低效问题。

1. 创建专用索引

针对两个子条件分别创建覆盖索引，包含排序字段和所需数据，避免回表：

-- 适配LiquidityRemoved方法+data->6的索引
CREATE INDEX idx_amm_removed_data6 ON public.events 
(module, method, (data->>6)::integer, block_height DESC, idx DESC)
INCLUDE (data)
WHERE method = 'LiquidityRemoved';

-- 适配Traded/LiquidityAdded方法+data->5的索引
CREATE INDEX idx_amm_tradeadd_data5 ON public.events 
(module, method, (data->>5)::integer, block_height DESC, idx DESC)
INCLUDE (data)
WHERE method IN ('Traded', 'LiquidityAdded');

注：使用data->>6将JSONB转为文本后再转成整数，可使用高效的B-tree索引，比直接用JSONB操作的索引性能更好。

2. 优化后的查询语句

SELECT data FROM (
    -- 处理LiquidityRemoved的情况
    SELECT data, block_height, idx 
    FROM public.events 
    WHERE module = 'amm' 
      AND method = 'LiquidityRemoved' 
      AND (data->>6)::integer IN (6002, 6003)
    
    UNION ALL
    
    -- 处理Traded和LiquidityAdded的情况
    SELECT data, block_height, idx 
    FROM public.events 
    WHERE module = 'amm' 
      AND method IN ('Traded', 'LiquidityAdded') 
      AND (data->>5)::integer IN (6002, 6003)
) AS combined_results
ORDER BY block_height DESC, idx DESC
LIMIT 500;

优化效果说明

• 每个子查询都能精准匹配对应的索引，直接从索引中获取符合条件的行，无需回表。
• 索引包含block_height DESC, idx DESC，可利用索引的有序性减少排序开销，甚至直接通过索引扫描获取Top-N结果。
• 避免了OR条件导致的位图合并和大量随机IO，大幅降低执行时间。

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