Ми нещодавно зіткнулися з ситуацією: сторінка звіту в адмінці завантажувалася 12 секунд. EXPLAIN ANALYZE показав Seq Scan на orders з 50 млн рядків — не вистачало індексу за статусом. Оптимізація зайняла 4 години, час виконання впав до 0,3 мс — прискорення в 40 000 разів. Це типовий приклад оптимізації SQL запитів для прискорення SELECT. У цій статті розберемо системний підхід до профілювання та оптимізації повільних запитів PostgreSQL, включаючи аналіз продуктивності БД та налаштування PostgreSQL. Наша команда має 5+ років досвіду в оптимізації PostgreSQL та виконала більше 50 проєктів із прискорення баз даних. Ми гарантуємо прискорення запитів у 10–100 разів за результатами аудиту. Наші сертифіковані спеціалісти мають багаторічний досвід роботи з PostgreSQL.
Що входить в роботу з оптимізації?
- Аудит продуктивності: збір статистики pg_stat_statements, профілювання топ-20 запитів.
- Детальний звіт із планами EXPLAIN ANALYZE та рекомендаціями щодо індексів.
- Створення та зміна індексів (із CONCURRENTLY для безблокувального деплою).
- Оновлення статистики та перевірка результатів.
- Налаштування PostgreSQL (параметри shared_buffers, work_mem, auto_explain).
- Консультація команди щодо написання ефективних запитів.
- Підготовка документації та навчання вашої команди.
- Технічна підтримка протягом 30 днів після впровадження.
Повільний запит у продакшні — це конкретна причина деградації: full table scan на таблиці в 50 мільйонів рядків, сортування без індексу, декартів добуток таблиць. EXPLAIN ANALYZE показує, що PostgreSQL робить насправді — не що, на думку планувальника, він зробить, а що реально відбулося в runtime.
Читання плану EXPLAIN ANALYZE
Приклад плану 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 — це офіційна документація.
Процес оптимізації
- Знайти топ-10 запитів за
total_exec_timeчерезpg_stat_statements. -
EXPLAIN (ANALYZE, BUFFERS)на кожен. - Визначити вузьке місце: Seq Scan, сортування, hash join.
- Створити або змінити індекс (
CONCURRENTLY— без блокування). -
ANALYZE table_name— оновити статистику. - Повторити
EXPLAIN ANALYZE— порівняти плани. -
pg_stat_statements_reset()— скинути та спостерігати нову статистику.
Цикл займає від кількох годин до кількох днів залежно від кількості проблемних запитів та обсягу даних. У 95% випадків достатньо одного-двох індексів.
Зведена таблиця проблем та рішень
| Проблема | Ознака | Рішення |
|---|---|---|
| Seq Scan | Rows Removed by Filter великий | Індекс за умовою фільтра |
| Сортування на диску | Sort Method: external merge | Індекс за полем сортування |
| Nested Loop без індексу | Множинні ітерації | Індекс на колонці JOIN |
Порівняння типів індексів
| Тип індексу | Застосування | Швидкість | Розмір |
|---|---|---|---|
| B-tree | Порівняння, сортування, рівність | Висока | Середній |
| GIN | Масиви, повнотекст, JSON | Середня | Великий |
| GiST | Геодані, діапазони | Середня | Великий |
| Частковий | Фільтр WHERE | Висока | Малий |
Вибір правильного типу індексу — ключ до прискорення. Наприклад, GIN індекс для повнотекстового пошуку працює в 20 разів швидше за B-tree.
Як оптимізувати SQL-запити в 100 разів?
Відповідь: системний підхід із використанням EXPLAIN ANALYZE та правильних індексів. Ми досягали прискорення в 40 000 разів на одному запиті, а в середньому — 10-100 разів. Ключові фактори: аналіз плану, усунення Seq Scan, використання покриваючих індексів та оптимізація JOIN.
Отримайте консультацію з оптимізації вже сьогодні. Зв'яжіться з нами для оцінки вашого проєкту — ми підготуємо план робіт та орієнтовні терміни. Якщо ви хочете прискорити SELECT запити в 100 разів, почніть з аудиту.