Оптимізація PostgreSQL-запитів: EXPLAIN ANALYZE, індекси та планувальник

Оптимізація PostgreSQL-запитів (EXPLAIN ANALYZE, індекси, планувальник)

Повільні запити в PostgreSQL — головний біль будь-якого DevOps-інженера. Запит, який працював півсекунди при 100k рядків, починає висіти по 8 секунд при 2 млн — і всі дивляться на розробника. При цьому сам запит правильний, схема нормальна, залізо не змінилося. Справа в планувальнику PostgreSQL, який обирав один план при маленькій таблиці та переключився на інший при зростанні даних. Або в тому, що ANALYZE не запускався місяць, і статистика застаріла. Або в тому, що індекс є, але планувальник його не використовує через неправильний statistics target. Ми стикалися з цими проблемами десятки разів і розробили системний підхід до оптимізації. Наші інженери з 10+ річним досвідом гарантують зниження часу відповіді бази даних до 50% та зменшення навантаження на сервер. Оцінимо ваш проект: пишіть, отримайте консультацію.

Як читати план EXPLAIN ANALYZE правильно?

EXPLAIN показує план без виконання запиту. EXPLAIN ANALYZE — реально виконує та показує фактичні vs очікувані рядки. Різниця між ними — ключ до діагностики.

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT u.email, COUNT(o.id)
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE u.created_at > CURRENT_DATE - INTERVAL '1 year'
GROUP BY u.email;

Прапор BUFFERS додає інформацію про потрапляння в shared_buffers та звернення до диска — критично для розуміння, наскільки запит навантажує I/O. Читати план знизу вгору: нижні вузли виконуються першими.

На що дивитися в першу чергу:

Rows estimation mismatch. Якщо планувальник очікував 10 рядків (rows=10), а отримав 50000 (actual rows=50000) — статистика застаріла або statistics_target занадто малий. Це змушує планувальник обирати Nested Loop там, де потрібен Hash Join.

-- подивитися статистику та збільшити деталізацію
SELECT n_distinct, correlation, most_common_vals
FROM pg_stats
WHERE tablename = 'orders' AND attname = 'status';

ALTER TABLE orders ALTER COLUMN status SET STATISTICS 500;
ANALYZE orders;

Sequential Scan на великій таблиці. Не завжди погано — якщо запит повертає >5-10% рядків таблиці, Seq Scan дешевший за Index Scan. Але якщо returning rows мало, а Seq Scan все одно обирається — швидше за все, індексу немає або він не покриває умову.

Hash Join з великим Batches. Якщо Batches > 1 в Hash Join — хеш-таблиця не влізла в work_mem і спилилася на диск. Рішення: SET work_mem = '64MB' для важких аналітичних запитів (не глобально — це з'їсть всю пам'ять при конкурентних сесіях).

Чому планувальник обирає неправильний план?

PostgreSQL використовує cost-based optimizer. Вартість операцій задається параметрами в postgresql.conf. Найчастіший випадок некоректного плану — застарілі параметри вартості для SSD:

# Для SSD знижуємо random_page_cost (default=4.0)
random_page_cost = 1.1
effective_cache_size = 12GB # ~75% від RAM сервера

З random_page_cost = 4.0 планувальник вважає випадковий доступ до диска дорогим і надає перевагу Seq Scan навіть там, де Index Scan логічніший. На SSD це призводить до неправильних рішень. > PostgreSQL Documentation: random_page_cost має бути знижений для SSD.

Якщо після збору статистики та виправлення параметрів запит все одно бере поганий план — можна зафіксувати його через pg_hint_plan (розширення) або через параметри сесії:

-- примусово відключити Nested Loop для поточної сесії
SET enable_nestloop = off;
EXPLAIN ANALYZE SELECT ...;
SET enable_nestloop = on;

Це інструмент діагностики, не постійне рішення. Постійне відключення оператора планувальника ламає інші запити.

Індекси: які, коли та навіщо

Стандартний B-tree індекс працює для рівності та діапазонів на звичайних типах. Для специфічних завдань потрібні інші:

-- частковий індекс: індексуємо тільки активні замовлення
CREATE INDEX idx_orders_active ON orders(created_at) WHERE status = 'active';

-- складений індекс: порядок колонок важливий
-- працює для WHERE user_id = ? AND status = ?
-- і для WHERE user_id = ? (перша колонка)
-- НЕ працює для WHERE status = ? (тільки друга колонка)
CREATE INDEX idx_orders_user_status ON orders(user_id, status);

-- covering index: включаємо потрібні колонки, уникаючи heap fetch
CREATE INDEX idx_orders_covering ON orders(user_id) INCLUDE (total_amount, created_at);

-- GIN для JSONB
CREATE INDEX idx_events_payload ON events USING GIN(payload jsonb_path_ops);

Covering index (з INCLUDE) особливо корисний для часто виконуваних запитів, які читають 2-3 конкретних поля. Планувальник робить Index Only Scan — дані беруться прямо з індексу, без звернення до heap.

Розпухлі індекси — окрема проблема. Після масових UPDATE/DELETE індекси накопичують мертві сторінки. pgstatindex покаже bloat:

SELECT * FROM pgstatindex('idx_orders_user_status'); -- leaf_fragmentation > 30% → пора REINDEX CONCURRENTLY 

REINDEX CONCURRENTLY не блокує читання та запис — на відміну від звичайного REINDEX.

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE status = 'pending'; 

SELECT * FROM products WHERE category_id = 5 AND is_active = true ORDER BY created_at DESC LIMIT 20 OFFSET 10000;