如何使用许多 LIKE 运算符并使用索引

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小提琴，适合那些想要使用这些查询的人：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 索引，该索引可能会更小，但搜索速度较慢。

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