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

PostgreSQL 中文网

 
 
 

日志

 
 

PostgreSQL:关于 " select max(id) from table_name " 的优化与分析  

2013-06-18 17:16:15|  分类: PG性能优化 |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |

          今天在一重要生产库上发现一个 SQL 执行较慢,严重影响数据库性能,以下是优化过程:

一 优化过程
--1.1 出现性能问题的 SQL    
 SELECT max(duser2) FROM mpt_table;
   
--1.2 老的 PLAN
 android_market=> explain analyze SELECT max(duser2) FROM mpt_table;
                                                                QUERY PLAN                                                          
      
------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=45159.95..45159.96 rows=1 width=4) (actual time=1010.232..1010.233 rows=1 loops=1)
   ->  Seq Scan on mpt_table  (cost=0.00..40891.56 rows=1707356 width=4) (actual time=0.019..413.291 rows=1707488 loops=1)
 Total runtime: 1010.278 ms
(3 rows)
备注:走了全表扫,执行时间为 1010.278 ms.  并且字段 duser2 上没加索引,经分析 duser2 的
            选择性较好,于是创建索引.
   
--1.3 创建索引   
 create index concurrently idx_mpt_table_duser2 on mpt_table using btree (duser2);
 
--1.4 新的 PLAN                                                                                           
 android_market=>  explain analyze SELECT max(duser2) FROM mpt_table;
                                                                                          QUERY PLAN                                
                                                           
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Result  (cost=0.03..0.04 rows=1 width=0) (actual time=0.093..0.093 rows=1 loops=1)
   InitPlan 1 (returns $0)
     ->  Limit  (cost=0.00..0.03 rows=1 width=4) (actual time=0.091..0.091 rows=1 loops=1)
           ->  Index Scan Backward using idx_mpt_table_duser2 on mpt_table  (cost=0.00..58384.34 rows=1707488 width=4) (actual time=0.090..0.090 rows=1 loops=1)
                 Index Cond: (duser2 IS NOT NULL)
 Total runtime: 0.134 ms
(6 rows)
 备注:在 duser2 字段上创建索引后,走了 "Index Scan Backward" 扫描,并且执行时间为 0.134 ms,
            非常的快,接着想测试下 oracle 对 max() 的处理。
       
二 oracle 测试
--2.1 创建测试表并导入数据
 SQL> create table test_max_oracle(id integer);
Table created

SQL> begin
  2   for i in 1..5000000 loop
  3   insert into test_max_oracle (id) values (i);
  4   end loop;
  5   commit;
  6   end;
  7  /
PL/SQL procedure successfully completed
 
--2.2 创建索引并表分析 
 SQL> create index idx_test_max_id on test_max_oracle (id);
Index created

SQL> execute dbms_stats.gather_table_stats(ownname=>'skytf',tabname=>'test_max_oracle',cascade=> true);
PL/SQL procedure successfully completed
 
 
--2.3 执行时间
 SQL> set timi on;

SQL> select max(id) from test_max_oracle;
   MAX(ID)
----------
   5000000
Executed in 0.031 seconds
 
SQL> select max(id) from test_max_oracle;
   MAX(ID)
----------
   5000000
Executed in 0.031 seconds
 备注:最短执行时间为 31 ms 左右。
   
--2.4 执行计划
 15:34:24 SKYTF@skytf> select max(id) from test_max_oracle;
   MAX(ID)
----------
   5000000
已用时间:  00: 00: 00.01

执行计划
----------------------------------------------------------
Plan hash value: 1160081309

----------------------------------------------------------------------------------------------
| Id  | Operation                  | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |                 |     1 |     5 |  1705   (3)| 00:00:21 |
|   1 |  SORT AGGREGATE            |                 |     1 |     5 |            |          |
|   2 |   INDEX FULL SCAN (MIN/MAX)| IDX_TEST_MAX_ID |  5000K|    23M|            |          |
----------------------------------------------------------------------------------------------

统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          3  consistent gets
          0  physical reads
          0  redo size
        407  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
 
    备注:走的是 "INDEX FULL SCAN ",全索引扫描会根据索引的顺序访问所有的索引 block,  这种扫描方式类似
              全表扫,效率不高。下面引用这段描述:
   
--2.5 关于 "INDEX FULL SCAN"
     In a full index scan, the database reads the entire index in order. A full index scan is available 
if a predicate (WHERE clause) in the SQL statement references a column in the index, and in some circumstances
when no predicate is specified. A full scan can eliminate sorting because the data is ordered by index key.


三 PostgreSQL 测试
--3.1 创建测试表并插入数据
 francs=> create table test_max_pg(id int4);
CREATE TABLE

francs=> insert into test_max_pg select generate_series(1,5000000);
INSERT 0 5000000
 
francs=> create index idx_test_max_id on test_max_pg using btree (id);
CREATE INDEX

francs=> analyze test_max_pg;
ANALYZE
 
--3.2 执行时间
 francs=> select max(id) from test_max_pg;
   max   
---------
 5000000
(1 row)

Time: 13.960 ms

francs=> select max(id) from test_max_pg;
   max   
---------
 5000000
(1 row)

Time: 2.493 ms

francs=> select max(id) from test_max_pg;
   max   
---------
 5000000
(1 row)

Time: 1.233 ms
 备注:最短执行时间只花了 1.233 ms 左右,比 oracle 测试结果要好些,这里不是对比 oracle 和
            pg 性能,只是争对 max() 函数应用场景分析两种数据库的处理方式。

--3.3 执行计划
 francs=> explain select max(id) from test_max_pg;
                                                        QUERY PLAN                                                        
--------------------------------------------------------------------------------------------------------------------------
 Result  (cost=0.03..0.04 rows=1 width=0)
   InitPlan 1 (returns $0)
     ->  Limit  (cost=0.00..0.03 rows=1 width=4)
           ->  Index Only Scan Backward using idx_test_max_id on test_max_pg  (cost=0.00..151011.64 rows=5000000 width=4)
                 Index Cond: (id IS NOT NULL)
(5 rows)
 备注:这里走的是 "Index Only Scan Backward",由于索引是顺序排序的,这种扫描方式直接后向读取
            索引项, 而不需要扫描整个索引 page,因此效率比 "INDEX FULL SCAN " 要高些。
 
 
四 补充
 1  这里的测试版本: Oracle 10.2.0.1 ;  PostgreSQL 9.2。
 2  Oracle 的索引扫描方式很多,例如 "index rang scan ","index unique scan","index fast full scan" 
    等,比 PostgreSQL 的索引访问方式多些,这里不再描述。
  评论这张
 
阅读(9826)| 评论(0)
推荐 转载

历史上的今天

在LOFTER的更多文章

评论

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

页脚

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