京东云 PostgreSQL 在 GIS 场景的应用分享

2021 年 12 月 15 日
本文字数：1766 字
阅读完需：约 6 分钟

作者：曲艺伟/彭智

在地图或地理信息有关的场景里，地址关键词的检索尤其重要。比如打开百度地图，想要查询某个位置的信息“北京市海淀区清华东路 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 中文分词插件，分词效果也有不错的表现。