以Postgresql为主,再聊聊资料库 PostgreSQL GIN index 介绍二 多栏位index的应用

PostgreSQL 官网文件 11.3 与 11.5 为我们阐述关于多栏位的index 运作情况,提到了 BTREE, GIN, GiST 以及BRIN等PostgreSQL的index.https://www.postgresql.org/docs/current/indexes-multicolumn.htmlhttps://www.postgresql.org/docs/current/indexes-bitmap-scans.html我们先来看使用GIN的例子.create table itgin0918c (  id int not null, c1 int not null, c2 int not null, c3 int not null);insert into itgin0918cselect generate_series(1,100000)     , random() * 10     , random() * 20     , random() * 30;create index itgin0918c_idx on itgin0918c using gin (c1, c2, c3);index size:+--------+----------------+-------+------------+--------+| Schema |      Name      | Owner |   Table    |  Size  |+--------+----------------+-------+------------+--------+| aki    | itgin0918c_idx | aki   | itgin0918c | 648 kB |+--------+----------------+-------+------------+--------+explain (analyze,verbose,timing,costs,buffers)  select *    from itgin0918c   where c1 = 6    and (c2 = 7 or c3 = 8);+----------------------------------------------------------------------------------------------------------------------------------+|                                                            QUERY PLAN                                                            |+----------------------------------------------------------------------------------------------------------------------------------+| Bitmap Heap Scan on aki.itgin0918c  (cost=48.81..628.68 rows=823 width=16) (actual time=5.598..6.440 rows=851 loops=1)           ||   Output: id, c1, c2, c3                                                                                                         ||   Recheck Cond: (((itgin0918c.c1 = 6) AND (itgin0918c.c2 = 7)) OR ((itgin0918c.c1 = 6) AND (itgin0918c.c3 = 8)))                 ||   Heap Blocks: exact=436                                                                                                         ||   Buffers: shared hit=454                                                                                                        ||   ->  BitmapOr  (cost=48.81..48.81 rows=840 width=0) (actual time=5.485..5.485 rows=0 loops=1)                                   ||         Buffers: shared hit=18                                                                                                   ||         ->  Bitmap Index Scan on itgin0918c_idx  (cost=0.00..25.07 rows=507 width=0) (actual time=1.777..1.777 rows=531 loops=1) ||               Index Cond: ((itgin0918c.c1 = 6) AND (itgin0918c.c2 = 7))                                                          ||               Buffers: shared hit=9                                                                                              ||         ->  Bitmap Index Scan on itgin0918c_idx  (cost=0.00..23.33 rows=333 width=0) (actual time=3.703..3.703 rows=336 loops=1) ||               Index Cond: ((itgin0918c.c1 = 6) AND (itgin0918c.c3 = 8))                                                          ||               Buffers: shared hit=9                                                                                              || Planning Time: 5.691 ms                                                                                                          || Execution Time: 14.472 ms                                                                                                        |+----------------------------------------------------------------------------------------------------------------------------------+(15 rows)观察上面的存取计画中, Output: id, c1, c2, c3id 虽然不在我们建立 GIN index 的栏位里面,但还是会包含.另外可以看到做 BitmapOr 运算, 底下有两个 Bitmap Index Scan Execution Time: 14.472 ms  我们来看一下选取单一栏位的情况explain (analyze,verbose,timing,costs,buffers)  select *    from itgin0918c   where c1 = 3;+------------------------------------------------------------------------------------------------------------------------------+|                                                          QUERY PLAN                                                          |+------------------------------------------------------------------------------------------------------------------------------+| Bitmap Heap Scan on aki.itgin0918c  (cost=95.49..758.24 rows=9740 width=16) (actual time=2.162..6.080 rows=9966 loops=1)     ||   Output: id, c1, c2, c3                                                                                                     ||   Recheck Cond: (itgin0918c.c1 = 3)                                                                                          ||   Heap Blocks: exact=541                                                                                                     ||   Buffers: shared hit=546                                                                                                    ||   ->  Bitmap Index Scan on itgin0918c_idx  (cost=0.00..93.05 rows=9740 width=0) (actual time=2.034..2.034 rows=9966 loops=1) ||         Index Cond: (itgin0918c.c1 = 3)                                                                                      ||         Buffers: shared hit=5                                                                                                || Planning Time: 0.145 ms                                                                                                      || Execution Time: 7.568 ms                                                                                                     |+------------------------------------------------------------------------------------------------------------------------------+explain (analyze,verbose,timing,costs,buffers)  select *    from itgin0918c   where c3 = 3;+------------------------------------------------------------------------------------------------------------------------------+|                                                          QUERY PLAN                                                          |+------------------------------------------------------------------------------------------------------------------------------+| Bitmap Heap Scan on aki.itgin0918c  (cost=36.83..617.86 rows=3203 width=16) (actual time=1.456..3.287 rows=3315 loops=1)     ||   Output: id, c1, c2, c3                                                                                                     ||   Recheck Cond: (itgin0918c.c3 = 3)                                                                                          ||   Heap Blocks: exact=538                                                                                                     ||   Buffers: shared hit=541                                                                                                    ||   ->  Bitmap Index Scan on itgin0918c_idx  (cost=0.00..36.02 rows=3203 width=0) (actual time=1.323..1.323 rows=3315 loops=1) ||         Index Cond: (itgin0918c.c3 = 3)                                                                                      ||         Buffers: shared hit=3                                                                                                || Planning Time: 0.220 ms                                                                                                      || Execution Time: 3.953 ms                                                                                                     |+------------------------------------------------------------------------------------------------------------------------------+explain (analyze,verbose,timing,costs,buffers)  select *    from itgin0918c   where c2 = 3;  +------------------------------------------------------------------------------------------------------------------------------+|                                                          QUERY PLAN                                                          |+------------------------------------------------------------------------------------------------------------------------------+| Bitmap Heap Scan on aki.itgin0918c  (cost=51.01..654.92 rows=5033 width=16) (actual time=1.577..3.915 rows=4968 loops=1)     ||   Output: id, c1, c2, c3                                                                                                     ||   Recheck Cond: (itgin0918c.c2 = 3)                                                                                          ||   Heap Blocks: exact=541                                                                                                     ||   Buffers: shared hit=544                                                                                                    ||   ->  Bitmap Index Scan on itgin0918c_idx  (cost=0.00..49.75 rows=5033 width=0) (actual time=1.442..1.442 rows=4968 loops=1) ||         Index Cond: (itgin0918c.c2 = 3)                                                                                      ||         Buffers: shared hit=3                                                                                                || Planning Time: 0.137 ms                                                                                                      || Execution Time: 4.792 ms                                                                                                     |+------------------------------------------------------------------------------------------------------------------------------+explain (analyze,verbose,timing,costs,buffers)  select *    from itgin0918c   where c2 = 4    and c1 = 9;+----------------------------------------------------------------------------------------------------------------------------+|                                                         QUERY PLAN                                                         |+----------------------------------------------------------------------------------------------------------------------------+| Bitmap Heap Scan on aki.itgin0918c  (cost=25.11..584.84 rows=499 width=16) (actual time=2.083..7.563 rows=505 loops=1)     ||   Output: id, c1, c2, c3                                                                                                   ||   Recheck Cond: ((itgin0918c.c1 = 9) AND (itgin0918c.c2 = 4))                                                              ||   Heap Blocks: exact=326                                                                                                   ||   Buffers: shared hit=334                                                                                                  ||   ->  Bitmap Index Scan on itgin0918c_idx  (cost=0.00..24.99 rows=499 width=0) (actual time=1.983..1.983 rows=505 loops=1) ||         Index Cond: ((itgin0918c.c1 = 9) AND (itgin0918c.c2 = 4))                                                          ||         Buffers: shared hit=8                                                                                              || Planning Time: 0.689 ms                                                                                                    || Execution Time: 7.904 ms                                                                                                   |+----------------------------------------------------------------------------------------------------------------------------+(10 rows)如同官网所说的A multicolumn GIN index can be used with query conditions that involve any subset of the index's columns. Unlike B-tree or GiST, index search effectiveness is the same regardless of which index column(s) the query conditions use.GIN 的查询是稳定的,不受栏位组合的影响.在一些情境下,可以运用.