postgresql: упростить код получения строки с уникальными словами, избавиться от лишних функций

Question

есть такой sql запрос

SELECT 
    t1.id AS id,
    array_to_string(
        array(
            SELECT DISTINCT unnest(
                string_to_array(
                    STRING_AGG(t2.words, '|')
                , '|')
            )
        )
    , '|') AS words
FROM libraries t1
JOIN library_file_relationships r ON t1.id = r.library_id
JOIN files t2 ON r.file_id = t2.id
GROUP BY t1.id

из таблицы files извлекаются поля words, которые содержат слова, разделённые разделителем '|'
затем поля разбиваются на отдельные слова по разделителю '|' и преобразуются в массив
из массива оставляются только уникальные значения
которые затем опять складываются в строку через разделитель '|'

Я сделал в лоб, что данный алгоритм гарантированно работает, но как по мне происходит очень много лишних функций работы со строками и массивами, как мне кажетяся

можно ли его упростить (а значит и ускорить)?

Нужно: в каждой группе из полученных строк, содержащих слова через разделитель '|', создать одну строку, содержащие только уникальные слова (без повторов) через разделитель '|'

Answer 1

Можно упростить разборку-сборку так

select id,string_agg(w1,'|') as words
from (
   select distinct id,string_to_table(fl.words,'|') w1
   from files fl
)x
group by id;

Должно получиться примерно так:

SELECT t1.id AS id,t2.words
FROM libraries t1
JOIN library_file_relationships r ON t1.id = r.library_id
JOIN (select id,string_agg(w1,'|') as words
   from (
      select distinct id,string_to_table(fl.words,'|') w1
      from files fl
   )x
   group by id
  )t2 ON r.file_id = t2.id
GROUP BY t1.id -- нужно ли это, вопрос

По комментарию, больше вариант

SELECT t1.id AS id,t2.words
FROM libraries t1
JOIN library_file_relationships r ON t1.id = r.library_id
JOIN (select library_id,string_agg(w1,'|') as words
   from (
      select distinct library_id,string_to_table(fl.words,'|') w1
      from files fl
      JOIN library_file_relationships r ON fl.id = r.library_id
   )x
   group by library_id
)t2 ON r.library_id = t2.library_id

Ваш вариант:Собрать 1-10000 строк в одну и потом разобрать на слова, выбрать уникальные и собрать в строку. И повторить это 10000 раз.

Предлагаемый: Разобрать 1000 000 строк на слова и собрать в готовую строку для каждого library_id (libraries.id) уникальные в пределах library_id.

Представляется, string_agg внутренний - можно пропустить и собирать DISTINCT по library_id. Умозрительно, это позволит не создавать 1 временную таблицу размером с files.
В любом случае, придется создавать временную таблицу на несколько миллионов строк(слов) и сортировать его по library_id и words.

Если запрос слишком тяжелый, делать поэтапно, по, например, 1000 library_id.

БЛОГ НА HUSL

postgresql: упростить код получения строки с уникальными словами, избавиться от лишних функций

Ответы (1 шт):