使用trigram优化“分类”搜索表

Question

我一直在阅读这个极好的答案，以了解pg_trgm是如何工作的，但我仍然不清楚解决这个查询的最有效方法(在搜索速度方面是高效的)：

我有一个表search，在上面运行trigram搜索，如下所示：

Column      |  Type   | Modifiers
------------+---------+-----------
id          | bpchar  | collate C
user_id     | integer |
type        | text    |
search_on   | text    | collate C
data        | json    |
Indexes:
 "index_search_id" UNIQUE, btree (id)
 "index_search_search_on" gist (search_on gist_trgm_ops)
 "index_search_type" btree (type)
 "index_search_user_id" btree (user_id)

在这个场景中，user_id是NULLable，type也是NULLable。我将运行的查询相当于以下几种可能性：

1. 搜索行(WHERE user_id = 123 OR user_id IS NULL) AND search_on % 'mystring'
2. 搜索行(WHERE user_id = 123 OR user_id IS NULL) AND type='my-type' AND search_on % 'mystring'

简单地说，我希望拥有我的user_id或NULL user_id的所有行都可以按type进行分类，并与传入的术语匹配。

现在，我只对可以根据查询进行更改的3列(如上面所示)有单独的索引。不过，我知道单一指数通常更有效率。

是否可以使用一个索引来执行trigram搜索，但也可以在user_id和type上使用它们可以选择为NULL的精确匹配。

Answer 1

是否可以使用一个索引来执行trigram搜索，但也可以在user_id和type上使用它们可以选择为NULL的精确匹配。

是的，NULL包含在索引中。你可以搜索它，就像搜索任何其他值一样。

是的，您可以有一个多颜色的trigram GiST索引。但是GiST索引通常对数据类型integer没有意义。Btree索引在各个方面都更好--除了您的多色索引的情况。因此Postgres在默认情况下不会安装所需的运算符类。您需要首先安装附加模块btree_gist，每个数据库一次：

CREATE EXTENSION IF NOT EXISTS btree_gist;  -- only if not installed, yet

然后，您可以创建您的多个索引：

CREATE INDEX foo ON search USING gist (user_id, type, search_on gist_trgm_ops);

相关(附有详细说明)：

• PostgreSQL排除使用错误:数据类型整数没有默认操作符类
• 优化对时间戳范围的查询(两列)

并使运算符优先在您的WHERE子句中正确：

WHERE (user_id = 123 OR user_id IS NULL)  -- parentheses!
AND    search_on % 'mystring'

或者：

WHERE (user_id = 123 OR user_id IS NULL)
AND   (type = 'my-type' OR type IS NULL)
AND    search_on % 'mystring'

根据数据分布、基数、谓词的选择性、成本设置等，Postgres仍然倾向于在一个(或两个)列(S)(偶尔)上设置索引。

Answer 2

很抱歉这次耽搁了。我不确定协议是否要求我回答我自己的问题来发布这些细节，但是评论(对于Erwin的回答)并没有提供足够的空间。

因此，当我运行查询时，我注意到使用上述单个索引的性能非常差。我有两个主要用途：

1. 查询“公共领域”中的所有内容或我自己的内容：(user_id = 123 OR user_id IS NULL) AND search_on % 'my_string'
2. 在“公共领域”或我自己的(user_id = 123 OR user_id IS NULL) AND type='my-type' AND search_on % 'mystring'中按类型查询所有事物

我忘了在所有这些中都提到了type IS NOT NULL，所以我从来不想获取空类型的行，只获取空的user_id行。

使用原始帖子中的单独索引，我可以看到从1s到10+s的查询。似乎“热身”和运行几个查询会缩短时间，但即使1秒也不足以进行预先输入，而不会对UX产生负面影响。

考虑到欧文的建议，我首先补充道：

CREATE INDEX idx_typed_search ON search USING gist (user_id, type, search_on gist_trgm_ops);

并同时运行类型化和非类型化查询。类型化查询速度非常快(~ 50 ms)，非类型查询仍然相当慢，但绝对比单独索引的原始发布(~ 400 ms)更快。

然后我又说：

CREATE INDEX idx_search ON search USING gist (user_id, search_on gist_trgm_ops);

并同时运行类型化和非类型化查询。正如预期的那样，类型化查询不受影响，它们使用了其他索引，但非类型查询实际上变得更糟(~ 1000+ ms)。把这个指数降低到(~ 400 to )

因此，我不确定是否存在两个可能相互竞争的索引，从而导致性能问题。由于我对类型化索引非常满意(大约50 is是我所期望的速度)，所以我决定只关注非类型化查询。

在没有索引的情况下，使用序列扫描的基线查询大约运行8s (大约700,000行)。

使用user_id上的单个btree索引和search_on上的gist_trgm，性能在400 is范围内。有趣的是，它从不使用user_id索引，因为当索引扫描搜索词时，可能只有几行可以从其他user_ids中筛选出来(考虑到我当前的数据)。尽管有更多的用户数据，这可能会随着时间的推移而改变，所以我认为user_id索引仍然是有意义的，即使在这种情况下它是未使用的。

使用组合gist (user_id，search_on)索引，我的性能与两个单独的索引(~ 400 to )大致相似。

我要指出的是，数据的形状是这样的:每个user_id拥有的记录将有100's，而‘user_id为空’的‘10,000’成都(大多数)记录为‘公共领域’。

考虑到数据的形状，似乎最好只是对搜索项进行优化并键入它们自己，因为查询结束时的user_id过滤很便宜。

由于一个特定的非类型索引实际上不会产生任何性能效益，所以最好的选择是只在type和search_on列上使用一个gist索引：

CREATE INDEX idx_typed_global_search ON searchables USING gist (type, search_on gist_trgm_ops);

我目前没有足够的用户来测试user_id过滤，但我的猜测是，随着用户基础的增长，user_id上的索引将产生结果。