Postgres多列文本索引

Question

如果我有一个包含数百万条记录的表，我在其中执行通配符搜索，我是否应该对所有列进行索引？

示例：

table.account_id = X 
AND (table.col1 LIKE '%abc%' 
     OR table.col2 LIKE %abc% 
     OR LOWER(table.col3) LIKE %abc% 
     OR LOWER(table.col4) LIKE %abc%)

这是在大约1-2秒内返回，这取决于我搜索的内容。但是，我需要它是< .5s，因为它是一个自动补全字段。

我的问题是，我是否应该创建一个索引，例如：

CREATE INDEX ix_xxxx ON table (col1, col2,lower(col3),lower(col4), owner_firm_id)

或者我应该在索引中查看to_tsvector或text_pattern_ops？

我确实尝试了下面这样的方法，但它没有达到< 1s。

CREATE INDEX ix_name ON table
            USING gist (col1 gist_trgm_ops, col2 gist_trgm_ops, LOWER(col3) gist_trgm_ops, col4 gist_trgm_ops, account_id);

Answer 1

您的GiST索引处理col4的方式与查询处理它的方式不同。此外，GiST索引对列顺序很敏感，可以用最少的工作量消除最多行的列应该放在第一位。假设这应该是account_id：

CREATE INDEX ix_name ON table
   USING gist (account_id, col1 gist_trgm_ops, col2 gist_trgm_ops, LOWER(col3) gist_trgm_ops, lower(col4) gist_trgm_ops);

相同的索引只有在GIN而不是GiST形式下才可能有效，你可以同时尝试这两种形式并查看。它不会对列在索引中指定的顺序敏感。

pg_trgm索引操作符同样支持LIKE和ILIKE。您可以只使用ILIKE查询，而不是像在原始列上一样，并在原始列上构建索引，而不是对其中两列执行lower。这可能不会对性能有太大影响，但更简单的索引会更灵活。

但是在你的查询中有必要使用前导'%‘吗？当人们从中间开始输入时，会期望自动完成功能吗？

Answer 2

如果执行子字符串搜索，请使用三元组索引：

CREATE EXTENSION pg_trgm;

CREATE INDEX ON atable USING gin ((col1 || ' ' || col2 ... gin_trgm_ops);

然后像这样查询

WHERE col1 || ' ' || col2 || ... ILIKE '%abcd%'

为了获得良好的性能，在字符串长度达到4左右之前不要开始搜索。