Все очень плохо?

И что, ты не раскажешь даже, что конкретно делает эта функция?

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

А проблемы с производительностью возникают из-за того, что не хватает:

а. Ресурсов ЦПУ

б. Производительности дисковой подсистемы.

(выбрать один из пунктов)

IO почти 0. Проц жрат, да.

Транзакции?

все обернуто в одну транзакцию, да. или все или ничего.

плюс обходит связанные таблицы

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

нет, строгая последовательность с таблицами который маппят внешний идентификатор с внутренним.

1. Избавиться от циклов «for i in (select ... from tablename) loop insert ... values ...; превратив циклы в insert ... select

2. Использовать временные таблицы (они не прокатываются через журналы и т.п.) и с ними всё намного быстрее, а потом также скопом делать insert/update (см. предыдущий пункт)

все обернуто в одну транзакцию

Уже несколько лет не смотрел близко на постгрис, но раньше был такой фичебаг: статистика и план выполнения в пределах транзакции не изменялись.

То есть, если перед функцией в таблице было несколько записей, оптимизатор считал что нужно делать sequential scan, то всю функцию он его и будет делать, хоть там уже 100500 записей и нужно использовать индекс.

была у нас такая же проблемы, хранили кучу данных, их в оракле агрегировали и записывали в другие таблички. данных было много всё это единолично работало на большом (по тем временам) сане 4-5 часов и иногда отваливалось по timeout. Пришёл клиент у которого это вообще бы не работало - переместили данные из оракла в файлики, и написали на java приложение которое грамотно эти данные обрабатывает и сохраняет в бд только то что нужно в итоге. Всё стало работать 20-25 минут и без мега загрузки базы. Возможно вам стоит поступить так же и переместить логику колбасанья данных в отдельное приложение _оптимизированное_ для работы с данными данными, а не пытаться поставить мёртвому банки.

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

накаленное приложение будет _оптимизированно_ делать то что надо, далеко не всё надо пихать в базу данных.

Использовать upsert из 9.5

На самом толстом тестовом наборе данных это занимает порядка 4 часов что совершенно неприемлемо. Есть ли способы заставить это работать быстрее?

Да. Легко можно заоптимизировать до порядка 8 минут. Дальше уже труднее.

Я бы начал с поиска профайлера для pl/pgsql.