TL;DR Иногда «убить» самый тяжёлый JOIN — проще, чем кажется. Достаточно вынести агрегат в коррелированный под-запрос и дать движку опереться на индекс.