在Postgresql中进行关键字查找

在Postgresql中通过关键字查询，一般情况下，利用like语句。比如：
select name from poi where name like '%key word%'.
但是众所周知，这样的匹配率不尽如人意。

本文通过示例，利用pg_trgm扩展进行实现更完美的关键字查询。

新建数据表如下：

-- public.t_poi definition
-- Drop table
-- DROP TABLE public.t_poi;
CREATE TABLE public.t_poi (
	id varchar NULL,    --唯一标示
	pname varchar NULL, --名称
	dtype varchar NULL, --类别
	address varchar NULL--地址
);
1
2
3
4
5
6
7
8
9

默认表中的数据存储了上千万条记录。

假设其中有如下数据：

SELECT name as pname,dtype,address FROM public.t_poi;

pname                    |dtype             |address                  |
-------------------------+------------------+-------------------------+
黑龙江广播电视网络股份有限公司(通河分公司营业厅)|生活服务;生活服务场所;生活服务场所|建安路与民强街交叉口东北50米通河县广播电视台附近|
闻名劳务咨询公司                 |生活服务;中介机构;中介机构    |通河镇长安街隆泰小区1号楼门市          |
四达劳务信息咨询通河分公司            |生活服务;人才市场;人才市场    |向阳街百合名苑小区4门市             |
黑龙江隆凯房地产估价有限公司           |生活服务;事务所;评估事务所    |隆达路1号附近                  |
哈尔滨旭苑会计服务公司              |生活服务;事务所;会计师事务所   |园丁公寓北门东侧                 |
哈工工具销售公司                 |购物服务;家居建材市场;建材五金市场|南马路86号                   |
哈通物流有限公司                 |生活服务;物流速递;物流速递    |中央大街6号附近                 |
刚子保洁服务公司                 |生活服务;中介机构;中介机构    |金街40号                    |
东讯劳务输出公司                 |生活服务;人才市场;人才市场    |通河镇隆达路大千市场道南中通速递对面       |
神舟线缆有限公司                 |购物服务;家居建材市场;建材五金市场|景阳街140号                  |
1
2
3
4
5
6
7
8
9
10
11
12
13
14

使用like查询包含"夕阳"关键字的记录，语句可能如下

SELECT * FROM public.t_poi WHERE PNAME LIKE '%夕阳红%老年%公寓%';
pname              |dtype            |address          |
-------------------+-----------------+-----------------+
延寿夕阳红老年公寓          |体育休闲服务;度假疗养场所;疗养院|东新华街与爱民南胡同交叉口北50米|
夕阳红老年公寓(通河县红十字医院西北)|体育休闲服务;度假疗养场所;疗养院|中央大街(通河县红十字医院西北) |
道里区夕阳红老年公寓         |体育休闲服务;度假疗养场所;疗养院|安静街104-2号        |
夕阳红老年公寓(清园东)       |体育休闲服务;度假疗养场所;疗养院|信恒现代城文园B栋7门市     |
夕阳红老年公寓            |体育休闲服务;度假疗养场所;疗养院|果园星城a区11栋1号      |
夕阳红老年公寓(信恒现代城东北)   |体育休闲服务;度假疗养场所;疗养院|信恒现代城文园b栋7门市     |
夕阳红老年公寓            |体育休闲服务;度假疗养场所;疗养院|团结路方正镇政府东南100米   |
1
2
3
4
5
6
7
8
9
10

用pg_trgm的方法如下：
先安装扩展：

CREATE EXTENSION pg_trgm;  
1

此时查询可以变为如下方式：

SELECT name as pname,dtype,address FROM  public.t_poi ORDER BY (name <->'夕阳红老年公寓') limit 10;
pname              |dtype             |address          |
-------------------+------------------+-----------------+
夕阳红老年公寓            |体育休闲服务;度假疗养场所;疗养院 |果园星城a区11栋1号      |
夕阳红老年公寓            |体育休闲服务;度假疗养场所;疗养院 |团结路方正镇政府东南100米   |
夕阳红老年公寓(清园东)       |体育休闲服务;度假疗养场所;疗养院 |信恒现代城文园B栋7门市     |
夕阳红老年公寓(信恒现代城东北)   |体育休闲服务;度假疗养场所;疗养院 |信恒现代城文园b栋7门市     |
夕阳老年公寓             |体育休闲服务;度假疗养场所;疗养院 |延川大街146号         |
延寿夕阳红老年公寓          |体育休闲服务;度假疗养场所;疗养院 |东新华街与爱民南胡同交叉口北50米|
道里区夕阳红老年公寓         |体育休闲服务;度假疗养场所;疗养院 |安静街104-2号        |
鑫阳红老年公寓            |体育休闲服务;度假疗养场所;疗养院 |丁香街与金街交叉口西北50米   |
夕阳红老年公寓(通河县红十字医院西北)|体育休闲服务;度假疗养场所;疗养院 |中央大街(通河县红十字医院西北) |
夕阳红老年服务站           |生活服务;生活服务场所;生活服务场所|新疆街道新祥里街劳动局家属楼61号|
1
2
3
4
5
6
7
8
9
10
11
12
13

在实际查找中，可能在pname,dtype,address三列同时查找,所以创建索引如下：

CREATE INDEX idx_poi_similarity 
    ON public.t_poi USING 
    gist (((((pname)::text || (dtype)::text) || (address)::text)) gist_trgm_ops);
1
2
3

由于<->方法类似与近似值计算，所如果被查找列如果比较长的情况下，查找结果可能不尽如人意。

所以结合实际情况对查询进行优化：

select pname,dtype,address from
(
	select distinct * from 
	(
	    --完全包含是我们期望的结果，但是相似度却不一定很高
	    --比如字段中存储了100个字符，搜索的只有两个字符
	    --相似性相比于和搜索结果长度一样，但是只有一个字符差异的记录要低
	    --所以我们先查找包含的记录。
		(select name as pname,dtype,address,0 as OD from public.t_poi pd where strpos(name||dtype||address,'夕阳红老年公寓') > 0 limit 10)
		union all
		(select name as pname,dtype,address,1 as OD from public.t_poi pd order by (name||dtype||address <-> '夕阳红老年公寓') limit 10)
	) a 
) x  limit 10;
pname           |dtype             |address          |
----------------+------------------+-----------------+
夕阳红老年服务站        |生活服务;生活服务场所;生活服务场所|新疆街道新祥里街劳动局家属楼61号|
夕阳红老年公寓         |体育休闲服务;度假疗养场所;疗养院 |果园星城a区11栋1号      |
夕阳红老年公寓         |体育休闲服务;度假疗养场所;疗养院 |果园星城a区11栋1号      |
夕阳红老年公寓         |体育休闲服务;度假疗养场所;疗养院 |团结路方正镇政府东南100米   |
夕阳红老年公寓(信恒现代城东北)|体育休闲服务;度假疗养场所;疗养院 |信恒现代城文园b栋7门市     |
夕阳红牙齿美容         |医疗保健服务;诊所;诊所      |庆木公路西50米         |
夕阳红老年公寓(清园东)    |体育休闲服务;度假疗养场所;疗养院 |信恒现代城文园B栋7门市     |
道里区夕阳红老年公寓      |体育休闲服务;度假疗养场所;疗养院 |安静街104-2号        |
道里区夕阳红老年公寓      |体育休闲服务;度假疗养场所;疗养院 |安静街104-2号        |
夕阳红老年公寓(信恒现代城东北)|体育休闲服务;度假疗养场所;疗养院 |信恒现代城文园b栋7门市     |
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

关于pg_trgm的详情，请查看pg_trgm。