我正在尝试优化这些缓慢的查询(请原谅SQL与Ruby on Rails混合):
WHERE name ILIKE %<the user's search text>%WHERE lower(NAME) LIKE :search OR lower(BARCODE) LIKE :search OR lower(SKU) like :search, search: "%<the user's search text>%"
可以看到，这些是通配符查询，开头和结尾都有%，这意味着正常的索引是无用的。表由项目组成，当用户没有很多项目时，查询很好，但是当用户有很多项目(成千上万)时，这需要很长时间(比如几十秒)。我怎样才能提高性能?搜索文本是条形码或产品名称的一部分，所以它与我只是试图搜索文本不同(在这种情况下，我会使用全文搜索，这样搜索"狗"会产生包含"狗"或"狗"等的结果)。在其中一个用例中，我还在同一表上的多个列中进行搜索。
我考虑了一些最初的方法，但不确定这些方法是否有效:
- 全文搜索(添加一个列，该列是要搜索的多个列中的
to_tsvector，然后为新列添加gin索引) - 三元索引(更合适吗?)
- 其他我没有想到的建议
我正在使用PostgreSQL 13和Ruby on Rails。
就像我已经暗示的那样:在目标列上创建一个三元组索引。对于第一个例子:
CREATE INDEX tbl_name_gin_trgm_idx ON tbl USING gin (name gin_trgm_ops);
三元组索引也支持不区分大小写的匹配，但是为了配合普通三元组索引，用ILIKE重写第二个例子:
WHERE name ILIKE :search OR barcode ILIKE :search OR sku ILIKE :search
你可以像上面的例子一样用三个索引来支持这个。这是最通用的。例如，当不同的列可能在同一个搜索中组合时。
或者一个索引有三个索引列。更少的用途，但更快和更小。
或带有一个连接表达式的一个三元组索引。最不通用，但最快。使用定义和解释的自定义函数immutable_concat_ws():
- 创建concat_ws的不可变克隆
CREATE INDEX tbl_special_gin_trgm_idx ON tbl USING gin (immutable_concat_ws('|', name, barcode, sku) gin_trgm_ops);
与WHERE子句结合使用:
WHERE immutable_concat_ws('|', name, barcode, sku) ILIKE :search
AND (name ILIKE :search OR barcode ILIKE :search OR sku ILIKE :search)
WHERE子句的第一行拉入快速索引。
如果:search在分隔符上匹配，第二行排除可能的(罕见的)误报。
如果:searchnever如果包含选定的分隔符|或嵌套的通配符，则可以删除第二行。
:
- PostgreSQL LIKE查询性能变化
- LOWER LIKE vs iLIKE
- 使用操作符"||"或format()函数
- 使用连接和LIKE的查询性能