Оптимізація повільних 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); 

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

-- Додаємо складовий індекс для типового фільтра 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; 

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

-- 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;