如何使用许多LIKE运算符并使用索引 [英] How to use many LIKE operators and use index

问题描述

在我的查询中，我想找到与许多LIKE运算符之一匹配的行。我知道做到这一点的3种方法，但只有其中一种可以使用索引。

In my query I want find rows that match one of many LIKE operators. I know 3 ways of doing that but only one of them can use index.

让我们从表开​​始：

CREATE TABLE dir (
    id BIGSERIAL PRIMARY KEY,
    path TEXT NOT NULL
);

CREATE INDEX path_idx ON dir(path TEXT_pattern_ops);

插入示例数据后，我可以执行以下操作：

After inserting sample data I can do:

EXPLAIN ANALYZE 
SELECT id, path FROM dir
   WHERE path LIKE 'A%'
      OR path LIKE 'B%'
      OR path LIKE 'C%';

以上查询正确使用了索引。

Above query use index correctly.

第二种方式：

EXPLAIN ANALYZE 
SELECT id, path FROM dir
  WHERE path LIKE ANY(ARRAY['A%', 'B%', 'C%']::TEXT[]);

此查询将不使用索引。
我知道的最后一种方法：

This query will not use index. Last approach I know:

CREATE TABLE patterns (pattern) AS VALUES
('A%'),
('B%'),
('C%');

EXPLAIN ANALYZE 
SELECT id, path FROM dir
  JOIN patterns ON (dir.path LIKE patterns.pattern);

与上一个查询类似，该查询将不使用索引。

This query like the previous one will not use index.

这里是SQL Fiddle，专门针对那些想如何使用这些查询的人： http://sqlfiddle.com/#!17/24031/2

Here is SQL Fiddle for those how want to play with those queries: http://sqlfiddle.com/#!17/24031/2

问题：对于许多模式，使用 path LIKE X或path LIKE Y 进行查询是完全不可读的（模式数可能从几到几百甚至几千不等），而且恐怕大型查询可能解析速度慢，甚至无法达到查询长度的1GB限制（某些模式的前缀可能很长）。

Problem: Query with path LIKE X OR path LIKE Y is completely unreadable for many patterns (number of patterns may vary from few to hundreds or small thousands) and I am afraid that large query may be slow to parse or even to hit 1GB limit of query length (some patterns may have very long prefixes).

问题： oder方法是否返回相同的结果，而无需将所有模式直接放入查询中（例如在带有join的此选项中）？

Question: is there any oder method returning same results that would not require putting all patterns directly in query (like in this option with join)?

推荐答案

您可以创建一个三字母索引以支持您的查询。

You can create a trigram index that will support your query.

为此，您需要 pg_trgm 扩展名；以超级用户身份运行以下命令：

For that you need the pg_trgm extension; run the following as superuser:

CREATE EXTENSION pg_trgm;

然后您可以创建GIN索引：

Then you can create a GIN index:

CREATE INDEX ON dir USING gin (path gin_trgm_ops);

此索引可以与第二种方法和第三种方法一起使用，因此它可以为您解决问题。

This index can be used with your second and third approach, so it should do the trick for you.

使用示例中的简短模式，索引将不会非常有效。

With short patterns like the one in your examples, the index will not be very effective.

您可以

请注意，您也可以将该索引用于以％。

Note that you can use that index also with patterns that start with %.

这篇关于如何使用许多LIKE运算符并使用索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！