Почему оконные функции могут тормозить запрос?
Я написал уже целую серию постов про оконки (раз, два, три) и там все выглядело красиво - добавил OVER() и получил профит. Но в комментах справедливо подметили, что оконные функции - это часто один из самых тяжелых видов запросов, который сильно жрет ресурсы.
Главный виновник тут обычно не сама функция, а сортировка. Сортировать много строк - дорогая операция на больших объемах данных, и ее лучше избегать везде, где она не нужна. Ниже 4 прикладных совета по оптимизации.
1⃣ Убери ORDER BY из окна, если порядок не влияет на смысл
Частая ошибка - писать ORDER BY внутри OVER() «на всякий случай». Как только он появляется, базе нужно обеспечить порядок строк, а это почти всегда дорого.
Если тебе нужен просто итог по группе (например, общий доход за день для каждой строки), а не накопительная сумма или скользящее окно - ORDER BY внутри окна не нужен.
-- Накопительная сумма (дороже)
SUM(revenue) OVER (PARTITION BY date ORDER BY created_at)
-- Просто итог по дню (дешевле)
SUM(revenue) OVER (PARTITION BY date)
2⃣ Чем меньше строк, тем быстрее
Оконки «проходят» по всем строкам, часто упорядочивают их, а иногда еще и держат большие куски данных в памяти. Поэтому самый простой ускоритель - сократить объем данных ДО оконки:
✔ Отфильтруй период (например, последние 30/90 дней);
✔ Не тащи лишние колонки («SELECT *» - плохо);
✔ Если можно, то сначала агрегируй данные до нужной гранулярности, а окно считай уже на агрегате.
3⃣ Несколько разных окон = несколько сортировок
Если в одном запросе несколько окон с разным ORDER BY, база может быть вынуждена упорядочивать данные несколько раз. И как итог: больше времени и памяти.
SUM(x) OVER (PARTITION BY a ORDER BY b) AS s1,
AVG(x) OVER (PARTITION BY a ORDER BY c) AS s2
Если можешь, унифицируй порядок в окнах. Если не можешь, то иногда лучше разнести расчеты на 2 шага, чем собирать все в одном SELECT.
4⃣ Используй план выполнения запроса
EXPLAIN - это команда, которая показывает план выполнения запроса: какие шаги база собирается сделать и где она потратит ресурсы. А EXPLAIN ANALYZE еще и выполняет запрос добавляя фактические цифры: сколько строк прошло через каждый шаг и сколько времени заняло.
Далее я разберу, как именно читать EXPLAIN и использовать эту команду для оптимизации.
#харды #sql
Я написал уже целую серию постов про оконки (раз, два, три) и там все выглядело красиво - добавил OVER() и получил профит. Но в комментах справедливо подметили, что оконные функции - это часто один из самых тяжелых видов запросов, который сильно жрет ресурсы.
Главный виновник тут обычно не сама функция, а сортировка. Сортировать много строк - дорогая операция на больших объемах данных, и ее лучше избегать везде, где она не нужна. Ниже 4 прикладных совета по оптимизации.
1⃣ Убери ORDER BY из окна, если порядок не влияет на смысл
Частая ошибка - писать ORDER BY внутри OVER() «на всякий случай». Как только он появляется, базе нужно обеспечить порядок строк, а это почти всегда дорого.
Если тебе нужен просто итог по группе (например, общий доход за день для каждой строки), а не накопительная сумма или скользящее окно - ORDER BY внутри окна не нужен.
-- Накопительная сумма (дороже)
SUM(revenue) OVER (PARTITION BY date ORDER BY created_at)
-- Просто итог по дню (дешевле)
SUM(revenue) OVER (PARTITION BY date)
2⃣ Чем меньше строк, тем быстрее
Оконки «проходят» по всем строкам, часто упорядочивают их, а иногда еще и держат большие куски данных в памяти. Поэтому самый простой ускоритель - сократить объем данных ДО оконки:
✔ Отфильтруй период (например, последние 30/90 дней);
✔ Не тащи лишние колонки («SELECT *» - плохо);
✔ Если можно, то сначала агрегируй данные до нужной гранулярности, а окно считай уже на агрегате.
3⃣ Несколько разных окон = несколько сортировок
Если в одном запросе несколько окон с разным ORDER BY, база может быть вынуждена упорядочивать данные несколько раз. И как итог: больше времени и памяти.
SUM(x) OVER (PARTITION BY a ORDER BY b) AS s1,
AVG(x) OVER (PARTITION BY a ORDER BY c) AS s2
Если можешь, унифицируй порядок в окнах. Если не можешь, то иногда лучше разнести расчеты на 2 шага, чем собирать все в одном SELECT.
4⃣ Используй план выполнения запроса
EXPLAIN - это команда, которая показывает план выполнения запроса: какие шаги база собирается сделать и где она потратит ресурсы. А EXPLAIN ANALYZE еще и выполняет запрос добавляя фактические цифры: сколько строк прошло через каждый шаг и сколько времени заняло.
Далее я разберу, как именно читать EXPLAIN и использовать эту команду для оптимизации.
#харды #sql