Оптимізація SQL-запитів веб-застосунку

Оптимізація SQL-запитів для веб-додатків

Повільні запити вбивають UX швидше ніж будь-який інший фактор. 95% проблем продуктивності на рівні БД вирішуються одним із чотирьох методів: додаванням індексу, переписуванням запиту, денормалізацією або кешуванням. Розберемо діагностику та конкретні техніки.

Інструменти діагностики

pg_stat_statements — перше, що потрібно включити на продакшені:

-- postgresql.conf
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.max = 10000
pg_stat_statements.track = all

-- Топ-20 запитів за сумарним часом
SELECT
    round(total_exec_time::numeric, 2) AS total_ms,
    round(mean_exec_time::numeric, 2)  AS mean_ms,
    calls,
    round((stddev_exec_time / mean_exec_time * 100)::numeric, 1) AS coeff_var_pct,
    left(query, 120) AS query
FROM pg_stat_statements
WHERE calls > 100
ORDER BY total_exec_time DESC
LIMIT 20;

coeff_var_pct — коефіцієнт варіації: високий відсоток говорить про нестабільний план (різні параметри дають кардинально різний час).

EXPLAIN ANALYZE:

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT p.*, c.name AS category_name
FROM products p
JOIN categories c ON c.id = p.category_id
WHERE p.status = 'published'
  AND p.created_at > NOW() - INTERVAL '30 days'
ORDER BY p.created_at DESC
LIMIT 50;

Ключові вузли в плані:

• Seq Scan на великій таблиці — немає індексу або планувальник вирішив, що індекс невигідний.
• Nested Loop з великою кількістю ітерацій — N+1 на рівні SQL.
• Hash Join з Batches > 1 — недостатньо work_mem.
• Sort без Index Scan на ORDER BY колонці — немає підходящого індексу.

Типові антипаттерни та рішення

1. SELECT * в ORM

-- Погано: тягнемо всі 30 колонок, включаючи BLOB-поля
SELECT * FROM products WHERE category_id = 5;

-- Добре: тільки потрібні поля
SELECT id, title, slug, price, status FROM products WHERE category_id = 5;

2. OFFSET пагінація на великих таблицях

-- Погано: при offset=10000 PostgreSQL читає 10050 рядків і відкидає 10000
SELECT * FROM products ORDER BY created_at DESC LIMIT 50 OFFSET 10000;

-- Добре: keyset пагінація
SELECT * FROM products
WHERE (created_at, id) < ('2024-03-01 12:00:00', 5000)
ORDER BY created_at DESC, id DESC
LIMIT 50;

Keyset пагінація вимагає складеного індексу (created_at DESC, id DESC) та передачі останнього значення з попередньої сторінки.

3. OR на різних колонках

-- Погано: OR часто не використовує індекс
SELECT * FROM users WHERE email = $1 OR phone = $1;

-- Добре: UNION
SELECT * FROM users WHERE email = $1
UNION ALL
SELECT * FROM users WHERE phone = $1
LIMIT 1;

4. Функції в WHERE

-- Погано: функція на колонці блокує індекс
SELECT * FROM orders WHERE DATE(created_at) = '2024-03-15';

-- Добре: range умова
SELECT * FROM orders
WHERE created_at >= '2024-03-15'
  AND created_at < '2024-03-16';

5. NOT IN з підзапитом

-- Погано: якщо підзапит повернеш NULL — весь NOT IN повернеш порожній результат
SELECT * FROM products WHERE id NOT IN (SELECT product_id FROM order_items);

-- Добре: NOT EXISTS або LEFT JOIN
SELECT p.* FROM products p
LEFT JOIN order_items oi ON oi.product_id = p.id
WHERE oi.product_id IS NULL;

Оптимізація JOIN

-- Додамо складений індекс для типового фільтру
CREATE INDEX idx_orders_user_status_created
    ON orders (user_id, status, created_at DESC);

-- Запит використовує index scan без Sort
SELECT id, total, status, created_at
FROM orders
WHERE user_id = $1
  AND status = 'completed'
ORDER BY created_at DESC
LIMIT 10;

Порядок колонок в індексі має значення: спочатку equality умови (user_id = $1, status = 'completed'), потім range/sort (created_at DESC).

work_mem та Sort Spill

Якщо в EXPLAIN ANALYZE бачимо external merge (Disk: ...) під час Sort — збільшуємо work_mem для сесії:

SET work_mem = '64MB';
-- Виконуємо важкий аналітичний запит
-- Повертаємо назад або використовуємо настройку на рівні з'єднання

В postgresql.conf краще залишити work_mem низьким (4-8MB за замовчуванням) і підносити для конкретних запитів через SET LOCAL work_mem.

CTE vs Підзапит vs Lateral

-- CTE (WITH) в PostgreSQL 12+ оптимізується як підзапит за замовчуванням
-- До PG12: CTE завжди матеріалізується — optimization fence
WITH recent_orders AS (
    SELECT user_id, COUNT(*) AS cnt
    FROM orders
    WHERE created_at > NOW() - INTERVAL '30 days'
    GROUP BY user_id
)
SELECT u.*, ro.cnt
FROM users u
JOIN recent_orders ro ON ro.user_id = u.id;

-- LATERAL: для row-dependent підзапитів
SELECT u.id, u.email, recent.total
FROM users u
CROSS JOIN LATERAL (
    SELECT SUM(total) AS total
    FROM orders o
    WHERE o.user_id = u.id
      AND o.created_at > NOW() - INTERVAL '30 days'
) AS recent;

LATERAL дозволяє використовувати змінні з зовнішнього запиту в підзапиті — часто дає кращий план, ніж JOIN на агрегований CTE.

Статистика планувальника

Якщо план вибирає неправильний метод доступу — оновлюємо статистику:

ANALYZE products;
-- або для оновлення статистики конкретної колонки з підвищеною точністю:
ALTER TABLE products ALTER COLUMN status SET STATISTICS 500;
ANALYZE products (status);

За замовчуванням default_statistics_target = 100. Для колонок із високою кардинальністю (timestamps, UUID) піднімаємо до 200–500.

Графіки

Діагностика та оптимізація 10–15 повільних запитів (через pg_stat_statements, EXPLAIN ANALYZE, додавання індексів, переписування): 2–3 дні. Глибокий аудит схеми та запитів для високонавантаженого додатку: 3–5 днів.