在PostgreSQL中，是否有一种索引方式可以实现快速的子字符串搜索？

Question

我有一个数据库，想要在表格中查找类似于以下搜索的内容： select * from table where column like "abc%def%ghi" 或者 select * from table where column like "%def%ghi" 有没有一种方法可以索引该列以使查询速度更快？

编辑： 我能否澄清一下，该数据库是只读的，并且不会经常更新。

- dan

Answer 1

文本搜索和索引的选项包括：

• 基于词典的全文索引，支持前缀搜索，例如to_tsvector(mycol) @@ to_tsquery('search:*')

• text_pattern_ops索引，支持前缀字符串匹配，例如LIKE 'abc%'，但不支持中缀搜索，如%blah%；反向索引可用于后缀搜索。

• 在较新版本中使用的pg_tgrm三元组索引，正如最近的dba.stackexchange.com帖子所示。

• 像Apache Solr这样的外部搜索和索引工具。

从上面给出的最少信息来看，我认为只有三元组索引能够帮助你，因为你正在对一个字符串进行中缀搜索，而不是寻找字典中的单词。不幸的是，三元组索引非常庞大而且相当低效；不要期望某种神奇的性能提升，并且要记住它们需要数据库引擎进行大量工作来构建和更新。

- Craig Ringer

---

“反转（reverse()d）索引”是指建立在表达式上的索引，例如在这种情况下使用“reverse(col)”。我之前并没有意识到这是可能的。此机制的另一个用例是通过对“lower(col)”进行索引来实现不区分大小写的搜索。 - febeling

Answer 2

如果您只需要在整个表中获取唯一的子字符串，可以创建一个子字符串索引：

CREATE INDEX  i_test_sbstr ON tablename (substring(columname, 5, 3)); 
-- start at position 5, go for 3 characters

It is important that the substring() parameters in the index definition are
the same as you use in your query.

参考：http://www.postgresql.org/message-id/[e-mail]

这篇文章涉及到IT技术。

- rogerdpack

Answer 3

对于like操作符，请使用其中一个操作符类varchar_pattern_ops或text_pattern_ops

create index test_index on test_table (col varchar_pattern_ops);

只有当模式不以%开头时，才能使用该方法，否则需要采用其他策略。

- Clodoaldo Neto

---

如果需要全文搜索，pg_trgm 可能是一个不错的选择。我曾经在过去使用过它，并且在某些条件下表现很好。需要注意的是索引会变得相当大。如果我没记错的话，索引列大小的约为2.5倍。 - bma