Блог

Я хотел бы создать индекс для текстового столбца для следующего варианта использования. У нас есть таблица 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) )  

И вот запрос (Рубин Ханами):

 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) gt; 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.') gt; 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—

А как насчет переиндексации? Первоначально мы импортируем около 2 миллионов устаревших записей. Когда и как часто, если вообще следует, мы должны перестраивать индекс?

—ОКОНЧАТЕЛЬНАЯ ПРАВКА 1—

Будет ли что-то вроде СОЗДАНИЯ ИНДЕКСА НА сегменте С ИСПОЛЬЗОВАНИЕМ gist (контента) в порядке? Я действительно не могу найти, какой из доступных индексов лучше всего подходит для нашего случая использования.

Лучший, себа

2-й запрос, который вы показываете, кажется, не имеет отношения к этому вопросу.

Ваш первый запрос не может использовать индекс триграммы, так как для этого запрос должен быть написан в форме оператора, а не в форме функции.

В форме оператора это выглядело бы так:

 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 lt;-gt; 'This will not work.';  

Для того % , чтобы быть эквивалентным similarity("content", 'This will not work.') gt; 0.45 , вам сначала нужно будет выполнить a set pg_trgm.similarity_threshold TO 0.45; .

Теперь, как вы заставляете ruby/hanami генерировать эту форму, я не знаю.

Оператор % может поддерживаться либо индексом gin_trgm_ops, либо индексом gist_index_ops. lt;-gt; может поддерживаться только gist_trgm_ops. Но довольно трудно предсказать, насколько эффективной будет эта поддержка. Если ваш столбец «содержимое» длинный или ваш текст для сравнения длинный, он вряд ли будет очень эффективным, особенно в случае gist.

В идеале вы бы разделили свою таблицу по идентификатору language_id. Если нет, то было бы полезно построить индекс с несколькими столбцами, содержащий оба столбца.

 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);