从bigint数组中查找索引的最快方法

Question

我有五千万行的桌子。我需要从数组中找到每一行id，但是当我输入"ANY“语句时，超过4个值，我的查询将持续45sec+。小于或等于4的值小于100 and。

做同样事情最快的方法是什么：

SELECT * FROM tbl WHERE id = ANY('{1, 12, 41, etc.}');或如何修复此行为？

id是主键

upd

account_id是上面示例中的id

用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

upd2：VACUUM(FULL, ANALYZE) tbl固定；

Answer 1

固定在

VACUUM(FULL, ANALYZE) tbl