насколько я знаю, есть некоторые методы оптимизации (как минимум отсечение заведомо неоптимальных укладок).
Есть, есть... но вменяемо они работают только в очень частных случаях. И практически все предназначены исключительно для одномерной задачи. Показанные же данные не только двумерны, но и достаточно равномерны - тут Вы скорее потеряете на проверках, чем приобретёте на отсечениях.
Это ни о чём. Серверу БД пофиг, что юзер, подключившись, сидит и пялится в загруженную страницу. Сколько запросов в секунду? распределение по типам? поток "тяжёлых" запросов? объём выборок (поток трафика от сервера)?
Первый INSERT - это CTE с соотв. выражением в RETURNING. Значения оттуда используются во втором INSERT. Проверку на существование выполнит ON CONFLICT.
Олег Откидач, FIRST_VALUE() не требует спецификации окна, так что RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING можно смело удалить. А спецификации окон лучше объединить.
Выглядит, правда, страшновато
Ну сделайте по-нормальному - ROW_NUMBER() в CTE и WHERE rn=1 снаружи.
1) Судя по тому, что я вижу, дублируются не поля, а записи.
2) Подход неверный - Вы пытаетесь совместить две ненумерованные кучки (параметры $1 и $2), тогда как надо ещё на входе в запрос иметь не отдельные значения, а их пары - т.е. определённому значению из $1 соответствует строго одно определённое значение из $2. И я бы построил эти пары ещё на клиенте, т.е. не [1,2,3] и [a,b,c], а [[1,a],[2,b],[3,c]], и передавал как единый параметр.
Вопросы о производительности либо использовании индексов без полных CREATE TABLE всех таблиц, точного текста запроса и сведений о статистике данных - бессмысленны.
Если Вы убеждены, что использование индекса будет эффективно - то всегда есть FORCE INDEX. Хотя начинать надо с ANALYZE TABLE.
Есть, есть... но вменяемо они работают только в очень частных случаях. И практически все предназначены исключительно для одномерной задачи. Показанные же данные не только двумерны, но и достаточно равномерны - тут Вы скорее потеряете на проверках, чем приобретёте на отсечениях.