Там index scan выбирает 1 строку, а отбрасывает по условию

фильтра 154269 и так 56826 раз. в сумме выбирается 8 766 490 194 строк. может не надо выбирать столько строк? может всё таки дело в индексе/запросе/структуре регистра накопления? А во втором случае самый нижней узел плана выбирает ~2.5 млн (на глаз понятно). Конечно 2.5 млн выбрать быстрее будет чем 8 млрд. а на Nested Loop в первом случае тратятся вообще копейки от всего времени плана. так что может быть дело не в бобине всё таки..?

Согласен что проблема в индексе/запросе/структуре, но конфигурация типовая, народ желанием не горит в неё лезть. Хотелось настройками postgres решить.

А по поводу того что вы отключили Nested Loop и у вас эта операция стала вместо часа выполняться 5 секунд, это вовсе не означает что что-нибудь другое при этом не стало хуже. Если там всё на самом деле прям вот типовое, а лезть туда и допиливать чтобы работало быстрее никто не хочет могу посоветовать только минимизировать дисковые операции связанные с чтением. чтобы 8 млрд строк читались не с диска а из памяти. Загнать всю базу в память. убедиться что temp_buffers и work_mem Достаточно, сделать tablespace для временных таблиц в оперативной памяти.

Да, с отключенным nested loop, другие документы тормозят, поэтому его отключать не вариант. Базу проблематично загнать в память, база 800 ГБ.

тут вопрос цены что предпочтительнее быстрее и дешевле для вас: 1) смириться и жить дальше, хоть и тормозит, но что-то делать дорого 2) нанять спеца, который зайдёт в конфигуратор ,посмотри что у вас есть и что-то поправит. вопрос в том сколько возьмёт спец, за сколько он это сделает (день, неделя, месяц, год?) и во сколько раз ускориться. 3) закпить памяти/сервера, в вашем случае возможно будет дороже чем спец, но зато какой-то результат прямо сразу, потому что бывают и сервера по 1 ТБ памяти и по 6 ТБ