PostgreSQL index only scan

概述index only scan ,是我们用select选择字段的时候，所选的字段全部都有索引，那么只需在索引中取数据，就不必访问数据块了，从而提高效率。 1. 建表 postgres=# create table tb_index_test(id serial primary key,name character varying);CREATE TABLE postgres=# postgr index only scan,是我们用select选择字段的时候，所选的字段全部都有索引，那么只需在索引中取数据，就不必访问数据块了，从而提高效率。
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 10000

3. 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 >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;SETpostgres=# 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: ,   Buffers: shared hit=6   InitPlan 1 (returns )     ->  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 )     ->  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)也是一样的。 总结

以上是内存溢出为你收集整理的PostgreSQL index only scan全部内容，希望文章能够帮你解决PostgreSQL index only scan所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。