Аналіз та оптимізація 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 разів, почніть з аудиту.