官术网_书友最值得收藏!

Speed up LIKE queries

LIKE queries definitely cause some of the worst performance problems faced by people around the globe these days. In most database systems, LIKE is pretty slow and requires a sequential scan. In addition to that, end users quickly figure out that a fuzzy search will in many cases return better results than precise queries. A single type of LIKE query on a large table can, therefore, often cripple the performance of an entire database server if it is called often enough.

Fortunately, PostgreSQL offers a solution to the problem and the solution happens to be installed already:

test=# explain SELECT * FROM t_location WHERE name LIKE '%neusi%'; 
QUERY PLAN
-----------------------------------------------------------------
Bitmap Heap Scan on t_location
(cost=4.33..19.05 rows=24 width=13)
Recheck Cond: (name ~~ '%neusi%'::text)
-> Bitmap Index Scan on idx_trgm (cost=0.00..4.32 rows=24 width=0)
Index Cond: (name ~~ '%neusi%'::text)
(4 rows)

The trigram index deployed in the previous section is also suitable to speed up LIKE. Note that the % symbols can be used at any point in the search string. This is a major advantage over standard B-trees, which just happen to speed up wild cards at the end of the query.

主站蜘蛛池模板: 巨野县| 旺苍县| 徐汇区| 北安市| 乌兰察布市| 巩义市| 阳城县| 武鸣县| 广昌县| 云南省| 于田县| 新乡市| 九龙县| 兴海县| 昌吉市| 卓资县| 启东市| 湘潭市| 敦煌市| 绥江县| 江安县| 大邑县| 彭阳县| 石泉县| 玛曲县| 安多县| 乡城县| 乐清市| 福鼎市| 如东县| 阿合奇县| 西乌珠穆沁旗| 新安县| 靖江市| 体育| 札达县| 本溪市| 富裕县| 台安县| 龙里县| 应城市|