Как декомпозировать большой SQL запрос с множеством джоинов?
Что проверяют
Зачем вы вообще декомпозируете: ради читаемости, ради скорости или чтобы убрать размножение строк. Интервьюер хочет увидеть, что вы начинаете с плана выполнения, а не с переписывания наугад. Следом часто спрашивают, как это выглядит на стороне Java, если запрос строит Hibernate.
Как отвечать
Сначала диагностика: EXPLAIN (ANALYZE, BUFFERS) покажет, где оценка числа строк расходится с фактом и какой узел съедает время. Дальше инструменты:
- CTE (
WITH): дают имена промежуточным шагам. В PostgreSQL начиная с 12-й версии CTE встраивается в основной запрос, если на него ссылаются один раз, аMATERIALIZEDзаставляет вычислить шаг отдельно; - агрегация до джоина: если соединить заказ сразу с позициями и с платежами, строки перемножатся, и
SUMпосчитает лишнее. Сверните каждую ветку подзапросом по ключу, потом соединяйте; - временная таблица: оправдана, когда промежуточный результат большой и используется несколько раз. После заполнения выполните
ANALYZE, иначе у планировщика нет статистики; - представление: для повторяющейся логики, которую используют несколько запросов;
- несколько запросов в приложении: иногда два простых запроса быстрее одного огромного, но так легко получить N+1.
В Java это означает: не джойнить в Hibernate сразу несколько @OneToMany через fetch join (для List это MultipleBagFetchException, для Set декартово произведение), а подтягивать коллекции отдельными запросами или через @BatchSize. Для сложных отчётов честнее нативный SQL или jOOQ, чем JPQL на двадцать строк.
Где спотыкаются
Выносят всё во временные таблицы без причины. Это лишняя запись на диск и потерянные оптимизации, которые планировщик сделал бы в одном запросе.
Не знают про порог перебора. При числе соединений больше join_collapse_limit (по умолчанию 8) PostgreSQL перестаёт перебирать все порядки и частично следует порядку JOIN из текста, поэтому порядок записи начинает влиять на план.
Декомпозируют без сравнения. Результат нужно сверить построчно со старым запросом и замерить время на реальных объёмах, а не на тестовой базе в сто строк.