Greenplum 空間(GIS)資料檢索 B-Tree & GiST 索引實踐 - 阿裡雲HybridDB for PostgreSQL最佳實踐

postgresql , gis , postgis , greenplum , 空間檢索 , gist , b-tree , geohash

氣象資料、地震資料、室内定位、室外定位、手機、車聯網、還有我們最喜歡的“左劃不喜歡、右劃喜歡”，越來越多的位置屬性的資料。将來會越來越多。

基于gis的資料分析、oltp業務也越來越受到決策者的青睐，例如商場的選址決策，o2o的廣告營銷等。有很多基于多邊形、時間、使用者對象屬性過濾的需求。

阿裡雲hybriddb for postgresql是一個支援gis資料類型處理的mpp分布式資料庫，支援海量的gis資料的存儲和分析處理。

支援三種索引接口：

bitmap

btree

gist

三種索引的原理請參考

<a href="https://github.com/digoal/blog/blob/master/201706/20170627_01.md">《postgresql 9種索引的原理和應用場景》</a>

在greenplum中，gist索引比較重（是r-tree結構的空間索引），但是它支援幾乎所有的空間搜尋，但是overhead相比btree也更大一些：

1、平面、三維、多元對象 幾何相交、不相交、相鄰。

2、平面、三維、多元對象的方位判斷（相交或嚴格在左邊、右邊、上邊、下邊），類似數值的大于、小于、大于等于、小于等于。

3、平面、三維、多元對象 包含 另一個對象

4、平面、三維、多元對象 等于 另一個對象

5、平面、三維、多元對象 與另一個對象的（邊、最近、中心點的）距離，按距離排序輸出滿足條件的行，輸出距離在xx以内的行。

postgresql 比greenplum支援的索引接口更多，比如brin是一個很好的塊級索引，對于基于多邊形的群體分析非常有效。但是greenplum中沒有brin。

<a href="https://github.com/digoal/blog/blob/master/201708/20170820_01.md">《postgis空間索引(gist、brin、r-tree)選擇、優化 - 阿裡雲rds postgresql最佳實踐》</a>

那麼greenplum中能否有類似brin塊級索引的功能的，在阿裡雲hybriddb for postgresql中是有這個功能的：

<a href="https://github.com/digoal/blog/blob/master/201708/20170809_02.md">《解密上帝之手 - 阿裡雲hdb for postgresql資料庫metascan特性(存儲級、塊級、batch級過濾與資料編排)》</a>

但是我們先來說說社群版本的greenplum，能否用b-tree替代overhead更大的gist？

答案必須是肯定的。

<a href="https://github.com/digoal/blog/blob/master/201704/20170422_01.md">《geohash vs postgis》</a>

geohash的精度随着它的位數變化，例如4位時，它是一個20公裡大的box。（也就是說一個點，被模糊化為1個方圓20公裡的box。夠粗糙的，但是我們就要用這個粗糙來過濾資料。）

使用geohash一重過濾，得到一個大範圍的box内的資料。

由于geohash是text類型，支援b-tree索引。

當point在某個範圍(box)内時，它的prefix會和這個box的prefix重疊。

一重過濾會得到一個較大範圍，例如我們要查詢5公裡内的人群，實際上傳回的是20公裡内的人群。

二重過濾，使用距離函數，計算真實的距離，過濾不符合條件的記錄。

1、建表，存儲geometry類型的point

2、寫入5000萬測試資料

3、建立geohash表達式索引

4、查詢st_point(100,90)方圓5公裡内的點，我們需要采用4位精度（一重過濾收縮到20公裡）。

postgresql 文法：

greenplum 文法（因為greenplum版本較老，不支援正規表達式的索引檢索，我們需要人為轉換一下）:

5、cluster優化，按geohash重排後，掃描的塊降低，速度大幅提升。

優化後效率如下，從掃描50個塊，降低到掃描5個塊。掃描成本直線下降10倍，對shared buffer的需求也下降10倍。

<a href="https://www.aliyun.com/product/rds/postgresql">阿裡雲 rds postgresql</a>

<a href="https://www.aliyun.com/product/gpdb">阿裡雲 hybriddb for postgresql</a>

greenplum中能否有類似brin塊級索引的功能？在阿裡雲hybriddb for postgresql中是有這個功能的：

對于greenplum來說，如果你覺得gist索引引入的資料導入overhead不值得，那麼建議改用b-tree索引，使用geohash的btree來代替gist索引，通過一重索引過濾，二重cpu過濾來進行空間資料的篩選。

在不太影響導入性能的同時，又能獲得不錯的空間資料篩選性能。何樂而不為呢?

最後，我們可能還有時間、空間、對象屬性多元度查詢的需求(可以使用多級分區表、多字段索引bitmap合并掃描等資料庫黑科技)，方法請參考：

<a href="https://github.com/digoal/blog/blob/master/201706/20170625_01.md">《postgresql\gpdb 毫秒級海量多元資料透視 案例分享》</a>

<a href="https://github.com/digoal/blog/blob/master/201707/20170722_01.md">《時間、空間、對象多元屬性 海量資料任意多元 高效檢索 - 阿裡雲rds postgresql最佳實踐》</a>

<a href="https://github.com/digoal/blog/blob/master/201708/20170802_02.md">《(新零售)商戶網格化營運 - 阿裡雲rds postgresql、hybriddb for postgresql最佳實踐》</a>

<a href="https://github.com/digoal/blog/blob/master/201708/20170818_01.md">《postgresql 單列組合查詢優化 - 多個多邊形查詢優化》</a>

二重過濾的cpu開銷較大，如果這部分操作放到程式端實作，那麼資料庫的查詢耗時将降低到0.17毫秒

<a href="https://github.com/digoal/blog/blob/master/201708/20170809_01.md">《postgis 空間資料學習建議》</a>

<a href="http://postgis.net/docs/st_distance_spheroid.html">http://postgis.net/docs/st_distance_spheroid.html</a>