PostgreSQL從 9.6 開始支援.
首先安裝 bloom extension.
create extension bloom;
以下會以
https://www.cybertec-postgresql.com/en/trying-out-postgres-bloom-indexes/
這篇為主來介紹,中間一些作者沒有明確寫出的操作,我已經補充了.
create table vehicle (
id int generated always as identity
, brand_code int not null
, model_code int not null
, color_code int not null
, year int not null
, body_type_code int not null
, doors int not null
, seats int not null
, gearbox_code int not null
, fuel_type_code int not null
, aircon_type_code int not null
);
-- 輸入500萬筆測試資料
insert into vehicle (brand_code, model_code, color_code, year, body_type_code, doors, seats, gearbox_code, fuel_type_code, aircon_type_code)
select random()*200 --brand
, random()*25 --model
, random()*25 --color
, random()*26 + 1990 --year
, random()*10 --body_type
, random()*4 + 1 --doors
, random()*8 + 1 --seats
, random()*3 --gearbox
, random()*10 --fuel
, random()*4 --aircon
from generate_series(1, 5*1e6);
commit;
analyze vehicle;
-- create btree indexs and bloom index
CREATE INDEX ON vehicle(brand_code, model_code);
CREATE INDEX ON vehicle(color_code);
CREATE INDEX ON vehicle(year);
CREATE INDEX ON vehicle(body_type_code);
CREATE INDEX ON vehicle(doors);
CREATE INDEX ON vehicle(seats);
CREATE INDEX ON vehicle(gearbox_code);
CREATE INDEX ON vehicle(fuel_type_code);
CREATE INDEX ON vehicle(aircon_type_code);
CREATE INDEX bloom_80_bits ON vehicle USING bloom (brand_code,model_code,color_code,year,body_type_code,doors,seats,gearbox_code,fuel_type_code,aircon_type_code);
查看 index size
\di+ vehicle*
List of relations
+--------+-----------------------------------+-------+-------+---------+--------+-------------+
| Schema | Name | Type | Owner | Table | Size | Description |
+--------+-----------------------------------+-------+-------+---------+--------+-------------+
| aki | vehicle_aircon_type_code_idx | index | aki | vehicle | 107 MB | |
| aki | vehicle_body_type_code_idx | index | aki | vehicle | 107 MB | |
| aki | vehicle_brand_code_model_code_idx | index | aki | vehicle | 107 MB | |
| aki | vehicle_color_code_idx | index | aki | vehicle | 107 MB | |
| aki | vehicle_doors_idx | index | aki | vehicle | 107 MB | |
| aki | vehicle_fuel_type_code_idx | index | aki | vehicle | 107 MB | |
| aki | vehicle_gearbox_code_idx | index | aki | vehicle | 107 MB | |
| aki | vehicle_seats_idx | index | aki | vehicle | 107 MB | |
| aki | vehicle_year_idx | index | aki | vehicle | 107 MB | |
+--------+-----------------------------------+-------+-------+---------+--------+-------------+
(9 rows)
\di+ bloom_80_bits
List of relations
+--------+---------------+-------+-------+---------+-------+-------------+
| Schema | Name | Type | Owner | Table | Size | Description |
+--------+---------------+-------+-------+---------+-------+-------------+
| aki | bloom_80_bits | index | aki | vehicle | 77 MB | |
+--------+---------------+-------+-------+---------+-------+-------------+
(1 row)
explain analyze
select *
from vehicle
where color_code = 12
and year > 2010
and body_type_code = 5
and doors = 4
and gearbox_code = 2
and fuel_type_code = 5
and aircon_type_code = 2;
+----------------------------------------------------------------------------------------------------------------------------------------------------------+
| QUERY PLAN |
+----------------------------------------------------------------------------------------------------------------------------------------------------------+
| Bitmap Heap Scan on vehicle (cost=21980.81..28509.72 rows=9 width=44) (actual time=503.417..817.789 rows=7 loops=1) |
| Recheck Cond: ((color_code = 12) AND (body_type_code = 5) AND (fuel_type_code = 5)) |
| Filter: ((year > 2010) AND (doors = 4) AND (gearbox_code = 2) AND (aircon_type_code = 2)) |
| Rows Removed by Filter: 1941 |
| Heap Blocks: exact=1911 |
| -> BitmapAnd (cost=21980.81..21980.81 rows=1947 width=0) (actual time=469.211..469.212 rows=0 loops=1) |
| -> Bitmap Index Scan on vehicle_color_code_idx (cost=0.00..3674.43 rows=198667 width=0) (actual time=81.800..81.801 rows=199258 loops=1) |
| Index Cond: (color_code = 12) |
| -> Bitmap Index Scan on vehicle_body_type_code_idx (cost=0.00..9126.94 rows=493667 width=0) (actual time=178.718..178.718 rows=500185 loops=1) |
| Index Cond: (body_type_code = 5) |
| -> Bitmap Index Scan on vehicle_fuel_type_code_idx (cost=0.00..9178.93 rows=496333 width=0) (actual time=199.431..199.431 rows=499639 loops=1) |
| Index Cond: (fuel_type_code = 5) |
| Planning Time: 64.370 ms |
| Execution Time: 818.359 ms |
+----------------------------------------------------------------------------------------------------------------------------------------------------------+
(14 rows)
PostgreSQL 的優化器選擇了三個 index 做 Bitmap Index Scan 然後用 BitmapAnd 運算.
沒有選擇 bloom index.
接著我們將上面9個 btree index 暫時disable
注意! 這是危險動作,若不熟悉,或是 index 的名稱不是這樣的.
可以改用 drop index
update pg_index
set indisvalid = false
where indexrelid::regclass::text like 'vehicle_%';
UPDATE 9
Time: 24.649 ms
commit;
設定不做 Sequence Scna
SET enable_seqscan = OFF;
explain analyze
select *
from vehicle
where color_code = 12
and year > 2010
and body_type_code = 5
and doors = 4
and gearbox_code = 2
and fuel_type_code = 5
and aircon_type_code = 2;
+-------------------------------------------------------------------------------------------------------------------------------------------------------------+
| QUERY PLAN |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Bitmap Heap Scan on vehicle (cost=139220.00..139381.49 rows=9 width=44) (actual time=185.860..191.202 rows=7 loops=1) |
| Recheck Cond: ((color_code = 12) AND (body_type_code = 5) AND (doors = 4) AND (gearbox_code = 2) AND (fuel_type_code = 5) AND (aircon_type_code = 2)) |
| Rows Removed by Index Recheck: 24 |
| Filter: (year > 2010) |
| Rows Removed by Filter: 33 |
| Heap Blocks: exact=64 |
| -> Bitmap Index Scan on bloom_80_bits (cost=0.00..139220.00 rows=41 width=0) (actual time=182.069..182.069 rows=64 loops=1) |
| Index Cond: ((color_code = 12) AND (body_type_code = 5) AND (doors = 4) AND (gearbox_code = 2) AND (fuel_type_code = 5) AND (aircon_type_code = 2)) |
| Planning Time: 0.787 ms |
| Execution Time: 191.528 ms |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------+
可以觀察到這次優化器選擇了 Bitmap Index Scan on bloom_80_bits
Execution Time: 191.528 ms
比上面使用了三個btree index 的 Execution Time: 818.359 ms
快了不少.