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

  • Mastering PostgreSQL 9.6
  • Hans Jurgen Schonig
  • 195字
  • 2021-07-09 19:57:22

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.

主站蜘蛛池模板: 林周县| 临洮县| 民乐县| 徐水县| 康马县| 昆山市| 荔波县| 安阳县| 河西区| 锡林浩特市| 文水县| 延川县| 旌德县| 盐亭县| 阳朔县| 贺兰县| 宿迁市| 东阿县| 秭归县| 张掖市| 两当县| 巍山| 内江市| 平塘县| 绵竹市| 临武县| 巴南区| 靖远县| 澎湖县| 大庆市| 苍南县| 大城县| 巴楚县| 都昌县| 家居| 宜州市| 卢龙县| 文登市| 襄垣县| 安丘市| 南京市|