Альтернативный текстовый поиск по одному символу

Требование: убедитесь, что текстовый поиск одного символа ci в составных столбцах обрабатывается наиболее эффективным и производительным способом, включая сортировку веса релевантности;
Наличие таблицы create table test_search (id int primary key, full_name varchar(300) not null, short_name varchar(30) not null); с 3 млн строк API-вызов подсказки отправляет запросы в базу данных, начиная с первого введенного символа и первых 20 символов. должны быть возвращены результаты, упорядоченные по релевантности.

Опции/недостатки:

  • like lower() / ilike больше '%c%': медленно работает с большими наборами данных, не релевантно;
  • pg_trgm с поиском на основе триграмм like/ilike + составной индекс gin/gist: один символ не может быть разбит на несколько триграмм, поэтому поиск выполняется через полное сканирование таблицы, без релевантности;
  • полнотекстовый поиск по индексу setweight(to_tsvector(lower())) gin/gist: вывод на основе релевантности, но меньше результатов из-за токенов, исключающих отдельные символы;

Существуют ли другие варианты улучшения поиска по одному символу? Как улучшить или смешать упомянутое выше, чтобы получить наилучший результат? Как заставить полный текст пропустить стоп-лист и создать все возможные лексемы, такие как it возможно для sqlserver?


postgresql postgresql-10
person revoua    schedule 18.12.2019    source источник
comment
По-видимому, решение в SQL Server состояло в том, чтобы создать словарь (или конфигурацию FTS) без стоп-слов. Это должно быть возможно и в Postgres.   -  person a_horse_with_no_name    schedule 18.12.2019

Ответы (1)


arrow_upward
1
arrow_downward

Полнотекстовый поиск вам в этом никак не поможет, потому что индексируются только целые слова, а искать подстроки нельзя.

Лучшее, что вы можете сделать, это использовать эту функцию:

CREATE FUNCTION get_chars(text) RETURNS char(1)[]
   LANGUAGE sql IMMUTABLE AS
$$SELECT array_agg(DISTINCT x)::char(1)[] FROM regexp_split_to_table($1, '') AS x$$;

Затем индекс

CREATE INDEX ON test_search USING gin (get_chars(full_name || short_name));

и ищите как

SELECT * FROM test_search
WHERE get_chars(full_name || short_name) @> ARRAY['c']::char(1)[];

Для частых символов в этом запросе все же следует использовать последовательное сканирование, так как это лучший метод доступа. Но для редких персонажей вы можете быть быстрее таким образом.

person Laurenz Albe    schedule 18.12.2019
comment
Я думал о чем-то вроде create index ix_chars on the_table using gin ( (string_to_array(full_name, null)) );, а затем использовал where string_to_array(...) @> array['x'] — кажется, это хорошо работает для редких персонажей. - person a_horse_with_no_name; 18.12.2019