PostgreSQL文本数组子字符串搜索

Question

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

让我们从表开​​始：

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

CREATE INDEX path_idx ON dir(path TEXT_pattern_ops);

插入样本数据后，我可以执行以下操作：

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

以上查询正确使用索引。

第二种方式：

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

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

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

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

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

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

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

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

Answer 1

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

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

CREATE EXTENSION pg_trgm;

然后，您可以创建一个GIN索引：

CREATE INDEX ON dir USING gin (path gin_trgm_ops);

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

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

您还可以使用GiST索引，该索引可能较小，但搜索速度较慢。

请注意，您也可以将该索引用于以开头的模式%。