PostgreSQL DBA(6) - SeqScan vs IndexScan vs Bit...

本节介绍了PostgreSQL中数据表的三种扫描类型，分别是顺序扫描SeqScan、索引扫描IndexScan和位图堆扫描BitmapHeapScan。

一、简介

选择率=条件过滤后的元组数/条件过滤前的元组数

顺序扫描SeqScan
直接对数据表堆数据(Heap Data)进行顺序扫描,适用于选择率较高的场景.
索引扫描IndexScan
通过访问索引获得元组位置指针后再访问堆数据,适用于选择率较低的场景.
位图堆扫描BitmapHeapScan
位图堆扫描需要首先通过BitmapIndexScan(位图索引扫描)把符合条件的元组所在的Page(Block) ID存储在Bitmap中,然后再通过Bitmap访问堆数据,适用于选择率不高不低的场景,介于上面两种扫描方式之间.

2018.10.01 修正，索引适用于选择率低的情况，顺序扫描适用于选择率高的情况

值得注意的地方:
1."选择率较高"是一种定性的表述,实际上PG是根据Cost计算来确定使用哪种扫描方式.通常情况下,索引扫描主要执行的操作是随机访问存储设备,在PG的初始化参数配置中,随机访问的Cost是4,而顺序访问的Cost是1,很粗略的估算,如果通过索引访问的Index Blocks + Heap Blocks超过顺序访问的Heap Blocks的1/4,那么PG会选择使用顺序扫描而不是索引扫描.
2.IndexScan的扫描方式是访问索引,如符合条件则马上根据索引中的元组位置指针访问堆数据从而获取元组,而BitmapIndexScan(位图索引扫描)是访问索引,把符合条件的Block ID存储在Bitmap中,这时候不涉及扫描堆数据,最终获取元组的操作通过BitmapHeapScan扫描完成.
这两者的不同,下面这段话总结得非常到位:

A plain Index Scan fetches one tuple-pointer at a time from the index, and immediately visits that tuple in the table. A bitmap scan fetches all the tuple-pointers from the index in one go, sorts them using an in-memory "bitmap" data structure, and then visits the table tuples in physical tuple-location order.

下面通过样例脚本直观感受这几种方式的不同.
测试数据表,t_dwxx,10000行数据,在dwbh上创建PK

testdb=# select count(*) from t_dwxx;
 count 
-------
 10000
(1 row)

二、SeqScan

测试脚本:

testdb=# explain verbose select t1.* from t_dwxx t1 where dwbh > '1000';
                              QUERY PLAN                              
----------------------------------------------------------------------
 Seq Scan on public.t_dwxx t1  (cost=0.00..189.00 rows=9999 width=20)
   Output: dwmc, dwbh, dwdz
   Filter: ((t1.dwbh)::text > '1000'::text)
(3 rows)

查询条件为dwbh > '1000',选择率较低,PG选择了顺序扫描SeqScan,成本189.00,该成本如何计算,有兴趣的可参照源码解读(53),通过gdb跟踪分析.

三、IndexScan

测试脚本:

testdb=# explain verbose select t1.* from t_dwxx t1 where dwbh = '10000';
                                     QUERY PLAN                                      
-------------------------------------------------------------------------------------
 Index Scan using t_dwxx_pkey on public.t_dwxx t1  (cost=0.29..8.30 rows=1 width=20)
   Output: dwmc, dwbh, dwdz
   Index Cond: ((t1.dwbh)::text = '10000'::text)
(3 rows)

查询条件为dwbh = '10000',选择率很高,只有1条记录,选择索引扫描.
总成本8.30=启动成本 + 一次Index Block访问 + 一次Heap Block访问=0.29 + 4 + 4≈8.30

四、BitmapHeapScan

测试脚本:

testdb=# explain verbose select t1.* from t_dwxx t1 where dwbh > '1000' and dwbh < '3000';
                                         QUERY PLAN                                          
---------------------------------------------------------------------------------------------
 Bitmap Heap Scan on public.t_dwxx t1  (cost=51.07..148.42 rows=2223 width=20)
   Output: dwmc, dwbh, dwdz
   Recheck Cond: (((t1.dwbh)::text > '1000'::text) AND ((t1.dwbh)::text < '3000'::text))
   ->  Bitmap Index Scan on t_dwxx_pkey  (cost=0.00..50.52 rows=2223 width=0)
         Index Cond: (((t1.dwbh)::text > '1000'::text) AND ((t1.dwbh)::text < '3000'::text))
(5 rows)

查询条件为dwbh > '1000' and dwbh < '3000',选择率不高不低,PG选择了BitmapHeapScan,启动成本为51.07,总成本为148.42,该成本如何计算,后续的源码解读会跟踪分析.
值得注意的是在BitmapIndexScan后有一步:Recheck,这是因为位图索引扫描只是把Heap Block ID找出来,并没有把符合条件的元组找出来,因此出现了Recheck这一步.

五、参考资料

PostgreSQL indexing: Index scan vs. Bitmap scan vs. Sequential scan
Bitmap indexes
What is a “Bitmap heap scan” in a query plan?