在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--地址
);
默认表中的数据存储了上千万条记录。
假设其中有如下数据：
SELECT name as pname,dtype,address FROM public.t_poi;
pname |dtype |address |
-------------------------+------------------+-------------------------+
黑龙江广播电视网络股份有限公司(通河分公司营业厅)|生活服务;生活服务场所;生活服务场所|建安路与民强街交叉口东北50米通河县广播电视台附近|
闻名劳务咨询公司 |生活服务;中介机构;中介机构 |通河镇长安街隆泰小区1号楼门市 |
四达劳务信息咨询通河分公司 |生活服务;人才市场;人才市场 |向阳街百合名苑小区4门市 |
黑龙江隆凯房地产估价有限公司 |生活服务;事务所;评估事务所 |隆达路1号附近 |
哈尔滨旭苑会计服务公司 |生活服务;事务所;会计师事务所 |园丁公寓北门东侧 |
哈工工具销售公司 |购物服务;家居建材市场;建材五金市场|南马路86号 |
哈通物流有限公司 |生活服务;物流速递;物流速递 |中央大街6号附近 |
刚子保洁服务公司 |生活服务;中介机构;中介机构 |金街40号 |
东讯劳务输出公司 |生活服务;人才市场;人才市场 |通河镇隆达路大千市场道南中通速递对面 |
神舟线缆有限公司 |购物服务;家居建材市场;建材五金市场|景阳街140号 |
使用like查询包含"夕阳"关键字的记录，语句可能如下
SELECT * FROM public.t_poi WHERE PNAME LIKE '%夕阳红%老年%公寓%';
pname |dtype |address |
-------------------+-----------------+-----------------+
延寿夕阳红老年公寓 |体育休闲服务;度假疗养场所;疗养院|东新华街与爱民南胡同交叉口北50米|
夕阳红老年公寓(通河县红十字医院西北)|体育休闲服务;度假疗养场所;疗养院|中央大街(通河县红十字医院西北) |
道里区夕阳红老年公寓 |体育休闲服务;度假疗养场所;疗养院|安静街104-2号 |
夕阳红老年公寓(清园东) |体育休闲服务;度假疗养场所;疗养院|信恒现代城文园B栋7门市 |
夕阳红老年公寓 |体育休闲服务;度假疗养场所;疗养院|果园星城a区11栋1号 |
夕阳红老年公寓(信恒现代城东北) |体育休闲服务;度假疗养场所;疗养院|信恒现代城文园b栋7门市 |
夕阳红老年公寓 |体育休闲服务;度假疗养场所;疗养院|团结路方正镇政府东南100米 |
用pg_trgm的方法如下：
先安装扩展：
CREATE EXTENSION pg_trgm;
此时查询可以变为如下方式：
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号|
在实际查找中，可能在pname,dtype,address三列同时查找,所以创建索引如下：
CREATE INDEX idx_poi_similarity
ON public.t_poi USING
gist (((((pname)::text || (dtype)::text) || (address)::text)) gist_trgm_ops);
由于<->方法类似与近似值计算，所如果被查找列如果比较长的情况下，查找结果可能不尽如人意。
所以结合实际情况对查询进行优化：
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门市 |
关于pg_trgm的详情，请查看pg_trgm。
