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

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

Повільний запит у продакшені — це конкретна причина деградації: повне сканування таблиці з 50 мільйонів рядків, сортування без індексу, декартів добуток таблиць через відсутнє JOIN-умовлення. EXPLAIN ANALYZE показує, що PostgreSQL насправді робить — не те, що, на думку планувальника, він зробить, а те, що насправді сталося під час виконання.

Як читати 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 = 'UA'
  AND o.created_at > '2025-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 > '2025-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 = 'UA')

Що ми бачимо та що з цим робити:

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

-- postgresql.conf
-- shared_preload_libraries = 'pg_stat_statements'
-- pg_stat_statements.max = 10000
-- pg_stat_statements.track = all

-- Топ за загальним часом (найважливіше)
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();

Патерни повільних запитів та рішення

Seq Scan на великій таблиці

-- Повільно: повне сканування
SELECT * FROM orders WHERE status = 'pending';
-- EXPLAIN: Seq Scan on orders (rows=5000000) Filter: status='pending'

-- Рішення: індекс
CREATE INDEX CONCURRENTLY idx_orders_status ON orders(status)
  WHERE status IN ('pending', 'processing');
-- Частковий індекс — тільки для активних статусів, менший розмір

-- Ще краще: покриваючий індекс (не звертається до таблиці)
CREATE INDEX CONCURRENTLY idx_orders_status_cover
  ON orders(status, created_at DESC)
  INCLUDE (id, user_id, total_amount)
  WHERE status IN ('pending', 'processing');

Неефективний JOIN

-- Повільно: JOIN без індексу на orders.user_id
SELECT u.name, o.total
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE u.registered_at > '2025-01-01';

-- EXPLAIN: Hash Join ... Seq Scan on orders (rows=5000000)

-- Рішення
CREATE INDEX CONCURRENTLY idx_orders_user_id ON orders(user_id);
-- Після: Hash Join ... Index Scan on orders

Запит N+1 в ORM

Це не SQL-проблема, а архітектурна, але проявляється як повільні запити:

-- N+1: 1 запит для списку + N запитів для кожного пов'язаного об'єкта
-- У pg_stat_statements: простий SELECT виконаний 10000 разів за секунду

-- У Eloquent: було
$orders = Order::all();
foreach ($orders as $order) {
    echo $order->user->name; // N запитів до users
}

-- Стало: eager loading
$orders = Order::with('user:id,name')->get();
-- Один JOIN замість N запитів

Сортування без індексу

-- Повільно: Sort Method: external merge Disk: 45678kB
SELECT * FROM events ORDER BY created_at DESC LIMIT 100;

-- EXPLAIN: Sort ... actual time=3400ms
-- Сортування через тимчасовий файл на диску

-- Рішення: індекс за полем сортування
CREATE INDEX CONCURRENTLY idx_events_created_at ON events(created_at DESC);
-- Після: Index Scan Backward — 0.3ms

LIKE з префіксним wildcard

-- B-Tree індекс не працює для LIKE '%текст%'
SELECT * FROM products WHERE name LIKE '%телефон%';
-- EXPLAIN: Seq Scan, Filter: name LIKE '%телефон%'

-- Рішення 1: pg_trgm для будь-якого LIKE
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX CONCURRENTLY idx_products_name_trgm
  ON products USING gin(name gin_trgm_ops);
-- Тепер LIKE '%телефон%' використовує індекс

-- Рішення 2: повнотекстовий пошук для текстових запитів
ALTER TABLE products ADD COLUMN search_vector tsvector;
CREATE INDEX idx_products_fts ON products USING gin(search_vector);
UPDATE products SET search_vector = to_tsvector('ukrainian', name || ' ' || description);

-- Запит:
SELECT * FROM products
WHERE search_vector @@ plainto_tsquery('ukrainian', 'телефон Samsung');

Функція на індексованій колонці

-- Погано: функція на колонці вимикає використання індексу
SELECT * FROM orders WHERE DATE(created_at) = '2025-01-15';
-- EXPLAIN: Seq Scan (функція DATE() застосовується до кожного рядка)

-- Добре: діапазон без функції
SELECT * FROM orders
WHERE created_at >= '2025-01-15 00:00:00'
  AND created_at <  '2025-01-16 00:00:00';
-- EXPLAIN: Index Scan (використовує індекс на created_at)

-- Або: функціональний індекс
CREATE INDEX idx_orders_date ON orders(DATE(created_at));
SELECT * FROM orders WHERE DATE(created_at) = '2025-01-15';

Інструменти аналізу

# auto_explain: автоматично логувати плани повільних запитів
# postgresql.conf:
# shared_preload_libraries = 'pg_stat_statements,auto_explain'
# auto_explain.log_min_duration = 1000  # ms
# auto_explain.log_analyze = true
# auto_explain.log_buffers = true

# Візуалізація плану: https://explain.dalibo.com або https://explain.depesz.com
# Копіювати вивід EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) — отримати візуальне дерево

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

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() — скинути та спостерігати нову статистику

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