我对PostgreSQL 中 index scan 与 seq scan 的对比学习

koflover

　　开始
　　数据量很小的时候，我们可以看到，seq scan 比 index scan 更加有效。那是因为 index scan 至少要发生两次I/O，一次是 读取索引块， 一次是读取数据块。当index 很大的时候，情况可能会更加复杂。



postgres=# select a.relpages, a.reltuples, a.relfilenode,a.reltype,b.typname from pg_class a, pg_type b where a.relname like 'gaotab%' and a.reltype=b.oid;   
relpages | reltuples | relfilenode | reltype | typname     
----------+-----------+-------------+---------+---------   
1 |      100 |       16396 |   16386 | gaotab   
　　数据量为 100条记录。
　　预估成本：



postgres=# set session enable_seqscan=false;                    
SET                    
postgres=# explain select name from gaotab where id=50;                    
QUERY PLAN                                                  
---------------------------------------------------------------------                    
Index Scan using idx_id on gaotab  (cost=0.00..8.27 rows=1 width=5)                    
Index Cond: (id = 50)                    
(2 rows)                    


postgres=# set session enable_seqscan=true;        
SET        
postgres=# explain select name from gaotab where id=50;        
QUERY PLAN                              
------------------------------------------------------        
Seq Scan on gaotab  (cost=0.00..2.25 rows=1 width=5)        
Filter: (id = 50)        
(2 rows)        
　　实际执行：



postgres=# set session enable_seqscan=false;        
SET        
postgres=# explain analyze select name from gaotab where id=50;        
QUERY PLAN                           
--------------------------------------------------------------------------------        
-------------------------------        
Index Scan using idx_id on gaotab  (cost=0.00..8.27 rows=1 width=5) (actual tim        
e=0.112..0.113 rows=1 loops=1)        
Index Cond: (id = 50)        
Total runtime: 0.133 ms        
(3 rows)        


postgres=# set session enable_seqscan=true;        
SET        
postgres=# explain analyze select name from gaotab where id=50;        
QUERY PLAN                                   
--------------------------------------------------------------------------------        
----------------        
Seq Scan on gaotab  (cost=0.00..2.25 rows=1 width=5) (actual time=0.014..0.018         
rows=1 loops=1)        
Filter: (id = 50)        
Rows Removed by Filter: 99        
Total runtime: 0.034 ms        
(4 rows)        
　　等到数据量大的时候，就是截然不同了。
　　数据为1000条记录时，通过查询可以看到，已经跨越了7个page：



postgres=# analyze;   
ANALYZE   
postgres=# select a.relpages, a.reltuples, a.relfilenode,a.reltype,b.typname from pg_class a, pg_type b where a.relname like 'gaotab%' and a.reltype=b.oid;   
relpages | reltuples | relfilenode | reltype | typname     
----------+-----------+-------------+---------+---------   
7 |      1000 |       16396 |   16386 | gaotab   
(1 row)   
postgres=#     
　　再次预估成本，此时seq scan 已经开始变得不划算了：



postgres=# set session enable_seqscan=false;
SET
postgres=# explain select name from gaotab where id=50;
QUERY PLAN                              
---------------------------------------------------------------------
Index Scan using idx_id on gaotab  (cost=0.00..8.27 rows=1 width=6)
Index Cond: (id = 50)
(2 rows)
postgres=# set session enable_seqscan=true;
SET
postgres=# explain select name from gaotab where id=50;
QUERY PLAN                              
---------------------------------------------------------------------
Index Scan using idx_id on gaotab  (cost=0.00..8.27 rows=1 width=6)
Index Cond: (id = 50)
(2 rows)
postgres=# set session enable_indexscan=false;
SET
postgres=# explain select name from gaotab where id=50;
QUERY PLAN                              
---------------------------------------------------------------------
Bitmap Heap Scan on gaotab  (cost=4.26..8.27 rows=1 width=6)
Recheck Cond: (id = 50)
->  Bitmap Index Scan on idx_id  (cost=0.00..4.26 rows=1 width=0)
Index Cond: (id = 50)
(4 rows)
postgres=# set session enable_bitmapscan=false;
SET
postgres=# explain select name from gaotab where id=50;
QUERY PLAN                       
-------------------------------------------------------
Seq Scan on gaotab  (cost=0.00..19.50 rows=1 width=6)
Filter: (id = 50)
(2 rows)
postgres=#
　　实际执行



postgres=# set session enable_seqscan=false;
SET
postgres=# explain analyze select name from gaotab where id=50;
QUERY PLAN                                      
------------------------------------------------------------------------------------------------------------
-----------------------
Index Scan using idx_id on gaotab  (cost=10000000000.00..10000000008.27 rows=1 width=6) (actual time=0.020.
.0.022 rows=1 loops=1)
Index Cond: (id = 50)
Total runtime: 0.051 ms
(3 rows)
postgres=# set session enable_seqscan=true;
SET
postgres=# set session enable_indexscan=false;
SET
postgres=# set session enable_bitmapscan=false;
SET
postgres=# explain analyze select name from gaotab where id=50;
QUERY PLAN                                            
-------------------------------------------------------------------------------------------------
Seq Scan on gaotab  (cost=0.00..19.50 rows=1 width=6) (actual time=0.015..0.095 rows=1 loops=1)
Filter: (id = 50)
Rows Removed by Filter: 999
Total runtime: 0.109 ms
(4 rows)
postgres=#
　　[作者：技术者高健@博客园  mail: luckyjackgao@gmail.com ]
　　结束