1. 建表
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 only scan的启动成本
对于IndexOnlyScan节点,虽然是从index输出结果,但是还要先检查visibility MAP,因此startup_cost也大于0. 但是,它的启动成本计算并未计入这部分开销. 而是和普通的index scan计算方法一样.当你新建表之后，没有进行过vacuum和autovacuum操作，这时还没有VM文件，加上索引并没有保存记录的版本信息，索引index only scan还是需要扫描数据块来获取版本信息，这个时候可能比index scan要慢了。
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.127..0.127 rows=1 loops=1)
Output: count(0)
Buffers: shared hit=6
-> Index Only Scan using tb_index_test_pkey on public.tb_index_test (cost=0.29..21.29 rows=400 width=0) (actual time=0.021..0.088 rows=399 loops=1)
Output: id
Index Cond: (tb_index_test.id < 400)
<span style="color:#ff0000;">Heap Fetches: 399 --没有visibility map文件之前，需要fetch所有的heap page。</span>
Buffers: shared hit=6
Total runtime: 0.150 ms
(9 rows)
4. 当筛选的数据集变大到一定程度的时候，优化器还是会选择全表扫描
postgres=# explain(analyze,buffers)select id from tb_index_test where id<8000;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------
Seq Scan on public.tb_index_test (cost=0.00..180.00 rows=8000 width=4) (actual time=0.009..1.526 rows=7999 loops=1)
Output: id
Filter: (tb_index_test.id < 8000)
Rows Removed by Filter: 2001
Buffers: shared hit=55
Total runtime: 1.886 ms
(6 rows)
postgres=# set enable_seqscan =off;
SET
postgres=# explain(analyze,buffers)select id from tb_index_test where id<8000;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------
Index Only Scan using tb_index_test_pkey on public.tb_index_test (cost=0.29..236.28 rows=8000 width=4) (actual time=0.028..2.342 rows=7999 loops=1)
Output: id
Index Cond: (tb_index_test.id < 8000)
Heap Fetches: 0
Buffers: shared hit=24
Total runtime: 3.439 ms
(6 rows)
如果把Seq Scan关闭，强制让优化器使用index only scan，发现成本比全表扫描的大。
5. 这个时候执行min(id),max(id)效率是很高的。
postgres=# explain(analyze,buffers)select min(id),max(id) from tb_index_test;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------
Result (cost=0.63..0.64 rows=1 width=0) (actual time=0.024..0.024 rows=1 loops=1)
Output: $0,$1
Buffers: shared hit=6
InitPlan 1 (returns $0)
-> Limit (cost=0.29..0.31 rows=1 width=4) (actual time=0.017..0.017 rows=1 loops=1)
Output: tb_index_test.id
Buffers: shared hit=3
-> Index Only Scan using tb_index_test_pkey on public.tb_index_test (cost=0.29..295.29 rows=10000 width=4) (actual time=0.015..0.015 rows=1 loops=1)
Output: tb_index_test.id
Index Cond: (tb_index_test.id IS NOT NULL)
Heap Fetches: 0
Buffers: shared hit=3
InitPlan 2 (returns $1)
-> Limit (cost=0.29..0.31 rows=1 width=4) (actual time=0.005..0.005 rows=1 loops=1)
Output: tb_index_test_1.id
Buffers: shared hit=3
-> Index Only Scan Backward using tb_index_test_pkey on public.tb_index_test tb_index_test_1 (cost=0.29..295.29 rows=10000 width=4) (actual time=0.005..0.005 rows=1 loops=1)
Output: tb_index_test_1.id
Index Cond: (tb_index_test_1.id IS NOT NULL)
Heap Fetches: 0
Buffers: shared hit=3
Total runtime: 0.061 ms
(22 rows)因为索引是按顺序存储的，只需访问一个索引块就可以得到min(id)，max(id)也是一样的。