т.е. это бизнес вам говорит делать такую структуру и такие запросы? хммм...

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

Причина в том, что PG на каждом уровне вложенности делает временную таблицу. А это получается не быстро. По крайней мере по сравнению с реализацией агрегата на С.
Кроме того, двухуровневые запросы с четко поделенными функциями на мой взгляд понятнее. Ведь суммарно отчетов очень много - несколько десятков, они периодически улучшаются(путем добавления показателей), и числота тоже имеет значение. У нас 3 кастомные функции, котрые легки в понимании, а дальше все стандартно, и всего в 2 уровня.

а проверка существования элемента в массиве, жсон и жсон пас в агрегате, это значит быстро, да?

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

Зачем? Какая у этого действия цель? Сделать чтобы было быстрее? Думаю, не получится - он исполняется оптимально. Фетчится только то, что надо и только один раз. Агрегаты тоже не делают лишних проходов. Структура запроса понятна. А делать декомпозинию разди декомпозиции - нерационально.

Когда речь о таблице на 50к строк, беспокоиться о временных таблицах - ну не знаю..

> Причина в том, что PG на каждом уровне вложенности делает временную таблицу.

Это в общем (т.е. почти всегда) просто не так, извините. Вы это в планах видели или так, запомнили то, что кто-то ляпнул? ;)

> Кроме того, двухуровневые запросы с четко поделенными функциями на мой взгляд понятнее.

Ну так вот на конкретных примерах лучше бы смотреть... я как-то чаще видел наоборот.

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

Рационально. Один долгий фетч это не лучше чем тысяча более мелких

Это раелизация на PGSQL, она нужна, но первичной не является. Первично исполняется вариант на C, а там поиск в b-деревьях и хранение в связанных списках.

Это почему?

По результатам эксплейна

я уже даже не говорю о том, что ваша функция может давать разные результаты причем неожиданно, она зависит от входящего ПОРЯДКА т.е. если у вас
в начале пойдет ид 1 10, 1 20, а позднее 1 20, 1 10 вы получите разные цифры.

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

Это почему?

я вроде бы объяснил там же.

особенно когда подселект использует ТОЛЬКО индекное чтение скорость космическая.

селекты должны всегда использовать индексы, если это не фулскан таблицы )

я говорю не только о поиске, но и когда у вас выборка только поля из индекса.