Как найти аббревиатуру строкового столбца индексируемым способом
Я работаю над проектом express.js, используя Sequelize в качестве формы и postgres в качестве движка.
Я работаю над повышением производительности медленного запроса выбора, который выполняет множество функций, и одна из них — сравнение аббревиатуры названия хранилища с поисковым запросом с помощью ilike, поэтому, если поисковый запрос — «STW», склад под названием «Супертестовый склад» будет соответствовать. Запрос выглядит следующим образом:
SELECT field1, field2, ...
FROM package p
LEFT OUTER JOIN warehouse w ON p.warehouse_id = w.id
-- many other joins
WHERE
regexp_replace(w.name, '([a-z ])+', '', 'g') ILIKE '%search term%'
-- many other conditions
Дело в том, что основное улучшение, которое я делаю, — это создание индексов (джин для всего, что делает ilike '%something%') и рефакторинг запроса, чтобы можно было их использовать. Но функция regexp_replace() не работает с индексами. Еще одна важная вещь, которую следует отметить, это то, что это решение работает только в том случае, если все слова в именах правильно написаны с заглавной буквы, что относится к большинству наших данных, но не ко всем, поэтому, если есть склад с именем «Склад Super TEST» или « супертестовый склад», он не будет работать корректно.
Я придумал простое решение: сделать это (при условии, что текст поиска «STW»):
w.name ILIKE 'S% T% W%' OR w.name ILIKE '% S% T% W%'
Он работает в среднем случае и использует созданный мной индекс джина. Но проблема в том, что между ними можно использовать другие слова. Таким образом, «STW» также будет соответствовать «Super Fake Test Warehouse».
Есть ли какое-либо условие, которое бы правильно соответствовало аббревиатуре и использовало бы индекс?
Порекомендовали бы вы использовать для этого сгенерированный столбец (при условии, что я улучшу существующий regexp_replace или использую что-то еще)? Есть ли какие-либо недостатки в этом? В идеале я не хочу изменять таблицу только для этой функции, но это может быть хорошим решением, если мы больше ничего не можем сделать.
Любые другие решения?
Вам лучше создать столбец для аббревиатуры и индексировать его. Согласен с первым комментарием.
«Но функция regexp_replace не работает с индексами» Почему не работает? Какую ошибку вы получаете?
Почему вы имеете в виду слово «ошибка»? Как я могу получить ошибку при использовании индексов? Когда я объясняю анализ запроса с помощью regexp_replace, он не использует созданный мной индекс gin, он выполняет последовательное сканирование, и, согласно тому, что я прочитал, никакой индекс не поможет в чем-то подобном. Был ли это общий ответ, призванный заставить людей добавлять больше информации в ответы?
Когда что-то «не работает», обычно выдается ошибка. Но я не могу угадать все возможные способы, чтобы что-то «не сработало», поэтому вам придется это объяснить. Так что да, я думаю, это общий ответ, призванный заставить вас объяснить, что вы имеете в виду. Для меня это работает, чтобы использовать индекс, хотя сам шаблон регулярного выражения мог бы быть улучшен, возможно, как то, что показывает Эрвин в своем ответе.
Ответы 2
В идеале я не хочу изменять таблицу только для этой функции.
Итак, создайте индекс выражений :
CREATE INDEX foo ON package (upper(regexp_replace(name, '\W*?\m(.).*?\M', '\1', 'g')));
Выражение надежно извлекает аббревиатуры в верхнем регистре (за исключением конечных символов, не являющихся словами, в ваших строках):
SELECT upper(regexp_replace('SUPER ware hOUse', '\W*?\m(.).*?\M', '\1', 'g')); -- SWH
Задействованные функции являются неизменяемыми, поэтому подходят для индекса выражения.
Повторите выражение в своих запросах, чтобы ввести индекс:
SELECT *
FROM package p
WHERE upper(regexp_replace(name, '\W*?\m(.).*?\M', '\1', 'g')) = 'SWH';
Однако я бы предпочел добавить сгенерированный столбец. Более простой запрос и немного быстрее, но при этом добавляется всего лишь несколько байтов в строке. Видеть:
В конце концов я создаю сгенерированный столбец следующим образом:
ALTER TABLE warehouse
ADD COLUMN name_acronym TEXT GENERATED ALWAYS AS (
array_to_string(
array(
SELECT substring(word FROM 1 FOR 1)
FROM unnest(string_to_array(name, ' ')) AS word
),
''
)
) STORED;
У меня также есть этот индекс для имени:
CREATE INDEX warehouse_name ON warehouse USING gin ("name" gin_trgm_ops);
Во-первых, это решение более точно для акронимов, поскольку фактически оно использует первую букву каждого слова. Кроме того, запросы ilike к сгенерированному столбцу будут использовать индекс.
Может быть, добавить индексированный столбец
acronymв таблицуwarehouseи заполнить егоregexp_replace("name", '[^A-Z]', '', 'g')по умолчанию? Тогда ваши поиски будут быстрыми. Если ваш домен данных не позволяетalter table, вы можете создать отдельную таблицуwh_name_acronymи присоединиться к ней.