京東雲PostgreSQL在GIS場景的應用分享

在地圖或地理資訊有關的場景裡，地址關鍵詞的檢索尤其重要。比如開啟百度地圖，想要查詢某個位置的資訊“北京市海淀區清華東路17號中國農業大學”，往往我們輸入的是關鍵詞“中國農業大學”而不是精確到街道的詳細地址資訊。在地址關鍵詞檢索的背後，需要的是一款可以支援全文檢索和模糊查詢的資料庫與之匹配，以此快速提高地址檢索的效率。

PostgreSQL被譽為“世界上可獲得的最先進的開源資料庫 ”，擁有很強的文字搜尋能力，不僅支援全文檢索，PostgreSQL還支援模糊查詢、正則查詢。除此之外，PostgreSQL還內建了表示式索引、Gin索引功能，配合豐富的外掛生態，在地址關鍵詞檢索方向有比較大的優勢。本文介紹了一種基於PostgreSQL物流地址關鍵詞檢索的方法，以此來說明如何用PostgreSQL提升物流地址關鍵詞的檢索效率。

一、應用背景

在需要地址檢索的場景中，使用者輸入地址文字後需要對地址進行分詞，然後透過全文索引技術與地址語料資料庫進行匹配，得到規範化的地址資訊，並在此基礎上進行地址定位。通常地址查詢語句在經過地址分詞處理後會被分割成幾段關鍵詞，透過關鍵詞匹配到歷史地址語料資料庫，再返回查詢語句得到查詢結果。

通常從使用者輸入關鍵詞查詢到得到返回結果由於關鍵詞分詞和匹配方法不同，會耗時幾秒到幾十秒不等。

檢索資料庫中的條目是很基本常見的功能，實現的方法也很多，常見包括：

1、基於Elasticsearch 或 Lucene這類專業獨立的檢索引擎實現

2、基於資料庫自帶的檢索功能實現

雖然基於Elasticsearch這類系統能實現比較靈活的檢索功能，但開發和運維成本也將大大增加，如何利用PostgresSQL內建的功能快速高效的實現大多數中文檢索場景是我們要討論的技術方案。

二、技術方案

GIN(Generalized Inverted Index, 通用倒排索引) 是一個儲存對(key, posting list)集合的索引結構，其中key是一個鍵值，而posting list 是一組出現過key的位置。如('hello', '14:2 23:4')中，表示hello在14:2和23:4這兩個位置出現過，在PostgreSQL中這些位置實際上就是元組的tid。表中的每一個屬性在建立索引時，都可能會被解析為多個鍵值，所以同一個元組的tid可能會出現在多個key的posting list中。透過這種索引結構可以快速的查詢到包含指定關鍵字的元組。

pg_trgm是PostgreSQL基於N-gram模型分詞的擴充套件外掛，它的基本思想是將文字里面的內容按照位元組進行大小為N的滑動視窗操作，形成了長度是N的位元組片段序列，pg_trgm就是三元的3-Gram，每連續的3個字元為一個TOKEN，然後在對TOKEN建立GIN倒排索引，就可以進行高效、精準的模糊查詢。

pgbigm與pg_trgm類似，也是PostgreSQL基於N-gram模型分詞的擴充套件外掛，區別在於pgbigm是二元的2-Gram。

結合PostgreSQL 索引和分詞模型的特點，我們構建了1億行左右的北京區域的本文地址資料進行效能測試，對比分析PostgreSQL在物流關鍵詞檢索的場景裡有明顯效率的提升，測試結果如下：

從以上結果可以看出，無論是pg_trgm+gin還是pgbigm+gin效能比常用的Btree在進行模糊查詢的時候，效能要好很多。同時，因為pg_trgm生成的TOKEN是三個字元，只有在三個字元以上條件，才能匹配到對應的TOKEN，當小於3個字元，需要前後模糊搜尋1個或者2個字元，所以檢索效能下降比較明顯，相比來說pgbigm（基於二元的Tri-Gram）在處理單字、雙字字元的模糊查詢效率都比較高。由於物流的關鍵字都是三個字元以上，所以採用的是pg_trgm+gin的方案進行關鍵詞檢索查詢，從而保證毫秒級別的響應時間。

另外對於文字地址資料，往往都具備自然語言的特性，jieba結巴分詞是一個強大的分詞庫，分詞更加貼合業務屬性特點，主要功能包含：支援不同模式的分詞、自定義字典、關鍵字提取、詞性標註。pg_jieba運用了jieba分詞演算法，構建了PostgreSQL中文分詞外掛，分詞效果也有不錯的表現。

三、總結

綜上，PostgreSQL支援豐富的索引，具備強大的全文檢索能力以及多樣的外掛生態，支援不同場景下的文字查詢，使用者完全不需要將資料同步到搜尋引擎，再來查詢，使用PostgreSQL可以大幅度的簡化使用者的架構，開發成本，同時保證資料查詢的絕對實時性。

京東雲基於開源的 PostgreSQL構建的一款功能強大的關係型資料庫雲資料庫 PostgreSQL ，支援豐富的資料型別及地理資訊擴充套件，具有強大的平行計算能力。支援備份、監控、遷移等全套解決方案。

作者：曲藝偉/彭智