BTREE、GIN和GIST索引的比较

Question

我正在使用Postgres数据库，我有一个名为MyObjects的表格，其中有几个varchar列。这些值不会被分开成单词（例如，网址、姓名等）。我将根据这些列进行大量过滤。搜索那些某个单词在值的开头、中间或结尾对应的行。 这个表本身不会很大（开始时行数约为n*10^3，但会增长到约n*10^5行）。 在这种情况下，是否需要索引？如果需要，你建议使用哪种类型的索引？ 谢谢！

- Mr T.

Answer 1

如果您正在寻找一个可以帮助搜索模式为WHERE col LIKE '%string%'的索引，唯一的选择是使用pg_tgrm的GIN或GiST索引。

这些索引可能会变得非常大，如果搜索短子字符串，则它们并没有太多帮助。

要确定这样的索引是否比顺序表扫描在您的情况下带来了好处，您必须尝试它-不能一般回答。

- Laurenz Albe

Answer 2

从PostgreSQL文档中得知：

11.2. 索引类型

• 默认情况下，CREATE INDEX命令创建B树索引
• 其他索引类型是通过编写关键字USING和索引类型名称来选择的
• B树可以处理可排序数据的等式和范围查询（< <= = >= > BETWEEN, IN）
• 如果模式是常量并且锚定在字符串开头，则优化器还可以使用B树索引进行涉及模式匹配运算符LIKE和〜的查询 - 例如，col LIKE 'foo％'或col〜'^ foo'，但不包括 col LIKE'%bar'

因此，由于您想匹配到中间和结尾，所以BTree不适用。现在看看这个页面：

PostgreSQL：更高效的LIKE和ILIKE语句

看起来Gin是正确的选择。您应该按@Laurenz Albe提到的激活pg_trgm：

CREATE EXTENSION pg_trgm;

查询应该类似于这样：

CREATE INDEX idx_gin ON MyObjects USING gin (column_name gin_trgm_ops);

希望这可以帮到你。请仔细阅读cybertec的文章以获取详细信息。

回答你的另一个问题（是否需要索引），我的观察是，如果你的数据量增长到10^5，那么很可能需要一个索引。并不是因为10^5太大了，而是因为你可能需要在该数据集上进行许多搜索。我曾经有一个包含180000行的表格（相当小，对吧？），但是，对于一个进程，我需要在其中搜索20000条记录。使用简单的BTree，我将查询执行时间从271秒降低到343毫秒。这是在SQLite上完成的。所以，是的，我会使用索引。

- Mostafa Zeinali