对于基于相似性的查询的文本列，哪个Postgresql索引最有效

Question

我想为下面的用例在文本列上创建一个索引。我们有一个Segment的表，其中列content为text类型。我们使用pg_trgm根据相似性进行查询。这在翻译编辑器中用于查找类似的字符串。以下是表格详细信息：

CREATE TABLE public.segments
(
id integer NOT NULL DEFAULT nextval('segments_id_seq'::regclass),
language_id integer NOT NULL,
content text NOT NULL,
created_at timestamp without time zone NOT NULL,
updated_at timestamp without time zone NOT NULL,
CONSTRAINT segments_pkey PRIMARY KEY (id),
CONSTRAINT segments_language_id_fkey FOREIGN KEY (language_id)
REFERENCES public.languages (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE CASCADE,
CONSTRAINT segments_content_language_id_key UNIQUE (content, language_id)
)

下面是查询(Ruby+Hanami(：

def find_by_segment_match(source_text_for_lookup, source_lang, sim_score)
aggregate(:translation_records)
.where(language_id: source_lang)
.where { similarity(:content, source_text_for_lookup) > sim_score/100.00 }
.select_append { float::similarity(:content, source_text_for_lookup).as(:similarity) }
.order { similarity(:content, source_text_for_lookup).desc }
end

---编辑---

这是一个查询：

SELECT "id", "language_id", "content", "created_at", "updated_at", SIMILARITY("content", 'This will not work.') AS "similarity" FROM "segments" WHERE (("language_id" = 2) AND (similarity("content", 'This will not work.') > 0.45)) ORDER BY SIMILARITY("content", 'This will not work.') DESC
SELECT "translation_records"."id", "translation_records"."source_segment_id", "translation_records"."target_segment_id", "translation_records"."domain_id",
"translation_records"."style_id",
"translation_records"."created_by", "translation_records"."updated_by", "translation_records"."project_name", "translation_records"."created_at", "translation_records"."updated_at", "translation_records"."language_combination", "translation_records"."uid",
"translation_records"."import_comment" FROM "translation_records" INNER JOIN "segments" ON ("segments"."id" = "translation_records"."source_segment_id") WHERE ("translation_records"."source_segment_id" IN (27548)) ORDER BY "translation_records"."id"

---结束编辑--

---编辑1-

重新索引怎么样？最初，我们将导入大约200万个遗留记录。我们应该何时以及多久重建一次索引(如果有的话(？

---结束编辑1-

使用要点(内容(在片段上创建索引这样的东西可以吗？我真的找不到哪一个可用的索引最适合我们的用例。

最佳，seba

Answer 1

您显示的第二个查询似乎与这个问题无关。

您的第一个查询不能使用三元索引，因为要做到这一点，查询必须以运算符形式而不是函数形式编写。

在操作员形式中，它看起来像这样：

SELECT "id", "language_id", "content", "created_at", "updated_at", SIMILARITY("content", 'This will not work.') AS "similarity" 
FROM segments 
WHERE language_id = 2 AND content % 'This will not work.'
ORDER BY content <-> 'This will not work.';

为了使%等效于similarity("content", 'This will not work.') > 0.45，您首先需要执行set pg_trgm.similarity_threshold TO 0.45;。

现在，我不知道你是如何让ruby/hanami生成这个表单的。

gin_trgm_ops索引或gist_index_ops索引都可以支持%运算符。&lt-&gt；只能由gist_rgm_ops支持。但很难预测这种支持的效率有多高；内容"；专栏很长，或者要比较的文本很长，这不太可能非常有效，尤其是在要点的情况下。

理想情况下，您应该按照language_id对表进行分区。如果不是，那么构建一个同时包含两列的多列索引可能会有帮助。

Answer 2

CREATE INDEX segment_language_id_idx ON segment USING btree (language_id);
CREATE INDEX segment_content_gin ON segment USING gin (content gin_trgm_ops);