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

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.

主站蜘蛛池模板: 金门县| 安岳县| 山东省| 临沭县| 巴彦县| 马山县| 新田县| 清徐县| 邯郸市| 句容市| 雅安市| 塘沽区| 肃南| 汶上县| 河南省| 普兰县| 长武县| 安陆市| 甘谷县| 漳州市| 武鸣县| 中宁县| 江西省| 新龙县| 全南县| 大理市| 图们市| 凤阳县| 榆社县| 新巴尔虎右旗| 安国市| 镇安县| 华安县| 锦屏县| 娄烦县| 安泽县| 集贤县| 习水县| 封开县| 彭山县| 休宁县|