Оптимизация 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;