1. 云图网首页
  2. 技术专区
  3. 数据库

PostgreSQL 9.2 Beta: Test Index-only scans

• 数据库

PostgreSQL 9.2 Beta 已经支持 Index-only scans ，今天测试了下，发现性能没明显区别 ，这里比较的是 PostgreSQL9.0。

Index-only scans

Index-only scans, allowing users to avoid inefficient scans of base tables
备注： Index-only scans 支持当查询索引项数据时，可以不用返回查询基表了，直接返回索引的索引项数据，当然这是有前提的，即索引指向的Heap Block 上的 tuples 都可见。

测试: PostgreSQL 9.0

1.1查询版本

1
2
3
4
5
 
skytf=> select version();  
 version  
-----------------------------------------------------------------------------------------------------------  
PostgreSQL 9.0.2 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.4.6 20110731 (Red Hat 4.4.6-3), 32-bit  
(1 row)

1.2 创建测试表并插入测试数据

1
2
3
4
5
 
skytf=> create table test_indexonly (id integer primary key,name varchar(32));  
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "test_indexonly_pkey" for table "test_indexonly"  
CREATE TABLE
skytf=> insert into test_indexonly select generate_series(1,10000),'index only';  
INSERT 0 10000

1.3 测试索引单条查询

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
 
skytf=> explain analyze select id from test_indexonly where id=1;  
 QUERY PLAN  
------------------------------------------------------------------------------------------------------------------------------------  
Index Scan using test_indexonly_pkey on test_indexonly (cost=0.00..8.27 rows=1 width=4) (actual time=0.089..0.091 rows=1 loops=1)  
 Index Cond: (id = 1)  
Total runtime: 0.173 ms  
(3 rows)  
  
skytf=> explain analyze select id from test_indexonly where id=1;  
 QUERY PLAN  
------------------------------------------------------------------------------------------------------------------------------------  
Index Scan using test_indexonly_pkey on test_indexonly (cost=0.00..8.27 rows=1 width=4) (actual time=0.022..0.026 rows=1 loops=1)  
 Index Cond: (id = 1)  
Total runtime: 0.068 ms  
(3 rows)
  
skytf=> explain analyze select id from test_indexonly where id=1;  
 QUERY PLAN  
------------------------------------------------------------------------------------------------------------------------------------  
Index Scan using test_indexonly_pkey on test_indexonly (cost=0.00..8.27 rows=1 width=4) (actual time=0.057..0.060 rows=1 loops=1)  
 Index Cond: (id = 1)  
Total runtime: 0.204 ms  
(3 rows)

1.4 测试扫描索引范围的情况

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
 
skytf=> explain analyze select count(*) from test_indexonly where id>300 and id <401;  
 QUERY PLAN  
  
------------------------------------------------------------------------------------------------------------------------------------  
Aggregate (cost=10.53..10.54 rows=1 width=0) (actual time=0.366..0.367 rows=1 loops=1)  
 -> Index Scan using test_indexonly_pkey on test_indexonly (cost=0.00..10.27 rows=101 width=0) (actual time=0.041..0.220 rows=100 loops=1)  
 Index Cond: ((id > 300) AND (id < 401))  
Total runtime: 0.462 ms  
(4 rows)
skytf=> explain analyze select count(*) from test_indexonly where id>300 and id <401;  
 QUERY PLAN  
  
-----------------------------------------------------------------------------------------------------------------------------------  
Aggregate (cost=10.53..10.54 rows=1 width=0) (actual time=0.357..0.358 rows=1 loops=1)  
 -> Index Scan using test_indexonly_pkey on test_indexonly (cost=0.00..10.27 rows=101 width=0) (actual time=0.035..0.201 rows=10  
0 loops=1)  
 Index Cond: ((id > 300) AND (id < 401))  
Total runtime: 0.421 ms  
(4 rows)

测试: PostgreSQL 9.2 Beta1

2.1 查询版本

1
2
3
4
5
 
francs=> select version();  
 version  
----------------------------------------------------------------------------------------------------------  
PostgreSQL 9.2beta1 on i686-pc-linux-gnu, compiled by gcc (GCC) 4.4.6 20110731 (Red Hat 4.4.6-3), 32-bit  
(1 row)

2.2 创建测试表并插入测试数据

1
2
3
4
5
 
francs=> create table test_indexonly (id integer primary key,name varchar(32));  
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "test_indexonly_pkey" for table "test_indexonly"  
CREATE TABLE
francs=> insert into test_indexonly select generate_series(1,10000),'index only';  
INSERT 0 10000

2.3 测试索引单条查询

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
 
francs=> explain analyze select id from test_indexonly where id=1;  
 QUERY PLAN  
  
------------------------------------------------------------------------------------------------------------------------------------  
Index Only Scan using test_indexonly_pkey on test_indexonly (cost=0.00..8.28 rows=1 width=4) (actual time=0.068..0.071 rows=1 loops=1)  
 Index Cond: (id = 1)  
 Heap Fetches: 1  
Total runtime: 0.142 ms(4 rows)


francs=> explain analyze select id from test_indexonly where id=1;  
 QUERY PLAN  
  
-----------------------------------------------------------------------------------------------------------------------------------  
Index Only Scan using test_indexonly_pkey on test_indexonly (cost=0.00..8.28 rows=1 width=4) (actual time=0.016..0.019 rows=1 loops=1)  
 Index Cond: (id = 1)  
 Heap Fetches: 1  
Total runtime: 0.049 ms  
(4 rows)
francs=> explain analyze select id from test_indexonly where id=1;  
 QUERY PLAN  
  
------------------------------------------------------------------------------------------------------------------------------------  
Index Only Scan using test_indexonly_pkey on test_indexonly (cost=0.00..8.28 rows=1 width=4) (actual time=0.027..0.030 rows=1 loops=1)  
 Index Cond: (id = 1)  
 Heap Fetches: 1  
Total runtime: 0.085 ms  
(4 rows)

2.4 测试扫描索引范围的情况

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
 
francs=> explain analyze select count(*) from test_indexonly where id>300 and id <401;  
 QUERY PLAN  
  
-----------------------------------------------------------------------------------------------------------------------------------  
Aggregate (cost=10.53..10.54 rows=1 width=0) (actual time=0.451..0.452 rows=1 loops=1)  
 -> Index Only Scan using test_indexonly_pkey on test_indexonly (cost=0.00..10.28 rows=101 width=0) (actual time=0.086..0.275 rows=100 loops=1)  
 Index Cond: ((id > 300) AND (id < 401))  
 Heap Fetches: 100  
Total runtime: 0.579 ms  
(5 rows)
  
francs=> explain analyze select count(*) from test_indexonly where id>300 and id <401;  
 QUERY PLAN  
  
------------------------------------------------------------------------------------------------------------------------------------  
Aggregate (cost=10.53..10.54 rows=1 width=0) (actual time=0.518..0.520 rows=1 loops=1)  
 -> Index Only Scan using test_indexonly_pkey on test_indexonly (cost=0.00..10.28 rows=101 width=0) (actual time=0.167..0.353 rows=100 loops=1)  
 Index Cond: ((id > 300) AND (id < 401))  
 Heap Fetches: 100  
Total runtime: 0.583 ms(5 rows)

总结

3.1 测试场景一：单条记录索引扫描

单条记录索引扫描 PostgreSQL 9.0 PostgreSQL 9.2
第一次查询 0.173 ms 0.142 ms
第二次查询 0.068 ms 0.049 ms
第三次查询 0.204 ms 0.085 ms

3.2 测试场景二：多条记录索引范围扫描

多条记录索引范围扫描 PostgreSQL 9.0 PostgreSQL 9.2
第一次查询 0.462 ms 0.579 ms
第二次查询 0.421 ms 0.583 ms

“Index Only Scan “ 和 “Index Scan using “ 的性能在单条检索，和范围检索上性能没有明显区别。

原创文章，作者：carmelaweatherly，如若转载，请注明出处：https://blog.ytso.com/237852.html

赞 (0)
0
上一篇 2022年1月29日
下一篇 2022年1月29日

相关推荐

发表回复

登录后才能评论