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

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

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

А что тут непонятного? Вам надо посчитать количкество уникальных записей - вы используете COUNT(DISTINCT table.id), а если Вы захотите посчитать сумму поля в уникальных записях, то сделать SUM(DISTINCT table.value) у Вас не получится.
А с этой функцией получится.
Разве это не удобно?

нет, если она выполняется дольше и больше ресурсов, у вас будет миллион записей, вы будете забивать массив до миллиона, вы сами вешаете якорь на запрос.
это не та структура и не тот инструмент для этого,
агрегатные функции выполняют ПРЕОБРАЗОВАНИЕ данных, а вы должны ПОДГОТОВИТЬ их ДО агрегации, я вам уже в который раз это пишу.

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

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

По ряду причин нам хотелось бы иметь и реализацию на чем-то, что работает на AWS RDB. Пусть не такую эффективную, но и не мертвую.

а откуда вообще здесь дубли будут
  common.sum_distinct(
               CAST((si.price #>> '{value}') AS numeric) *
               CAST((si.quantity #>> '{value}') AS numeric) *
               CASE WHEN CAST(si.options #>> '{deliveries}' AS numeric) > 0
                        THEN CAST(si.options #>> '{deliveries}' AS numeric)
                    ELSE 1
                   END,
               si.id) sum,
у вас все данные из si, si.id первичный ключ.
вы где то делаете join где возникают дубли?

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

ух, жесть какая

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

Welcome to the real world!

к счастью, это не реал ворлд, а параллельная вселенная

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

Так объясните, как классически это решить. Вариант с подзапросом не катит - делали - сильно проигрывает по производительности.

> но это добавит еще один уровень вложенности

Да, добавит.

> , и суммарно получится хуже и запутаннее.

А Вы пробовали (вдруг получится на самом деле приемлемо)?
И да, для уменьшения запутанности и уровней вложенности можно использовать CTE — так зачастую намного нагляднее получается в нетривиальных случаях.

У нас не принято объяснять бизнесу что то, что они хотят, им на самом деле не нужно, так как это не позволяет парадигма программирования.

Может, стоило это пытаться решить (т.е. выявить причину низкой производительности и устранить)?

объяснять надо не бизнесу, что им нужно другое, а программисту, что его парадигма излишне запутана и не эффективна