FROM pg_stat_activity
WHERE pid IN (SELECT pid FROM pg_stat_activity WHERE state = 'active');
зачем ты in (select используешь на простую проверку where state='...', убери и оставь это условие в основном запросе.
И еще, каждый раз, когда ты не понимаешь почему тормозит, сначала делай explain запроса, как минимум сюда его закинь, так увидишь, используются ли индексы.
Судя по тому что у тебя 20 секунд выполняется примитивный запрос на всего миллионах записях с условием state='...' то у тебя на полях, используемых в условии нет индекса, а без них база перелопачивается вся целиком, и если быстро - значит она закешировалась, а если медленно - другие операции вытеснили ее из кеша.
