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

数据量很小的时候，我们可以看到，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=#