postgresql中like和ilike语句的性能提升

LIKE和ILIKE是SQL的两个基本功能。人们在他们的应用程序中到处使用这些东西，因此从性能的角度来看这个主题是有意义的。PostgreSQL可以采取哪些措施来加快这些操作的速度，通常可以采取哪些措施要首先了解问题，其次才能获得更好的PostgreSQL数据库性能。

创建示例数据

在本文中，你将学到关于Gist和GIN索引的大部分知识。这两种索引类型都可以处理LIKE和ILIKE。这些索引类型的效率不一样，因此深入研究主题并找出最佳的索引类型是有意义的。

在开始之前，我创建了一些示例数据。为了避免在web上搜索示例数据，我决定生成一些数据。一个简单的md5哈希就足以证明我的观点。

?

1 2 3 4 5

test=# CREATE TABLE t_hash AS SELECT id, md5(id::text) FROM generate_series(1, 50000000) AS id; SELECT 50000000 test=# VACUUM ANALYZE; VACUUM

让我们看一下数据。我们这里有5000万个id和它们的哈希表。下面的清单显示了数据的一般情况:

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14

test=# SELECT * FROM t_hash LIMIT 10;  id | md5 ----+----------------------------------   1 | c4ca4238a0b923820dcc509a6f75849b   2 | c81e728d9d4c2f636f067f89cc14862c   3 | eccbc87e4b5ce2fe28308fd9f2a7baf3   4 | a87ff679a2f3e71d9181a67b7542122c   5 | e4da3b7fbbce2345d7772b0674a318d5   6 | 1679091c5a880faf6fb5e6087eb1b2dc    7 | 8f14e45fceea167a5a36dedd4bea2543   8 | c9f0f895fb98ab9159f51fd0297e236d   9 | 45c48cce2e2d7fbdea1afc51c7c6ad26  10 | d3d9446802a44259755d38e6d163e820 (10 rows)

运行简单的like查询

让我们把注意力转向LIKE:下面的查询选择数据中只存在一次的子字符串。请注意，百分号不仅在匹配的末尾，而且也在匹配的开头:

?

1 2 3 4 5 6 7 8

test=# \timing Timing is on. test=# SELECT * FROM t_hash WHERE md5 LIKE '%e2345679a%';        id | md5 ----------+----------------------------------  37211731 | dadb4b54e2345679a8861ab52e4128ea (1 row) Time: 4767.415 ms (00:04.767)

在我的iMac上，查询需要4.7秒才能完成。在90％以上的应用程序中，这已经太长了。用户体验已经受到煎熬，长期运行的查询很有可能已经大大增加了服务器的负载。

为了了解到底发生了什么，我决定看看SQL语句的执行计划：

?

1 2 3 4 5 6 7 8 9

test=# explain SELECT * FROM t_hash WHERE md5 LIKE '%e2345679a%';                                   QUERY PLAN ------------------------------------------------------------------------------  Gather (cost=1000.00..678583.88 rows=5000 width=37)    Workers Planned: 2    -> Parallel Seq Scan on t_hash (cost=0.00..677083.88 rows=2083 width=37)       Filter: (md5 ~~ '%e2345679a%'::text) (4 rows) Time: 11.531 ms

由于表的大小，PostgreSQL查询优化器将进行并行查询。这基本上是一件好事，因为执行时间减少了一半。但是：这也意味着我们很容易牺牲两个CPU内核来回答仅返回一行的查询。

性能不佳的原因是表实际上很大，并且数据库必须从头到尾读取它来处理请求：

?

1 2 3 4 5 6

test=# \dt+ List of relations  Schema |  Name  |  Type | Owner |    Size | Description --------+--------+-------+-------+---------+-------------  public | t_hash | table |    hs | 3256 MB | (1 row)

读取3.2 GB来获取单个数据现在已经非常高效了。

那么我们能做什么来解决这个问题呢?

pg_trgm：高级索引

幸运的是，PostgreSQL提供了一个模块，它在模式匹配方面提供了很多技巧。pg_trgm扩展实现了“trigrams”，这是一种帮助模糊搜索的方法。该扩展是PostgreSQL contrib包的一部分，因此应该出现在绝大多数系统上:

?

1 2 3

test=# CREATE EXTENSION pg_trgm; CREATE EXTENSION Time: 77.216 ms

如你所见，启用扩展很容易。现在的问题是:什么是trigram?让我们一起来看看:

?

1 2 3 4 5

test=# SELECT show_trgm('dadb4b54e2345679a8861ab52e4128ea');                                                               show_trgm ---------------------------------------------------------------------------------------------------------------------------------------------  {" d"," da",128,1ab,234,28e,2e4,345,412,456,4b5,4e2,52e,54e,567,61a,679,79a,861,886,8ea,9a8,a88,ab5,adb,b4b,b52,b54,dad,db4,e23,e41,"ea "} (1 row)

你可以观察到trigram就像一个滑动的3个字符的窗口。所有这些标记都将显示在索引中，稍后你将看到。

为了支持like使用索引，pg_trgm模块支持两种PostgreSQL索引类型:Gist和GIN。两种都将被评估。

Gist

许多人在PostgreSQL中加速模糊搜索的方法是使用Gist索引。下面是如何部署这种类型的索引:

?

1 2 3

test=# CREATE INDEX idx_gist ON t_hash USING gist (md5 gist_trgm_ops); CREATE INDEX Time: 2383678.930 ms (39:43.679)

可以看到，构建索引需要相当长的时间。需要指出的重要一点是，即使设置更高的maintenance_work_mem也不会加快进程。即使有4GB的maintenance_work_mem，这个过程也需要40分钟。

值得注意的是，这个索引非常大:

?

1 2 3 4 5 6

test=# \di+ List of relations  Schema |   Name   |  Type | Owner |  Table |   Size  | Description --------+----------+-------+-------+--------+---------+-------------  public | idx_gist | index |    hs | t_hash | 8782 MB | (1 row)

这个表只有3.5 GB——索引是表的2.5倍大。

但是，索引总是会让事情变得更快，对吗?嗯，实际上不是…

?

1 2 3 4 5 6

test=# SELECT * FROM t_hash WHERE md5 LIKE '%e2345679a%';        id | md5 ----------+----------------------------------  37211731 | dadb4b54e2345679a8861ab52e4128ea (1 row) Time: 105506.064 ms (01:45.506)

我们真的“优化”了查询?不是4.7秒，PostgreSQL几乎需要2分钟来完成这项工作。为什么会这样呢?让我们来看看执行计划是怎么说的:

?

1 2 3 4 5 6 7 8 9

test=# explain SELECT * FROM t_hash WHERE md5 LIKE '%e2345679a%';                                   QUERY PLAN ----------------------------------------------------------------------------  Bitmap Heap Scan on t_hash (cost=495.30..18812.90 rows=5000 width=37)     Recheck Cond: (md5 ~~ '%e2345679a%'::text)     -> Bitmap Index Scan on idx_gist (cost=0.00..494.05 rows=5000 width=0)        Index Cond: (md5 ~~ '%e2345679a%'::text) (4 rows) Time: 13.433 ms

PostgreSQL优化器决定进行“位图索引扫描”。所以直接索引扫描可能更好?

?

1 2 3 4 5 6 7 8 9 10 11 12 13

test=# SET enable_bitmapscan TO off; SET Time: 11.302 ms test=# explain analyze SELECT * FROM t_hash WHERE md5 LIKE '%e2345679a%';                                   QUERY PLAN ----------------------------------------------------------------------------- Index Scan using idx_gist on t_hash (cost=0.55..20428.04 rows=5000 width=37)    (actual time=13750.850..99070.510 rows=1 loops=1)    Index Cond: (md5 ~~ '%e2345679a%'::text)  Planning Time: 0.074 ms  Execution Time: 99070.618 ms (4 rows) Time: 99072.657 ms (01:39.073)

实际上，查询仍然会显示糟糕的执行时间。

简而言之:在这里使用Gist索引可能并不合适。它需要很长的时间来创建，很大，比顺序扫描要慢得多。

Gin

幸运的是，pg_trgm扩展提供了第二个操作符类来完成这项工作。GIN索引通常用于PostgreSQL全文搜索(FTS)。让我们测试看看是否也能加速like和ilike查询？在此之前，我们重置当前连接并删除旧索引:

?

1 2 3 4 5 6

test=# DISCARD ALL; DISCARD ALL Time: 12.000 ms test=# DROP INDEX idx_gist; DROP INDEX Time: 3123.336 ms (00:03.123)

创建一个新索引:

?

1 2 3

test=# CREATE INDEX idx_gin ON t_hash USING gin (md5 gin_trgm_ops); CREATE INDEX Time: 698063.038 ms (11:38.063)

在我的机器上，它需要11分钟，这是很多，但实际上比创建Gist索引要快得多。但是，索引创建只发生一次，所以在这种情况下我们不应该太担心。通常更重要的是查询执行时间:

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

test=# explain analyze SELECT * FROM t_hash WHERE md5 LIKE '%e2345679a%';                                        QUERY PLAN ------------------------------------------------------------------------------ Bitmap Heap Scan on t_hash (cost=2270.75..20588.36 rows=5000 width=37)     (actual time=74.592..74.593 rows=1 loops=1)    Recheck Cond: (md5 ~~ '%e2345679a%'::text)    Heap Blocks: exact=1    -> Bitmap Index Scan on idx_gin (cost=0.00..2269.50 rows=5000 width=0)        (actual time=74.584..74.584 rows=1 loops=1)       Index Cond: (md5 ~~ '%e2345679a%'::text) Planning Time: 0.066 ms Execution Time: 74.665 ms (7 rows)    Time: 75.031 ms

可以在75毫秒内运行查询，而不是4.7秒。这是一个重大的飞跃。一个索引的帮助——数据库性能的巨大飞跃。

如预期的那样，查询正好返回一行:

?

1 2 3 4 5 6 7

test=# SELECT * FROM t_hash WHERE md5 LIKE '%e2345679a%';        id | md5 ----------+----------------------------------  37211731 | dadb4b54e2345679a8861ab52e4128ea (1 row)    Time: 74.487 ms

到目前为止，你所看到的是GIN索引解决了这个问题。但是，这里可能仍然需要第二个索引。GIN不会加速“=”操作符。因此，如果你正在执行一个普通的查找，你将需要第二个索引，如下面的例子所示:

?

1 2 3 4 5 6 7 8 9 10

test=# CREATE INDEX idx_btree ON t_hash (md5); CREATE INDEX Time: 274778.776 ms (04:34.779) test=# \di+ List of relations  Schema |    Name   |  Type | Owner |  Table |    Size | Description --------+-----------+-------+-------+--------+---------+-------------  public | idx_btree | index |    hs | t_hash | 2816 MB |  public |  idx_gist | index |    hs | t_hash | 2807 MB | (2 rows)

需要一个btree来加速普通的比较。仅仅一个GIN索引是不够的:

?

1 2 3 4 5 6 7

test=# SELECT * FROM t_hash WHERE md5 = 'dadb4b54e2345679a8861ab52e4128ea';        id | md5 ----------+----------------------------------  37211731 | dadb4b54e2345679a8861ab52e4128ea (1 row)    Time: 0.379 ms

最后

PostgreSQL提供了真正强大的索引策略。除了btree索引之外，还有很多东西需要发现。Gist和Gin也有它们的优点。GIN对于所有类型的全文操作特别有用，而Gist对于几何数据(GIS)非常理想。

如果你想了解更多关于Gin索引的信息，请查看我发布的关于 Gin索引的文章列表和vacuum的帖子(https://www.cybertec-postgresql.com/en/what-postgresql-full-text-search-has-to-do-with-vacuum/)。

 

 

 

 

 

 

 

https://www.cybertec-postgresql.com/en/postgresql-more-performance-for-like-and-ilike-statements/