Аналіз та оптимізація SQL-запитів (EXPLAIN ANALYZE)

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT u.name, COUNT(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE u.country = 'RU' AND o.created_at > '2023-01-01'
GROUP BY u.id, u.name
ORDER BY order_count DESC
LIMIT 20;
-- Вивід:
Limit (cost=45231.23..45231.28 rows=20) (actual time=892.341..892.345 rows=20)
  -> Sort (cost=45231.23..45387.41) (actual time=892.340..892.341 rows=20)
        Sort Key: (count(o.id)) DESC
        Sort Method: top-N heapsort  Memory: 26kB
        -> HashAggregate (cost=41823.10..43011.52) (actual time=867.234..880.123 rows=12340)
              -> Hash Left Join (cost=12345.00..40234.12) (actual time=234.123..801.234 rows=450000)
                    Hash Cond: (o.user_id = u.id)
                    Buffers: shared hit=234 read=12890
                    -> Seq Scan on orders o (cost=0.00..18234.00 rows=450000) (actual time=0.023..345.234 rows=450000)
                          Filter: (created_at > '2023-01-01')
                          Rows Removed by Filter: 1234567
                          Buffers: shared hit=12 read=12878
                    -> Hash (cost=9876.00..9876.00 rows=123456) (actual time=234.012..234.012 rows=98765)
                          -> Seq Scan on users u (cost=0.00..9876.00 rows=123456) (actual time=0.021..189.234 rows=98765)
                                Filter: (country = 'RU')

Згідно з офіційною документацією PostgreSQL, EXPLAIN ANALYZE виконує запит і повертає дійсний час виконання. Ось що ми бачимо і що з цим робити:

• Seq Scan on orders з Rows Removed by Filter: 1234567 — виконує послідовне сканування 1.7 млн рядків, фільтрує 1.23 млн. Потрібен індекс на (created_at) або (user_id, created_at).
• Buffers: shared hit=12 read=12878 — майже всі сторінки читаються з диска (read), не з кешу. Або таблиця більша за shared_buffers, або дані рідко запитуються.
• actual time=892ms — для кнопки в інтерфейсі це катастрофа.

Пошук повільних запитів за допомогою pg_stat_statements

-- Увімкнути розширення та отримати топ за сумарним часом
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
SELECT left(query, 100) AS query_preview, calls, round(total_exec_time::numeric, 0) AS total_ms, round(mean_exec_time::numeric, 2) AS avg_ms, round(stddev_exec_time::numeric, 2) AS stddev_ms, rows
FROM pg_stat_statements
WHERE dbid = (SELECT oid FROM pg_database WHERE datname = current_database())
ORDER BY total_exec_time DESC
LIMIT 20;
-- Скидання статистики після оптимізації
SELECT pg_stat_statements_reset();

Цей запит одразу видає топ-20 запитів, які споживають найбільше ресурсів. У типовому проєкті 80% часу йде на 10% запитів — їх ми й оптимізуємо.

Типові патерни повільних запитів

Розглянемо кілька типових випадків із практики. У кожному з них індекс вирішує проблему, але важливо вибрати правильний тип індексу PostgreSQL.

Seq Scan та сортування

-- Повільно: повне сканування таблиці та сортування на диску
SELECT * FROM orders WHERE status = 'pending' ORDER BY created_at DESC LIMIT 100;
-- Рішення: частковий покриваючий індекс
CREATE INDEX CONCURRENTLY idx_orders_pending ON orders(status, created_at DESC) INCLUDE (id, user_id, total_amount) WHERE status IN ('pending', 'processing');

Індекс B-tree прискорює сортування в 1000 разів краще, ніж сортування зовнішнім злиттям (external merge).

Неефективний JOIN та N+1

JOIN оптимізація включає створення індексів на колонках з'єднання.

-- Повільно: JOIN без індексу та N+1 запити
SELECT u.name, o.total
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE u.registered_at > '2023-01-01';
-- В ORM: $orders = Order::all(); foreach ($orders as $order) { echo $order->user->name; }
-- Рішення: індекс та eager loading
CREATE INDEX CONCURRENTLY idx_orders_user_id ON orders(user_id);
-- В Laravel Eloquent: $orders = Order::with('user:id,name')->get();

Типова ситуація: на таблиці orders немає індексу за user_id, і PostgreSQL виконує Nested Loop з повним скануванням. Після додавання індексу час JOIN падає в 50-100 разів.

LIKE та функції на колонках

-- Повільно: префіксний wildcard та функція на даті
SELECT * FROM products WHERE name LIKE '%телефон%';
SELECT * FROM orders WHERE DATE(created_at) = '2023-01-15';
-- Рішення: pg_trgm та діапазон замість функції
CREATE INDEX CONCURRENTLY idx_products_name_trgm ON products USING gin(name gin_trgm_ops);
SELECT * FROM orders WHERE created_at >= '2023-01-15 00:00:00' AND created_at < '2023-01-16 00:00:00';

Покриваючий індекс скорочує I/O в 5-10 разів порівняно зі звичайним індексом.

Які інструменти аналізу використовувати?

Для автоматичного логування повільних запитів використовуйте auto_explain — він не вимагає ручного запуску EXPLAIN. Налаштуйте auto_explain.log_min_duration = 1000 (у мілісекундах), і всі запити довші за секунду будуть записані в лог із повним планом.

Для візуалізації планів використовуйте PostgreSQL EXPLAIN — це офіційна документація.

Процес оптимізації

1. Знайти топ-10 запитів за total_exec_time через pg_stat_statements.
2. EXPLAIN (ANALYZE, BUFFERS) на кожен.
3. Визначити вузьке місце: Seq Scan, сортування, hash join.
4. Створити або змінити індекс (CONCURRENTLY — без блокування).
5. ANALYZE table_name — оновити статистику.
6. Повторити EXPLAIN ANALYZE — порівняти плани.
7. pg_stat_statements_reset() — скинути та спостерігати нову статистику.

Цикл займає від кількох годин до кількох днів залежно від кількості проблемних запитів та обсягу даних. У 95% випадків достатньо одного-двох індексів.

Зведена таблиця проблем та рішень

Порівняння типів індексів

Вибір правильного типу індексу — ключ до прискорення. Наприклад, GIN індекс для повнотекстового пошуку працює в 20 разів швидше за B-tree.

Як оптимізувати SQL-запити в 100 разів?

Відповідь: системний підхід із використанням EXPLAIN ANALYZE та правильних індексів. Ми досягали прискорення в 40 000 разів на одному запиті, а в середньому — 10-100 разів. Ключові фактори: аналіз плану, усунення Seq Scan, використання покриваючих індексів та оптимізація JOIN.

Отримайте консультацію з оптимізації вже сьогодні. Зв'яжіться з нами для оцінки вашого проєкту — ми підготуємо план робіт та орієнтовні терміни. Якщо ви хочете прискорити SELECT запити в 100 разів, почніть з аудиту.