PostgreSQL 官網文件 11.3 與 11.5 為我們闡述關於多欄位的index 運作情況,
提到了 BTREE, GIN, GiST 以及BRIN等PostgreSQL的index.
https://www.postgresql.org/docs/current/indexes-multicolumn.html
https://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 itgin0918c
select 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, c3
id 雖然不在我們建立 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 的查詢是穩定的,不受欄位組合的影響.
在一些情境下,可以運用.
一級屠豬士
iThome鐵人賽
看影片追技術