postgres=# create table tb_index_test(id serial primary key,name character varying); CREATE TABLE postgres=# postgres=# \d tb_index_test; Table "public.tb_index_test" Column | Type | Modifiers --------+-------------------+------------------------------------------------------------ id | integer | not null default nextval('tb_index_test_id_seq'::regclass) name | character varying | Indexes: "tb_index_test_pkey" PRIMARY KEY,btree (id)2. 插入测试数据:
postgres=# insert into tb_index_test values(generate_series(1,10000),'john'); INSERT 0 100003. 区别一、启动成本
> index sacn: 因为需要扫描索引块,节点才能输出,索引启动成本大于0
postgres=# explain(analyze,verbose,buffers)select count(0) from tb_index_test where id<400; QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------------------------------- Aggregate (cost=22.29..22.30 rows=1 width=0) (actual time=0.108..0.108 rows=1 loops=1) Output: count(0) Buffers: shared hit=6 -> Index Scan using tb_index_test_pkey on public.tb_index_test (<span style="color:#ff0000;">cost=0.29</span>..21.29 rows=400 width=0) (actual time=0.016..0.074 rows=399 loops=1) Output: id,name Index Cond: (tb_index_test.id < 400) Buffers: shared hit=6 Total runtime: 0.140 ms (8 rows) Time: 0.620 ms> bitmap index scan: 因为需要扫描索引块,节点才能放输出,索引启动成本也大于0,但是bitmap index scan的启动成本却是0,可以解释为bitmapIndex Scan节点和Bitmap Heap Scan是一对的,启动成本算在Bitmap Heap Scan上.
postgres=# explain(analyze,buffers)select count(0) from tb_index_test where id<400; QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------- Aggregate (cost=72.39..72.40 rows=1 width=0) (actual time=0.178..0.178 rows=1 loops=1) Output: count(0) Buffers: shared hit=6 -> Bitmap Heap Scan on public.tb_index_test (cost=11.38..71.39 rows=400 width=0) (actual time=0.062..0.120 rows=399 loops=1) Output: id,name Recheck Cond: (tb_index_test.id < 400) Buffers: shared hit=6 -> Bitmap Index Scan on tb_index_test_pkey (c<span style="color:#ff0000;">ost=0.00</span>..11.29 rows=400 width=0) (actual time=0.047..0.047 rows=399 loops=1) Index Cond: (tb_index_test.id < 400) Buffers: shared hit=3 Total runtime: 0.217 ms (11 rows) Time: 0.741 ms4. 区别二、节点返回的类型
index scan: 输出的是tuple,它先扫描索引块,然后得到rowid扫描数据块得到目标记录。
bitmap index scan; 输出的是索引条目,并不是行的数据,输出索引条目后,交给上一个几点 bitmap heap scan(之间可能将索引条目根据物理排列顺序进行排序)。
以上参考自 德哥 的教程
5. 区别三、扫描索引块和数据块的区别。index scan: 一次只读一条索引项,那么一个 PAGE面有可能被多次访问;
bitmap index scan : 一次性将满足条件的索引项全部取出,然后交给bitmap heap scan节点,并在内存中进行排序,根据取出的索引项访问表数据。
参考自:http://francs3.blog.163.com/blog/static/405767272011665227181/