Оглавление

• Зачем и когда оптимизировать
• Как измерять: p95/p99, лог медленных запросов и pg_stat_statements
• Читаем планы: EXPLAIN (ANALYZE, BUFFERS) без боли
• Индексы: какие бывают и как выбирать
  • B-Tree
  • GIN и триграммы (поиск по подстроке и тексту)
  • GiST и гео/диапазоны
  • BRIN для «холодных» больших таблиц
  • Выражения, частичные и покрывающие индексы
• Кейс 1: топ-список заказов за неделю без полной сортировки
• Кейс 2: быстрый поиск товаров по подстроке и по смыслу
• Кейс 3: аналитика по датам и логи на миллиарды строк
• Типичные ошибки и как их избежать
• Процесс внедрения без простоев
• Как посчитать бизнес-эффект
• Чек-лист оптимизации

Зачем и когда оптимизировать

Тормоза в базе бьют по бизнесу: пользователи дольше ждут, конверсия падает, инфраструктура дорожает. Но «оптимизировать всё» — плохая стратегия. Нужна прицельная работа по запросам, которые реально влияют на опыт пользователей и счета за серверы.

Оптимизировать стоит, если:

• p95/p99 по ключевым эндпоинтам заметно выше целевого SLA;
• CPU и дисковые операции базы растут быстрее, чем выручка;
• резервные окна и фоновые задачи или реплика «захлёбываются» из-за тяжёлых сканов.

Как измерять: p95/p99, лог медленных запросов и pg_stat_statements

Измерения нужны до и после изменений.

1. Включаем лог медленных запросов и агрегатор запросов.

-- Включается в postgresql.conf или через ALTER SYSTEM (после — reload/restart при необходимости):
ALTER SYSTEM SET log_min_duration_statement = '200ms';
ALTER SYSTEM SET log_checkpoints = on;
ALTER SYSTEM SET log_lock_waits = on;
ALTER SYSTEM SET log_temp_files = 0; -- логируем внешние сортировки

-- Расширение для статистики часто/долго исполняемых запросов
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
ALTER SYSTEM SET shared_preload_libraries = 'pg_stat_statements'; -- нужен перезапуск
ALTER SYSTEM SET pg_stat_statements.max = 20000;
ALTER SYSTEM SET pg_stat_statements.track = all;

После перезапуска проверяем топ «тяжёлых» запросов:

SELECT
  query,
  calls,
  round(total_exec_time/1000, 2) AS total_sec,
  round(mean_exec_time, 2) AS mean_ms,
  round((total_exec_time/calls), 2) AS avg_ms,
  rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

2. Для «погружения» добавляем авто‑объяснение самых медленных запросов:

CREATE EXTENSION IF NOT EXISTS auto_explain;
ALTER SYSTEM SET shared_preload_libraries = 'pg_stat_statements,auto_explain';
ALTER SYSTEM SET auto_explain.log_min_duration = '200ms';
ALTER SYSTEM SET auto_explain.log_analyze = on;
ALTER SYSTEM SET auto_explain.log_buffers = on;
SELECT pg_reload_conf();

Читаем планы: EXPLAIN (ANALYZE, BUFFERS) без боли

Минимальный набор для диагностики:

• Seq Scan на миллионах строк — почти всегда красный флаг для OLTP.
• Sort с «Disk:» в планах — сортировка ушла на диск, увеличит задержки.
• Nested Loop, где внутренний узел дорогой — признак плохого индекса на условие соединения.
• HashAggregate/Hash Join со «Peak Memory Usage» большим, чем work_mem — будет пролив на диск.

Команда для чтения конкретного запроса:

EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT ...

Смотрите на:

• Actual Rows и Rows Removed by Filter — бесполезная работа.
• Buffers: shared read/hit — много read означает чтение с диска.
• Planning Time vs Execution Time — иногда проблема на стадии выбора плана (статистика).

Индексы: какие бывают и как выбирать

B-Tree

Универсальный тип по умолчанию. Подходит для равенств, диапазонов, сортировок. Допускает составные, покрывающие (INCLUDE) и частичные индексы.

GIN и триграммы (поиск по подстроке и тексту)

GIN эффективен для множеств/документов. С операторным классом gin_trgm_ops ускоряет LIKE/ILIKE с подстановками. Для полнотекстового — GIN по tsvector.

GiST и гео/диапазоны

Хорош для пространственных данных, интервалов, «похоже на» (fuzzy), но требует аккуратной настройки.

BRIN для «холодных» больших таблиц

Очень компактный индекс, который хранит «сводки» по блокам. Идеален, когда значения коррелируют с физическим порядком (логи, метрики по времени).

Выражения, частичные и покрывающие индексы

• Индекс по выражению ускоряет WHERE lower(email) = '...'.
• Частичный индекс (WHERE ...) уменьшает размер и улучшает селективность.
• INCLUDE добавляет столбцы только для покрытия SELECT/ORDER BY без влияния на порядок ключей.

Кейс 1: топ-список заказов за неделю без полной сортировки

Запрос продукта: показать последние оплаченные заказы за 7 дней, отсортировать по дате.

-- Пример запроса
SELECT id, user_id, total, created_at
FROM orders
WHERE status = 'paid' AND created_at >= now() - interval '7 days'
ORDER BY created_at DESC
LIMIT 50;

Симптомы в плане: Seq Scan по orders + Sort с проливом на диск.

Решение: частичный покрывающий индекс с направленным порядком по дате, чтобы избежать сортировки и лишнего чтения.

-- Индекс создаётся без простоя записи
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_orders_paid_created_at_desc
ON orders (created_at DESC)
INCLUDE (id, user_id, total)
WHERE status = 'paid';

Почему так:

• WHERE status = 'paid' делает индекс компактным и селективным.
• created_at DESC позволяет сканировать «с конца» без сортировки.
• INCLUDE покрывает выборку — возможен Index Only Scan (минимум I/O).

Проверка плана:

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, user_id, total, created_at
FROM orders
WHERE status = 'paid' AND created_at >= now() - interval '7 days'
ORDER BY created_at DESC
LIMIT 50;

Ожидаем: Index Only Scan Backward по idx_orders_paid_created_at_desc, без Sort.

Кейс 2: быстрый поиск товаров по подстроке и по смыслу

Запрос: искать товары по названию «iphone» или «чехол».

Наивно:

SELECT id, name
FROM products
WHERE name ILIKE '%iphone%'
LIMIT 20;

Это заставит читать всю таблицу. Фикс с триграммами:

-- Разово
CREATE EXTENSION IF NOT EXISTS pg_trgm;

-- Индекс для подстрочного поиска
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_products_name_trgm
ON products USING GIN (name gin_trgm_ops);

-- Теперь запрос использует GIN и работает за миллисекунды
SELECT id, name
FROM products
WHERE name ILIKE '%iphone%'
ORDER BY similarity(name, 'iphone') DESC
LIMIT 20;

Если нужен полнотекстовый поиск с учётом формы слова (русский язык):

-- Полнотекст
ALTER TABLE products ADD COLUMN IF NOT EXISTS name_tsv tsvector;

-- Обновляем tsvector и делаем триггер для актуальности
UPDATE products
SET name_tsv = to_tsvector('russian', coalesce(name,''));

CREATE FUNCTION products_tsv_update() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
  NEW.name_tsv := to_tsvector('russian', coalesce(NEW.name,''));
  RETURN NEW;
END;
$$;

CREATE TRIGGER products_tsv_update_trg
BEFORE INSERT OR UPDATE ON products
FOR EACH ROW EXECUTE FUNCTION products_tsv_update();

CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_products_name_tsv
ON products USING GIN (name_tsv);

-- Запрос
SELECT id, name
FROM products
WHERE name_tsv @@ plainto_tsquery('russian', 'чехол для iphone')
ORDER BY ts_rank(name_tsv, plainto_tsquery('russian', 'чехол для iphone')) DESC
LIMIT 20;

Кейс 3: аналитика по датам и логи на миллиарды строк

Таблица логов растёт километр в день. Сканы «за месяц» мучительны.

Шаги:

• BRIN по дате, чтобы быстро отсечь ненужные блоки.
• Партиционирование по месяцам, чтобы планировщик читал только нужные партиции.

-- BRIN-индекс
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_logs_created_at_brin
ON logs USING BRIN (created_at) WITH (pages_per_range = 128);

-- Партиционирование по диапазону дат
CREATE TABLE IF NOT EXISTS logs_partitioned (
  id bigserial PRIMARY KEY,
  created_at timestamptz NOT NULL,
  user_id bigint,
  payload jsonb
) PARTITION BY RANGE (created_at);

-- Ежемесячные партиции
CREATE TABLE IF NOT EXISTS logs_2026_04
  PARTITION OF logs_partitioned FOR VALUES FROM ('2026-04-01') TO ('2026-05-01');
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_logs_2026_04_brin
  ON logs_2026_04 USING BRIN (created_at) WITH (pages_per_range = 128);

-- Пример запроса с отсечением партиций и использованием BRIN
EXPLAIN (ANALYZE, BUFFERS)
SELECT date_trunc('day', created_at) AS d, count(*)
FROM logs_partitioned
WHERE created_at >= now() - interval '30 days'
GROUP BY 1
ORDER BY 1;

На больших данных такой подход сокращает чтение на порядки.

Типичные ошибки и как их избежать

• Неверный порядок полей в составном индексе. Первое поле — самое селективное условие или ключ сортировки, который реально используется.
• Функция на столбце в WHERE без индексного выражения. Решение: индекс по выражению, например (lower(email)).
• Несоответствие типов (text vs varchar vs citext; integer vs bigint). Порой ломает использование индекса. Приводите типы явно.
• Запрос с LIKE '%abc' в начале — обычный B-Tree бесполезен. Нужны триграммы.
• Устаревшая статистика. После массовых изменений данных выполняйте ANALYZE, настройте autovacuum.
• Плохие параметры планировщика: слишком высокий random_page_cost заставляет избегать индексов на SSD.
• Внешние сортировки. Увеличьте work_mem с умом (точечно для сессии), чтобы убрать «Disk:» в плане.

-- Точечно для одной сессии/задачи, не глобально
SET work_mem = '128MB';

Процесс внедрения без простоев

• План: измерить → переписать запрос/индекс → проверить план → нагрузочное тестирование → выкатывать поэтапно.
• Создавайте индексы без долгих блокировок записи:

CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_example ON t (col1, col2);
-- Удаление тоже без «стопа мира»
DROP INDEX CONCURRENTLY IF EXISTS idx_example;

• Следите за прогрессом:

SELECT * FROM pg_stat_progress_create_index;

• Снижайте риск: ставьте statement_timeout для миграции, чтобы случайно не подвесить прод.

SET LOCAL statement_timeout = '5min';

• Не забывайте про блоат: REINDEX CONCURRENTLY, если индекс разросся.

REINDEX INDEX CONCURRENTLY idx_example;

• Проверяйте планы на «боевых» кардинальностях. На маленьком стенде планировщик ведёт себя иначе.

Как посчитать бизнес-эффект

• До оптимизации: p95=800 мс по ключевому эндпоинту, 6 vCPU упираются в 85% загрузки днём.
• После индекса: p95=250 мс, CPU падает до 55%. Это даёт:
  • Больше пропускной способности без апгрейда.
  • Меньше инцидентов в пике (меньше очередей, таймаутов, ретраев).
  • Отсрочку покупки новых узлов/IOPS на диске — экономия в месяцах окупает время инженеров.

Подсказка: умножьте сэкономленные миллисекунды на количество запросов в пике — получите выигрыш в окнах продаж и маркетинговых кампаниях.

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

• Ясные цели: какие эндпоинты и SLA важны.
• Измерения включены: pg_stat_statements, лог медленных запросов, p95/p99 в APM.
• Для каждого медленного запроса: EXPLAIN (ANALYZE, BUFFERS), фиксация baseline.
• Проверьте тип данных и приведения, перепишите выражения так, чтобы индекс использовался.
• Выберите тип индекса осознанно: B-Tree/GIN/GiST/BRIN, частичный/выражение/INCLUDE.
• Учитывайте сортировку и покрытие: ORDER BY и SELECT должны бенефитить от индекса.
• Настройте autovacuum и статистику, актуализируйте ANALYZE после больших заливок.
• Внедряйте CONCURRENTLY, с контролем таймаутов и мониторингом прогресса.
• Сравните планы и метрики до/после, откатите, если регрессии.
• Заведите каталог «шаблонов индексов» для повторного использования в новых фичах.

---

Главное: оптимизация — это не магия, а понятный цикл «измерь → пойми план → добавь/исправь индекс → проверь → внедри». Пара правильных индексов и переписанный WHERE часто дают больший эффект, чем новый сервер. И это прямые деньги: меньше инфраструктуры, выше конверсия, спокойнее релизы.