PostgreSQL中的索引介紹-GiST

張梓彤發表於2020-09-04
SQL索引

Gist


GiST是“廣義搜尋樹”的縮寫。這是一個平衡的搜尋樹，就像前面討論的“ b-tree”一樣。


有什麼區別？«btree»索引與比較語義是嚴格聯絡在一起的：支援«greater»，«less»和«equal»運算子（但非常強大！）。但是，現代資料庫儲存的資料型別對這些操作符毫無意義：譬如地理資料，文字文件，影像等。


GiST索引方法可以幫助我們處理這些資料型別。它允許定義規則來將任意型別的資料分佈到一個平衡的樹中，並且允許定義一個方法使用此表示形式來讓某些運算子訪問。例如，對於空間資料，GiST索引可以«accommodate» “容納” R樹，以支援相對位置運算子（位於左側，右側，包含等），而對於樹形圖，RD樹可以支援相交或包含運算子。


由於可擴充套件性，可以在PostgreSQL中從頭開始建立一種全新的方法：為此，必須實現與索引引擎的介面。但是，這不僅需要預先考慮索引邏輯，還需要對資料結構對映到頁面，鎖的有效實現以及對預寫日誌的支援。所有這些都需要具備高水平技能的開發人員和大量的人力投入。GiST通過接管低階問題並提供自己的介面來簡化任務：幾個與技術無關的函式，而與應用程式領域有關。從這個意義上講，我們可以將GiST視為構建新訪問方法的框架。


結構


GiST是由節點頁面組成的高度平衡樹。 節點由索引行組成。


通常，葉節點的每一行(leafrow)都包含一些謂詞（布林表示式）和對錶行（TID）的引用。 索引資料(key)必須符合此謂詞。


內部節點的每一行(internalrow)還包含一個謂詞和對子節點的引用，並且子樹的所有索引資料都必須滿足此謂詞。 換句話說，內部行的謂詞包括所有子行的謂詞。 GiST索引的這一重要特徵取代了 B-tree的簡單排序。


在GiST樹中搜尋使用專門的一致性函式（«consistent»）—由介面定義並以其自己的方式實現的函式之一，適用於每個受支援的操作符族。


為索引行呼叫一致性函式，並確定該行的謂詞是否與搜尋謂詞一致（指定為“ 索引欄位運算子表示式 ”）。 對於內部行，此函式確定是否需要下降到相應的子樹，而對於葉行，此函式確定索引的資料是否滿足謂詞。


搜尋從根節點開始，就像普通的樹搜尋一樣。 一致性函式允許找出有意義的子節點（可能有多個）可以輸入，而哪些子節點不需要輸入。 然後對找到的每個子節點重複該演算法。 如果節點是葉子節點，則一致性函式選擇的行將作為結果之一返回。


搜尋是深度優先的： 演算法首先嚐試到達一個葉子節點。 這樣可以儘可能快地返回第一個結果（如果使用者只對幾個結果感興趣，而不對所有結果感興趣，那麼這一點可能很重要）。


讓我們再次注意，一致性函式與«greater», «less», 或«equal» 運算子無關。 一致性函式的語義可能完全不同，因此，假定索引不以特定順序返回值。


我們不會討論GiST中值的插入和刪除演算法： 其他一些 介面函式 執行這些操作。 然而，有一點很重要。 將新值插入索引後，將選擇該值在樹中的位置，以便儘可能少地擴充套件其父行的謂詞（理想情況下完全不擴充套件）。 但是，當刪除一個值時，父行的謂詞不再減少。 僅在以下情況下會發生這種情況： 將頁面分為兩部分（當頁面沒有足夠的空間來插入新的索引行時），或者從頭開始重新建立索引（使用REINDEX或VACUUM FULL命令）。 因此，GiST索引用於頻繁更改資料的效率會隨著時間的推移而降低。


此外，我們將考慮一些針對各種資料型別和GiST有用屬性的索引示例：

（1）點（和其他幾何實體）和鄰近搜尋。

（2）區間和排除約束。

（3）全文搜尋。


點的R樹


我們將通過一個平面中的點的索引示例來說明上述內容（我們還可以為其他幾何實體建立類似的索引）。 常規的B-tree不適合這種資料型別的資料，因為沒有為點定義比較運算子。


R-tree的想法是將平面拆分為矩形，這些矩形總共覆蓋所有索引的點。 索引行儲存一個矩形，可以這樣定義謂詞： “所尋找的點位於給定的矩形內”。


R-tree的根將包含幾個最大的矩形（可能相交）。 子節點將包含較小尺寸的矩形，這些矩形嵌入到父節點中，並且覆蓋所有基礎點。


從理論上講，葉節點必須包含要索引的點，但是所有索引行中的資料型別必須相同，因此，再次儲存矩形，但是將«collapsed» “摺疊”成點。


為了視覺化這種結構，我們提供了R-tree的三個層次的影像。 點是機場的座標（類似於 演示資料庫 的“ airports”表中的座標，但提供了來自 openflights.org的 更多資料）。


PostgreSQL中的索引介紹-GiST

一級：兩個大的相交的可見矩形。


PostgreSQL中的索引介紹-GiST

第二級：將大矩形分割成較小的區域。


PostgreSQL中的索引介紹-GiST

第三級：每個矩形包含的點數最多可以容納一個索引頁。


現在讓我們考慮一個非常簡單的“一級”示例：

PostgreSQL中的索引介紹-GiST

    
postgres=
# create table points(p point);

postgres=
# insert into points(p) values

  (point 
'(1,1)'), (point 
'(3,2)'), (point 
'(6,3)'),

  (point 
'(5,5)'), (point 
'(7,8)'), (point 
'(8,6)');



    

postgres=
# create index on points using gist(p);

    通過這種拆分，索引結構將如下所示：

    PostgreSQL中的索引介紹-GiST


    建立的索引可用於加快以下查詢的速度，例如： “查詢給定矩形中包含的所有點”。 可以將這種情況形式化如下： p <@ box '(2,1),(6,3)'（來自«points_ops»運算子族的運算子<@意思是“包含在”）：

      postgres=# set enable_seqscan = off;postgres=# explain(costs off) select * from points where p <@ box '(2,1),(7,4)';                  QUERY PLAN                  ---------------------------------------------- Index Only Scan using points_p_idx on points   Index Cond: (p <@ '(7,4),(2,1)'::box)(2 rows)

      運算子的一致性函式（“ 索引欄位 <@ 表示式 ”，其中索引欄位是一個點，表示式是一個矩形）定義如下： 對於內部行，如果其矩形與表示式定義的矩形相交，則返回«yes» 。 對於葉行，如果它的點（“摺疊”矩形）包含在表示式的定義矩形中，則該函式返回«yes»。

      PostgreSQL中的索引介紹-GiST

      搜尋從根節點開始。 矩形（2,1）-（7,4）與（1,1）-（6,3）相交，但不與（5,5）-（8,8）相交，因此不需要下降到第二個子樹。

      PostgreSQL中的索引介紹-GiST

      到達葉節點後，我們經過其中包含的三個點，並返回其中兩個作為結果： （3,2））（6,3）。

        postgres=# select * from points where p <@ box '(2,1),(7,4)';
     p  
   ------- 
   (3,2) (6,3)
   (2 rows)


        內部


        不幸的是，通常“pageinspect”不允許檢視GiST索引。 但是還有另一種方法： «gevel»擴充套件。 它不包括在標準安裝中，因此請參閱 安裝說明 （ http://www.sai.msu.su/~megera/wiki/Gevel ）。


        如果一切順利，將為您提供三個功能。


        首先，我們可以獲得一些統計資訊：

          postgres=# select * from gist_stat('airports_coordinates_idx'); 
               gist_stat                
               ------------------------------------------ 
               Number of levels:          4            
               + Number of pages:           690          
               + Number of leaf pages:      625          
               + Number of tuples:          7873         
               + Number of invalid tuples:  0            
               + Number of leaf tuples:     7184         
               + Total size of tuples:      354692 bytes 
               + Total size of leaf tuples: 323596 bytes + Total size of index:       
               5652480 bytes+ 
               
               (1 row)

          顯然，機場座標上的索引大小為690頁，並且該索引包含四個級別： 上圖顯示了根級別和兩個內部級別，而第四級是葉子。


          實際上，八千個點的索引會小很多： 此處建立時使用10％的填充因子來建立。


          其次，我們可以輸出索引樹：

            postgres=# select * from gist_tree('airports_coordinates_idx');                                       gist_tree                                              ----------------------------------------------------------------------------------------- 0(l:0) blk: 0 numTuple: 5 free: 7928b(2.84%) rightlink:4294967295 (InvalidBlockNumber) +     1(l:1) blk: 335 numTuple: 15 free: 7488b(8.24%) rightlink:220 (OK)                 +         1(l:2) blk: 128 numTuple: 9 free: 7752b(5.00%) rightlink:49 (OK)               +             1(l:3) blk: 57 numTuple: 12 free: 7620b(6.62%) rightlink:35 (OK)           +             2(l:3) blk: 62 numTuple: 9 free: 7752b(5.00%) rightlink:57 (OK)            +             3(l:3) blk: 72 numTuple: 7 free: 7840b(3.92%) rightlink:23 (OK)            +             4(l:3) blk: 115 numTuple: 17 free: 7400b(9.31%) rightlink:33 (OK)          + ...

            第三，我們可以輸出儲存在索引行中的資料。 請注意以下細微差別： 函式的結果必須強制轉換為所需的資料型別。 在我們的情況下，此型別為«box»（邊界矩形）。 例如，注意頂層的五行：

              postgres=# select level, a from gist_print('airports_coordinates_idx')  as t(level int, valid bool, a box) where level = 1; 
level |                                   a                                  -------+-----------------------------------------------------------------------     1 | (47.663586,80.803207),(-39.2938003540039,-90)     1 | (179.951004028,15.6700000762939),(15.2428998947144,-77.9634017944336)     1 | (177.740997314453,73.5178070068359),(15.0664,10.57970047)     1 | (-77.3191986083984,79.9946975708),(-179.876998901,-43.810001373291)     1 | (-39.864200592041,82.5177993774),(-81.254096984863,-64.2382965088)(5 rows)

              實際上，以上提供的圖形就是根據此資料建立的。


              用於搜尋和排序的運算子


              到目前為止討論的運算子（例如謂詞p <@ box '(2,1),(7,4)'中的<@）可以稱為搜尋運算子，因為它們在查詢中指定了搜尋條件。


              還有另一種運算子型別： 排序運算子。 它們用於ORDER BY子句中排序順序的規範，而不是列名的常規規範。 以下是此類查詢的示例：

                postgres=# select * from points order by p <-> point '(4,7)' limit 2;
   p  
   ------- 
   (5,5) (7,8)
   (2 rows)


                p <-> point '(4,7)'這是一個使用排序運算子<->的表示式，它表示從一個引數到另一個引數的距離。 查詢的意思是返回最接近點（4,7）的兩個點。 這樣的搜尋稱為k-NN-k近鄰搜尋。


                為了支援此類查詢，訪問方法必須定義一個附加的距離函式，並且必須在適當的運算子類（例如，用於點的«points_ops»類）中包括排序運算子。 以下查詢顯示了運算子及其型別（«s»—搜尋和«o»—排序）：

                  
postgres=
# select amop.amopopr::regoperator, amop.amoppurpose, amop.amopstrategy

from pg_opclass opc, pg_opfamily opf, pg_am am, pg_amop amop

where opc.opcname = 
'point_ops'


and opf.oid = opc.opcfamily


and am.oid = opf.opfmethod


and amop.amopfamily = opc.opcfamily


and am.amname = 
'gist'


and amop.amoplefttype = opc.opcintype;



                  



                  

      amopopr      
| amoppurpose | amopstrategy

-------------------+-------------+--------------

 <<(point,point)   
| s           |            
1  strictly left

 
>>(point,point)   
| s           |            
5  strictly right

 ~=(point,point)   
| s           |            
6  coincides

 <^(point,point)   
| s           |           
10  strictly below

 >^(point,point)   
| s           |           
11  strictly above

 <->(point,point)  
| o           |           
15  distance

 <@(point,box)     
| s           |           
28  contained 
in rectangle

 <@(point,polygon) 
| s           |           
48  contained 
in polygon

 <@(point,circle)  
| s           |           
68  contained 
in circle

(
9 rows)

                  還顯示了策略的數量，並解釋了其含義。 顯然，比“ btree”策略要多得多，只有部分策略支援點。 可以為其他資料型別定義不同的策略。


                  距離函式是為索引元素呼叫的，它必須根據表示式（“ 索引欄位排序運算子”表示式）定義的值來計算到給定元素距離（考慮運算子的語義）。 對於葉子元素，這只是到索引值的距離。 對於內部元素，該函式必須返回到葉子元素的最小距離。 因為遍歷所有子行代價非常大，允許該函式樂觀地低估距離，但代價是降低搜尋效率，但是，絕不允許該函式高估距離，因為這會破壞索引的工作。


                  distance函式可以返回任何可排序型別的值（對於排序值，PostgreSQL將使用來自«btree»訪問方法的適當運算子族的比較語義， 如前所述 ）。


                  對於平面中的點，距離可以用一種非常普通的方式來解釋： (x1,y1) <-> (x2,y2)等於橫座標和縱座標的差的平方之和的平方根。 從點到邊界矩形的距離被認為是從點到該矩形的最小距離； 如果點位於矩形內，則為零。 無需遍歷子點即可輕鬆計算該值，並且該值肯定不大於到任何子點的距離。


                  讓我們考慮以上查詢的搜尋演算法。

                  PostgreSQL中的索引介紹-GiST

                  搜尋從根節點開始。 該節點包含兩個邊界矩形。 到（1,1）-（6,3）的距離是4.0，到（5,5）-（8,8）的距離是1.0。


                  按照增加距離的順序遍歷子節點。 這樣，我們首先下降到最近的子節點並計算到這些點的距離（我們將在圖中顯示數字以提高可見性）：

                  PostgreSQL中的索引介紹-GiST

                  該資訊足以返回前兩個點（5,5）和（7,8）。 由於我們知道到矩形（1,1）-（6,3）內的點的距離為4.0或更大，因此我們不必下降到第一個子節點。


                  但是，如果我們需要找到前三個點呢？

                    postgres=# select * from points order by p <-> point '(4,7)' limit 3;   p  ------- (5,5) (7,8) (8,6)(3 rows)

                    儘管第二個子節點包含所有這些點，但是如果不檢視第一個子節點，我們將無法返回（8,6），因為該節點可以包含更近的點（因為4.0 <4.1）。

                    PostgreSQL中的索引介紹-GiST

                    對於內部行，此示例闡明瞭對距離函式的要求。 通過為第二行選擇較小的距離（4.0而不是實際的4.5），我們降低了效率（演算法不必要地開始檢查額外的節點），但並未破壞演算法的正確性。


                    直到最近，GiST還是唯一能夠處理排序運算子的訪問方法。 但是情況發生了變化： RUM訪問方法（將進一步討論）已經加入了這組方法，有用的經典B-tree也不太可能加入它們： 我們的同事Nikita Glukhov開發的補丁正在由社群討論。


                    R 樹的區間


                    使用GiST訪問方法的另一個示例是區間的索引，例如時間區間（“ tsrange”型別）。 不同的是，內部節點將包含邊界區間，而不是邊界矩形。


                    讓我們考慮一個簡單的例子。 我們將出租一間小屋並將預訂時間區間儲存在一張表中：

                      postgres=# create table reservations(during tsrange);
postgres=# insert into reservations(during) values('[2016-12-30, 2017-01-09)'),('[2017-02-23, 2017-02-27)'),('[2017-04-29, 2017-05-02)');
postgres=# create index on reservations using gist(during);


                      例如，可以使用索引來加速以下查詢：


                        postgres=# select * from reservations where during && '[2017-01-01, 2017-04-01)';                    during                    ----------------------------------------------- ["2016-12-30 00:00:00","2017-01-08 00:00:00") ["2017-02-23 00:00:00","2017-02-26 00:00:00")(2 rows)postgres=# explain (costs off) select * from reservations where during && '[2017-01-01, 2017-04-01)';                                     QUERY PLAN                                    ------------------------------------------------------------------------------------ Index Only Scan using reservations_during_idx on reservations   Index Cond: (during && '["2017-01-01 00:00:00","2017-04-01 00:00:00")'::tsrange)(2 rows)

                        &&區間運算子表示交集； 因此，查詢必須返回與給定區間相交的所有區間。 對於這種運算子，一致性函式確定給定的區間是與內部行還是葉行中的值相交。


                        請注意，儘管為區間定義了比較運算子，但這不是以一定順序獲取區間。 我們可以為區間使用«btree»索引，但是在這種情況下，我們將必須在不支援以下操作的情況下進行操作：

                          
postgres=
# select amop.amopopr::regoperator, amop.amoppurpose, amop.amopstrategy

from pg_opclass opc, pg_opfamily opf, pg_am am, pg_amop amop

where opc.opcname = 
'range_ops'


and opf.oid = opc.opcfamily


and am.oid = opf.opfmethod


and amop.amopfamily = opc.opcfamily


and am.amname = 
'gist'


and amop.amoplefttype = opc.opcintype;



                          



                          

         amopopr         
| amoppurpose | amopstrategy

-------------------------+-------------+-------------- 

 @>(anyrange,anyelement) 
| s           |           
16  contains element

 <<(anyrange,anyrange)   
| s           |            
1  strictly left

 &<(anyrange,anyrange)   
| s           |            
2  
not beyond right boundary

 &&(anyrange,anyrange)   
| s           |            
3  intersects

 &>(anyrange,anyrange)   
| s           |            
4  
not beyond left boundary

 
>>(anyrange,anyrange)   
| s           |            
5  strictly right

 -
|-(anyrange,anyrange)  | s           
|            6  adjacent


 @>(anyrange,anyrange)   | s           
|            7  contains interval


 <@(anyrange,anyrange)   | s           
|            8  contained 
in interval


 =(anyrange,anyrange)    | s           
|           18  equals

(10 rows)


                          內部


                          我們可以使用相同的«gevel»擴充套件檢視內部。需要記住在對gist_print的呼叫中更改資料型別：

                            postgres=# select level, a from gist_print('reservations_during_idx')as t(level int, valid bool, a tsrange); level |                       a                      -------+-----------------------------------------------     1 | ["2016-12-30 00:00:00","2017-01-09 00:00:00")     1 | ["2017-02-23 00:00:00","2017-02-27 00:00:00")     1 | ["2017-04-29 00:00:00","2017-05-02 00:00:00")(3 rows)


                            排除約束


                            GiST索引可用於支援排除約束（EXCLUDE）。


                            排除約束確保任何兩個錶行的給定欄位在某些運算子方面都不會彼此“對應”。 如果選擇“等於”運算子，我們將確切地獲得唯一約束： 任意兩行的給定欄位彼此不相等。


                            索引和唯一性約束均支援排除約束。 我們可以選擇任何一個運算子，以便：

                            1.      索引方法 -«can_exclude» 屬性（例如， «btree» ， GiST 或 SP-GiST ，但不包含 GIN ）支援該方法。

                            2.      它是可交換的，即滿足條 件：a運算子b = b運算子a。


                            這是合適的策略和操作符示例列表（操作符可以具有不同的名稱，並且不適用於所有資料型別）：

                            ·          對於 “ btree” ：

                            ·          “ 等於 ”  =

                            ·          對於 GiST 和 SP-GiST ：

                            ·          “ 交集 ”  &&

                            ·          “ 並存 ”  ~=

                            ·          “ 鄰接 ”  -|-


                            請注意，我們可以在排除約束中使用相等運算子，但這是不切實際的：唯一約束將更加有效。這就是為什麼我們在討論B樹時沒有觸及排除約束的原因。


                            讓我們提供一個使用排除約束的示例。不保留相交的區間。

                              postgres=# alter table reservations add exclude using gist(during with &&);

                              建立排除約束後，我們可以新增行：

                                postgres=# insert into reservations(during) values ('[2017-06-10, 2017-06-13)');

                                但是嘗試在表中插入相交的區間會導致錯誤： 

                                  postgres=# insert into reservations(during) values ('[2017-05-15, 2017-06-15)');ERROR: conflicting key value violates exclusion constraint "reservations_during_excl"DETAIL: Key (during)=(["2017-05-15 00:00:00","2017-06-15 00:00:00")) conflicts with existing key (during)=(["2017-06-10 00:00:00","2017-06-13 00:00:00")).


                                   «btree_gist» 擴充套件


                                  讓問題複雜化。 我們擴充套件了自己的小業務，並打算出租幾棟小屋： 

                                    postgres=# alter table reservations add house_no integer default 1;

                                    我們需要更改排除約束，以便考慮房屋門牌號。 但是，GiST不支援整數的相等運算：

                                      
postgres=# alter table reservations drop constraint reservations_during_excl;



                                      

postgres=# alter table reservations add exclude using gist(during 
with &&, house_no 
with =);

ERROR: data 
type integer has no 
default operator 
class 
for access method 
"gist"

HINT: You must specify an operator 
class 
for the index or define a 
default operator 
class 
for the data 
type.

                                      在這種情況下，“  btree_gist  ”副檔名會有所幫助，它新增了對B樹固有操作的GiST支援。 GiST最終可以支援任何運算子，那麼為什麼我們不應該使它支援“更大”，“更少”和“相等”運算子呢？ 

                                        postgres=# create extension btree_gist;postgres=# alter table reservations add exclude using gist(during with &&, house_no with =);

                                        現在我們仍然無法在同一日期預訂第一座小屋： 

                                          postgres=# insert into reservations(during, house_no) values ('[2017-05-15, 2017-06-15)', 1);ERROR: conflicting key value violates exclusion constraint "reservations_during_house_no_excl"

                                          但是，我們可以保留第二個： 

                                            postgres=# insert into reservations(during, house_no) values ('[2017-05-15, 2017-06-15)', 2);

                                            但是請注意，儘管GiST可以以某種方式支援«greater», «less»和«equal»運算子，但B-tree在這方面做得更好。因此，僅在本質上需要GiST索引的情況下才值得使用此技術，就像我們的示例一樣。


                                            用於全文搜尋的 RD 樹


                                            讓我們從對PostgreSQL全文搜尋的簡單介紹開始（如果您知道的話，可以跳過本節）。


                                            全文搜尋的任務是從文件集中選擇與搜尋查詢匹配的那些文件。（如果有很多匹配的文件，那麼找到最佳的匹配很重要，但是在這裡我們不做討論了。）


                                            出於搜尋目的，文件被轉換為專門的型別“ tsvector”，其中包含詞素（lexemes）及其在文件中的位置。詞素（lexemes）是轉換為適合搜尋形式的單詞。例如，單詞通常會轉換為小寫字母，並且變數的結尾會被截斷：

                                              postgres=# select to_tsvector('There was a crooked man, and he walked a crooked mile');               to_tsvector               ----------------------------------------- 'crook':4,10 'man':5 'mile':11 'walk':8(1 row)

                                              我們還可以看到，某些單詞（稱為停止單詞）被完全丟棄（«there», «was», «a», «and», «he»），因為它們可能出現得太頻繁，搜尋它們沒有意義。當然，所有這些轉換都可以配置，但這是另一回事。


                                              搜尋查詢用另一種型別表示：«tsquery»。大致上，一個查詢由一個或幾個連線詞連線的詞素組成：«and»&，«or»|，«not»！..我們也可以使用括號來闡明操作優先順序。

                                                postgres=# select to_tsquery('man & (walking | running)');         to_tsquery         ---------------------------- 'man' & ( 'walk' | 'run' )(1 row)

                                                全文搜尋僅使用一個匹配運算子@@ 

                                                  
postgres=
# select to_tsvector('There was a crooked man, and he walked a crooked mile') @@ to_tsquery('man & (walking | running)');

 ?column?

----------

 t

(1 row)



                                                  



                                                  

postgres=
# select to_tsvector('There was a crooked man, and he walked a crooked mile') @@ to_tsquery('man & (going | running)');

 ?column?

----------

 f

(1 row)

                                                  目前該資訊已足夠。在下一篇介紹GIN索引的文章中，我們將對全文搜尋進行更深入的研究。


                                                  RD樹


                                                  為了快速進行全文搜尋，首先，該表需要儲存一列“ tsvector”型別的列（以避免每次搜尋時都進行昂貴的轉換），其次，必須在此列上建立索引。GiST是一種可能的訪問方法。

                                                    postgres=# create table ts(doc text, doc_tsv tsvector);postgres=# create index on ts using gist(doc_tsv);postgres=# insert into ts(doc) values  ('Can a sheet slitter slit sheets?'),   ('How many sheets could a sheet slitter slit?'),  ('I slit a sheet, a sheet I slit.'),  ('Upon a slitted sheet I sit.'),   ('Whoever slit the sheets is a good sheet slitter.'),   ('I am a sheet slitter.'),  ('I slit sheets.'),  ('I am the sleekest sheet slitter that ever slit sheets.'),  ('She slits the sheet she sits on.');postgres=# update ts set doc_tsv = to_tsvector(doc);

                                                    當然，將觸發器委託給最後一步（將文件轉換為“ tsvector”）很方便。

                                                      postgres=# select * from ts;-[ RECORD 1 ]----------------------------------------------------doc     | Can a sheet slitter slit sheets?doc_tsv | 'sheet':3,6 'slit':5 'slitter':4-[ RECORD 2 ]----------------------------------------------------doc     | How many sheets could a sheet slitter slit?doc_tsv | 'could':4 'mani':2 'sheet':3,6 'slit':8 'slitter':7-[ RECORD 3 ]----------------------------------------------------doc     | I slit a sheet, a sheet I slit.doc_tsv | 'sheet':4,6 'slit':2,8-[ RECORD 4 ]----------------------------------------------------doc     | Upon a slitted sheet I sit.doc_tsv | 'sheet':4 'sit':6 'slit':3 'upon':1-[ RECORD 5 ]----------------------------------------------------doc     | Whoever slit the sheets is a good sheet slitter.doc_tsv | 'good':7 'sheet':4,8 'slit':2 'slitter':9 'whoever':1-[ RECORD 6 ]----------------------------------------------------doc     | I am a sheet slitter.doc_tsv | 'sheet':4 'slitter':5-[ RECORD 7 ]----------------------------------------------------doc     | I slit sheets.doc_tsv | 'sheet':3 'slit':2-[ RECORD 8 ]----------------------------------------------------doc     | I am the sleekest sheet slitter that ever slit sheets.doc_tsv | 'ever':8 'sheet':5,10 'sleekest':4 'slit':9 'slitter':6-[ RECORD 9 ]----------------------------------------------------doc     | She slits the sheet she sits on.doc_tsv | 'sheet':4 'sit':6 'slit':2

                                                      索引應如何組織？不能直接使用R樹，因為尚不清楚如何為文件定義“邊界矩形”。但是我們可以對這種方法進行一些修改，即所謂的RD樹（RD代表“Russian Doll”）。在這種情況下，集合被理解為詞素集合，但是通常，集合可以是任何集合。


                                                      RD樹的一個思想是用邊界集（即包含子集的所有元素的集）替換邊界矩形。


                                                      一個重要的問題是如何在索引行中表示集合。最直接的方法就是列舉集合中的所有元素。如下所示：

                                                      PostgreSQL中的索引介紹-GiST

                                                      例如，對於按條件訪問，doc_tsv @@ to_tsquery('sit')我們只能下降到包含«sit»詞素的那些節點：

                                                      PostgreSQL中的索引介紹-GiST

                                                      這種表示存在明顯的問題。文件中的詞素數量可能非常大，因此索引行會非常大，並進入TOAST中，從而使索引的效率大大降低。即使每個文件只有很少的唯一詞素，集合的並集仍然可能非常大：根目錄越高，索引行就越大。


                                                      有時會使用這樣的表示形式，但針對其他資料型別。全文搜尋使用另一個更緊湊的解決方案-簽名樹。所有處理Bloom過濾器的人都非常熟悉它的思路。


                                                      每個詞素都可以用其簽名表示：一定長度的位串，其中除一位以外的所有位均為零。該位的位置由所述詞位的雜湊函式的值來確定（我們之前討論的雜湊函式內部）。


                                                      文件簽名是所有文件詞素的按位或按位的簽名。


                                                      讓我們假設以下詞素簽名：

                                                        could    1000000ever     0001000good     0000010mani     0000100sheet    0000100sleekest 0100000sit      0010000slit     0001000slitter  0000001upon     0000010whoever  0010000


                                                        然後，文件簽名如下： 

                                                          Can a sheet slitterslit sheets?                      0001101How many sheets could asheet slitter slit?            1001101I slit a sheet, a sheetI slit.                        0001100Upon a slitted sheet Isit.                            0011110Whoever slit the sheetsis a good sheet slitter.       0011111I am a sheetslitter.                                 0000101I slit sheets.                                        0001100I am the sleekest sheetslitter that ever slit sheets. 0101101She slits the sheet shesits on.                       0011100

                                                          索引樹可以表示如下：

                                                          PostgreSQL中的索引介紹-GiST

                                                          這種方法的優點顯而易見：索引行的大小相等，並且這樣的索引很緊湊。但缺點也很明顯：為了緊湊而犧牲了準確性。


                                                          讓我們考慮相同的條件doc_tsv @@ to_tsquery('sit')。讓我們以與文件相同的方式計算搜尋查詢的簽名：本例中為0010000。一致性函式必須返回其簽名至少包含查詢簽名一位的所有子節點：

                                                          PostgreSQL中的索引介紹-GiST

                                                          與上圖比較：我們可以看到樹變成了黃色，這意味著出現誤報，並且在搜尋過程中經過了過多的節點。在這裡，我們選擇了“ whoever”語素，但不幸的是，其簽名與“sit”語素的簽名相同。重要的是，模式中不會出現誤報，也就是說，我們確保不要錯過所需的值。


                                                          此外，可能會發生這樣的情況，即不同的文件也將獲得相同的簽名：在我們的示例中，不幸的文件是“I slit a sheet, asheet I slit”和“I slit sheets”（都具有0001100的簽名）。而且，如果葉子索引行未儲存«tsvector»的值，則索引本身將給出假肯定。當然，在這種情況下，該方法將要求索引引擎使用表重新檢查結果，因此使用者不會看到這些誤報。但是搜尋效率可能會受到影響。


                                                          實際上，簽名在當前實現中為124位元組大小，而不是圖中的7位，因此與示例相比，上述問題發生的可能性要小得多。但實際上，還有更多文件也被索引了。為了以某種方式減少索引方法的誤報次數，該實現有些棘手：被索引的“ tsvector”儲存在葉索引行中，但前提是其大小不大（略小於索引的1/16的一個頁面，對於8 KB的頁面大約為半KB）。


                                                          例


                                                          要了解對實際資料進行索引的方式，讓我們以“ pgsql-hackers”電子郵件歸檔為例。 該示例 中 使用的版本 包含356125條訊息，其中包含傳送日期，主題，作者和文字：

                                                            fts=# select * from mail_messages order by sent limit 1;-[ RECORD 1 ]------------------------------------------------------------------------id         | 1572389parent_id  | 1562808sent       | 1997-06-24 11:31:09subject    | Re: [HACKERS] Array bug is still there....author     | "Thomas G. Lockhart" <Thomas.Lockhart@jpl.nasa.gov>body_plain | Andrew Martin wrote:                                                    +           | > Just run the regression tests on 6.1 and as I suspected the array bug +           | > is still there. The regression test passes because the expected output+           | > has been fixed to the *wrong* output.                                 +           |                                                                         +           | OK, I think I understand the current array behavior, which is apparently+           | different than the behavior for v1.0x.                                  +             ...

                                                            新增並填充“tsvector”型別的列並構建索引。在這裡，我們將三個值合併在一個向量中（主題，作者和訊息文字），以表明文件不必是一個欄位，而是可以包含完全不同的任意部分。 

                                                              
fts=
# alter table mail_messages add column tsv tsvector;



                                                              



                                                              

fts=
# update mail_messages

set tsv = to_tsvector(subject||' '||author||' '||body_plain);


NOTICE:  word is too long to be indexed


DETAIL:  Words longer than 2047 characters are ignored.

...

UPDATE 356125

fts=
# create index on mail_messages using gist(tsv);

                                                              如我們所見，一些單詞由於太大而被刪除。但是該索引最終會建立，並且可以支援搜尋查詢： 

                                                                fts=# explain (analyze, costs off)select * from mail_messages where tsv @@ to_tsquery('magic & value');                        QUERY PLAN---------------------------------------------------------- Index Scan using mail_messages_tsv_idx on mail_messages (actual time=0.998..416.335 rows=898 loops=1)   Index Cond: (tsv @@ to_tsquery('magic & value'::text))   Rows Removed by Index Recheck: 7859 Planning time: 0.203 ms Execution time: 416.492 ms(5 rows)

                                                                我們可以看到，與符合條件的898行一起，訪問方法返回了7859多行，這些行通過重新檢查表而被過濾掉。這證明了準確性損失對效率的負面影響。


                                                                內部


                                                                為了分析索引的內容，我們將再次使用«gevel»擴充套件：

                                                                  fts=# select level, afrom gist_print('mail_messages_tsv_idx') as t(level int, valid bool, a gtsvector)where a is not null; level |               a              -------+-------------------------------     1 | 992 true bits, 0 false bits     2 | 988 true bits, 4 false bits     3 | 573 true bits, 419 false bits     4 | 65 unique words     4 | 107 unique words     4 | 64 unique words     4 | 42 unique words...

                                                                  索引行中儲存的特殊型別“gtsvector”的值實際上是簽名加上源“ tsvector”。如果向量可用，則輸出包含詞素（唯一單詞）的數量，否則，包含簽名中的真位和假位的數量。


                                                                  顯然，在根節點中，簽名退化為“all ones”，即一個索引級別變得絕對無用（另外一個索引級別變得幾乎無用，只有四個假位）。


                                                                  屬性


                                                                  讓我們看一下GiST訪問方法的屬性（使用之前提供的查詢）： 

                                                                     amname |     name      | pg_indexam_has_property--------+---------------+------------------------- gist   | can_order     | f gist   | can_unique    | f gist   | can_multi_col | t gist   | can_exclude   | t

                                                                    不支援值的排序和唯一約束。如我們所見，索引可以建立在幾列上，並用於排除約束中。


                                                                    以下索引層屬性可用：

                                                                           name      | pg_index_has_property---------------+----------------------- clusterable   | t index_scan    | t bitmap_scan   | t backward_scan | f

                                                                      最有趣的屬性是列層的屬性。一些屬性獨立於運算子類：

                                                                                name        | pg_index_column_has_property--------------------+------------------------------ asc                | f desc               | f nulls_first        | f nulls_last         | f orderable          | f search_array       | f search_nulls       | t

                                                                        （不支援排序；不能使用索引搜尋陣列；支援NULL。）


                                                                        但是剩餘的兩個屬性«distance_orderable»和«returnable»將取決於所使用的運算子類。例如，對於點points，我們將得到：

                                                                                  name        | pg_index_column_has_property--------------------+------------------------------ distance_orderable | t returnable         | t

                                                                          第一個屬性表明距離運算子可用於搜尋最近的鄰居。第二個屬性告訴索引可以用於僅索引掃描。儘管葉索引行儲存矩形而不是點，但是訪問方法可以返回所需的內容。


                                                                          以下是區間的屬性：

                                                                                    name        | pg_index_column_has_property--------------------+------------------------------ distance_orderable | f returnable         | t

                                                                            對於區間，距離函式沒有定義，因此無法搜尋鄰近值。


                                                                            對於全文搜尋，我們得到：

                                                                              name | pg_index_column_has_property--------------------+------------------------------ distance_orderable | f returnable         | f

                                                                              因為葉行只能包含簽名而不能包含資料本身，所以失去了對僅索引掃描的支援。 但是，這是一個很小的損失，因為無論如何都沒有人對«tsvector»型別的值感興趣： 該值用於選擇行，而它是需要顯示的源文字，但無論如何都不會從索引中丟失。


                                                                              其他資料型別


                                                                              最後，除了已經討論過的幾何型別（以點為例），區間和全文搜尋型別之外，我們還將提及GiST訪問方法當前支援的其他幾種型別。


                                                                              在標準型別中，這是IP地址的“ inet ” 型別。 所有其餘的都通過擴充套件新增：

                                                                              ·      cube為多維資料集提供了“cube”資料型別。 對於這種型別，就像在平面上的幾何型別一樣，定義了GiST運算子類： R樹，支援搜尋鄰近值。

                                                                              ·       seg為具有指定精度的邊界的間隔提供“ seg”資料型別，併為此資料型別（R樹）新增GiST索引支援。

                                                                              ·       intarray擴充套件了整數陣列的功能，並增加了對它們的GiST支援。 實現了兩個運算子類： “ gist__int_ops”（帶有索引行中鍵的完整表示的RD樹）和“ gist__bigint_ops”（簽名RD樹）。 第一類可用於小陣列，第二類可用於大陣列。

                                                                              ·       ltree為樹狀結構新增了“ ltree”資料型別，併為此資料型別（RD-tree）提供了GiST支援。

                                                                              ·        pg_trgm新增了一個專門的運算子類《 gist_trgm_ops》，用於在全文搜尋中使用三元組。 但這將與GIN索引一起進一步討論。


                                                                              來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/31483620/viewspace-2717291/，如需轉載，請註明出處，否則將追究法律責任。

                                                                              相關文章