sql - 为什么此查询不在 postgresql 中使用仅索引扫描

Question

我有一个包含 16 列的表，其中有一个主键和一个用于存储值的列。我想选择一定范围内的所有值。值列(easyid)已被索引。

create table tb1 (
    id Int primary key,
    easyid Int,
    .....
)
create index i_easyid on tb1 (easyid)

其他信息:postgresql 9.4，没有自动清理。sql是这样的。

select "easyid" from "tb1" where "easyid" between 12183318 and 82283318

理论上 postgresql 应该在 i_easyid 上使用索引扫描。 只有在"easyid"between A and B范围较小的情况下才做index only scan。当范围很大时，即B-A 是一个很大的数字，postgresql 在i_easyid 上使用位图索引扫描，然后在tb1 上进行位堆扫描。

我说索引扫描是否仅取决于范围大小是错误的。我用不同的参数尝试了相同的查询，有时只是索引扫描，有时不是。

tb1 表非常大，高达 17G。 i_easyid 为 600MB。

这里是sql的解释。而且我不明白为什么 4000 行会花费超过 10 秒。

sample_pg=# explain analyze select easyid from tb1 where "easyid" between 152183318 and 152283318;
                                                         QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on tb1  (cost=97.70..17227.71 rows=4416 width=4) (actual time=1.155..14346.311 rows=5004 loops=1)
   Recheck Cond: ((easyid >= 152183318) AND (easyid <= 152283318))
   Heap Blocks: exact=4995
   ->  Bitmap Index Scan on i_easyid  (cost=0.00..96.60 rows=4416 width=0) (actual time=0.586..0.586 rows=5004 loops=1)
         Index Cond: ((easyid >= 152183318) AND (easyid <= 152283318))
 Planning time: 0.080 ms
 Execution time: 14348.037 ms
(7 rows)

这是一个仅索引扫描的例子:

sample_pg=# explain analyze verbose select easyid from tb1 where "easyid" between 32280318 and 32283318;
                                                               QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------
 Index Only Scan using i_easyid on public.tb1  (cost=0.44..281.82 rows=69 width=4) (actual time=14.585..160.624 rows=33 loops=1)
   Output: easyid
   Index Cond: ((tb1.easyid >= 32280318) AND (tb1.easyid <= 32283318))
   Heap Fetches: 33
 Planning time: 0.085 ms
 Execution time: 160.654 ms
(6 rows)

Answer 1

autovacuum is not running

PostgreSQL 仅索引扫描需要一些关于哪些行对当前事务“可见”的信息 - 即未删除、更新行的旧版本、未提交的插入或更新的新版本。

此信息保存在“可见性 map ”中。

可见性 map 由 VACUUM 维护，通常由 autovacuum 工作人员在后台维护。

如果 autovacuum 不能很好地跟上写入事件，或者如果 autovacuum 已被禁用，则可能不会使用仅索引扫描，因为 PostgreSQL 会发现可见性映射没有足够的表数据。

重新打开 autovaccum。然后手动 VACUUM 表使其立即更新。

顺便说一句，除了可见性映射信息之外，autoVACUUM 还可以写入提示位信息，使最近插入/更新数据的SELECT 更快。

Autovacuum 还维护对有效查询计划至关重要的表统计信息。关闭它会导致规划器使用越来越陈旧的信息。

这对于防止称为事务 ID 环绕的问题也绝对重要，这是一种紧急情况，可能导致整个数据库进入紧急关闭状态，直到耗时执行全表 VACUUM。

不要关闭 autovacuum。

至于为什么它有时使用仅索引扫描有时不使用，有几种可能性:

• 当前的 random_page_cost 设置让它认为随机 I/O 会比实际速度慢，因此它更努力地避免它；

• 表格统计信息，尤其是限制值，已过时。因此它没有意识到很有可能在仅索引扫描中快速发现正在查找的值；

• 可见性 map 已过时，因此它认为仅索引扫描会发现太多需要堆提取来检查的值，这使得它比其他方法慢，特别是如果它认为值的比例可能是发现很高。

大多数这些问题都可以通过单独使用 autovacuum 来解决。事实上，对于频繁附加的表，您应该将 autovacuum 设置为比默认值更频繁地运行，以便它更多地更新限制统计信息。 (这样做有助于解决 PostgreSQL 的规划器问题，其中最常查询的数据是最近插入的，带有递增的 ID 或时间戳，这意味着最需要的值永远不会出现在表直方图和限制统计信息中)。

重新打开 autovacuum - 然后打开它。