Оптимізація запитів з віконними функціями

Залишилась ще одна важлива деталь, яку ми ще не обговорювали, — продуктивність запитів з віконними функціями. Бо навіть найелегантніший запит може перетворитись на повільну черепаху, якщо не враховувати оптимізацію. Сьогодні ми якраз цим і займемось!

Віконні функції неймовірно гнучкі та потужні. Але їх гнучкість — це не тільки подарунок, а й потенційна загроза продуктивності. 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 любить, коли його використання добре продумане!