Оптимізація повільних SQL-запитів PostgreSQL

Оптимізація повільних SQL-запитів PostgreSQL

Ви чекаєте 20 секунд на завантаження звіту. Користувачі йдуть, база даних — вузьке горло. Типова ситуація: один запит до PostgreSQL виконується 5 секунд, а таких запитів сотні на хвилину. Ми розбираємося, чому це відбувається, і усуваємо проблему: знижуємо час відповіді БД у 3–5 разів на навантаженні. Діагностика триває день, оптимізація — ще пару днів. Всі зміни документуються, виміри до та після обов'язкові.

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

Діагностика повільних запитів

За даними документації PostgreSQL, pg_stat_statements є стандартним інструментом для моніторингу. pg_stat_statements — перше розширення, яке потрібно ввімкнути на продакшні. Воно збирає статистику по кожному запиту: сумарний та середній час, кількість викликів, стандартне відхилення. Коефіцієнт варіації (coeff_var) допомагає виявити запити з нестабільним планом.

Процес діагностики за п'ять кроків:

1. Увімкніть розширення pg_stat_statements (якщо вимкнено) та зберіть статистику за кілька годин.
2. Виконайте запит топ-20 за total_exec_time.
3. Для кожного підозрілого запиту отримайте план через EXPLAIN (ANALYZE, BUFFERS).
4. Визначте вузли плану: Seq Scan, Nested Loop, Hash Join з Batches > 1.
5. Застосуйте відповідну оптимізацію: додайте індекс, перепишіть запит, налаштуйте work_mem.

-- Ввімкнення pg_stat_statements
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 колонці — немає відповідного індексу.

Чому індекси не використовуються?

Навіть за наявності індексу планувальник може його проігнорувати. Основні причини:

• Функція на колонці в WHERE (наприклад, DATE(created_at))
• Низька селективність (індекс на булевій колонці зі зміщеним розподілом)
• Сортування, що не збігається з порядком індексу

Рішення: переписуємо запит, щоб прибрати обгортки функцій, і створюємо складові індекси під конкретні патерни. Порядок колонок у складовому індексі: спочатку умови рівності, потім діапазонні та сортування.

Які антипатерни зустрічаються найчастіше?

-- Погано: SELECT * тягне зайві колонки; OFFSET збільшує навантаження; OR не використовує індекс; функція на колонці; NOT IN з NULL
SELECT * FROM products WHERE category_id = 5 LIMIT 50 OFFSET 10000;
SELECT * FROM users WHERE email = $1 OR phone = $1;
SELECT * FROM orders WHERE DATE(created_at) = $1;
SELECT * FROM products WHERE id NOT IN (SELECT product_id FROM order_items);

-- Добре: лише потрібні поля; keyset pagination; UNION ALL; range condition; NOT EXISTS
SELECT id, title, slug FROM products WHERE (created_at, id) > ($1, $2) ORDER BY created_at DESC LIMIT 50;
SELECT * FROM users WHERE email = $1 UNION ALL SELECT * FROM users WHERE phone = $1 LIMIT 1;
SELECT * FROM orders WHERE created_at >= $1 AND created_at < $2;
SELECT p.* FROM products p WHERE NOT EXISTS (SELECT 1 FROM order_items oi WHERE oi.product_id = p.id);

Порівняймо методи пагінації:

Keyset pagination в 100 разів ефективніша за OFFSET на великих даних.

Оптимізація 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 conditions (user_id = $1, status = 'completed'), потім range/sort (created_at DESC).

Налаштування work_mem та LATERAL

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

SET work_mem = '64MB';
-- Виконуємо важкий аналітичний запит

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

-- LATERAL: для row-dependent subqueries
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.

Наприклад, оптимізація 15 запитів може заощадити до $1500 на місяць на серверах.

Що входить в роботу з оптимізації

• Аудит 10–15 найважчих запитів через pg_stat_statements та EXPLAIN ANALYZE
• Переписування запитів з усуненням антипатернів
• Додавання та налаштування індексів (включаючи складові та часткові)
• Налаштування параметрів PostgreSQL (shared_buffers, work_mem, effective_cache_size)
• Надання звіту з вимірами до/після та рекомендаціями для команди
• Додатково: навчання розробників роботі з планами запитів

Команда має 7+ років досвіду в оптимізації PostgreSQL, понад 50 успішних проектів.

В одному з проєктів ми скоротили час виконання запитів з 4 секунд до 200 мс — це знизило навантаження на сервер та дозволило уникнути upgrade інфраструктури. Оптимізація 15 повільних запитів може вивільнити значну частину ресурсів сервера.

Строки та вартість

Діагностика та оптимізація 10–15 повільних запитів — 2–3 дні. Глибокий аудит схеми та запитів для високонавантаженого застосунку — 3–5 днів. Вартість розраховується індивідуально після оцінки обсягу.

Пропонуємо оптимізацію SQL-запитів під ключ: від діагностики до впровадження за 2–3 дні. Напишіть нам для безкоштовної оцінки вашого проекту. Для старту проєкту зв'яжіться з нами в Telegram або поштою — ми проведемо безкоштовний аналіз перших двох запитів. Замовте діагностику — і отримайте звіт з вимірами до/після. Ми гарантуємо вимірне зниження часу запитів не менш ніж на 30% — результат фіксуємо до та після оптимізації.

Послуги бекенд-розробки: production-grade надійність

На production-сервері о 3:14 ночі черга Laravel Jobs перестала оброблятися — 40 000 необроблених завдань у Redis. Причина: worker упав через memory leak у статичній змінній Eloquent observer, supervisor не перезапустив через misconfigured stopwaitsecs. Ми розбирали такий інцидент на проекті з 500 RPS: діагностика 4 години, фікс — 20 хвилин. Щоб ви не втрачали гроші, пропонуємо послуги бекенд-розробки з акцентом на production-grade надійність — 10+ років досвіду, 50+ проектів, 5 років на ринку. Оцінимо ваш проект за 2 дні.

Які проблеми вирішуємо

N+1 запити: головний вбивця швидкості

N+1 — найпоширеніша причина повільних сторінок у Laravel-додатках. Стандартна історія: сторінка працювала нормально на dev з 10 записами, на production з 10 000 — 8-секундне завантаження.

Laravel Debugbar у dev-оточенні показує кількість запитів. Більше 20 — сигнал для audit.

Model::preventLazyLoading(! app()->isProduction());

Telescope для профілювання: логує всі запити, jobs, mail, notifications з деталізацією. Після впровадження eager loading час завантаження сторінки падає з 8 с до 0.3 с — у 27 разів.

Memory leak у статичних змінних

У Laravel Octane або Swoole додаток тримається в пам’яті між запитами. Статичні змінні не скидаються — призводять до неконтрольованого росту пам’яті. Використовуємо defer-функції та контейнерні біндинги для коректного скидання стану.

Неправильний connection pool

Rails, Laravel, Django відкривають нове з'єднання PostgreSQL на кожен PHP/Python процес. 100 воркерів — 100 з'єднань. PostgreSQL деградує від 200+ активних з'єднань через overhead на управління.

PgBouncer у transaction pooling: 1000 воркерів → 20–50 реальних з'єднань. Це знижує latency на 40% та зменшує витрати на хостинг на 30% — при середній вартості хостингу $2,000/міс економить $600/міс. GIN-індекс для JSONB до 100 разів швидший за B-tree при пошуку.

Як Octane справляється з високим навантаженням?

Laravel Octane (RoadRunner або Swoole) прибирає overhead bootstrap на кожен HTTP-запит. Приріст: 3–8x на синтетичних бенчмарках, 2–4x на реальних додатках. Важливо: не зберігати стан у статичних змінних — застосовуємо це на проектах >1000 RPS.

Як PostgreSQL допомагає уникнути повільних запитів?

Використовуємо composite indexes для WHERE + ORDER BY, partial indexes для фільтрів з високою селективністю, GIN-індекси для JSONB та full-text search. to_tsvector + GIN замість LIKE '%query%' — запобігає seq scan навіть на мільйонах записів. Аналізуємо плани через EXPLAIN ANALYZE та pg_stat_statements.

Як обрати стек для вашого проекту?

Node.js виправданий для realtime: Laravel публікує події в Redis Pub/Sub, Node.js підписується та транслює клієнтам. Go — для goroutines (10k з'єднань на сервер — норма), але розробка повільніша, ніж Laravel.

Чому Redis критичний для продуктивності?

Redis виконує кілька ролей:

Redis Cluster для горизонтального масштабування, Sentinel для автоматичного failover. Замовте консультацію щодо оптимізації Redis для вашого проекту.

Що входить в роботу під ключ

• Архітектурне проектування (документація API, схема БД, діаграма сервісів)
• Реалізація за узгодженим ТЗ з code review
• Налаштування CI/CD (GitHub Actions, Docker), моніторингу (Sentry, Grafana), алертингу
• Навантажувальне тестування (k6, wrk) зі звітом
• Передача вихідних кодів, доступів, інструкція з деплою
• Навчання команди замовника (2–3 сесії)
• Гарантійна підтримка 1 місяць після здачі

Орієнтири по термінах

Вартість розраховується індивідуально після аналізу вимог до навантаження, інтеграцій та бізнес-логіки. Зв'яжіться з нами для безкоштовного аудиту вашого поточного backend — отримайте план оптимізації за 2 дні. Замовте консультацію та дізнайтеся, як знизити витрати на інфраструктуру на 30% без втрати продуктивності.