跨多个表搜索(最佳实践)

为什么不创建一个视图,它是表的并集,将要搜索的列聚合到一个表中,然后搜索该聚合列?

你可以这样做:

select 'tenants:' + ltrim(str(t.Id)), <shared fields> from Tenants as t union
select 'landlords:' + ltrim(str(l.Id)), <shared fields> from Tenants as l union
...

领域

也就是说,如果您只需要一个单独的列,其中包含一个“类型”值(例如,房东、租户),然后对类型和ID进行过滤,可能会更好,因为这样计算成本更低(并且可以更好地优化)。

你想用 the built-in full text search 或者像这样的单独产品 Lucene . 这针对异构数据上的非结构化搜索进行了优化。

另外,不要忘记正常指数不能用于 something LIKE '%...%' . 使用全文搜索引擎也可以进行高效的子字符串搜索。

我建议使用专门的全文索引工具,比如Lucene。它可能更容易启动和运行,而且结果也更快、更具特色。如果您还需要结构化搜索功能,Postgres全文索引将非常有用,或者搜索索引的事务性非常重要。

如果您确实希望在数据库中实现此功能,假设您使用代理密钥,则以下方案可能会起作用:

1. 为每个可搜索表创建一个视图,该视图包含该表的主键列、表名以及该表中所有可搜索字段的串联。
2. 在完全相同连接的to_tsvector()上的基础上创建函数GIN或GiST索引。
3. 在所有视图上创建联合以创建可搜索视图。

之后,您可以执行如下搜索:

SELECT id, table_name, ts_rank_cd(body, query) AS rank
    FROM search_view, to_tsquery('search&words') query
    WHERE query @@ body
    ORDER BY rank DESC
    LIMIT 10;

你应该很好,没有其他好的(简单的)方法可以做到这一点。不过,请确保您正在搜索的字段已正确索引。