PostgreSQL 中 index scan 与 seq scan 的对比学习
来源:互联网 发布:mac如何安装flash插件 编辑:程序博客网 时间:2024/04/30 08:25
数据量很小的时候,我们可以看到,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=#
0 0
- PostgreSQL 中 index scan 与 seq scan 的对比学习
- PostgreSQL index only scan
- PostgreSQL index scan,bitmap index scan区别
- index unique scan 与index range scan等的区别
- index unique scan 与index range scan等的区别
- sql server中clustered index scan,table scan,index scan
- index seek与index scan
- index seek与index scan
- index seek与index scan .
- INDEX FAST FULL SCAN,INDEX FULL SCAN与排序
- INDEX FULL SCAN 与 INDEX FAST FULL SCAN
- INDEX FULL SCAN 与 INDEX FAST FULL SCAN
- Oracle INDEX FAST FULL SCAN与 INDEX FULL SCAN
- MySQL的loose index scan
- index range scan,index fast full scan,index skip scan发生的条件
- index range scan,index fast full scan,index skip scan发生的条件
- index range scan,index fast full scan,index skip scan发生的条件
- index range scan,index fast full scan,index skip scan发生的条件
- esqw掏纪号纪迷迂惶naoqi
- 【项目1-动物这样叫】
- 14周项目一动物这样叫
- 非常高效的文件处理 API 和 CFile 的使用详解
- eclipse中android单元测试
- PostgreSQL 中 index scan 与 seq scan 的对比学习
- android_wifi读书笔记之3-JNI层 分析
- Linux程序设计-mmap使用
- JAVA学习随记1
- android_wifi读书笔记之4-HAL层分析
- linux 比较文件相同部分comm命令和不同部分diff命令
- 推荐一个比较好的.net论坛,http://bbs.netluntan.com
- 多参多线程
- 形状类族的中的纯虚函数