iT邦幫忙

0

以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.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 的查詢是穩定的,不受欄位組合的影響.
在一些情境下,可以運用.

尚未有邦友留言

立即登入留言