从bigint数组快速查找索引的最优方案及性能异常解决

我有一张包含5000万行数据的表，需查找所有account_id（为主键）在指定bigint数组中的行。使用语句SELECT * FROM tbl WHERE account_id = ANY('{1, 12, 41, ...}')时，数组元素超过4个后查询耗时达45秒以上；4个及以下元素时，耗时则小于100ms。

4个元素时的执行计划 #

Gather  (cost=194818.11..14487783.08 rows=8426816 width=195) (actual time=62.011..67.316 rows=0 loops=1)
  Workers Planned: 2
  Workers Launched: 2
  Buffers: shared hit=16
  ->  Parallel Bitmap Heap Scan on player_match  (cost=193818.11..13644101.48 rows=3511173 width=195) (actual time=1.080..1.081 rows=0 loops=3)
        Recheck Cond: (account_id = ANY ('{4,6322,435,75}'::bigint[]))
        Buffers: shared hit=16
        ->  Bitmap Index Scan on player_match_pkey  (cost=0.00..191711.41 rows=8426816 width=0) (actual time=0.041..0.042 rows=0 loops=1)
              Index Cond: (account_id = ANY ('{4,6322,435,75}'::bigint[]))
              Buffers: shared hit=16
Planning Time: 0.118 ms
JIT:
  Functions: 6
  Options: Inlining true, Optimization true, Expressions true, Deforming true
  Timing: Generation 1.383 ms, Inlining 0.000 ms, Optimization 0.000 ms, Emission 0.000 ms, Total 1.383 ms
Execution Time: 67.925 ms

5个元素时的执行计划 #

Gather  (cost=1000.00..14995098.33 rows=10533520 width=195) (actual time=59544.067..59557.119 rows=0 loops=1)
  Workers Planned: 2
  Workers Launched: 2
  Buffers: shared hit=10327 read=11077591
  ->  Parallel Seq Scan on player_match  (cost=0.00..13940746.33 rows=4388967 width=195) (actual time=59498.932..59498.933 rows=0 loops=3)
        Filter: (account_id = ANY ('{4,6322,435,75,1}'::bigint[]))
        Rows Removed by Filter: 140446932
        Buffers: shared hit=10327 read=11077591
Planning Time: 0.137 ms
JIT:
  Functions: 6
  Options: Inlining true, Optimization true, Expressions true, Deforming true
  Timing: Generation 1.742 ms, Inlining 203.984 ms, Optimization 25.001 ms, Emission 20.096 ms, Total 250.823 ms
Execution Time: 59557.657 ms

解决方案 #

执行VACUUM(FULL, ANALYZE) tbl后，该性能异常问题得到解决。

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