添加GIN索引后PostgreSQL中相同JSONB查询变慢的原因排查

我有一张仅包含jsonb类型列news的public.financial_news表，为提升news @> '{"language": "english"}'这个JSONB包含查询的速度，给该列添加了GIN索引，但执行相同查询时速度反而比添加索引前更慢，以下是具体信息及排查分析：

1. 表结构 #

pg_tech=# \d+ financial_news 
                              Table "public.financial_news"
 Column | Type  | Collation | Nullable | Default | Storage  | Stats target | Description 
--------+-------+-----------+----------+---------+----------+--------------+-------------
 news   | jsonb |           |          |         | extended |              | 
Access method: heap

2. 添加索引前的执行计划 #

pg_tech=# explain analyze select * from financial_news where news @> '{"language": "english"}';
                                                       QUERY PLAN                                                        
------------------------------------------------------------------------------------------------------------------------
 Seq Scan on financial_news  (cost=0.00..30082.24 rows=273 width=725) (actual time=0.107..2553.104 rows=273459 loops=1)
   Filter: (news @> '{"language": "english"}'::jsonb)
 Planning Time: 0.178 ms
 Execution Time: 2561.911 ms
(4 rows)

3. 添加GIN索引的语句 #

pg_tech=# create index idx_json_news on financial_news using gin(news);

4. 添加索引后的执行计划 #

pg_tech=# explain analyze select * from financial_news where news @> '{"language": "english"}';
                                                           QUERY PLAN                                                             
---------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on financial_news  (cost=46.12..1055.12 rows=273 width=725) (actual time=29.058..2609.573 rows=273459 loops=1)
   Recheck Cond: (news @> '{"language": "english"}'::jsonb)
   Heap Blocks: exact=26664
   ->  Bitmap Index Scan on idx_json_news  (cost=0.00..46.05 rows=273 width=0) (actual time=26.693..26.693 rows=273459 loops=1)
         Index Cond: (news @> '{"language": "english"}'::jsonb)
 Planning Time: 0.248 ms
 Execution Time: 2617.969 ms
(7 rows)

原因排查分析 #

• 返回数据占比过高：查询返回了273459行，和全表扫描的行数一致，说明匹配条件的记录占表中数据的绝大部分。当结果集占比超过10%-20%时，顺序扫描的连续IO效率远高于索引扫描的“索引定位+堆表随机读取”流程，额外的索引操作开销反而拖慢了整体速度。
• 统计信息严重失真：执行计划中预估行数仅273，和实际行数273459相差三个数量级，这会误导优化器选择索引扫描。执行ANALYZE financial_news;更新统计信息后，优化器会重新评估成本，大概率会选择更高效的顺序扫描。
• GIN索引适用场景不匹配：GIN索引擅长高选择性查询（返回少量数据），对于这种低选择性的查询，索引本身的查找、位图构建过程都会产生额外开销，叠加后续的堆表访问，整体成本超过顺序扫描。
• 位图堆扫描的随机IO开销：索引扫描需要先通过GIN索引生成数据位置的位图，再根据位图去堆表中读取数据。如果数据在堆表中分布不连续，会产生大量随机IO，而顺序扫描是连续读取，在数据量大时，随机IO的延迟会显著增加耗时。

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