- 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.
- Mastering Matplotlib 2.x
- 精通MATLAB圖像處理
- 輕松學C#
- Getting Started with MariaDB
- Visual C# 2008開發技術實例詳解
- JMAG電機電磁仿真分析與實例解析
- Hadoop Real-World Solutions Cookbook(Second Edition)
- Kubernetes for Serverless Applications
- Hadoop應用開發基礎
- 嵌入式操作系統原理及應用
- 精通ROS機器人編程(原書第2版)
- 單片機C51應用技術
- 大數據時代的調查師
- Getting Started with Tableau 2019.2
- Flink內核原理與實現