Появилась задача - оптимизировать сложный аналитический sql-запрос.

Первый возникший вопрос - как измерять эффективность оптимизации. Ведь при повторном выполнении запроса данные кэшируются, и запрос может выполняться на порядки быстрее, чем в первый раз. Существуют хаки для очистки кэшей - остановить Postgresql, очистить кэш ОС. Но это применимо только для локальной БД. В условиях кластера это может не сработать, да и прав на такие действия может не быть.
Остается только ориентироваться на план запроса. Основная цель - на каждом этапе минимизировать количество обрабатываемых строк. В моем случае запрос представлял собой соединение основной таблицы со многими другими, фильтрация по определенным атрибутам, а затем группировка по сущностям из основной таблицы.

План показал, что промежуточные результаты содержали миллионы строк, поэтому группировка была очень медленной. Также, в отчете были необходимы поля из основной таблицы, и для этого по ним тоже проводилась группировка (просто чтобы иметь возможность указать их в select).
Что было сделано:

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

2. Тут оказалось, что количество таблиц, соединяемых в основном подзапросе, оказалось больше 10. В Postgresql после определенного количества соединяемых таблиц (по умолчанию 8) оптимизатор перестает искать оптимальный план и просто обрабатывает их в порядке объявления. А таблица с наиболее селективным фильтром оказалась в конце списка таблиц, поэтому в плане сначала не было заметно никакого улучшения. Но, при перемещении этой таблицы наверх списка, селективный фильтр сработал первым и дал значительное ограничение промежуточных результатов.

3. После группировки еще раз присоединила основную таблицу, чтобы выбрать из нее атрибуты, необходимые в отчете. Так ушла необходимость "протаскивать" их наверх из основного подзапроса, и проводить по ним лишнюю группировку.

Также надо иметь в виду, что оптимизатор Postgresql строит план на основе статистики - количества строк в таблицах, селективности атрибутов, по которым идет фильтрация и т.д.. Поэтому оптимизация, проведенная на dev-стенде, может быть неактуальной для промышленных данных, и ее надо обязательно протестировать на prod-стенде.

#рабочее