PG中的Index-Only Scans解密

PG索引的多版本实现

可见性映射表

在PostgreSQL数据库中,索引上并没有多版本信息,因此即使SELECT的列都是索引列,在PostgreSQL 9.2之前的版本中还是需要再到表上去查询一次,但在PostgreSQL 9.2版本之后,该查询就可以省略了。这种扫描方式被称为“Index-Only Scans”。实际上,9.2版本也没有在索引中添加多版本信息,那它是如何实现该功能的呢?它是靠使用可见性映射表(Visibility Map)来实现这一功能的。可见性映射表文件中记录了一个数据块(大小通常是8KB)中的行是否对全部事务可见(当然还存在数据块中的行对部分事务可见和部分不可见的情况),如果对全事务可见,则PostgreSQL不需要通过访问表中的行来做可见性判断了。

下面用一个示例来说明PostgreSQL 9.2版本之后的Index-Only Scans与之前版本中的不同。首先在PostgreSQL 8.4版本和PostgreSQL 9.3中同时建一个测试表,命令如下:

CREATE TABLE test01(id int primary key, note text);

insert into test01 select generate_series(1,100000), 'testcontent';

postgres=# explain select id from test01 where id>10 and id<20;
QUERY PLAN
-------------------------------------------------------------------------------
Index Only Scan using test01_pkey on test01 (cost=0.29..8.47 rows=9 width=4)
Index Cond: ((id > 10) AND (id < 20))

我们可以看到,上面的执行计划中的“Index Scan”被换成了“Index Only Scan”。

Index Only Scan

如果数据块中的行对部分事务可见,部分不可见,就不能仅通过索引来获得数据了,还需要读取数据块,但此时执行计划中仍然显示为“Index Only Scan”,因此当看到执行计划中有“Index Only Scan”这几个词时,并不表示仅读取索引就可以了,这几个词或许改成“Index Mostly Scans”更合适一些。要查看一个SQL的“Index Only Scan”实际上访问了多少数据块中的行,可以在EXPLAIN语句中加上“ANALYZE”,让其实际执行一下,然后查看其执行结果,命令如下:

postgres=# explain select id from test01 where id>10 and id<20;
QUERY PLAN
-------------------------------------------------------------------------------
Index Only Scan using test01_pkey on test01 (cost=0.29..8.47 rows=9 width=4)
Index Cond: ((id > 10) AND (id < 20))
(2 rows)

postgres=# explain analyze select id from test01 where id>10 and id<20;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------
Index Only Scan using test01_pkey on test01 (cost=0.29..8.47 rows=9 width=4) (actual time=0.012..0.014 rows=9 loops=1)
Index Cond: ((id > 10) AND (id < 20))
Heap Fetches: 9
Planning Time: 0.099 ms
Execution Time: 0.032 ms
(5 rows)

从上面的执行计划中可以看到“Heap Fetches:9”,说明从数据块中读取了9行,这实际上是数据块中所有的行,说明“Index Only Scan”基本没有起作用。

可见性事务

要衡量一个表的“Index Only Scan”的效果,关键在于这个表的数据块有多少对所有事务可见。该信息可以在pg_class中查询,命令如下:

postgres=# select relname,relpages,relallvisible from pg_class where relname=
postgres-# 'test01';
relname | relpages | relallvisible
---------+----------+---------------
test01 | 541 | 0
(1 row)

从上面的查询可以知道,该测试表的“relallvisiable”是“0”,所以“Index Only Scan”没起到任何优化效果。这时可以VACUUM一个表,命令如下

VACUUM达到效果


postgres=# vacuum test01;
VACUUM
postgres=# select relname,relpages,relallvisible from pg_class where relname='test01';
relname | relpages | relallvisible
---------+----------+---------------
test01 | 541 | 541
(1 row)

postgres=#
postgres=# explain analyze select id from test01 where id>10 and id <20;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------
Index Only Scan using test01_pkey on test01 (cost=0.29..4.47 rows=9 width=4) (actual time=0.008..0.010 rows=9 loops=1)
Index Cond: ((id > 10) AND (id < 20))
Heap Fetches: 0
Planning Time: 0.151 ms
Execution Time: 0.024 ms
(5 rows)
发表评论

相关文章