Продвинутая оптимизация PostgreSQL и индексы

ОБЯЗАТЕЛЬНО

Инженеру Бэкенду

Раздел 8.11, шаг 2 из 12. Дальше — postgresql.conf.

---

EXPLAIN — чтение плана запроса

Планировщик PostgreSQL выбирает порядок join, тип scan и использование индексов на основе статистики (ANALYZE) и стоимостной модели (seq_page_cost, random_page_cost, cpu_* — см. шаг 3).

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT o.id, c.name
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.created_at >= '2025-01-01'
  AND o.status = 'paid';

Опция	Зачем
ANALYZE	Реальное выполнение + фактическое время и строки
BUFFERS	Попадания/промахи shared buffers и read
VERBOSE	Имена столбцов, фильтры
SETTINGS	Показать нестандартные GUC на время запроса

Типовые узлы плана:

Узел	Смысл
Seq Scan	Полный проход таблицы — норма для малых таблиц или слабой селективности
Index Scan	Обход B-tree + чтение heap
Index Only Scan	Данные только из индекса (нужен актуальный visibility map)
Bitmap Index Scan + Bitmap Heap Scan	Сбор TID из индекса, затем пакетное чтение heap
Nested Loop / Hash Join / Merge Join	Стратегии соединения

Сравнение rows и actual rows

Если rows=1000, а actual rows=500000 — статистика устарела или нужен более точный ANALYZE, увеличение default_statistics_target или extended statistics (CREATE STATISTICS).

---

B-tree — индекс по умолчанию

B-tree (USING btree, тип по умолчанию) подходит для =, <, >, BETWEEN, ORDER BY, LIKE 'prefix%'.

CREATE INDEX idx_orders_created ON orders (created_at DESC);
CREATE INDEX idx_orders_status_created ON orders (status, created_at);

Порядок столбцов в составном индексе важен — индекс (status, created_at) помогает WHERE status = 'paid', но хуже — только WHERE created_at > … без фильтра по status.

---

GiST и SP-GiST — геоданные и нестандартные типы

GiST (Generalized Search Tree) — семейство структур для сложных типов и операторов "пересечение / содержит / рядом":

• геометрия и PostGIS (&&, @, <->);
• полнотекстовый поиск (@@);
• диапазоны (daterange, int4range).

CREATE EXTENSION IF NOT EXISTS postgis;

CREATE INDEX idx_places_geom ON places USING gist (geom);

SELECT * FROM places
WHERE geom && ST_MakeEnvelope(37.5, 55.6, 37.8, 55.8, 4326);

SP-GiST (Space-Partitioned GiST) — для разреженных пространств: quad-tree, k-d tree. Используют для IP-адресов (inet), точек на карте с неравномерной плотностью.

Тип индекса	Типичный кейс
GiST	PostGIS, tsvector, ranges
SP-GiST	inet, point, text prefix (альтернатива btree для некоторых паттернов)

---

GIN — массивы, JSONB, полнотекст

GIN (Inverted Index) хранит ключ → список строк. Идеален, когда одно поле содержит много элементов:

• JSONB — поиск по ключам и @>;
• массивы — @>, &&;
• tsvector — полнотекст.

CREATE INDEX idx_events_payload ON events USING gin (payload jsonb_path_ops);
-- или default jsonb_ops — больше операторов, тяжелее индекс

SELECT * FROM events
WHERE payload @> '{"type": "payment", "status": "ok"}';

Подробный практикум JSONB — 3.07/66.

GIN тяжелее на запись, чем B-tree; для write-heavy таблиц оцените частоту фильтров.

---

BRIN — огромные последовательные таблицы

BRIN (Block Range INdex) хранит min/max (и др. summary) на диапазон блоков. Размер индекса — kilobytes на миллиарды строк, если данные физически коррелируют с ключом (логи по времени, IoT по timestamp).

CREATE INDEX idx_logs_ts ON access_logs USING brin (logged_at)
WITH (pages_per_range = 128);

BRIN бесполезен, если строки случайно разбросаны по таблице после массовых UPDATE.

---

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

Partial index индексирует только строки, удоворствующие WHERE:

CREATE INDEX idx_orders_open ON orders (customer_id, created_at)
WHERE status IN ('new', 'processing');

Плюсы — меньше размер, быстрее обновление, точнее статистика для "горячего" подмножества. Запрос должен повторять условие (или его следствие), иначе индекс не выберут.

---

CTE и планировщик в PostgreSQL 12+

Начиная с версии 12 планировщик PostgreSQL может встроить блок WITH в основной SELECT. Тогда CTE исчезает из плана выполнения, а фильтры из внешнего запроса применяются прямо к исходным таблицам.

Когда добавлять подсказку

• AS MATERIALIZED — дорогой подзапрос нельзя пересчитывать; нужен один проход по данным и узел CTE Scan в плане.
• AS NOT MATERIALIZED — лёгкий CTE, выгодно встроить фильтры внутрь подзапроса.

WITH day_events AS MATERIALIZED (
  SELECT * FROM huge_events
  WHERE created_at >= now() - interval '1 day'
)
SELECT COUNT(*) FROM day_events;

В EXPLAIN ищите узел CTE Scan. Если его нет, планировщик, скорее всего, встроил CTE. Сравните время и Buffers с вариантом без MATERIALIZED — на больших таблицах разница иногда достигает десятков раз.

Полный разбор терминов, истории версий и примеров — CTE, материализация в PostgreSQL. Базовый синтаксис CTE — общие табличные выражения.

---

Другие приёмы

Приём	Пример
Covering index (INCLUDE)	CREATE INDEX … ON t (a) INCLUDE (b, c); — index-only scan без heap
Expression index	CREATE INDEX ON users (lower(email));
Concurrent build	CREATE INDEX CONCURRENTLY — без блокировки записи (дольше, два прохода)

---

Типовые ошибки

1. Индекс на каждый столбец — планировщик не склеит их в один; нужен составной индекс под реальный WHERE + ORDER BY.
2. Функция на столбце без expression index — WHERE lower(email) = … не использует btree на email.
3. OR across columns — часто seq scan; перепишите в UNION или используйте partial indexes.
4. OFFSET 100000 — линейная стоимость; keyset pagination (WHERE id > $last).
5. N+1 из ORM — сотни мелких запросов; batch + join или WHERE id = ANY($1).

---

Практика

1. Возьмите медленный запрос из production (или synth 1M строк), снимите EXPLAIN (ANALYZE, BUFFERS).
2. Добавьте индекс, переснимите план — сравните Buffers: shared hit/read.
3. Создайте BRIN на time-series и GIN на JSONB — сравните размер индексов через pg_indexes_size.

---

Связанные материалы

• Конфигурация postgresql.conf — work_mem, random_page_cost.
• Оптимизация SQL и сложные индексы — теория в разделе 3.07.
• phpPgAdmin — SQL-вкладка — запуск EXPLAIN ANALYZE в lab.
• Профилирование — auto_explain, pgBadger.
• Оконные функции — оптимизация аналитических запросов.

---