Query Tuning: от медленного к быстрому

Команда Uber в 2016 году переехала с PostgreSQL на MySQL - и одной из причин назвали накопившиеся медленные запросы. Но большинство проблем были диагностированы постфактум: N+1 в ORM, implicit casts, устаревшая статистика после bulk-загрузок. Инструменты для поиска этих проблем существовали всегда - просто никто не смотрел в pg_stat_statements.

• **Gitlab** публично задокументировал свой процесс query tuning: каждый MR автоматически запускает EXPLAIN на затронутых таблицах и блокирует мерж если появляется Seq Scan на таблице >10k строк.
• **Notion** обнаружил, что их главная страница делала 47 SQL-запросов из-за N+1 в GraphQL резолверах. Переход на DataLoader сократил это до 3 запросов и ускорил страницу на 400 мс.
• **pg_stat_statements** в связке с Grafana - стандартный стек мониторинга: дашборд обновляется каждые 60 секунд и показывает деградирующие запросы до того как пользователи замечают проблему.

Антипаттерны медленных запросов

90% медленных запросов имеют одну из пяти причин: функция в WHERE по индексированной колонке, LIKE с ведущим wildcard, SELECT *, условие OR вместо UNION, коррелированный подзапрос. Каждый из этих паттернов убивает индексы и вынуждает планировщик делать Seq Scan на всей таблице.

**Убийцы индексов:** `WHERE UPPER(email) = 'TEST@EXAMPLE.COM'` - функция делает индекс на `email` бесполезным (нужен functional index). `WHERE name LIKE '%smith%'` - ведущий wildcard = Seq Scan (нужен pg_trgm + GIN). `WHERE status != 'active'` - негативные условия обычно не используют индексы. `WHERE created_at::date = '2024-01-01'` - cast делает индекс на `created_at` бесполезным.

N+1: самая дорогая ошибка ORM

N+1 - это когда для загрузки списка из N объектов выполняется N+1 запросов: один для списка и по одному для каждой связанной записи. При N=1000 это 1001 round-trip к базе. На локальной машине незаметно, на продакшне с сетевой задержкой 1 мс - 1 секунда только на сетевые туры.

**Признаки N+1 в логах:** повторяющийся запрос с разными параметрами (`WHERE id = $1` с разными значениями). **Решение в ORM (TypeORM):** `relations: ['author']` или `leftJoinAndSelect`. **Решение на SQL:** JOIN или `WHERE id IN (...)`. **Для глубоких иерархий:** рекурсивный CTE (`WITH RECURSIVE`). **Паттерн DataLoader** (GraphQL): группирует N запросов в один batch за один tick event loop.

Implicit Casts: когда тип убивает индекс

Неявное приведение типов - коварная ловушка: запрос выглядит правильно, возвращает верные данные, но индекс не используется. PostgreSQL строго типизирован: если колонка `bigint`, а параметр `varchar`, планировщик не может применить B-tree индекс без explicit cast. ORM-библиотеки или драйверы иногда передают параметры неверного типа.

**Типичные случаи:** колонка `user_id bigint`, параметр передан как строка `'42'` - Seq Scan. Колонка `status varchar`, сравнение с `text` - обычно ок. Колонка `phone varchar(20)`, параметр `char(20)` с пробелами - неявный cast. **Диагностика:** `EXPLAIN` покажет `Filter` вместо `Index Cond` - значит индекс не используется в фильтрации. **Решение:** explicit cast в запросе или исправление типа параметра на стороне приложения.

Статистика: когда планировщик ошибается

Планировщик PostgreSQL работает с оценками, а не с реальными данными. Оценки строятся на статистике - гистограммах распределения значений, собираемых ANALYZE. Устаревшая или неточная статистика - причина 40% проблем с планами запросов в production. После масштабных INSERT/DELETE/UPDATE статистика устаревает мгновенно.

**autovacuum и статистика:** autovacuum автоматически запускает ANALYZE при изменении >20% строк таблицы (threshold). Для горячих таблиц с частыми обновлениями это может быть недостаточно. **Настройка:** `ALTER TABLE hot_table SET (autovacuum_analyze_scale_factor = 0.01)` - запускать при изменении 1% вместо 20%. **Skewed данные:** значение с частотой >5% попадает в Most Common Values - статистика точная. Остаток - гистограмма из N бакетов (default 100). Для skewed колонок - `SET STATISTICS 500`.

pg_stat_statements: найти самые дорогие запросы

Нельзя оптимизировать то, что не измеряешь. `pg_stat_statements` - расширение PostgreSQL, которое агрегирует статистику по всем выполненным запросам: total_time, calls, mean_time, rows. Это первый инструмент диагностики в любом production PostgreSQL-сервере.

**Что показывает pg_stat_statements:** query (нормализованный - параметры заменены на $1, $2), calls, total_exec_time, mean_exec_time, stddev_exec_time, rows, shared_blks_hit/read (cache hit ratio). **Топ-кандидаты на оптимизацию:** (1) максимальный total_exec_time - запрос тратит больше всего CPU времени суммарно, (2) максимальный mean_exec_time - каждый вызов медленный, (3) низкий cache hit ratio (blks_hit / (blks_hit + blks_read)) - запрос читает много с диска.

Ключевые идеи

• **Функции в WHERE** по индексированной колонке делают индекс бесполезным - нужен functional index или переписанное условие с range filter.
• **N+1** - архитектурная проблема ORM: использовать JOIN через relations или batch-загрузку через WHERE id IN (...). **Implicit cast** - тип параметра не совпадает с типом колонки = Seq Scan вместо Index Scan.
• **pg_stat_statements** - первый инструмент диагностики: сортировать по total_exec_time, брать топ-3, запускать EXPLAIN ANALYZE, исправлять. Повторять каждую неделю.

Связанные темы

Query Tuning применяет знания о планах запросов и индексах:

• EXPLAIN ANALYZE: читаем план запроса — Базовые инструменты анализа плана, используемые при tuning
• Транзакции и изоляция — MVCC и bloat таблицы влияют на производительность запросов

Вопросы для размышления

• Как обнаружить N+1 проблему в production без доступа к коду приложения?
• Когда implicit cast не является проблемой и PostgreSQL всё равно использует индекс?
• Каков оптимальный порог для autovacuum_analyze_scale_factor для таблицы с 100 млн строк и 10 000 INSERT в секунду?

Связанные уроки

• alg-01-big-o