Оптимизация запросов с оконными функциями

Осталась еще одна важная деталь, которую мы ещё не обсуждали, — производительность запросов с оконными функциями. Ведь даже самый элегантный запрос может превратиться в медленного черепаху, если не учитывать оптимизацию. Сегодня мы займёмся именно этим!

Оконные функции невероятно гибкие и мощные. Но их гибкость — это не только подарок, но и потенциальная угроза производительности. PostgreSQL, к сожалению, не работает по принципу "магии", и ему нужны ресурсы, чтобы обработать данные. А уж если подключить оконные функции к огромным таблицам, ваш запрос может выполнять нечто, напоминающее марафон на месте.

Оптимизация позволит вам:

• Ускорить запросы, работающие с большими объёмами данных.
• Минимизировать нагрузку на базу данных.
• Сделать ваши запросы более дружелюбными к серверу (и коллегам, если они тоже работают с базой!).

Давайте погружаться и разбираться, что можно сделать, чтобы ваши запросы летали, как болид на автодроме.

Основы работы оконных функций

Прежде чем мы начнём оптимизировать, хорошо бы понять, что именно замедляет наш запрос. PostgreSQL работает с оконными функциями следующим образом:

1. Сортирует данные, если в запросе есть ORDER BY внутри OVER().
2. Обрабатывает каждую строку в пределах заданной оконной рамки или группы.
3. Возвращает результат для каждой строки.

Теперь представьте, что у нас есть таблица sales с 10 миллионами строк. Если в вашем запросе не предусмотрены фильтры, PostgreSQL будет обрабатывать каждую из этих строк. Это уже не марафон — это настоящая беговая дорожка без выхода.

Как ускорить оконные функции?

1. Использование индексов для ускорения сортировки

Большинство оконных функций используют ORDER BY внутри OVER() для управления порядком строк. Это означает, что PostgreSQL должен сортировать ваши данные перед выполнением оконной функции.

Если у вас есть индекс на столбце (или столбцах), которые используются в ORDER BY, PostgreSQL сможет значительно ускорить эту сортировку.

Пример

CREATE INDEX idx_sales_date ON sales (sale_date);

Теперь, если вы напишете запрос с сортировкой по sale_date, индекс вступит в игру:

SELECT
    sale_date,
    product_id,
    SUM(amount) OVER (PARTITION BY product_id ORDER BY sale_date) AS running_total
FROM sales;

Без индекса на sale_date каждое выполнение запроса будет включать затратную сортировку — PostgreSQL будет в панике искать, как быстрее упорядочить строки.

2. Применение фильтров с помощью WHERE

Сужение объёма данных — ключевая техника оптимизации. Если вам не нужно обрабатывать все 10 миллионов строк, но нужно работать только с последним годом — сократите диапазон данных с помощью WHERE!

Пример

SELECT
    sale_date,
    product_id,
    SUM(amount) OVER (PARTITION BY product_id ORDER BY sale_date) AS running_total
FROM sales
WHERE sale_date >= '2023-01-01';

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

3. Выбор подходящего оконного фрейма

При работе с оконными функциями с агрегацией, например, с SUM(), важно выбирать правильный оконный фрейм. Если вы используете рамку по умолчанию (RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), PostgreSQL будет включать все строки до текущей строки. Это может быть неоптимально для больших таблиц.

Пример: использование ROWS

Если вам нужно включить только пару строк до текущей строки, лучше явно указать это через ROWS:

SELECT
    sale_date,
    product_id,
    SUM(amount) OVER (
        PARTITION BY product_id 
        ORDER BY sale_date 
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS rolling_sum
FROM sales;

В данном случае PostgreSQL обрабатывает только три строки (две предыдущие + текущая) для каждой строки. Это эффективнее, чем обработка сотен строк по умолчанию.

4. Минимизация количества оконных функций

Каждая оконная функция обрабатывается PostgreSQL индивидуально. Если вы используете несколько оконных функций, PostgreSQL может выполнять сортировку для каждой из них отдельно, что приводит к замедлению. Однако, если параметры окон (например, PARTITION BY и ORDER BY) совпадают, PostgreSQL может переработать это более эффективно.

Пример: оптимизация с одинаковым окном

SELECT
    product_id,
    sale_date,
    SUM(amount) OVER (PARTITION BY product_id ORDER BY sale_date) AS running_total,
    ROW_NUMBER() OVER (PARTITION BY product_id ORDER BY sale_date) AS row_num
FROM sales;

Обе функции (SUM() и ROW_NUMBER()) используют одну и ту же рамку. PostgreSQL выполнит только одну сортировку — и это круто.

5. Партиционирование таблиц

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

Пример создания партиционированной таблицы

CREATE TABLE sales_partitioned (
    sale_date DATE NOT NULL,
    product_id INT NOT NULL,
    amount NUMERIC NOT NULL
) PARTITION BY RANGE (sale_date);

После этого создаётся определённое количество партиций, например, по годам:

CREATE TABLE sales_2022 PARTITION OF sales_partitioned
FOR VALUES FROM ('2022-01-01') TO ('2022-12-31');

CREATE TABLE sales_2023 PARTITION OF sales_partitioned
FOR VALUES FROM ('2023-01-01') TO ('2023-12-31');

Теперь, если вы используете WHERE sale_date BETWEEN '2023-01-01' AND '2023-12-31', PostgreSQL автоматически обратится только к соответствующей партиции.

Подробнее про партиционирование таблиц вы узнаете ближе к концу курса :P

6. Избегайте лишних данных (SELECT только что нужно)

Выбирайте только те столбцы, которые необходимы для работы функции и вашего результата. Если вашей оконной функции нужны только product_id, sale_date и amount, не стоит тянуть за собой весь зоопарк таблицы с био-данными клиента.

Пример "экономного" запроса

SELECT
    product_id,
    sale_date,
    SUM(amount) OVER (PARTITION BY product_id ORDER BY sale_date) AS running_total
FROM sales;

Меньше данных — меньше работы для PostgreSQL.

7. Использование материалации (MATERIALIZED VIEW)

Если вы часто выполняете одни и те же вычисления с оконными функциями, можно сохранить результаты в материализованном представлении. Materialized View сохраняет данные на диске, чтобы избежать повторного выполнения сложных запросов.

Пример создания материализованного представления

CREATE MATERIALIZED VIEW sales_running_total AS
SELECT 
    product_id,
    sale_date,
    SUM(amount) OVER (PARTITION BY product_id ORDER BY sale_date) AS running_total
FROM sales;

Теперь данные можно просто запросить:

SELECT * FROM sales_running_total WHERE product_id = 10;

8. Планирование запросов с использованием EXPLAIN и EXPLAIN ANALYZE

Как и в других аспектах SQL, вы можете использовать EXPLAIN или EXPLAIN ANALYZE, чтобы понять, как PostgreSQL выполняет ваш запрос — и где скрываются узкие места.

Пример анализа запросов

EXPLAIN ANALYZE
SELECT 
    product_id,
    sale_date,
    SUM(amount) OVER (PARTITION BY product_id ORDER BY sale_date) AS running_total
FROM sales;

Этот инструмент покажет вам, где PostgreSQL тратит больше всего времени, и вы сможете оптимизировать узкие места.

Оконные функции — это мощный инструмент анализа данных, но с ними нужно быть осторожными. Хотите скорости? Выбирайте индексы, добавляйте фильтры, не забывайте про партиции и не стесняйтесь пользоваться материализованными представлениями. PostgreSQL любит, когда его использование хорошо продумано!