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

发布时间：2020-12-13 16:31:29 所属栏目：百科来源：网络整理

导读：我有一个包含16列的表,其中有一个主键和一个用于存储值的列. 我想选择某个范围内的所有值. 值列(easyid)已编入索引. create table tb1 ( id Int primary key,easyid Int,.....)create index i_easyid on tb1 (easyid) 其他信息：postgresql 9.4,没有自动真空

我有一个包含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上使用索引扫描.当A和B之间的“easyid”范围很小时,它只进行索引扫描.
当范围很大,即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)

autovacuum is not running

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

此信息保存在“可见性图”中.

可见性图由VACUUM维护,通常由autovacuum工作人员在后台维护.

如果autovacuum没有很好地跟上写入活动,或者如果autovacuum已被禁用,则可能不会使用仅索引扫描,因为PostgreSQL将看到可见性映射没有足够的表的数据.

重新打开autovaccum.然后手动对表进行VACUUM以使其立即更新.

顺便说一句,除了可见性图信息之外,autoVACUUM还可以写入提示位信息,这些信息可以使最近插入/更新的数据的SELECT更快.

Autovacuum还维护对有效查询计划至关重要的表统计信息.将其关闭将导致计划员使用越来越陈旧的信息.

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

不要关闭autovacuum.

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

>当前的random_page_cost设置使得它认为随机I / O将比实际更慢,因此它更难以避免它;
>表统计信息,尤其是限制值,已过时.因此,它没有意识到在一个仅索引扫描中很快就能发现所寻找的值;
>可见性图已过时,因此它认为仅索引扫描会找到太多需要检索堆读取的值,这使得它比其他方法慢,特别是如果它认为可能找到的值的比例很高.

大多数这些问题都是通过单独保留autovacuum来解决的.实际上,在频繁附加的表中,您应该将autovacuum设置为比默认值更频繁地运行,以便更新更新限制统计信息. (这样做有助于解决PostgreSQL的计划程序问题,其中最常查询的数据是最近插入的,具有递增ID或时间戳,这意味着最期望的值永远不会出现在表直方图和限制统计数据中).

重新打开autovacuum – 然后将其打开.

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!