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

本節(jié)介紹了PostgreSQL中數(shù)據(jù)表的三種掃描類型，分別是順序掃描SeqScan、索引掃描IndexScan和位圖堆掃描BitmapHeapScan。

一、簡介

選擇率=條件過濾后的元組數(shù)/條件過濾前的元組數(shù)

順序掃描SeqScan
直接對(duì)數(shù)據(jù)表堆數(shù)據(jù)(Heap Data)進(jìn)行順序掃描,適用于選擇率較高的場景.
索引掃描IndexScan
通過訪問索引獲得元組位置指針后再訪問堆數(shù)據(jù),適用于選擇率較低的場景.
位圖堆掃描BitmapHeapScan
位圖堆掃描需要首先通過BitmapIndexScan(位圖索引掃描)把符合條件的元組所在的Page(Block) ID存儲(chǔ)在Bitmap中,然后再通過Bitmap訪問堆數(shù)據(jù),適用于選擇率不高不低的場景,介于上面兩種掃描方式之間.

2018.10.01 修正，索引適用于選擇率低的情況，順序掃描適用于選擇率高的情況

值得注意的地方:
1."選擇率較高"是一種定性的表述,實(shí)際上PG是根據(jù)Cost計(jì)算來確定使用哪種掃描方式.通常情況下,索引掃描主要執(zhí)行的操作是隨機(jī)訪問存儲(chǔ)設(shè)備,在PG的初始化參數(shù)配置中,隨機(jī)訪問的Cost是4,而順序訪問的Cost是1,很粗略的估算,如果通過索引訪問的Index Blocks + Heap Blocks超過順序訪問的Heap Blocks的1/4,那么PG會(huì)選擇使用順序掃描而不是索引掃描.
2.IndexScan的掃描方式是訪問索引,如符合條件則馬上根據(jù)索引中的元組位置指針訪問堆數(shù)據(jù)從而獲取元組,而BitmapIndexScan(位圖索引掃描)是訪問索引,把符合條件的Block ID存儲(chǔ)在Bitmap中,這時(shí)候不涉及掃描堆數(shù)據(jù),最終獲取元組的操作通過BitmapHeapScan掃描完成.
這兩者的不同,下面這段話總結(jié)得非常到位:

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.

下面通過樣例腳本直觀感受這幾種方式的不同.
測試數(shù)據(jù)表,t_dwxx,10000行數(shù)據(jù),在dwbh上創(chuàng)建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,該成本如何計(jì)算,有興趣的可參照源碼解讀(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=啟動(dòng)成本 + 一次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,啟動(dòng)成本為51.07,總成本為148.42,該成本如何計(jì)算,后續(xù)的源碼解讀會(huì)跟蹤分析.
值得注意的是在BitmapIndexScan后有一步:Recheck,這是因?yàn)槲粓D索引掃描只是把Heap Block ID找出來,并沒有把符合條件的元組找出來,因此出現(xiàn)了Recheck這一步.

五、參考資料

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