database - 动态或列化的 tsvector 索引？

Question

我正在为我正在构建的站点创建自定义论坛软件，其中包括 2 个表(与此问题相关):topics 和 posts。帖子属于主题，主题包含主题，而每个帖子包含正文。

这是基本的表结构，其中包含与我的问题相关的列:

CREATE TABLE topics (
  id bigserial NOT NULL,
  title varchar(128) NOT NULL,
  created timestamp with time zone NOT NULL default NOW(),
  updated timestamp with time zone NOT NULL default NOW(),
  PRIMARY KEY (id)
);

CREATE TABLE posts (
  id bigserial NOT NULL,
  topic_id bigint NOT NULL REFERENCES topics(id) ON DELETE CASCADE,
  body text NOT NULL,
  created timestamp with time zone NOT NULL default NOW(),
  updated timestamp with time zone NOT NULL default NOW(),
  PRIMARY KEY (id)
);

这是我构建全文索引的两个选项。

选项 1:在标题/正文列上创建动态 tsvector 索引。

CREATE INDEX topics_title_idx ON topics USING gin(to_tsvector(title));
CREATE INDEX posts_body_idx ON posts USING gin(to_tsvector(body));

选项 2:创建额外的列来保存 tsvector 化的标题/正文数据，并在这些列上添加索引。

ALTER TABLE topics ADD COLUMN topics_vector tsvector NOT NULL;
CREATE TRIGGER topics_ins BEFORE INSERT OR UPDATE ON topics FOR EACH ROW EXECUTE PROCEDURE tsvector_update_trigger(title_vector, 'pg_catalog.english', title);
CREATE INDEX topics_title_idx ON topics USING gin(title_vector);

ALTER TABLE posts ADD COLUMN posts_vector tsvector NOT NULL;
CREATE TRIGGER posts_ins BEFORE INSERT OR UPDATE ON posts FOR EACH ROW EXECUTE PROCEDURE tsvector_update_trigger(body_vector, 'pg_catalog.english', body);
CREATE INDEX posts_body_idx ON posts USING gin(body_vector);

我在这两者之间争论不休，因为选项 1 可以节省磁盘空间，但搜索速度较慢，而选项 2 需要额外的磁盘空间，同时提供更快的搜索。

假设每天有 20 个新主题和 100 个新帖子。你会选择哪个？如果每天的主题/帖子数量是它的两倍怎么办？五倍？十次？你对一个人和另一个人的决定会改变吗？

Answer 1

使用选项 1 不会使您的搜索变慢。

无论您是在实例化列还是计算表达式上创建，都将使用 GIN 索引。

您只需要更改查询语法:

SELECT  *
FROM    posts
WHERE   TO_TSVECTOR('english', title) @@ myquery

在第一种情况下，或者

SELECT  *
FROM    posts
WHERE   title_vector @@ myquery

第二种情况。

在实例化列上使用 TS_RANK 可能会节省一点时间。