注册 登录  
 加关注
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

PostgreSQL 中文网

 
 
 

日志

 
 

PostgreSQL 9.2 Beta: Test Index-only scans  

2012-05-17 17:10:40|  分类: Postgres基础 |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |

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

 

--PostgreSQL9.2Beta1 说明
Index-only scans, allowing users to avoid inefficient scans of base tables

    备注: Index-only scans 支持当查询索引项数据时,可以不用\返回查询基表了,直接返回索引的
                 索引项数据,当然这是有前提的,即索引指向的Heap Block 上的 tuples 都可见。


一 测试PostgreSQL 9.0
--1.1查询版本

 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 创建测试表并插入测试数据

 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 测试索引单条查询

 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 测试扫描索引范围的情况

 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 查询版本

 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 创建测试表并插入测试数据

 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 测试索引单条查询

 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 测试扫描索引范围的情况

 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 测试场景一: 单条记录索引扫描

单条记录索引扫描 PostgreSQL9.0 PostgreSQL9.2
第一次查询 0.173 ms 0.142 ms
第二次查询  0.068 ms  0.049 ms
第三次查询  0.204 ms  0.085 ms

 

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

多条记录索引范围扫描 PostgreSQL9.0 PostgreSQL9.2
第一次查询  0.462 ms  0.579 ms
第二次查询  0.421 ms  0.583 ms

 

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

  评论这张
 
阅读(27695)| 评论(4)
推荐 转载

历史上的今天

在LOFTER的更多文章

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2016