Такая задача встала для формирования промежуточной таблицы на основе которой веб-приложение выводит необходимую информацию. Изначально выборка делалась из 4 связанных таблиц. но такой запрос выполняется несколько секунд, что в принципе для приложения через чур. тесты с промежуточной таблице дали результат в 0,8-0,9 секунд что становится более менее приемлемым. в дальнейшем планируется совмещение с nosql решением, но перевод данных и переписывание кода под совместное использование postgresql и nosql потребует значительных временных затрат. поэтому собственно и встала создание промежуточного решения.
изучение мануалов и метод тыка пока привел вот к такому варианту.
UPDATE films SET acters = (SELECT json_agg (ALL(actors.id, actors.name))
FROM actor_in_films INNER JOIN actors ON actors.id=actor_in_films.id_actor WHERE actor_in_films.id_film= films.id GROUP BY actor_in_films.id_film);
если кто может помочь с более оптимальным вариантом буду безмерно благодарен.
Кэширование вариант хороший, но вот проблема в том что данные ежедневно обновляются. (в среднем в день ок 100К-200К записей обновляются, и еще столько же добавляются. Кеширование было бы просто великолепно, есл бы к одним и тем же данным обращались по 5 и более раз (конкретной записи в таблице). Но увы более 2-3 раз к одной и той же записи обращаются очень редко. Основную нагрузку делает большое количество единичных обращений