以Postgresql为主,再聊聊资料库 PostgreSQL Bloom index 介绍

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 indexCREATE 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 indexupdate pg_index   set indisvalid = false where indexrelid::regclass::text like 'vehicle_%';UPDATE 9Time: 24.649 mscommit;设定不做 Sequence ScnaSET 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_bitsExecution Time: 191.528 ms 比上面使用了三个btree index 的 Execution Time: 818.359 ms 快了不少.