Postgresql - 索引删除的行

Question

有问题的表在 time 上有一个 B 树索引

testdb=> explain analyze select avg(gl) from cdstest where time between 1407700790 and 1407711590;
                                                                 QUERY PLAN                                                                  
---------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=1434716.75..1434716.76 rows=1 width=2) (actual time=20106.951..20106.952 rows=1 loops=1)
   ->  Bitmap Heap Scan on cdstest  (cost=231261.49..1411280.42 rows=9374529 width=2) (actual time=811.495..10871.963 rows=9438824 loops=1)
         Recheck Cond: (("time" >= 1407700790) AND ("time" <= 1407711590))
         Rows Removed by Index Recheck: 204734
         ->  Bitmap Index Scan on timeindex  (cost=0.00..228917.86 rows=9374529 width=0) (actual time=810.108..810.108 rows=9438824 loops=1)
               Index Cond: (("time" >= 1407700790) AND ("time" <= 1407711590))
 Total runtime: 20107.001 ms
(7 rows)

索引重新检查删除的行:204734 - 这是什么意思？这似乎是一个相当随意的数字。

给定时间范围内的行数:

testdb=> select count(*) from cdstest where time between 1407700790 and 1407711590;
  count  
---------
 9438824
(1 row)

该表包含约 6000 万行。

Answer 1

内部 Bitmap Index Scan 节点正在生成一个位图，将 1 放入找到与您的搜索关键字匹配的记录的所有位置，并将 0 否则。由于您的表很大，位图的大小越来越大，然后可以通过 work_mem 配置这些操作的可用内存。 , 变小以保留整个位图。

当内存不足时，内部节点将开始生成 1，但不是针对记录，而是针对已知包含匹配记录的 block 。这意味着，外部节点 Bitmap Heap Scan 必须从该 block 中读取所有 记录并重新检查它们。很明显，会有一些不匹配的，它们的数量就是您看到的 Rows Removed by Index Recheck。

在即将推出的 9.4 中，添加了一项新功能，报告位图索引扫描返回了多少exact和/或lossy页面> 节点。 lossy 是您想要避免的。您可以查看更多相关信息 here .

最后，查阅您的 work_mem 设置并尝试增加它，仅针对此特定 session 。我认为，增加大约 40% 应该就足够了。

---

编辑
我这里运行的是 9.4beta3，所以我准备了一个小案例:

DROP TABLE IF EXISTS tab;
SELECT id, id%10 mod
  INTO tab
  FROM generate_series(1,(1e7)::int) id;
CREATE INDEX i_tab_mod ON tab(mod);
VACUUM ANALYZE tab;

现在，我将 work_mem 设置为可能的最小值并检查它:

SET work_mem TO '64kB';
EXPLAIN (analyze, buffers)
SELECT * FROM tab WHERE mod=5;

EXPLAIN 提供以下两行:

  Rows Removed by Index Recheck: 8896308
  Heap Blocks: exact=510 lossy=43738
  ...
Execution time: 1356.938 ms

这意味着，64kB 可以容纳 510 个 block 。所以我在这里计算总内存需求:

new_mem_in_bytes = (work_mem_in_bytes / exact) * lossy
                 = (( 64.0 * 1024 / 510 ) * 43738) / 1024
                 = 5488.7kB

事实上，这不是计算所需内存的精确方法，但我认为它足以满足我们的需求。所以我尝试使用 SET work_mem TO '5MB':

  Heap Blocks: exact=44248
  ...
Execution time: 283.466 ms