Как перевести числовой массив в последовательность чисел для IN

Question

[whats]

Добрый день. Имеется таблица с такой структурой

В полях services и brands стоит тип - числовой массив.
В другой таблице имеются соответствия этим ключам.

Как сделать запрос который бы заджоинил эту таблицу и преобразовал идентификаторы к текстовому представлению ?
Такой запрос

select *,
//Вложенный запрос который представляет select * from unnest(services) наш массив как последовательность строк, а далее мы в этой последовательности ищем в таблице соответствий текстовое представление и потом опять же агрегируем это все в 1 строку.
(select string_agg(service,',') from autorepair_service where id in(select * from unnest(services))) from autorepair

Запрос получается мягко говоря тяжелый с точки зрения понимания. Может быть есть способ полегче ? Не нашел ни одной функции которая бы массив представляла как последовательность чисел через запятую, есть array to string которая преобразует массив в строку, но тогда эту строку не получается подставить в предикат IN, выпадает ошибка
operator does not exist: integer = text

Интересует сам вопрос. Делать связи один ко многим нет желания в связи с производительностью запроса.

select *,(
    select string_agg(service,',') 
              from autorepair_service 
    where id=any(services)) 
from autorepair

"Seq Scan on autorepair  (cost=0.00..18772.45 rows=5889 width=693) (actual time=0.143..120.302 rows=5590 loops=1)"
"  SubPlan 1"
"    ->  Aggregate  (cost=3.07..3.08 rows=1 width=36) (actual time=0.021..0.021 rows=1 loops=5590)"
"          ->  Seq Scan on autorepair_service  (cost=0.00..3.05 rows=10 width=36) (actual time=0.003..0.018 rows=29 loops=5590)"
"                Filter: (id = ANY (autorepair.services))"
"                Rows Removed by Filter: 62"
"Total runtime: 120.585 ms"

select *,(select string_agg(service,',') from autorepair_service where id in(select * from unnest(services))) from autorepair

"Seq Scan on autorepair  (cost=0.00..33899.82 rows=5889 width=693) (actual time=0.142..139.699 rows=5590 loops=1)"
"  SubPlan 1"
"    ->  Aggregate  (cost=5.64..5.65 rows=1 width=36) (actual time=0.024..0.024 rows=1 loops=5590)"
"          ->  Hash Semi Join  (cost=2.25..5.41 rows=91 width=36) (actual time=0.009..0.021 rows=29 loops=5590)"
"                Hash Cond: (autorepair_service.id = unnest.unnest)"
"                ->  Seq Scan on autorepair_service  (cost=0.00..1.91 rows=91 width=40) (actual time=0.001..0.005 rows=91 loops=5558)"
"                ->  Hash  (cost=1.00..1.00 rows=100 width=4) (actual time=0.006..0.006 rows=29 loops=5590)"
"                      Buckets: 1024  Batches: 1  Memory Usage: 1kB"
"                      ->  Function Scan on unnest  (cost=0.00..1.00 rows=100 width=4) (actual time=0.002..0.003 rows=29 loops=5590)"
"Total runtime: 140.058 ms"

Answer 1

[Timosha]

я смутно понял в чём действительно состоит вопрос, но быть может это поможет
where id in(select * from unnest(services))
можно заменить на
where id = any (services)

Comments

[whats]

да, это то решение которое я озвучил. То есть по сути у нас есть массив чисел через запятую, мы его приводим к виду, каждое число в своей строке, далее подставляем в предикат IN, ищем соответствие в другой таблице, и далее агрегируем это соответствие опять в 1 строку.

[whats]

Второе то что нужно. По скорости в принципе что первый вариант, что второй работает одинаково. Таблица без индексов вида представленного выше. почти 6 тысяч строк. 15 секунд

[Timosha]

ак поди что там seq scan что тут, поэтому и 15 секунд

[whats]

@Timosha Извиняюсь, забыл что это пгадмин делает запрос. в вопросе выложил профайлер двух запросов. Разница есть, но не существенная

[Timosha]

видится мне что оно 5590 раз делает seq scan по autorepair_service, и либо там нет индекса по id, либо сделать seq scan эффективней чем дёргать индекс

[whats]

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

А индекс по ид есть. УК и ПК колонка

Answer 2

[Алексей Сундуков]

Вообще-то функция array_to_string принимает вторым параметром разделитель. Пример прямо в документации: www.postgresql.org/docs/9.3/static/functions-array.html

Но в целом у вас проблема с архитектурой и неправильным использованием реляционной базы. Потому что если там id записей другой таблицы, то в случае связи "многие-ко-многим" нужно просто "линковочная", промежуточная таблица.

Comments

[whats]

Покажите пожалуйста где вы увидели что я array to string(которую я даже не правильно написал) использую в своем запросе ? Это к вашему ВООБЩЕТО! От этого ее вывод не меняется. И она возвращает всегда строку.

С архитектурой проблем нет, вы скорее просто застряли в прошлом. Но вопрос не в этом. Представьте таблицу в 900 миллионов строк, то есть по вашему я должен создать таблицу связей в которой будет минимум 9 миллиардов строк ? Этот вариант не подходит, тем более что массивы это именно то что нужно.

И для вас ссылочка - profyclub.ru/docs/264
Но за критику спасибо, первый ответ я именно такой и ожидал