PostgreSQL LIKE查询性能变化

前端之家收集整理的这篇文章主要介绍了PostgreSQL LIKE查询性能变化前端之家小编觉得挺不错的,现在分享给大家,也给大家做个参考。
我已经看到相当大的变化响应时间LIKE查询到我的数据库中的特定表。有时我会得到200-400毫秒(非常可接受)的结果,但其他时候,可能需要30秒的时间才能返回结果。

我知道LIKE查询是非常资源密集,但我只是不明白为什么会有这么大的响应时间的差异。我已经在owner1字段上构建了一个btree索引,但我不认为它有助于LIKE查询。任何人都有什么想法?

示例sql

SELECT gid,owner1 FORM parcels
WHERE owner1 ILIKE '%someones name%' LIMIT 10

我也试过:

SELECT gid,owner1 FROM parcels
WHERE lower(owner1) LIKE lower('%someones name%') LIMIT 10

和:

SELECT gid,owner1 FROM parcels
WHERE lower(owner1) LIKE lower('someones name%') LIMIT 10

结果类似。
表行数:约95,000。

FTS不支持LIKE

previously accepted answer不正确。 Full Text Search及其全文索引不是为LIKE操作符,它有自己的操作符,不适用于任意字符串。它运行于基于词典和词干的词。它支持字词的前缀匹配,但不支持LIKE运算符:

> Get partial match from GIN indexed TSVECTOR column

LIKE的Trigram索引

安装附加模块pg_trgm,它为GIN and GiST trigram indexes提供运算符类,以支持所有LIKE和ILIKE模式,而不只是左侧锚定模式:

示例索引:

CREATE INDEX tbl_col_gin_trgm_idx  ON tbl USING gin  (col gin_trgm_ops);

要么:

CREATE INDEX tbl_col_gist_trgm_idx ON tbl USING gist (col gist_trgm_ops);

> Difference between GiST and GIN index

示例查询

SELECT * FROM tbl WHERE col LIKE '%foo%';   -- leading wildcard
SELECT * FROM tbl WHERE col ILIKE '%foo%';  -- works case insensitive as well

text_pattern_ops用于前缀匹配

对于刚刚左边锚定的模式(没有前导通配符),你得到一个合适的operator class为btree索引:text_pattern_ops或varchar_pattern_ops的最佳。两个内置功能的标准Postgres,无需额外的模块。类似性能,但指数小得多。

示例索引:

CREATE INDEX tbl_col_text_pattern_ops_idx ON tbl(col text_pattern_ops);

示例查询

SELECT * FROM tbl WHERE col LIKE 'foo%';  -- no leading wildcard

或者,如果您应该使用’C’语言环境(实际上没有语言环境)运行您的数据库,那么一切都按照字节顺序排序,并且使用默认操作符类执行作业的纯B树索引。

更多详细信息,解释,示例和链接在这些相关的答案在dba.SE:

> Pattern matching with LIKE,SIMILAR TO or regular expressions in PostgreSQL
> How is LIKE implemented?
> Finding similar strings with PostgreSQL quickly

原文链接:https://www.f2er.com/postgresql/193741.html

猜你在找的Postgre SQL相关文章