Query by keyword in Postgresql, generally using the like statement. for example:
select name from poi where name like '%key word%'.
But as we all know, this matching rate is not satisfactory.
This article uses examples to use the pg_trgm extension to achieve a more perfect keyword query.
Create a new data table as follows:
-- public.t_poi definition -- Drop table -- DROP TABLE public.t_poi; CREATE TABLE public.t_poi ( id varchar NULL, -- unique identifier pname varchar NULL, --name dtype varchar NULL, --category address varchar NULL--Address );
The data in the default table stores tens of millions of records.
Suppose there is the following data:
SELECT name as pname,dtype,address FROM public.t_poi; pname |dtype |address | ----------------------- + ------------------ + ----- -------------------- + Heilongjiang Radio and Television Network Co., Ltd. (Tonghe Branch Business Hall) | Life services; Life service places; Life service places | 50 meters northeast of the intersection of Jian'an Road and Minqiang Street near Tonghe County Radio and Television Station | Well-known labor consulting company |Life services;Intermediary agencies;Intermediary agencies |Building No. 1 store, Longtai Community, Chang'an Street, Tonghe Town | Star Labor Information Consulting Tonghe Branch |Life Services;Talent Market;Talent Market |4 stores in Baihe Mingyuan Community, Xiangyang Street | Heilongjiang Longkai Real Estate Appraisal Co., Ltd. |Life Services; Office; Appraisal Office | Near No. 1 Longda Road | Harbin Xuyuan Accounting Service Company |Life Services;Firm;Accounting Firm |East side of the north gate of Gardener Apartment | Harbin Tools Sales Company | Shopping Service; Home Building Materials Market; Building Materials Hardware Market | No. 86, Nanma Road | Hartong Logistics Co., Ltd. |Life services;Logistics express;Logistics express |Near No. 6 Central Street | Gangzi Cleaning Service Company |Life Services;Intermediaries;Intermediaries |No. 40, Jin Street | Dongxun Labor Export Company |Life Services;Talent Market;Talent Market |Opposite to Nanzhongtong Express, Daqian Market Road, Longda Road, Tonghe Town | Shenzhou Cable Co., Ltd. | Shopping service; Home building materials market; Building materials and hardware market | No. 140 Jingyang Street |
Use like to query records containing the “sunset” keyword. The statement may be as follows
SELECT * FROM public.t_poi WHERE PNAME LIKE '%sunset red%senior%apartment%'; pname |dtype |address | ------------------ + ------------------ + ---------- ----- + Yanshou Sunset Red Apartment for the Elderly |Sports and leisure services; vacation and recuperation facilities; nursing homes | 50 meters north of the intersection of East Xinhua Street and Aimin South Hutong | Sunset Red Apartment for the Elderly (Northwest of Tonghe County Red Cross Hospital) | Sports and leisure services; vacation and recuperation facilities; nursing homes | Central Street (Northwest of Tonghe County Red Cross Hospital) | Sunset Red Apartment for the Elderly, Daoli District |Sports and leisure services; vacation and health facilities; nursing homes | No. 104-2, Quiet Street | Sunset Red Senior Apartment (Qingyuan East) |Sports and leisure services; vacation and recuperation facilities; nursing homes | 7 stores in Building B, Xinheng Modern City Wenyuan | Sunset Red Apartment for the Elderly |Sports and leisure services; vacation and health facilities; nursing homes | No. 1, Building 11, District A, Orchard Star City | Sunset Red Apartment for the Elderly (Northeast of Xinheng Modern City) |Sports and leisure services; vacation and recuperation facilities; nursing homes | Store 7, Building B, Wenyuan, Xinheng Modern City | Sunset Red Apartment for the Elderly |Sports and leisure services; vacation and recuperation facilities; nursing homes | 100 meters southeast of Fangzheng Town Government, Tuanjie Road |
The method of using pg_trgm is as follows:
Install the extension first:
CREATE EXTENSION pg_trgm;
At this time, the query can be changed as follows:
SELECT name as pname,dtype,address FROM public.t_poi ORDER BY (name <->'Sunset Red Senior Apartment') limit 10; pname |dtype |address | ------------------ + ------------------ + ---------- ------+ Sunset Red Apartment for the Elderly |Sports and leisure services; vacation and health facilities; nursing homes | No. 1, Building 11, District A, Orchard Star City | Sunset Red Apartment for the Elderly |Sports and leisure services; vacation and recuperation facilities; nursing homes |100 meters southeast of Fangzheng Town Government, Tuanjie Road | Sunset Red Apartment for the Elderly (Qingyuan East) |Sports and leisure services; vacation and health facilities; nursing homes | 7 stores in Building B, Xinheng Modern City Wenyuan | Sunset Red Apartment for the Elderly (Northeast of Xinheng Modern City) |Sports and leisure services; vacation and health facilities; nursing homes |Shop 7, Building B, Wenyuan, Xinheng Modern City | Sunset Apartment for the Elderly |Sports and leisure services; holiday resorts; nursing homes | No. 146 Yanchuan Street | Yanshou Sunset Red Apartment for the Elderly |Sports and leisure services; vacation and recuperation facilities; nursing homes |50 meters north of the intersection of East Xinhua Street and Aimin South Hutong| Sunset Red Apartment for the Elderly in Daoli District |Sports and leisure services; vacation and recuperation facilities; nursing homes | No. 104-2, Quiet Street | Xinyanghong Apartment for the Elderly |Sports and leisure services; vacation and health facilities; nursing homes |50 meters northwest of the intersection of Lilac Street and Jin Street | Sunset Red Apartment for the Elderly (Northwest of Tonghe County Red Cross Hospital) | Sports and leisure services; vacation and recuperation facilities; nursing homes | Central Street (Northwest of Tonghe County Red Cross Hospital) | Sunset Red Elderly Service Station |Life services;Life service places;Life service places|No. 61, Family Building, Labor Bureau, Xinxiangli Street, Xinjiang Street|
In actual search, it is possible to search in three columns of pname, dtype, address at the same time, so the index is created as follows:
CREATE INDEX idx_poi_similarity
ON public.t_poi USING
gist (((((pname)::text || (dtype)::text) || (address)::text)) gist_trgm_ops);
Since the <-> method is similar to approximate value calculation, if the column being searched is relatively long, the search results may not be satisfactory.
So optimize the query based on the actual situation:
select pname,dtype,address from ( select distinct * from ( --Complete inclusion is the result we expect, but the similarity is not necessarily very high --For example, 100 characters are stored in the field, and only two characters are searched. --The similarity is lower than records that are the same length as the search results but differ by only one character --So we first look for the included records. (select name as pname,dtype,address,0 as OD from public.t_poi pd where strpos(name||dtype||address,'Sunset Red Senior Apartment') > 0 limit 10) union all (select name as pname,dtype,address,1 as OD from public.t_poi pd order by (name||dtype||address <-> 'Sunset Red Senior Apartment') limit 10) ) a ) x limit 10; pname |dtype |address | ---------------- + ------------------ + ---------------- --- + Sunset Red Elderly Service Station |Life services;Life service places;Life service places|No. 61, Family Building, Labor Bureau, Xinxiangli Street, Xinjiang Street| Sunset Red Apartment for the Elderly |Sports and leisure services; vacation and health facilities; nursing homes | No. 1, Building 11, District A, Orchard Star City | Sunset Red Apartment for the Elderly |Sports and leisure services; vacation and health facilities; nursing homes | No. 1, Building 11, District A, Orchard Star City | Sunset Red Apartment for the Elderly |Sports and leisure services; vacation and recuperation facilities; nursing homes |100 meters southeast of Fangzheng Town Government, Tuanjie Road | Sunset Red Apartment for the Elderly (Northeast of Xinheng Modern City) | Sports and leisure services; vacation and health facilities; nursing homes | Store 7, Building B, Wenyuan, Xinheng Modern City | Sunset Red Teeth Beauty | Healthcare Services; Clinic; Clinic | 50 meters west of Qingmu Highway | Sunset Red Apartment for the Elderly (Qingyuan East) |Sports and leisure services; vacation and health facilities; nursing homes | 7 stores in Building B, Xinheng Modern City Wenyuan | Sunset Red Apartment for the Elderly in Daoli District |Sports and leisure services; vacation and recuperation facilities; nursing homes | No. 104-2, Quiet Street | Sunset Red Apartment for the Elderly in Daoli District |Sports and leisure services; vacation and recuperation facilities; nursing homes | No. 104-2, Quiet Street | Sunset Red Apartment for the Elderly (Northeast of Xinheng Modern City) | Sports and leisure services; vacation and health facilities; nursing homes | Store 7, Building B, Wenyuan, Xinheng Modern City |
For details about pg_trgm, see pg_trgm.