我想选择某个范围内的所有值.
值列(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 – 然后将其打开.
