PostgreSQLbitmapAnd,bitmapOr,bitmapindexscan,bitmapheapscan
標籤
PostgreSQL , bitmap index scan , bitmap heap scan
背景
在PostgreSQL中，多個單列索引是可以用在組合查詢SQL中的，也就是說實現了bitmap scan。
比如
select * from tbl where c1=1 and c2=1 or c3=1;
用到了3列，如果這3列分別有一個索引，那麼PostgreSQL會使用這三個索引的bitmap scan。
PostgreSQL是如何處理多個組合條件的BITMAP SCAN的呢？
Bitmap Heap Scan on customers (cost=25.76..61.62 rows=10 width=13) (actual time=0.077..0.077 rows=2 loops=1)
Recheck Cond: (((username)::text < `user100`::text) AND (customerid < 1000))
-> BitmapAnd (cost=25.76..25.76 rows=10 width=0) (actual time=0.073..0.073 rows=0 loops=1)
-> Bitmap Index Scan on ix_cust_username (cost=0.00..5.75 rows=200 width=0) (actual time=0.006..0.006 rows=2 loops=1)
Index Cond: ((username)::text < `user100`::text)
-> Bitmap Index Scan on customers_pkey (cost=0.00..19.75 rows=1000 width=0) (actual time=0.065..0.065 rows=999 loops=1)
Index Cond: (customerid < 1000)
bitmap scan原理
對於每個查詢條件，在對應索引中找到符合條件的堆表PAGE，每個索引構造一個bitmap串。
在這個bitmap串中，每一個BIT位對應一個HEAP PAGE，代表這個HEAP PAGE中有符合該條件的行。
根據條件的多少，組成了多個bitmap。
例如 a=1 or a=2 是兩個bitmap。
postgres=# explain select * from tbl where id in (1,2,3); -- in 可以直接使用index掃描
QUERY PLAN
--------------------------------------------------------------------------
Index Scan using idx_tbl on tbl (cost=0.29..257.15 rows=10000 width=12)
Index Cond: (id = ANY (`{1,2,3}`::integer[]))
(2 rows)
postgres=# explain select * from tbl where id =1 or id=2 or id=3;
QUERY PLAN
----------------------------------------------------------------------------------
Bitmap Heap Scan on tbl (cost=124.97..354.97 rows=10000 width=12)
Recheck Cond: ((id = 1) OR (id = 2) OR (id = 3))
-> BitmapOr (cost=124.97..124.97 rows=10000 width=0)
-> Bitmap Index Scan on idx_tbl (cost=0.00..114.28 rows=10000 width=0)
Index Cond: (id = 1)
-> Bitmap Index Scan on idx_tbl (cost=0.00..1.59 rows=1 width=0)
Index Cond: (id = 2)
-> Bitmap Index Scan on idx_tbl (cost=0.00..1.59 rows=1 width=0)
Index Cond: (id = 3)
(9 rows)
postgres=# explain select * from tbl where id=1 and id=2 or id=3;
QUERY PLAN
----------------------------------------------------------------------------
Bitmap Heap Scan on tbl (cost=3.19..4.51 rows=1 width=12)
Recheck Cond: (((id = 1) AND (id = 2)) OR (id = 3))
-> BitmapOr (cost=3.19..3.19 rows=1 width=0)
-> Bitmap Index Scan on idx_tbl (cost=0.00..1.59 rows=1 width=0)
Index Cond: ((id = 1) AND (id = 2)) -- and 可以合併
-> Bitmap Index Scan on idx_tbl (cost=0.00..1.59 rows=1 width=0)
Index Cond: (id = 3)
(7 rows)
不同欄位也一樣。
postgres=# create table t12 (c1 int, c2 int, c3 int);
CREATE TABLE
postgres=# create index t12_c1 on t12 (c1);
CREATE INDEX
postgres=# create index t12_c2 on t12 (c2);
CREATE INDEX
postgres=# create index t12_c3 on t12 (c3);
CREATE INDEX
postgres=# explain select * from t12 where c1=1 and c2=1 or c3=1;
QUERY PLAN
----------------------------------------------------------------------------------
Bitmap Heap Scan on t12 (cost=4.84..12.36 rows=10 width=12)
Recheck Cond: (((c2 = 1) AND (c1 = 1)) OR (c3 = 1))
-> BitmapOr (cost=4.84..4.84 rows=10 width=0)
-> BitmapAnd (cost=3.31..3.31 rows=1 width=0)
-> Bitmap Index Scan on t12_c2 (cost=0.00..1.53 rows=10 width=0)
Index Cond: (c2 = 1)
-> Bitmap Index Scan on t12_c1 (cost=0.00..1.53 rows=10 width=0)
Index Cond: (c1 = 1)
-> Bitmap Index Scan on t12_c3 (cost=0.00..1.53 rows=10 width=0)
Index Cond: (c3 = 1)
(10 rows)
postgres=# explain select * from t12 where c1=1 and c2=1 or c3=1 or c3=2;
QUERY PLAN
----------------------------------------------------------------------------------
Bitmap Heap Scan on t12 (cost=6.38..16.78 rows=20 width=12)
Recheck Cond: (((c2 = 1) AND (c1 = 1)) OR (c3 = 1) OR (c3 = 2))
-> BitmapOr (cost=6.38..6.38 rows=20 width=0)
-> BitmapAnd (cost=3.31..3.31 rows=1 width=0)
-> Bitmap Index Scan on t12_c2 (cost=0.00..1.53 rows=10 width=0)
Index Cond: (c2 = 1)
-> Bitmap Index Scan on t12_c1 (cost=0.00..1.53 rows=10 width=0)
Index Cond: (c1 = 1)
-> Bitmap Index Scan on t12_c3 (cost=0.00..1.53 rows=10 width=0)
Index Cond: (c3 = 1)
-> Bitmap Index Scan on t12_c3 (cost=0.00..1.53 rows=10 width=0)
Index Cond: (c3 = 2)
(12 rows)
在生成多個bitmap串後，對這些bitmap串執行BIT & | 操作，得到一個新的BIT串，然後根據這個BIT串，順序搜尋bit=1 (bit=0的heap page不會被掃描) 的對應的資料塊(也就是bitmap heap scan)。
因為bitmap index scan返回的是塊級別的bit串，所以在bitmap heap scan時還需要recheck。即搜尋對應的heap page裡的所有tuple(行)，同時對bitmap index scan的條件進行再次過濾。
stackoverflow相關問題
問題
How does PostgreSQL knows by just a bitmap anything about rows` physical order?
回答
The bitmap is one bit per heap page. The bitmap index scan sets the bits based on the heap page address that the index entry points to.
So when it goes to do the bitmap heap scan, it just does a linear table scan, reading the bitmap to see whether it should bother with a particular page or seek over it.
問題
Or generates the bitmap so that any element of it can be mapped to the pointer to a page easily?
回答
No, the bitmap corresponds 1:1 to heap pages.
I wrote some more on this here.
OK, it looks like you might be misunderstanding what "bitmap" means in this context.
It`s not a bit string like "101011" that`s created for each heap page, or each index read, or whatever.
The whole bitmap is a single bit array, with as many bits as there are heap pages in the relation being scanned.
One bitmap is created by the first index scan, starting off with all entries 0 (false). Whenever an index entry that matches the search condition is found, the heap address pointed to by that index entry is looked up as an offset into the bitmap, and that bit is set to 1 (true). So rather than looking up the heap page directly, the bitmap index scan looks up the corresponding bit position in the bitmap.
The second and further bitmap index scans do the same thing with the other indexes and the search conditions on them.
Then each bitmap is ANDed together. The resulting bitmap has one bit for each heap page, where the bits are true only if they were true in all the individual bitmap index scans, i.e. the search condition matched for every index scan. These are the only heap pages we need to bother to load and examine. Since each heap page might contain multiple rows, we then have to examine each row to see if it matches all the conditions - that`s what the "recheck cond" part is about.
One crucial thing to understand with all this is that the tuple address in an index entry points to the row`s ctid, which is a combination of the heap page number and the offset within the heap page. A bitmap index scan ignores the offsets, since it`ll check the whole page anyway, and sets the bit if any row on that page matches the condition.
Graphical example
Heap, one square = one page:
+---------------------------------------------+
|c____u_____X___u___X_________u___cXcc______u_|
+---------------------------------------------+
Rows marked c match customers pkey condition.
Rows marked u match username condition.
Rows marked X match both conditions.
Bitmap scan from customers_pkey:
+---------------------------------------------+
|100000000001000000010000000000000111100000000| bitmap 1
+---------------------------------------------+
One bit per heap page, in the same order as the heap
Bits 1 when condition matches, 0 if not
Bitmap scan from ix_cust_username:
+---------------------------------------------+
|000001000001000100010000000001000010000000010| bitmap 2
+---------------------------------------------+
Once the bitmaps are created a bitwise AND is performed on them:
+---------------------------------------------+
|100000000001000000010000000000000111100000000| bitmap 1
|000001000001000100010000000001000010000000010| bitmap 2
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
|000000000001000000010000000000000010000000000| Combined bitmap
+-----------+-------+--------------+----------+
| | |
v v v
Used to scan the heap only for matching pages:
+---------------------------------------------+
|___________X_______X______________X__________|
+---------------------------------------------+
The bitmap heap scan then seeks to the start of each page and reads the page:
+---------------------------------------------+
|___________X_______X______________X__________|
+---------------------------------------------+
seek------->^seek-->^seek--------->^
| | |
------------------------
only these pages read
and each read page is then re-checked against the condition since there can be >1 row per page and not all necessarily match the condition.
哪些查詢可能會使用bitmap index scan
1. btree 索引的多個組合查詢
不同列的 andor 查詢
相同列的 or 查詢
2. brin 索引
由於brin索引本身儲存的就是一些連續塊的元資訊，所以本身就無法實現精確查詢，所以通過brin查詢時，首先也是構建heap page的bitmap串，（符合條件的為1，不符合條件的為0），然後根據這個bitmap串搜尋tuple.
並在bitmap heap scan階段 recheck 條件。
postgres-# d cluster_test_brin
Unlogged table "public.cluster_test_brin"
Column | Type | Modifiers
----------+-----------------------------+-----------
id | integer |
info | text |
crt_time | timestamp without time zone |
Indexes:
"idx_cluster_test_brin_id" brin (id) WITH (pages_per_range=`128`)
postgres=# explain select * from cluster_test_brin where id=1;
QUERY PLAN
---------------------------------------------------------------------------------------------
Bitmap Heap Scan on cluster_test_brin (cost=115.60..13767.33 rows=10722 width=45)
Recheck Cond: (id = 1)
-> Bitmap Index Scan on idx_cluster_test_brin_id (cost=0.00..112.92 rows=10722 width=0)
Index Cond: (id = 1)
(4 rows)
3. gin 索引
gin 索引儲存的是KEY，以及ctid （heap行號）組成的posting list或posting tree，它理論上是可以支援index scan的，但是PostgreSQL目前僅對GIN實施了bitmap scan。
所以在使用gin索引時，首先也是構造heap page的bitmap串，（符合條件的為1，不符合條件的為0），然後根據這個bitmap串搜尋tuple.
並在bitmap heap scan階段 recheck 條件。
這也是目前gin值得改進的地方。
postgres=# d cluster_test_btree
Unlogged table "public.cluster_test_btree"
Column | Type | Modifiers
----------+-----------------------------+-----------
id | integer |
info | text |
crt_time | timestamp without time zone |
Indexes:
"idx_cluster_test_gin" gin (id)
postgres=# explain select * from cluster_test_btree where id=1;
QUERY PLAN
----------------------------------------------------------------------------------------
Bitmap Heap Scan on cluster_test_btree (cost=90.83..13732.45 rows=10714 width=45)
Recheck Cond: (id = 1)
-> Bitmap Index Scan on idx_cluster_test_gin (cost=0.00..88.16 rows=10714 width=0)
Index Cond: (id = 1)
(4 rows)
參考
http://stackoverflow.com/questions/33100637/undestanding-bitmap-indexes-in-postgresql
https://wiki.postgresql.org/wiki/What`s_new_in_PostgreSQL_9.5#BRIN_Indexes
《寶劍贈英雄 – 任意組合欄位等效查詢, 探探PostgreSQL多列展開式B樹》
《PostgreSQL GIN multi-key search 優化》
《PostgreSQL 聚集儲存 與 BRIN索引 – 高併發行為、軌跡類大吞吐資料查詢場景解說》