PostgreSQL大表更新后未选用GIN+Btree联合索引执行计划求助

问题描述 #

更新前，PostgreSQL针对目标查询会选择BitmapAnd结合GIN+Btree索引的高效执行计划；但在对大表执行大量随机更新后，查询不再使用GIN索引，仅依赖Btree索引过滤，导致执行时间翻倍（从_4.7ms涨到11ms）。

环境复现步骤 #

1. 创建表与插入测试数据 #

CREATE SCHEMA icn;
CREATE TABLE icn.jsonbs (dat jsonb);

插入1万条随机jsonb数据：

DO
$do$
    BEGIN
        FOR i IN 1..10000 LOOP
            EXECUTE format('INSERT INTO icn.jsonbs VALUES (''{"name": "%s", "role": "%s", "addr": {"city": "%s"}}'')',
                   (SELECT ('[0:2]={dog,cat,bird}'::text[])[floor(random()*3)]),
                   (SELECT ('[0:1]={true,false}'::text[])[floor(random()*2)]),
                   (SELECT ('[0:11]={toronto,vancouver,montreal,dhaka,sylhet,alberta,a,b,c,d,e,f}'::text[])[floor(random()*12)])
                );
        END LOOP;
    END
$do$;

2. 创建索引 #

-- Btree索引：匹配name字段的大写查询
CREATE INDEX btree_name ON icn.jsonbs USING btree ((UPPER((dat -> 'name')::text)));
-- GIN索引：匹配jsonb包含查询
CREATE INDEX gin_dat ON icn.jsonbs USING gin(dat);

3. 初始查询（高效计划） #

执行查询：

EXPLAIN ANALYZE
SELECT * FROM icn.jsonbs
WHERE
     UPPER((dat -> 'name')::text) = '"DOG"' AND dat @> '{"addr": {"city": "toronto"}}'
;

执行计划（BitmapAnd双索引扫描）：

QUERY PLAN                                                          
------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on jsonbs  (cost=29.66..33.69 rows=1 width=32) (actual time=2.929..4.634 rows=281 loops=1)
   Recheck Cond: ((upper(((dat -> 'name'::text))::text) = '"DOG"'::text) AND (dat @> '{"addr": {"city": "toronto"}}'::jsonb))
   Heap Blocks: exact=115
   ->  BitmapAnd  (cost=29.66..29.66 rows=1 width=0) (actual time=2.833..2.834 rows=0 loops=1)
         ->  Bitmap Index Scan on btree_name  (cost=0.00..4.66 rows=50 width=0) (actual time=0.876..0.876 rows=3297 loops=1)
               Index Cond: (upper(((dat -> 'name'::text))::text) = '"DOG"'::text)
         ->  Bitmap Index Scan on gin_dat  (cost=0.00..24.75 rows=100 width=0) (actual time=1.929..1.929 rows=861 loops=1)
               Index Cond: (dat @> '{"addr": {"city": "toronto"}}'::jsonb)
 Planning Time: 0.470 ms
 Execution Time: 4.720 ms
(10 rows)

4. 执行大量随机更新 #

DO
$do$
    BEGIN
        FOR i IN 1..5000 LOOP
            UPDATE icn.jsonbs SET dat = dat || '{"role": "mouse"}' WHERE ctid = (SELECT ctid FROM icn.jsonbs OFFSET floor(random()*10000) LIMIT 1);
        END LOOP;
    END
$do$;

5. 更新后查询（低效计划） #

再次执行同一查询，执行计划退化为仅使用Btree索引：

QUERY PLAN                                                       
-----------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on jsonbs  (cost=4.74..119.63 rows=1 width=32) (actual time=1.267..11.013 rows=281 loops=1)
   Recheck Cond: (upper(((dat -> 'name'::text))::text) = '"DOG"'::text)
   Filter: (dat @> '{"addr": {"city": "toronto"}}'::jsonb)
   Rows Removed by Filter: 3016
   Heap Blocks: exact=150
   ->  Bitmap Index Scan on btree_name  (cost=0.00..4.74 rows=60 width=0) (actual time=1.095..1.095 rows=3968 loops=1)
         Index Cond: (upper(((dat -> 'name'::text))::text) = '"DOG"'::text)
 Planning Time: 0.209 ms
 Execution Time: 11.087 ms
(9 rows)

现状与需求 #

• 重建GIN索引可恢复高效计划，但更新行数较少（<1000行）时无此问题
• 执行VACUUM ANALYZE无效，希望无需重建索引即可恢复最优执行计划

解决方案 #

• 强制更新GIN索引统计信息
  PostgreSQL的GIN索引统计信息可能未及时更新，导致优化器低估GIN索引的过滤效率。执行以下命令手动更新索引统计：

  ANALYZE icn.jsonbs;
  -- 或者针对特定索引更新统计
  SELECT pg_statginindex('icn.gin_dat');
  

  注：pg_statginindex会返回GIN索引的详细统计，帮助优化器重新评估索引价值

• 调整优化器参数（临时/永久）
  如果统计更新无效，可临时降低random_page_cost，让优化器更倾向于使用索引组合：

  -- 会话级临时调整
  SET random_page_cost = 1.1;
  -- 全局永久调整（需修改postgresql.conf后重启）
  # random_page_cost = 1.1
  

  原理：降低随机页成本会让优化器认为使用多索引BitmapAnd的代价更低

• 使用查询提示强制使用GIN索引
  通过INDEX提示强制优化器使用GIN索引，结合Btree索引生成BitmapAnd计划：

  EXPLAIN ANALYZE
  SELECT * FROM icn.jsonbs
  WHERE
       UPPER((dat -> 'name')::text) = '"DOG"' AND dat @> '{"addr": {"city": "toronto"}}'
  INDEX gin_dat, btree_name;
  

  注：PostgreSQL 11+支持INDEX提示，低版本可使用SET enable_seqscan = off等间接方式，但不推荐长期使用

• 优化更新操作减少索引碎片化
  大量随机更新会导致GIN索引碎片化，降低索引效率。可改为批量更新或使用CONCURRENTLY重建索引（无锁）：

  -- 无锁重建GIN索引
  DROP INDEX CONCURRENTLY icn.gin_dat;
  CREATE INDEX CONCURRENTLY gin_dat ON icn.jsonbs USING gin(dat);
  

验证方法 #

执行上述解决方案后，重新运行EXPLAIN ANALYZE查询，确认执行计划恢复为BitmapAnd结合双索引，且执行时间回到更新前水平。

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