Оптимизация запросов PostgreSQL

От понимания планов к эффективным индексам

PostgreSQL — одна из лучших СУБД на рынке с точки зрения оптимизатора запросов. Но даже лучший оптимизатор не поможет, если не понимать, как он работает. Разберём путь запроса от текста до результата и научимся читать планы выполнения.

На основе книги «Оптимизация запросов в PostgreSQL» (Домбровская, Новиков, Бейликова) и практического опыта работы с высоконагруженными системами.
Этапы выполнения запроса Как считается стоимость Селективность и статистика Индексы: когда использовать Читаем EXPLAIN ANALYZE Оптимизация коротких запросов Оптимизация длинных запросов Чек-лист оптимизации

Этапы выполнения запроса

Каждый SQL-запрос проходит три этапа: разбор, планирование, выполнение.

1. Разбор (Parsing)

2. Планирование (Query Planning)

Оптимизатор строит множество возможных планов и оценивает их стоимость:

Выбирается план с минимальной оценочной стоимостью. PostgreSQL не имеет хинтов для оптимизатора — он сам принимает решения на основе статистики.

3. Выполнение (Execution)

План представляет собой дерево операций. Узлы выполняются снизу вверх, данные передаются от детей к родителям.

Ключевой принцип: пишите декларативно. Вложенные CTE с конструкцией WITH — это императивный стиль. JOIN позволяет базе самостоятельно определять способ извлечения данных.

Как считается стоимость

Стоимость (cost) — абстрактная единица, отражающая ресурсы (диск, память, CPU) для выполнения операции. 

Total Cost = Startup Cost + Run Cost

Ключевые параметры конфигурации

Параметр По умолчанию Описание
seq_page_cost 1.0 Стоимость последовательного чтения страницы
random_page_cost 4.0 Стоимость случайного доступа (обычно дороже из-за seek)
cpu_tuple_cost 0.01 Стоимость обработки одной строки
cpu_index_tuple_cost 0.005 Стоимость обработки строки индекса
work_mem 4MB Память для сортировок и хеш-таблиц

Типы сканирования

Селективность и статистика

Селективность — доля строк, которую выбирает условие: 

Селективность = Количество строк, удовлетворяющих условию / Общее количество строк

Правило: индекс обычно эффективен для выборок не более 2-5% таблицы.

Откуда берётся статистика

PostgreSQL собирает статистику командой ANALYZE и хранит её в pg_statistic:

Важно: при обновлении PostgreSQL статистика сбрасывается. После апгрейда обязательно запустите ANALYZE на всех таблицах.

Индексы: когда использовать

B-tree индексы

Основной тип индекса в PostgreSQL. Только листья хранят ссылки на строки. Узлы хранят наименьший ключ следующего уровня. Сложность поиска — O(log n).

Виды индексов по применению

Составные индексы

CREATE INDEX idx_name ON account (last_name, first_name, middle_name);

Будет использоваться для запросов по last_name, last_name + first_name, last_name + first_name + middle_name. Но не будет использоваться для запросов только по first_name.

Индексы по выражениям

-- Обычный индекс не поможет для lower()
SELECT * FROM account WHERE lower(last_name) = 'smith';

-- Нужен индекс по выражению
CREATE INDEX idx_lower_name ON account (lower(last_name));

Покрывающие индексы

CREATE INDEX idx_name ON account (last_name) INCLUDE (phone, email);

Дополнительные колонки в INCLUDE позволяют выполнить Index Only Scan без обращения к таблице.

Частичные индексы

CREATE INDEX idx_active ON orders (created_at) WHERE status = 'pending';

Индексирует только часть таблицы — меньше размер, выше селективность.

Когда индексы невыгодны

Читаем EXPLAIN ANALYZE

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE order_date >= '2024-01-01';

Ключевые метрики

Метрика Описание
cost=0.00..1000.00 Startup cost..Total cost (оценка)
rows=50000 Ожидаемое количество строк (оценка)
actual time=0.012..15.034 Реальное время выполнения в мс
actual rows=48000 Реальное количество строк
loops=1 Сколько раз выполнялся узел
Buffers: shared hit=100 read=50 Страницы из кеша / с диска

На что обращать внимание

Оптимизация коротких запросов

Короткие запросы (OLTP) — используют небольшое количество данных. Цель: минимизировать количество чтений через индексы.

Стратегия

  1. Найти самый ограничивающий критерий в запросе
  2. Проверить, есть ли индекс для этого критерия
  3. Рассмотреть составной индекс для нескольких условий
  4. Добавить покрывающие колонки для Index Only Scan

Мониторинг использования индексов

SELECT relname, idx_scan, seq_scan, idx_tup_read
FROM pg_stat_all_indexes
WHERE schemaname = 'public'
ORDER BY idx_scan DESC;

Следите за неиспользуемыми индексами — они замедляют запись без пользы.

Оптимизация длинных запросов

Длинные запросы (OLAP) — высокая селективность, читают значительную часть таблиц.

Стратегии

CTE: материализация vs инлайн

С PostgreSQL 12 CTE по умолчанию могут инлайниться. Для контроля используйте: 

-- Принудительная материализация
WITH cte AS MATERIALIZED (
  SELECT * FROM large_table WHERE condition
)
SELECT * FROM cte WHERE other_condition;

-- Принудительный инлайн
WITH cte AS NOT MATERIALIZED (
  SELECT * FROM large_table WHERE condition
)
SELECT * FROM cte WHERE other_condition;

Материализованные представления

Для тяжёлых аналитических запросов рассмотрите Materialized Views: 

CREATE MATERIALIZED VIEW monthly_sales AS
SELECT product_id, SUM(amount) AS total
FROM sales
WHERE sale_date >= DATE_TRUNC('month', CURRENT_DATE)
GROUP BY product_id;

-- Обновление (можно по расписанию)
REFRESH MATERIALIZED VIEW CONCURRENTLY monthly_sales;

Чек-лист оптимизации

Перед оптимизацией

Анализ плана

Типичные решения

Проблема Решение
Seq Scan вместо Index Scan Создать индекс, проверить селективность
Ошибки в оценке rows ANALYZE, увеличить default_statistics_target
Медленная сортировка Увеличить work_mem, создать индекс
Nested Loop на больших данных Добавить индекс на ключ соединения
Индекс не используется для функции Создать индекс по выражению
Связанные материалы: