在实际SQL优化工作中,我们经常会发现SQL 执行计划明明是 “Index Only Scan”,但执行计划后面却有 “Heap Fetches: x” ,也就是说实际执行计划还是访问了表记录。这是为什么了?
一、举个例子
1、创建数据
create table t1(id1 integer,id2 integer,name text);
insert into t1 select generate_series(1,100),generate_series(1,100),repeat('a',1000);
create index ind_t1 on t1(id1,id2);
2、查看执行计划
test=# explain analyze select id2 from t1 where id1=1;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------
Index Only Scan using ind_t1 on t1 (cost=0.14..8.16 rows=1 width=4) (actual time=0.024..0.025 rows=1 loops=1)
Index Cond: (id1 = 1)
Heap Fetches: 1
Planning Time: 0.286 ms
Execution Time: 0.044 ms
(5 rows)
可以看到,虽然SQL 只访问一条记录,但 heap fetches 值是 1 ,也就是实际需要访问表。
3、原因分析
test=# select pg_relation_filepath('t1');
pg_relation_filepath
----------------------
base/16089/16428
(1 row)
查看该路径下是否有 vm 文件。没有visibility map,postgresql就不知道是否所有的行对当前事务都是可见的,因此需要去访问表获取数据。只有fsm ,没有vm
[kb21@dbhost03 data]$ ls -l base/16089/16428*
-rw------- 1 kb21 kb21 122880 Sep 20 09:54 base/16089/16428
-rw------- 1 kb21 kb21 24576 Sep 20 09:54 base/16089/16428_fsm
4、vacuum 后执行计划
test=# vacuum analyze t1;
VACUUM
test=# explain analyze select id2 from t1 where id1=1;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------
Index Only Scan using ind_t1 on t1 (cost=0.14..4.16 rows=1 width=4) (actual time=0.011..0.012 rows=1 loops=1)
Index Cond: (id1 = 1)
Heap Fetches: 0
Planning Time: 0.249 ms
Execution Time: 0.031 ms
(5 rows)
vacuum 后,heap fetches 变为 0
二、进一步分析
1、通过 sys_visibility 扩展进行分析
test=# create extension sys_visibility;
CREATE EXTENSION
test=# \dx+ sys_visibility
Objects in extension "sys_visibility"
Object description
-----------------------------------------------
function pg_check_frozen(regclass)
function pg_check_visible(regclass)
function pg_truncate_visibility_map(regclass)
function pg_visibility_map(regclass)
function pg_visibility_map(regclass,bigint)
function pg_visibility_map_summary(regclass)
function pg_visibility(regclass)
function pg_visibility(regclass,bigint)
pg_visibility_map 函数的参数:regclass, blkno bigint, all_visible OUT boolean, all_frozen OUT boolean
2、删除记录
test=# begin;
BEGIN
test=# delete from t1 where id1=1;
DELETE 1
test=# rollback;
ROLLBACK
test=# select pg_visibility_map('t1'::regclass, 0);
pg_visibility_map
-------------------
(f,f)
(1 row)
test=# select pg_visibility_map('t1'::regclass, 1);
pg_visibility_map
-------------------
(t,f)
(1 row)
因为id1=1 是在数据块0,因此,数据块并不是all visible
3、验证执行计划
test=# explain analyze select id2 from t1 where id1=1;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------
Index Only Scan using ind_t1 on t1 (cost=0.14..4.16 rows=1 width=4) (actual time=0.016..0.017 rows=1 loops=1)
Index Cond: (id1 = 1)
Heap Fetches: 1
Planning Time: 0.054 ms
Execution Time: 0.033 ms
(5 rows)
test=# explain analyze select distinct id1,id2 from t1;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------
Unique (cost=0.14..10.14 rows=100 width=8) (actual time=0.014..0.042 rows=100 loops=1)
-> Index Only Scan using ind_t1 on t1 (cost=0.14..9.64 rows=100 width=8) (actual time=0.013..0.024 rows=100 loops=1)
Heap Fetches: 7
Planning Time: 0.057 ms
Execution Time: 0.060 ms
(5 rows)
test=# vacuum analyze t1;
VACUUM
test=# explain analyze select distinct id1,id2 from t1;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------
Unique (cost=0.14..10.14 rows=100 width=8) (actual time=0.009..0.034 rows=100 loops=1)
-> Index Only Scan using ind_t1 on t1 (cost=0.14..9.64 rows=100 width=8) (actual time=0.008..0.017 rows=100 loops=1)
Heap Fetches: 0
Planning Time: 0.213 ms
Execution Time: 0.051 ms
(5 rows)
至于在Vacuum之前 heap fetches 为什么是 7 , 没搞明白。但明确的是vacuum 之后,heap fectches 变为0.