performance - 应该将 search_data tsvector 存储在同一张表还是外部表中？

Question

我正在 postgres 中实现全文搜索。

我想搜索我系统中的所有帖子。帖子全文索引是帖子标题和帖子正文的合并。

我有两种实现方式:

1. 在 posts 表中创建一个 tsvector 列，触发对其的更新。
2. 使用包含索引数据的 post_id 和 tsvector 列创建第二个表 (posts_search)。
3. 创建一个简单的 gin 索引 ...(不可能，因为我的现实世界问题需要多个表中的数据作为索引)

考虑到我有时需要通过表中的其他属性过滤搜索(例如 deleted_at 为 null 等等)，什么会表现得更好。

将 tsvector 列与数据保持在同一个表中(副作用 select * 现在很糟糕)还是单独的表(副作用，需要连接，索引过滤很复杂)是更好的方法吗？

Answer 1

在我的实验中，tsvector 列的典型大小约为文本字段大小的 1%，此 tsvector 是使用 to_tsvector() 计算得出的。

考虑到这一点，将 tsvector 列存储在另一个表中应该会提供性能优势。例如，即使您不使用 SELECT *(实际上您不应该使用)，原始单表中的任何 seqscan 仍将不得不加载包含原始文本的页面。如果将 tsvector 字段卸载到单独的表，页面加载速度将提高 100 倍。

换句话说，我赞成将 tsvector 字段卸载到单独表的第二种解决方案。或者，或者，将帖子(原始文本)卸载到更深的表层次结构中(但我想这几乎是一回事)。

请注意，要进行全文搜索，原文不是必需的。您甚至不想将其存储在数据库中，或者以高度压缩的格式存储(并且不一定很容易被 SQL 例程访问)。只要某些东西可以基于原始文本创建 tsvector，或者在它发生变化时进行更新，它就可以工作。