Итак, после недолгих тестов выснилось следующее: самое узкое место это жесткий диск, после него идет оперативная память и потом настройки в PostgreSQL.conf, после улучшения обоих характеристик допилил конфиг и запрос с результатом в 400000 строк строился 10 сек, а запрос на лимит 100 0,40 ms. Общий вывод: виноваты ресурсы