Производительность БД: планы запросов, индексы, пул соединений, N+1

В подавляющем большинстве бэкендов, которые вы будете чинить, ответ на вопрос «куда ушло время» звучит одинаково: в базу. Не потому, что базы медленные — они как раз чудовищно быстры, — а потому, что база единственное место в системе, где встречаются диск, сеть, конкурентный доступ, глобальное состояние и оптимизатор, принимающий решения за вас. Четыре из пяти этих факторов ведут себя нелинейно, и любой из них умеет за одну ночь превратить запрос с временем 3 мс в запрос с временем 3 с при неизменном коде.

Дисциплина здесь та же, что и во всём треке: https://courses.digitable.life/post/performance/01-measuring/ научил нас смотреть на перцентили, а не на средние, https://courses.digitable.life/post/performance/02-benchmarking/ — не верить одиночному замеру, https://courses.digitable.life/post/performance/03-cpu-profiling/ — брать прибор, а не интуицию. Разница в том, что у базы данных собственный набор приборов, и главный из них — не профайлер CPU. Профиль CPU процесса postgres почти всегда покажет вам одно и то же (сравнение ключей в B-tree, разбор строк), и это будет бесполезная правда.

Ключевая мысль статьи: производительность БД — это не «скорость SQL», а четыре независимые подсистемы, каждая со своим прибором. План запроса, доступ к данным (индексы и кэш), очередь за соединением и число round-trip’ов из приложения. Ломается любая из четырёх, симптом при этом у всех один — «база тормозит», а лечение диаметрально разное. Сначала научимся различать.

Схема выше — карта на всю статью. Обратите внимание на её правый нижний угол: слои «очередь пула», «RTT», «ожидание блокировки» и «выгрузка результата» не видны в EXPLAIN вообще. Именно поэтому типичное расследование «я посмотрел план, там всё хорошо, но эндпоинт медленный» заходит в тупик.

1. Прибор №0: где вообще теряется время

Прежде чем открывать SQL, ответьте на три вопроса числами. Каждый отсекает целый класс гипотез.

1. Какая доля времени запроса приходится на БД? Берётся из трассировки: сумма длительностей span’ов вида db.query внутри HTTP-span’а. Если это 15% — не трогайте базу, идите в https://courses.digitable.life/post/performance/03-cpu-profiling/.
2. Сколько запросов делается на один HTTP-запрос? Число span’ов. Один — работаем с планом. Сорок — работаем с N+1, и план каждого из сорока может быть идеальным.
3. Сколько времени поток ждал соединения? Метрика пула, а не базы. Если ждал 200 мс из 240 — база вообще ни при чём, она перегружена или у вас утечка соединений.

Эта схема — не украшение, а протокол. Каждая ветка ниже разобрана отдельным разделом.

2. pg_stat_statements: профайлер нагрузки, а не запроса

EXPLAIN отвечает на вопрос «почему медленный вот этот запрос». Он не отвечает на вопрос «какой запрос вообще стоит чинить». Для второго нужен агрегат по всей нагрузке — pg_stat_statements, который нормализует запросы (заменяет литералы на плейсхолдеры) и накапливает по каждому классу счётчики: число вызовов, суммарное и среднее время, число прочитанных и попавших в кэш блоков.

По сути это сэмплирующий профайлер уровня СУБД, только вместо стеков вызовов у него классы запросов. И читать его надо ровно с той же дисциплиной, что и flame graph: смотреть на суммарное время, а не на среднее.

-- «Кто съел базу»: топ по суммарному времени, а не по среднему.
SELECT
    substr(regexp_replace(query, '\s+', ' ', 'g'), 1, 60) AS q,
    calls,
    round(total_exec_time::numeric)                       AS total_ms,
    round(mean_exec_time::numeric, 2)                     AS mean_ms,
    round((100 * total_exec_time / sum(total_exec_time) OVER ())::numeric, 1) AS pct,
    rows / greatest(calls, 1)                             AS rows_per_call,
    round(100.0 * shared_blks_hit
          / nullif(shared_blks_hit + shared_blks_read, 0), 1) AS cache_hit_pct
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

                    q                     | calls  | total_ms | mean_ms | pct  | rows_per_call | cache_hit_pct
------------------------------------------+--------+----------+---------+------+---------------+---------------
 SELECT * FROM users WHERE id = $1        | 8412330|  1180244 |    0.14 | 41.2 |             1 |          99.9
 SELECT o.id, o.created_at, o.total_cents |   14201|   744109 |   52.40 | 26.0 |            20 |          61.3
 UPDATE sessions SET last_seen = $1 WHERE |  902114|   402388 |    0.45 | 14.1 |             1 |          99.8
 SELECT count(*) FROM orders WHERE status |    2044|   198871 |   97.30 |  6.9 |             1 |          33.0

Первая строка — весь урок этой статьи в одной таблице. Запрос со средним временем 0,14 мс съедает 41% всего времени базы. Ни в одном логе медленных запросов он не появится, ни один EXPLAIN по нему не покажет ничего плохого: план идеален, кэш-хит 99,9%. Проблема в том, что его зовут восемь миллионов раз — это N+1 из раздела 7. Оптимизировать его SQL бессмысленно, надо убрать вызовы.

Что важно знать про этот прибор, чтобы он вас не обманул:

• Счётчики кумулятивные. Снимайте два снимка с интервалом и вычитайте, иначе увидите усреднение за месяц, включая ночную миграцию. Практика: писать снапшот в таблицу раз в минуту и смотреть дельты.
• Ошибка выжившего. Запросы, убитые по statement_timeout или отменённые клиентом, в статистику успешных не попадают в привычном виде — если у вас агрессивный таймаут, «улучшение метрик» может означать, что тяжёлые запросы просто перестали доживать до конца. Всегда смотрите рядом на счётчик ошибок и отмен.
• Нормализация теряет параметры. Один класс WHERE user_id = $1 может исполняться и за 0,1 мс, и за 4 с — в зависимости от того, у кого 3 заказа, а у кого 400 тысяч. Среднее по такому классу бессмысленно; с PostgreSQL 17 доступны перцентили не из коробки, поэтому берите min_exec_time/max_exec_time/stddev_exec_time и подозревайте бимодальность (о ней подробно в https://courses.digitable.life/post/performance/01-measuring/).
• pg_stat_statements.track = all, иначе запросы внутри функций и триггеров учитываются как один внешний вызов, и вы будете чинить не то.
• Ограничение pg_stat_statements.max (по умолчанию 5000) — при вытеснении редких запросов вы теряете длинный хвост. Для приложений, генерирующих SQL динамически, это реальная проблема.

Аналоги в других СУБД: в MySQL — performance_schema.events_statements_summary_by_digest и удобная обёртка sys.statement_analysis, плюс slow query log с long_query_time = 0 на время расследования; в Oracle и в Amazon RDS Performance Insights — Active Session History, которая идёт ещё дальше и даёт профиль по событиям ожидания. Про сами СУБД и их различия — трек «Базы данных», в частности https://courses.digitable.life/post/databases/02-postgresql/.

3. EXPLAIN ANALYZE: как читать, чтобы не обмануться

Схема данных для всех примеров ниже — обычный интернет-магазин:

Восемь миллионов заказов, миллион пользователей. Запрос — «последние 20 оплаченных заказов пользователей из Германии»:

EXPLAIN (ANALYZE, BUFFERS, SETTINGS)
SELECT o.id, o.created_at, o.total_cents, u.email
FROM orders o
JOIN users u ON u.id = o.user_id
WHERE u.country = 'DE' AND o.status = 'paid'
ORDER BY o.created_at DESC
LIMIT 20;

Limit  (cost=185432.11..185434.45 rows=20 width=48) (actual time=2841.702..2841.719 rows=20 loops=1)
  Buffers: shared hit=12844 read=98211
  ->  Sort  (cost=185432.11..186119.63 rows=275008 width=48) (actual time=2841.700..2841.712 rows=20 loops=1)
        Sort Key: o.created_at DESC
        Sort Method: top-N heapsort  Memory: 27kB
        ->  Hash Join  (cost=4812.00..178104.77 rows=275008 width=48) (actual time=61.204..2698.331 rows=248913 loops=1)
              Hash Cond: (o.user_id = u.id)
              Buffers: shared hit=12841 read=98211
              ->  Seq Scan on orders o  (cost=0.00..162203.00 rows=2749960 width=28)
                                        (actual time=0.031..1974.552 rows=2748103 loops=1)
                    Filter: ((status)::text = 'paid'::text)
                    Rows Removed by Filter: 5251897
                    Buffers: shared hit=11302 read=95901
              ->  Hash  (cost=4187.00..4187.00 rows=50000 width=28) (actual time=60.958..60.959 rows=49812 loops=1)
                    Buckets: 65536  Batches: 1  Memory Usage: 3521kB
                    ->  Seq Scan on users u  (cost=0.00..4187.00 rows=50000 width=28)
                                             (actual time=0.019..48.221 rows=49812 loops=1)
                          Filter: ((country)::text = 'DE'::text)
                          Rows Removed by Filter: 950188
                          Buffers: shared hit=1539 read=2310
Planning Time: 0.412 ms
Execution Time: 2841.802 ms

Разберём построчно то, что реально несёт информацию.

cost — не время. Это безразмерные единицы планировщика: одна единица примерно равна последовательному чтению одной страницы (seq_page_cost = 1.0), случайное чтение по умолчанию стоит 4.0, обработка строки — 0.01. Числа в cost бесполезны для сравнения с секундомером, но полезны для одного: понять, почему планировщик выбрал этот план, а не другой. Классический случай — random_page_cost = 4.0 из эпохи HDD на машине с NVMe: планировщик систематически переоценивает цену индексного доступа и уходит в Seq Scan. На SSD осмысленное значение — 1.1–1.5.

actual time=A..B — это время до первой строки и до последней. Разрыв говорит о характере узла: у Sort первая строка появляется только после сортировки всего входа (0.03..1974 внизу против 2841.700..2841.712 наверху — типичная «стена»).

rows в скобках — среднее на одну итерацию, а не всего. Это источник половины ошибок чтения планов. Настоящее число строк равно rows × loops. Узел с rows=1 loops=500000 обработал полмиллиона строк, а не одну.

Rows Removed by Filter: 5251897 — прямая улика. База прочитала восемь миллионов строк, чтобы выбросить пять. Это ровно та работа, которую убирает индекс.

Buffers — самая недооценённая строка. Всегда включайте BUFFERS (в PostgreSQL 16+ он включён по умолчанию вместе с ANALYZE). shared hit — страницы, найденные в shared_buffers, цена ≈1 мкс. shared read — страницы, которых там не было: их пришлось просить у ОС, и это либо page cache (≈5 мкс), либо диск (десятки-сотни мкс). В примере read=98211 — это 98 тысяч страниц по 8 КБ, то есть 767 МБ прочитано ради двадцати строк. Число буферов — самая стабильная, не зависящая от шума соседей метрика запроса. По ней и надо сравнивать «до» и «после»: секунды прыгают от нагрузки, буферы — нет.

Что не видно без дополнительных настроек. Включите track_io_timing = on, тогда появится блок I/O Timings: read=…, разделяющий «страниц прочитано» и «сколько на это ушло времени» — без него shared read из page cache и с сетевого диска выглядят одинаково. Для пишущих запросов полезен EXPLAIN (ANALYZE, WAL).

Ловушки самого EXPLAIN ANALYZE

• ANALYZE реально выполняет запрос. EXPLAIN ANALYZE DELETE … удалит данные. Оборачивайте в BEGIN; … ROLLBACK;.
• Накладные расходы таймера. На планах с миллионами вызовов узлов gettimeofday сам по себе добавляет десятки процентов, и профиль искажается в пользу «горячих» узлов — ровно тот же systematic bias, что у инструментирующих профайлеров из https://courses.digitable.life/post/performance/03-cpu-profiling/. Проверка: EXPLAIN (ANALYZE, TIMING OFF) — если Execution Time резко упал, верьте только структуре плана и счётчикам строк, а не временам узлов. Оценить накладные расходы платформы можно утилитой pg_test_timing.
• Первый прогон холодный. Разница между первым и вторым запуском — это разница между диском и shared_buffers, и она достигает двух порядков. Меряйте оба состояния осознанно (см. https://courses.digitable.life/post/performance/02-benchmarking/ про разогрев), а сравнивайте варианты запроса в одинаковых условиях кэша.
• План на стенде ≠ план на проде. Отличаются объём данных, статистика, work_mem, effective_cache_size, версия. Именно поэтому в выводе полезен SETTINGS — он печатает изменённые от умолчания параметры.
• Чтобы получить план прода, не воспроизводя запрос, включайте auto_explain с auto_explain.log_min_duration = 500ms и log_analyze = on. Это единственный надёжный способ увидеть план, который база выбрала в момент инцидента, а не сейчас.

Читать большие планы глазами тяжело — пользуйтесь визуализаторами: explain.depesz.com подсвечивает узлы с наибольшим собственным временем и худшими оценками, explain.dalibo.com строит дерево, pgMustard даёт советы. В MySQL 8.0.18+ есть свой EXPLAIN ANALYZE, читается похоже, но формат вывода — вложенные -> вместо дерева.

4. Корень зла: ошибка оценки кардинальности

Вернитесь к плану выше и сравните оценки с фактами: Seq Scan on users ожидал 50000 строк и получил 49812 — отлично; Hash Join ожидал 275008, получил 248913 — тоже нормально. Здесь планировщик не ошибся, он просто не имел индекса. Но в проде куда чаще картина другая:

->  Nested Loop  (cost=0.85..18.92 rows=1 width=64) (actual time=0.089..48213.446 rows=1841203 loops=1)

Оценка 1, факт 1 841 203. Планировщик выбрал Nested Loop, потому что для одной строки это дёшево; для полутора миллионов это катастрофа. Практически все внезапные деградации планов — это ошибки кардинальности, а не «плохой оптимизатор».

Почему оценки врут:

1. Устаревшая статистика. autovacuum не успел после массовой загрузки. Лечение — ANALYZE table явно после ETL.
2. Корреляция колонок. Планировщик по умолчанию считает предикаты независимыми: селективность city = 'Мюнхен' AND country = 'DE' он оценит как произведение, хотя город однозначно задаёт страну. Ошибка получается в разы. Лечение — расширенная статистика: CREATE STATISTICS s_geo (dependencies, ndistinct) ON city, country FROM users; ANALYZE users;
3. Мало корзин гистограммы. default_statistics_target = 100 даёт 100 корзин и 100 частых значений. Для колонки с перекошенным распределением поднимите точечно: ALTER TABLE orders ALTER COLUMN user_id SET STATISTICS 1000;
4. Выражения и функции. Селективность WHERE lower(email) = … или вызова PL/pgSQL-функции оценивается константой-заглушкой. Помогает индекс по выражению — он заодно даёт статистику.
5. Ошибка умножается по джойнам. Промах в 10 раз на одном скане превращается в промах в 1000 раз после трёх джойнов. Поэтому читать план надо снизу вверх и искать первый узел, где оценка разошлась с фактом: всё выше — следствие.

Хорошая привычка — при разборе плана считать не время, а отношение actual rows / estimated rows для каждого узла и сортировать по нему. Именно это делают визуализаторы, окрашивая узлы.

5. Индексы: что они на самом деле дают и чего стоят

Индекс не «ускоряет запрос». Индекс сокращает число страниц, которые надо прочитать, обменивая это на работу при записи и на память под кэш. Обе стороны сделки надо считать.

Чиним запрос из раздела 3. Нужен доступ к заказам сразу в порядке created_at DESC и только со статусом paid — значит частичный индекс:

CREATE INDEX CONCURRENTLY orders_paid_created_idx
    ON orders (created_at DESC)
    WHERE status = 'paid';

Limit  (cost=0.86..142.31 rows=20 width=48) (actual time=0.094..3.612 rows=20 loops=1)
  Buffers: shared hit=1284 read=11
  ->  Nested Loop  (cost=0.86..1944218.55 rows=274841 width=48) (actual time=0.093..3.605 rows=20 loops=1)
        ->  Index Scan Backward using orders_paid_created_idx on orders o
                  (cost=0.43..382110.19 rows=2749960 width=28) (actual time=0.041..1.088 rows=417 loops=1)
              Buffers: shared hit=402 read=7
        ->  Index Scan using users_pkey on users u
                  (cost=0.43..0.57 rows=1 width=28) (actual time=0.005..0.005 rows=0 loops=417)
              Index Cond: (id = o.user_id)
              Filter: ((country)::text = 'DE'::text)
              Rows Removed by Filter: 1
              Buffers: shared hit=882 read=4
Planning Time: 0.386 ms
Execution Time: 3.641 ms

2841 мс → 3,6 мс, 111 тысяч буферов → 1295. Механизм: индекс отдаёт заказы уже отсортированными, LIMIT останавливает выполнение после двадцатого подходящего, и всего пришлось просмотреть 417 заказов вместо восьми миллионов. Это «abort early» план.

И тут же — предупреждение, которое отличает инженера от человека с рецептом. Этот план хрупок. Он опирается на предположение «примерно каждый двадцатый заказ — от немца». Для страны с долей 0,01% база пройдёт по индексу сотни тысяч заказов, прежде чем наберёт двадцать, и станет медленнее исходного варианта. Проверять надо на реалистичном распределении параметров, а не на удобном. Это ровно та ошибка выжившего, о которой говорил https://courses.digitable.life/post/performance/02-benchmarking/: вы измерили случай, который хорошо выглядит.

Когда индекс не сработает: sargability

Порядок колонок в составном индексе

Правило, которое стоит запомнить как мнемонику E-S-R: сначала колонки с равенством (Equality), затем колонка из ORDER BY (Sort), затем колонка с диапазоном (Range). Индекс (status, created_at) обслужит WHERE status = 'paid' ORDER BY created_at DESC LIMIT 20 без сортировки, а (created_at, status) — нет, потому что после диапазона по первой колонке порядок по второй теряется. Один составной индекс (a, b, c) работает и как индекс по (a), и по (a, b) — три отдельных индекса не нужны, они только удорожают запись.

Index-only scan

Если все нужные запросу колонки лежат в индексе, PostgreSQL может не ходить в таблицу вовсе. Признак в плане — Index Only Scan и строка Heap Fetches: 0. Оговорка, которая всех ловит: это работает, только когда карта видимости (visibility map) свежая, то есть после VACUUM. На горячей таблице Heap Fetches окажется огромным, и выигрыш испарится. Не нужные для поиска, но нужные для выдачи колонки кладут в INCLUDE:

CREATE INDEX orders_user_created_idx ON orders (user_id, created_at DESC) INCLUDE (total_cents, status);

Цена индекса

• Каждый индекс — это дополнительная запись при INSERT, при DELETE и при UPDATE колонки, входящей в индекс. Шесть индексов на горячей таблице — это ×6 к работе на запись и ×6 к объёму WAL.
• PostgreSQL умеет HOT-обновления (новая версия строки в той же странице без правки индексов), но только если не менялась ни одна проиндексированная колонка и в странице есть место. Отсюда практический приём для таблиц с частыми UPDATE: ALTER TABLE … SET (fillfactor = 85) и минимум индексов по часто меняющимся колонкам.
• Индексы конкурируют с данными за shared_buffers. Мёртвый индекс на 12 ГБ вытесняет из кэша живые страницы и делает медленнее всё остальное.

Ищите мёртвые индексы:

SELECT s.relname AS tbl, s.indexrelname AS idx, s.idx_scan,
       pg_size_pretty(pg_relation_size(s.indexrelid)) AS size
FROM pg_stat_user_indexes s
JOIN pg_index i ON i.indexrelid = s.indexrelid
WHERE NOT i.indisunique
ORDER BY s.idx_scan ASC, pg_relation_size(s.indexrelid) DESC
LIMIT 20;

Осторожно: счётчики обнуляются при pg_stat_reset() и считаются отдельно на каждой реплике — индекс, «неиспользуемый» на мастере, может быть основным на аналитической реплике. Перед удалением проверьте все узлы и посмотрите, нет ли редких квартальных отчётов. В MySQL 8 есть безопасная репетиция: ALTER TABLE t ALTER INDEX idx INVISIBLE — оптимизатор перестаёт видеть индекс, но индекс продолжает поддерживаться, и решение можно откатить мгновенно.

Наконец, CREATE INDEX без CONCURRENTLY берёт блокировку, запрещающую запись, на всё время построения. На таблице в сотни гигабайт это часы простоя. Всегда CONCURRENTLY в проде — ценой двух проходов по таблице и риска остаться с INVALID-индексом при конфликте (тогда его надо удалить и построить заново). Глубже про внутренности индексов — https://courses.digitable.life/post/databases/06-indexes-and-query-plans/.

6. Пул соединений: очередь, которую никто не измеряет

В PostgreSQL каждое соединение — это отдельный процесс ОС: несколько мегабайт приватной памяти, своя запись в списке блокировок, свой кусок work_mem на каждый узел сортировки или хеша в плане. Тысяча соединений — это тысяча процессов, конкурирующих за 16 ядер, за защёлки буферного кэша и за пропускную способность памяти. Ровно те эффекты, что разбирались в https://courses.digitable.life/post/performance/07-concurrency-performance/: контеншн растёт быстрее линейного, полезная работа падает.

Отсюда контринтуитивное, но многократно измеренное правило: пул должен быть маленьким. Считается он не на глаз, а по закону Литтла (L = λ · W, число одновременно обслуживаемых = интенсивность × время обслуживания). Разверните его в сторону пропускной способности:

пропускная_способность ≈ размер_пула / среднее_время_запроса

Среднее время запроса берётся из pg_stat_statements — предположим, 2 мс. Тогда одно соединение даёт 500 запросов в секунду, а 20 соединений — 10 000. Если ваш сервис отдаёт 3000 rps, вам нужно около 6–8 соединений плюс запас на всплески, а не 200, которые обычно стоят в конфиге.

Известная эвристика HikariCP — connections = (ядер × 2) + число_шпинделей — даёт похожий порядок, но помните её возраст: она из 2015 года и из мира вращающихся дисков. «Шпинделей» на NVMe не существует, и слагаемое надо заменять измерением глубины очереди устройства. Пользуйтесь ею как отправной точкой, а не как истиной: см. HikariCP About Pool Sizing.

Правильный способ выбрать размер — снять кривую. Прогнать нагрузочный тест при размере пула 4, 8, 16, 32, 64 и построить пропускную способность и p99. Кривая всегда выглядит как на схеме выше: до колена растёт пропускная способность, после колена растёт только задержка. Подробности методики — https://courses.digitable.life/post/performance/11-load-testing/.

Жизненный цикл соединения и где он ломается

Три состояния на этой диаграмме отвечают почти за все инциденты с пулом.

Waiting — та самая невидимая очередь. Если вы не экспортируете метрику времени ожидания acquire, у вас в системе есть латентность, которую нельзя объяснить ни одним планом запроса. Экспортируйте её обязательно: HikariCP отдаёт hikaricp_connections_acquire_seconds, pgxpool в Go — Stat().EmptyAcquireCount() и AcquireDuration(), SQLAlchemy — через события пула.

IIT (idle in transaction) — тихий убийца. Транзакция открыта, запросов нет, но она держит снапшот: autovacuum не может убрать мёртвые версии строк во всей базе, таблицы пухнут, планы деградируют. Типичная причина — HTTP-вызов внутри транзакции или @Transactional на методе, который час ждёт внешний API. Лечение: idle_in_transaction_session_timeout = 30s на уровне сервера и запрет сетевых вызовов внутри транзакции на уровне ревью.

Установка соединения стоит дорого: TCP + TLS + аутентификация + инициализирующие SET — это единицы-десятки миллисекунд. Serverless-функции, создающие соединение на вызов, кладут базу мгновенно. Отсюда обязательный внешний пул.

PgBouncer и режимы пулинга

Внешний пулер ставят, когда клиентов много и они короткоживущие. PgBouncer держит небольшое число реальных соединений к серверу и мультиплексирует в них тысячи клиентских.

На практике используют transaction. Главная историческая боль — серверные prepared statements, которые в этом режиме ломались (клиент готовит на одном бэкенде, исполняет на другом); с PgBouncer 1.21 поддержка prepared statements на уровне протокола появилась, но её надо включить (max_prepared_statements) и проверить, что драйвер с ней дружит. Второй нюанс: пулер добавляет свой RTT и становится ещё одной точкой насыщения — его собственные метрики (SHOW POOLS, колонки cl_waiting, sv_active) надо мониторить так же, как метрики базы.

7. N+1: проблема, которой нет ни в одном плане

Вернитесь к первой строке pg_stat_statements из раздела 2: SELECT * FROM users WHERE id = $1, 8,4 миллиона вызовов, 41% времени базы. Каждый вызов идеален. Беда в их количестве.

Арифметика простая и неумолимая. Пусть RTT до базы 0,3 мс, а сам запрос по первичному ключу — 0,1 мс. Тогда N+1 при N = 50 стоит 51 × 0,4 ≈ 20 мс, из которых 15 мс — чистая сеть. Батч стоит 2 × 0,5 ≈ 1 мс. Сложность по round-trip’ам: O(N) против O(1); по объёму переданных данных одинаково — O(N) строк. Именно поэтому N+1 не «оптимизируется» ускорением запроса: вы боретесь с константой, помноженной на N. И именно поэтому он становится в разы хуже при переезде базы в другую зону доступности — RTT растёт, а количество round-trip’ов остаётся.

Как обнаружить, а не заметить случайно

Правило трека: не смотреть глазами, а измерять. Число запросов на HTTP-запрос — такая же характеристика эндпоинта, как и время ответа, и её надо фиксировать в тесте.

"""Ловим N+1 в тестах: считаем запросы, а не разглядываем логи."""
import contextlib
from sqlalchemy import event


@contextlib.contextmanager
def count_queries(engine):
    """Считает SQL-операторы, прошедшие через engine внутри блока."""
    stats = {"n": 0, "sql": []}

    def before(conn, cursor, statement, params, context, executemany):
        stats["n"] += 1
        stats["sql"].append(" ".join(statement.split())[:90])

    event.listen(engine, "before_cursor_execute", before)
    try:
        yield stats
    finally:
        event.remove(engine, "before_cursor_execute", before)


def test_orders_page_has_query_budget(engine, session):
    with count_queries(engine) as st:
        render_orders_page(session, user_id=42, limit=50)

    # Бюджет запросов — часть контракта эндпоинта, как и бюджет задержки.
    # Проверяем константу, а не «мало»: N+1 узнаётся по зависимости от limit.
    assert st["n"] <= 3, f"ожидали <=3 запроса, получили {st['n']}:\n" + "\n".join(st["sql"])

Ключевой приём в последней строке: тест должен ловить не «много запросов», а зависимость числа запросов от размера выборки. Запустите тот же сценарий с limit=5 и limit=50 — если счётчик вырос, у вас N+1, даже если оба раза он ниже порога. В Django для этого есть готовый assertNumQueries, в Rails — bullet, в EF Core — логирование с LogTo и счётчиком команд. В проде тот же сигнал даёт трассировка: количество db-span’ов внутри одного HTTP-span’а (см. https://courses.digitable.life/post/distributed-systems/12-observability/).

Как чинить и чем это может обернуться

Отдельно стоит упомянуть raiseload("*") и его аналоги: они превращают случайную ленивую загрузку в исключение. Это переводит N+1 из класса «регрессия производительности, замеченная через месяц» в класс «падающий тест», что несравнимо дешевле.

Обратная ловушка — декартов взрыв. Соблазн «починить всё одним JOIN» приводит к тому, что при загрузке заказа с 20 позициями и 5 платежами вы получаете 100 строк вместо 25, и каждая тащит полную копию полей заказа. При двух-трёх коллекциях объём выдачи растёт мультипликативно, и один «оптимизированный» запрос оказывается медленнее пяти простых. Правило: «к одному» — JOIN, «ко многим» — отдельный запрос с IN. Ровно это и делают selectinload и AsSplitQuery.

8. Четыре классических убийцы, которые не лечатся индексом

Пагинация через OFFSET. LIMIT 20 OFFSET 100000 заставляет базу построить и выбросить 100 000 строк. Стоимость страницы линейна по её номеру, а полный проход по всем страницам — O(n²/k) прочитанных строк. Keyset-пагинация («seek method») делает страницу за O(log n + k):

ФУНКЦИЯ следующая_страница(курсор, k):
    ЕСЛИ курсор пуст:
        ВЕРНУТЬ первые k строк в порядке (created_at DESC, id DESC)
    ИНАЧЕ:
        ВЕРНУТЬ первые k строк, у которых (created_at, id) < курсор
    новый_курсор ← (created_at, id) последней строки

-- Плохо: цена растёт с номером страницы, и данные «съезжают» при вставках.
SELECT id, created_at FROM orders WHERE user_id = 42
ORDER BY created_at DESC, id DESC LIMIT 20 OFFSET 100000;

-- Хорошо: постоянная цена, устойчиво к вставкам. Нужен индекс (user_id, created_at DESC, id DESC).
SELECT id, created_at FROM orders
WHERE user_id = 42 AND (created_at, id) < ('2026-07-01 10:00:00+03', 918273)
ORDER BY created_at DESC, id DESC LIMIT 20;

Сравнение строк (a, b) < (x, y) — стандартный SQL и корректно ложится на составной индекс, но только если направления сортировки у всех колонок совпадают. Подробный разбор — use-the-index-luke.com/no-offset.

Блокировки. План быстрый, запрос медленный — почти всегда ожидание. Смотрите не в план, а в состояние сессий:

SELECT pid, state, wait_event_type, wait_event,
       now() - xact_start AS xact_age,
       pg_blocking_pids(pid) AS blocked_by,
       left(regexp_replace(query, '\s+', ' ', 'g'), 60) AS q
FROM pg_stat_activity
WHERE backend_type = 'client backend' AND state <> 'idle'
ORDER BY xact_start;

Систематический подход — сэмплировать pg_stat_activity раз в секунду и агрегировать по wait_event. Получится профиль ожиданий: аналог flame graph, только для базы. Готовые решения: расширение pg_wait_sampling, pgsentinel, RDS Performance Insights, Active Session History в Oracle. Это самый недооценённый прибор в арсенале: он отвечает на вопрос «чего база ждала», на который EXPLAIN не отвечает в принципе.

Раздувание (bloat) и autovacuum. MVCC оставляет мёртвые версии строк; пока autovacuum их не уберёт, таблица и индексы растут, а Seq Scan читает всё больше пустого места. Долгие транзакции и незакрытые слоты репликации блокируют очистку. Симптом — растущее отношение n_dead_tup / n_live_tup в pg_stat_user_tables и медленно деградирующие все запросы разом.

Горячая строка. Счётчик в одной строке, обновляемый тысячей транзакций в секунду, сериализует систему полностью: закон Амдала в чистом виде. Лечение архитектурное — шардировать счётчик на N строк и суммировать при чтении, либо агрегировать в очереди. Транзакционная часть темы — https://courses.digitable.life/post/databases/07-transactions-and-isolation/.

9. Как честно бенчмаркать базу

Все правила из https://courses.digitable.life/post/performance/02-benchmarking/ действуют, плюс специфические для СУБД.

• Объём данных решает всё. План на таблице в 10 000 строк и в 10 миллионах — разные планы. Бенчмарк на маленьком датасете не просто неточен, он отвечает на другой вопрос.
• Соотношение данных и RAM — главный параметр. База, целиком влезающая в shared_buffers, и база в десять раз больше памяти — это две разные системы. Явно зафиксируйте, какой сценарий вы меряете, и не переносите выводы между ними.
• Разогрев обязателен, но осознанный. pg_prewarm заполняет кэш детерминированно; иначе первые минуты теста меряют диск, а не запрос.
• Распределение ключей. Равномерный случайный доступ — самый недружелюбный к кэшу сценарий, реальный трафик почти всегда перекошен. У pgbench для этого есть random_zipfian. Равномерное распределение систематически занижает hit rate и завышает пользу от индексов относительно реальности.
• Протокол имеет значение. pgbench -M prepared убирает разбор и планирование из каждого замера; -M simple их включает. Разница на коротких запросах — десятки процентов.
• Не выключайте fsync «чтобы было быстрее». Вы получите числа несуществующей системы.
• Coordinated omission. Нагрузочный клиент с закрытой моделью (фиксированное число потоков, каждый ждёт ответа) при перегрузке перестаёт слать нагрузку и рисует красивый p99. Используйте открытую модель с фиксированной интенсивностью: в k6 — constant-arrival-rate. Подробно — https://courses.digitable.life/post/performance/11-load-testing/.

# Датасет заведомо больше памяти: scale 2000 ≈ 30 ГБ.
pgbench -i -s 2000 --foreign-keys shop

# Прогрев, затем открытая модель: 3000 транзакций в секунду, 10 минут, перекошенные ключи.
psql shop -c "SELECT pg_prewarm('pgbench_accounts');"
pgbench -M prepared -c 32 -j 8 -T 600 -R 3000 --latency-limit=200 -P 10 shop

Флаг -R (rate) как раз включает открытую модель, а --latency-limit честно считает транзакции, не уложившиеся в бюджет, как пропущенные, а не растягивает измерение.

И главное — сравнивайте буферы, а не секунды. Buffers: shared hit/read до и после изменения запроса — метрика, устойчивая к шуму соседей, к состоянию кэша и к тому, что кто-то параллельно запустил отчёт. Если число прочитанных страниц упало вдесятеро, улучшение реально. Если упало только время — вы, скорее всего, измерили прогретый кэш.

10. Порядки величин — и почему им нельзя верить буквально

Эта таблица — потомок знаменитого списка Джеффа Дина «Latency numbers every programmer should know» (2012, наиболее известна интерактивная версия colin-scott.github.io/personal_website/research/interactive_latency.html). Пользоваться им надо с двумя оговорками. Первая: цифры устарели неравномерно. Строки про диск изменились на два порядка (7200 rpm HDD против NVMe), про сеть внутри ДЦ — заметно, про RTT между континентами — вообще нет, там ограничение скоростью света. Вторая, важнее: это порядки для сравнения вариантов, а не значения для расчёта SLA. Единственный способ получить свои числа — измерить: fio для диска, pg_test_fsync для журнала, ping и трассировка для сети. Виртуализация, соседи по хосту, троттлинг IOPS и лимиты burst делают облачные цифры отличающимися от табличных в разы и меняющимися во времени.

11. Типичные ошибки

• Чинить самый медленный запрос вместо самого дорогого. Правильный порядок — по суммарному времени. Запрос на 0,14 мс, вызванный 8 миллионов раз, важнее запроса на 5 с, вызванного дважды.
• Читать план по времени узлов, а не по буферам и кардинальности. Времена шумят, число страниц и actual/estimated rows — нет.
• Забыть про loops. rows=1 loops=500000 — это полмиллиона строк.
• Ставить индекс на каждую колонку из WHERE. Индексы стоят записи, WAL, места в кэше и планировщику — времени на перебор вариантов.
• Увеличивать пул, когда растут таймауты. Это ускоряет коллапс. Считайте по закону Литтла и снимайте кривую.
• Открывать транзакцию вокруг HTTP-вызова. Приводит к idle in transaction, остановке очистки и деградации всей базы, а не одного эндпоинта.
• Мерить на пустой или синтетически равномерной базе. Планы будут другие, кэш-хит будет другой, выводы — неприменимые.
• Считать EXPLAIN исчерпывающим прибором. Он ничего не знает про очередь пула, RTT, ожидание блокировок и время выгрузки результата.
• Оптимизировать до измерения доли. Если БД — 15% времени ответа, идеальный SQL даст 15% улучшения в лучшем случае. Закон Амдала не отменяли.

Мини-итог

Порядок действий, который работает почти всегда:

1. Из трассировки — доля времени в БД и число запросов на HTTP-запрос. Это решает, куда идти вообще.
2. pg_stat_statements, отсортированный по суммарному времени: находим дорогой класс, а не медленный.
3. EXPLAIN (ANALYZE, BUFFERS) на реалистичных параметрах: ищем первый узел, где оценка разошлась с фактом, и смотрим на прочитанные страницы.
4. Правим причину: статистика, индекс, переписанный запрос, убранный N+1 — в этом порядке по соотношению «эффект/риск».
5. Проверяем буферами, а не секундами, и закрепляем бюджетом запросов в тесте (см. https://courses.digitable.life/post/performance/12-optimization-workflow/).
6. Отдельно — пул и ожидания: acquire latency, pg_stat_activity по wait_event. Это тот слой, который никогда не появится в плане.

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

Источники

• PostgreSQL: EXPLAIN и Using EXPLAIN — официальная документация.
• pg_stat_statements, auto_explain, CREATE STATISTICS.
• Markus Winand, Use The Index, Luke! — лучший бесплатный учебник по индексам и sargability, с разделами по всем основным СУБД.
• Егор Рогов, «PostgreSQL изнутри» — postgrespro.com/community/books/internals, свободно доступна; главы про буферный кэш, блокировки и планировщик.
• HikariCP: About Pool Sizing — с поправкой на возраст эвристики.
• PgBouncer features — режимы пулинга и их ограничения.
• MySQL 8: EXPLAIN ANALYZE и sys schema.
• explain.depesz.com, explain.dalibo.com — визуализаторы планов.
• pgbench — встроенный нагрузочный инструмент.

Что дальше

Мы дважды упирались в одно и то же: самый быстрый запрос — тот, который не выполнялся. Индексы уменьшают число прочитанных страниц, батчинг — число round-trip’ов, но следующий шаг радикальнее: не ходить в базу вовсе. У этого шага своя цена — устаревшие данные, инвалидация, лавина промахов при истечении срока жизни ключа. Разбираемся: Кэширование: уровни, инвалидация, cache stampede, hit rate.