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

Разбирай медленный запрос по фактическому плану: сравнивай оценки строк с реальностью, ищи лишние чтения и только потом меняй SQL или индекс. Эта глава также показывает, как распознать N+1, дорогой OFFSET и неудачную пакетную загрузку в Go-коде.

EXPLAIN и EXPLAIN ANALYZE

EXPLAIN показывает план, который выбрал планировщик, вместе с его оценками: сколько строк он ожидает и во что оценивает стоимость. Запрос при этом не выполняется. EXPLAIN ANALYZE же реально выполняет запрос и добавляет к плану фактические числа: сколько строк вернул каждый узел, сколько времени занял, сколько раз был вызван.

Разница принципиальная: EXPLAIN отвечает на вопрос «что база собирается делать», EXPLAIN ANALYZE - «что она сделала и где ошиблась в оценках». Диагностика почти всегда живёт во втором: плохие планы чаще всего растут из расхождения между rows и actual rows.

BEGIN;
EXPLAIN (ANALYZE, BUFFERS) UPDATE orders SET status = 'cancelled' WHERE id = 42;
ROLLBACK;   -- обязательно; лучше набрать заранее, а не после

Но даже с ROLLBACK побочные эффекты остаются. Транзакция успела взять блокировки на изменяемые строки и держит их до конца блока; сработали триггеры (включая внешние вызовы, если они есть); сгенерировался WAL; выросли счётчики последовательностей - nextval не откатывается. На горячей таблице такой «безопасный» эксперимент легко выстроит очередь блокировок.

EXPLAIN (ANALYZE, BUFFERS, VERBOSE, SETTINGS, FORMAT TEXT)
SELECT o.id, o.total, c.name
FROM   orders o JOIN customers c ON c.id = o.customer_id
WHERE  o.created_at >= now() - interval '7 days' AND o.status = 'paid'
ORDER  BY o.created_at DESC
LIMIT  50;

Как читать план: снизу вверх и изнутри наружу

План устроен как дерево. В текстовом выводе оно записано с отступами: чем глубже отступ, тем ниже узел в дереве. Данные текут снизу вверх: самые вложенные узлы читают таблицы и индексы, отдают строки родителю, тот соединяет или агрегирует, и так до корня, который отдаёт результат клиенту. Поэтому читать нужно от самых глубоких строк, а не сверху.

Узлы, которые надо узнавать

Числа в плане и что они значат

Seq Scan on orders  (cost=0.00..24583.00 rows=980 width=64)
                    (actual time=0.021..312.447 rows=51230 loops=1)
  Filter: (status = 'paid'::text)
  Rows Removed by Filter: 948770
  Buffers: shared hit=1204 read=13379

• cost=A..B дан в условных единицах планировщика, не в миллисекундах. A оценивает путь до первой строки (важен при LIMIT), B - до последней. За единицу принята стоимость последовательного чтения одной страницы (seq_page_cost = 1.0); случайное чтение по умолчанию стоит 4.0. Сравнивать cost можно только между планами одного запроса.
• rows vs actual rows сравнивают оценку с фактом. Расхождение в 10+ раз и есть главная зацепка: почти всегда это устаревшая статистика, коррелированные колонки, которые планировщик считает независимыми, или неудачная оценка селективности выражения. Лечится ANALYZE, default_statistics_target, CREATE STATISTICS.
• loops показывает, сколько раз узел выполнялся. Время и строки в плане указаны на одну итерацию: реальное время узла = actual time × loops. Классическая ловушка: «0.03 ms, всё быстро» при loops=200000, а это 6 секунд.
• Rows Removed by Filter считает строки, которые прочитали и выбросили. Миллион выброшенных при тысяче нужных прямо говорит, что нужен индекс по этому предикату (возможно, частичный).
• Buffers: shared hit=... read=... делит страницы на hit, взятые из shared_buffers, и read, прочитанные из ОС/диска. Точнее меры «сколько данных мы реально перелопатили» нет, и от прогретости кэша она зависит меньше, чем время. dirtied/written показывают побочную запись.
• Planning Time / Execution Time: если планирование сравнимо с выполнением, а запрос простой, смотри в сторону избытка индексов или подготовленных выражений.

Кейс: запрос на таблице в несколько терабайт тормозит

Здесь важен порядок действий, а не знание команд: начинать надо с измерения, а не с «добавлю индекс».

1. Понять, что именно тормозит. Не «сайт медленный», а конкретный запрос: pg_stat_statements с сортировкой по total_exec_time (не по mean: главным вредителем часто оказывается быстрый запрос, который выполняется 50 000 раз в минуту) и pg_stat_activity для того, что висит прямо сейчас.
2. Снять план. EXPLAIN (ANALYZE, BUFFERS) на репрезентативных параметрах. Если запроса не дождаться, хватит EXPLAIN без ANALYZE плюс auto_explain с log_min_duration в проде.
3. Проверить, не блокировки ли это. Медленный не значит «плохой план»: посмотреть wait_event_type = 'Lock' в pg_stat_activity. Если ждём блокировку, план тут ни при чём.
4. Проверить статистику. rows против actual rows. Если оценка врёт на порядки, запустить ANALYZE и снять план заново. Половина «загадочных» проблем закрывается здесь.
5. Уменьшить объём читаемых данных. В порядке предпочтения:
   • индекс под предикат (составной, частичный, покрывающий);
   • переписать запрос: убрать функцию с колонки, заменить OR на UNION ALL, коррелированный подзапрос - на джойн или оконную функцию, DISTINCT - на EXISTS, вынести тяжёлую агрегацию в CTE с MATERIALIZED или наоборот;
   • убрать SELECT *, чтобы стал возможен index-only scan.
6. Партиционирование. Если таблица многотерабайтная и запросы всегда ограничены по времени или тенанту, подойдёт RANGE по дате или HASH по tenant_id. Заодно решается проблема обслуживания: удаление старых данных превращается из многочасового DELETE в мгновенный DETACH PARTITION.
7. Изменить модель. Предагрегировать (материализованное представление, счётчики, роллапы), вынести холодные данные в архивную таблицу, денормализовать нужную колонку, чтобы избавиться от джойна. Это уже не «оптимизация запроса», а изменение схемы - но на терабайтах именно оно обычно и работает.
8. Только потом - железо и параметры. work_mem для конкретного запроса, random_page_cost под SSD, max_parallel_workers_per_gather, вынос отчётов на реплику.

Проблема N+1

N+1 случается, когда вместо одного запроса за связанными данными приложение делает один запрос за списком и ещё по одному на каждый его элемент. Сто заказов - сто один запрос. Каждый из них быстрый (0,4 мс), поэтому в логах БД проблема не выглядит проблемой; убивает сетевой round-trip: 101 × (RTT 0,5 мс + разбор + планирование) вместо одного.

Как обнаружить

• Логи БД: временно log_min_duration_statement = 0 на одном инстансе и посмотреть, что один HTTP-запрос породил 200 строк лога с одинаковым текстом и разными параметрами. В pg_stat_statements то же видно по огромному calls при крошечном mean_exec_time.
• Трейсинг: в OpenTelemetry на спане обработчика видно частокол одинаковых дочерних спанов db.query. Это самый наглядный способ показать проблему продакту.
• Счётчик запросов в тесте лучше всего: он ловит регрессию до прода. Оборачиваем драйвер и проверяем ожидаемое число обращений.

// Тест, который ловит N+1 до релиза: считаем запросы через обёртку над драйвером.
type countingDriver struct {
    inner driver.Driver
    n     *atomic.Int64
}

func TestListOrders_NoNPlusOne(t *testing.T) {
    db, counter := newCountingDB(t)          // регистрирует обёрнутый драйвер
    svc := service.New(repo.New(db))

    _, err := svc.ListOrdersWithCustomers(ctx, 100)
    require.NoError(t, err)

    // Ровно два запроса: заказы + пачка клиентов одним IN.
    // Если кто-то вернёт цикл с запросом внутри, тест упадёт с n = 101.
    require.LessOrEqual(t, counter.Load(), int64(2),
        "похоже на N+1: запросов %d", counter.Load())
}

Как чинить

// Плохо: N+1
orders, _ := repo.ListOrders(ctx, 100)
for i, o := range orders {
    // отдельный поход в БД на каждой итерации
    c, err := repo.GetCustomer(ctx, o.CustomerID)
    if err != nil {
        return err
    }
    orders[i].Customer = c
}

// Хорошо: два запроса, батч по ключам
orders, _ := repo.ListOrders(ctx, 100)

ids := make([]int64, 0, len(orders))
seen := make(map[int64]struct{}, len(orders))
for _, o := range orders {
    if _, ok := seen[o.CustomerID]; !ok {
        seen[o.CustomerID] = struct{}{}
        ids = append(ids, o.CustomerID)
    }
}
// WHERE id = ANY($1): один запрос, один план
custs, _ := repo.GetCustomersByIDs(ctx, ids)
byID := make(map[int64]Customer, len(custs))
for _, c := range custs {
    byID[c.ID] = c
}
for i, o := range orders {
    orders[i].Customer = byID[o.CustomerID]
}

-- Вариант 1: JOIN, когда нужна плоская выборка
SELECT o.id, o.total, c.id, c.name
FROM   orders o JOIN customers c ON c.id = o.customer_id
WHERE  o.created_at >= $1;

-- Вариант 2: батч по ключам. В Go с pgx массив передаётся напрямую:
SELECT id, name FROM customers WHERE id = ANY($1);
-- ANY($1) лучше, чем IN (...) со склейкой: один текст запроса → один план в кэше,
-- а не тысяча разных текстов с разным числом плейсхолдеров.

-- Вариант 3: собираем вложенный список на стороне БД (вместо N запросов за позициями)
SELECT o.id, o.total,
       coalesce(jsonb_agg(jsonb_build_object('sku', i.sku, 'qty', i.qty))
                FILTER (WHERE i.id IS NOT NULL), '[]') AS items
FROM   orders o LEFT JOIN order_items i ON i.order_id = o.id
WHERE  o.id = ANY($1)
GROUP  BY o.id, o.total;

Dataloader / батчинг обобщает этот приём: слой собирает запросы по ключам, пришедшие за короткое окно (или за время одного HTTP-запроса), склеивает их в один WHERE id = ANY(...) и раздаёт результаты обратно. Обязателен в GraphQL, где структура запроса заранее неизвестна, и очень полезен в gRPC-агрегаторах. В Go это обычно канал заявок + таймер на 1–5 мс + дедупликация ключей + singleflight, чтобы одинаковые ключи не ходили в БД дважды.

Пагинация: OFFSET/LIMIT против keyset

OFFSET n не умеет «перепрыгнуть» n строк. База обязана их найти, материализовать и выбросить. Значит, стоимость страницы растёт линейно с её номером: первая стоит 20 строк, пятитысячная - 100 020 строк работы ради тех же 20. Отсюда чаще всего и берётся «у нас в админке последние страницы отваливаются по таймауту».

-- Плохо: деградирует линейно
SELECT id, created_at, title FROM posts
ORDER BY created_at DESC, id DESC
LIMIT 20 OFFSET 100000;

-- Хорошо: keyset по составному ключу (created_at может повторяться,
-- поэтому обязательно добавляем уникальный тай-брейкер id)
SELECT id, created_at, title FROM posts
WHERE  (created_at, id) < ($1, $2)      -- значения последней строки прошлой страницы
ORDER  BY created_at DESC, id DESC
LIMIT  20;

-- Нужен индекс ровно в порядке сортировки:
CREATE INDEX posts_created_id_idx ON posts (created_at DESC, id DESC);

Кортежи (a, b) < ($1, $2) сравниваются лексикографически, ровно как ключи составного индекса, поэтому запрос превращается в один спуск по дереву. Разворачивать его руками в a < $1 OR (a = $1 AND b < $2) не надо: планировщик такой OR обычно уже не превращает в один Index Scan.

// Курсор наружу отдаём непрозрачной строкой, чтобы клиент не завязывался на формат
type Cursor struct {
    CreatedAt time.Time `json:"c"`
    ID        int64     `json:"i"`
}

func encodeCursor(c Cursor) string {
    b, _ := json.Marshal(c)
    return base64.RawURLEncoding.EncodeToString(b)
}

func (r *Repo) ListPosts(ctx context.Context, after *Cursor, limit int) ([]Post, *Cursor, error) {
    const q = `
        SELECT id, created_at, title FROM posts
        WHERE  ($1::timestamptz IS NULL) OR (created_at, id) < ($1, $2)
        ORDER  BY created_at DESC, id DESC
        LIMIT  $3`
    // limit+1, чтобы без отдельного COUNT понять, есть ли следующая страница
    rows, err := r.db.Query(ctx, q, after.time(), after.id(), limit+1)
    ...
}

SELECT * - чем он плох

• Ломает index-only scan. Даже идеально покрывающий индекс бесполезен, если запрос требует все колонки: придётся идти в heap за каждой строкой.
• Тянет TOAST. Одна колонка jsonb или text на мегабайт, которая никому не нужна, превращает лёгкий запрос в чтение внешних чанков.
• Трафик и память. Строки едут по сети целиком и материализуются в приложении; на пагинации по 1000 строк 4 нужные колонки против 40 дают разницу в десятки мегабайт.
• Ломкость при ALTER. Кто-то добавил колонку - и rows.Scan с фиксированным набором приёмников падает в рантайме с «sql: expected 12 destination arguments in Scan, not 11». Или, что хуже, порядок колонок меняется и данные тихо разъезжаются по полям.
• Непрозрачность. По коду нельзя понять, какие поля реально нужны, и рефакторинг схемы превращается в гадание.

Отдельно про SELECT count(*): он честно проверяет видимость каждой строки, поэтому на больших таблицах всегда дорог. Для «примерно сколько» есть SELECT reltuples::bigint FROM pg_class WHERE relname = 'orders', для точного - либо инкрементальный счётчик в отдельной таблице, либо смирение. Уместен SELECT * разве что при интерактивной отладке в psql.

Батчевые вставки

Вставка миллиона строк по одной оборачивается миллионом round-trip, миллионом парсингов, миллионом фиксаций транзакции с fsync. Батчи дают 10–100×.

-- 1. Multi-row INSERT: один запрос, один разбор, одна транзакция
INSERT INTO events (user_id, kind, payload) VALUES
  ($1, $2, $3), ($4, $5, $6), ($7, $8, $9);
-- Практический предел: 65535 параметров на запрос в протоколе PostgreSQL.
-- При 3 колонках это ~21 800 строк; на практике батчи по 500–5000 строк оптимальны.

-- 2. unnest: текст запроса не меняется с размером батча, поэтому план в кэше один
INSERT INTO events (user_id, kind, payload)
SELECT * FROM unnest($1::bigint[], $2::text[], $3::jsonb[]);

-- 3. ON CONFLICT: идемпотентная вставка (upsert)
INSERT INTO counters (key, n) VALUES ($1, 1)
ON CONFLICT (key) DO UPDATE SET n = counters.n + EXCLUDED.n;

INSERT INTO events (id, ...) VALUES (...)
ON CONFLICT (id) DO NOTHING;    -- повторная доставка из брокера не сломает запись

-- 4. COPY: самый быстрый путь массовой загрузки
COPY events (user_id, kind, payload) FROM STDIN WITH (FORMAT csv);

// pgx: CopyFrom идёт по бинарному протоколу COPY, быстрее в Go не загрузить
rows := make([][]any, 0, len(events))
for _, e := range events {
    rows = append(rows, []any{e.UserID, e.Kind, e.Payload})
}
n, err := pool.CopyFrom(ctx,
    pgx.Identifier{"events"},
    []string{"user_id", "kind", "payload"},
    pgx.CopyFromRows(rows),
)

// pgx: Batch шлёт несколько разных запросов одним сетевым обменом (pipelining)
b := &pgx.Batch{}
for _, e := range events {
    b.Queue(`INSERT INTO events (user_id, kind) VALUES ($1, $2)`, e.UserID, e.Kind)
}
br := pool.SendBatch(ctx, b)
defer br.Close()
for range events {
    if _, err := br.Exec(); err != nil {   // ошибки надо вычитать по одной
        return err
    }
}

Партиционирование таблиц

Партиционирование разбивает одну логическую таблицу на несколько физических («партиций») по значению ключа. Для приложения таблица остаётся одна: запросы и вставки идут в родительскую таблицу, а PostgreSQL сам решает, в какую партицию положить строку и какие партиции читать. Все партиции живут в одной базе, так что это не про масштабирование на несколько серверов, а про размер, обслуживание и отсечение лишнего при чтении.

-- RANGE по времени: самый частый вариант
CREATE TABLE events (
    id          bigserial,
    created_at  timestamptz NOT NULL,
    user_id     bigint      NOT NULL,
    payload     jsonb,
    PRIMARY KEY (id, created_at)   -- ключ партиционирования обязан входить в PK
) PARTITION BY RANGE (created_at);

CREATE TABLE events_2026_07 PARTITION OF events
    FOR VALUES FROM ('2026-07-01') TO ('2026-08-01');
CREATE TABLE events_2026_08 PARTITION OF events
    FOR VALUES FROM ('2026-08-01') TO ('2026-09-01');

-- DEFAULT-партиция ловит всё, что не попало ни в один диапазон.
-- Полезна как страховка, но если она распухла, значит, забыли нарезать новые партиции.
CREATE TABLE events_default PARTITION OF events DEFAULT;

-- HASH: N партиций примерно равного размера
CREATE TABLE sessions (user_id bigint, ...) PARTITION BY HASH (user_id);
CREATE TABLE sessions_0 PARTITION OF sessions FOR VALUES WITH (MODULUS 8, REMAINDER 0);
-- ... ещё 7 штук

-- Старые данные удаляем мгновенно: не DELETE на 300 млн строк с распуханием и VACUUM,
-- а операцией над одними метаданными, за миллисекунды
DROP TABLE events_2025_08;
-- Или мягко: отцепить, выгрузить в архив, потом дропнуть. CONCURRENTLY не блокирует
-- запись, но при DEFAULT-партиции (как выше) не работает - там без него.
ALTER TABLE events DETACH PARTITION events_2025_08 CONCURRENTLY;

Ограничения, о которых спрашивают: ключ партиционирования обязан входить в любой UNIQUE/PRIMARY KEY (глобального уникального индекса поверх партиций не существует); внешние ключи на партиционированную таблицу поддерживаются только с PG 12; партиций лучше держать не больше нескольких сотен: планировщик обрабатывает их список на каждом запросе, и на тысячах партиций растёт время планирования; нарезку будущих партиций надо автоматизировать (pg_partman, cron-джоба или миграция), иначе однажды все строки уедут в DEFAULT или вставка упадёт с «no partition of relation found for row».

EXPLAIN (ANALYZE, BUFFERS, VERBOSE, SETTINGS, FORMAT TEXT) SELECT ...;

-- Пишущий запрос: выполнить и откатить
BEGIN;
EXPLAIN (ANALYZE, BUFFERS) UPDATE orders SET status = 'done' WHERE id = 42;
ROLLBACK;

-- Кто съедает базу суммарно (а не кто медленнее всех в единичном вызове)
SELECT queryid, calls, total_exec_time, mean_exec_time, rows, query
FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 20;

-- Что происходит прямо сейчас и кто кого ждёт
SELECT pid, state, wait_event_type, wait_event, now() - query_start AS dur, query
FROM pg_stat_activity WHERE state <> 'idle' ORDER BY dur DESC;

// N+1: 1 запрос за заказами + 100 запросов за клиентами
orders, _ := repo.ListOrders(ctx, 100)
for i := range orders {
    orders[i].Customer, _ = repo.GetCustomer(ctx, orders[i].CustomerID)
}

// Батч: 2 запроса всего
orders, _ := repo.ListOrders(ctx, 100)
ids := lo.Uniq(lo.Map(orders, func(o Order, _ int) int64 { return o.CustomerID }))
customers, _ := repo.GetCustomersByIDs(ctx, ids)   // WHERE id = ANY($1)
byID := make(map[int64]*Customer, len(customers))
for _, c := range customers { byID[c.ID] = c }
for i := range orders { orders[i].Customer = byID[orders[i].CustomerID] }

-- OFFSET: стоимость растёт линейно с номером страницы
SELECT id, title, created_at FROM posts
ORDER BY created_at DESC, id DESC
LIMIT 20 OFFSET 200000;      -- прочитано 200 020 строк, отдано 20

-- Keyset: стоимость постоянна
SELECT id, title, created_at FROM posts
WHERE (created_at, id) < ($1, $2)     -- курсор с прошлой страницы
ORDER BY created_at DESC, id DESC
LIMIT 20;                              -- прочитано 20 строк

CREATE INDEX posts_feed_idx ON posts (created_at DESC, id DESC);

Что стоит запомнить

• EXPLAIN показывает оценки, а EXPLAIN ANALYZE выполняет запрос и добавляет фактические показатели.
• Сначала ищи узел с неверной оценкой или чрезмерным числом строк, затем проверяй статистику и доступные индексы.
• Для глубокой пагинации keyset использует значения последней строки и стабильный уникальный тай-брейкер.

Частые ошибки

• EXPLAIN ANALYZE для UPDATE/DELETE реально меняет данные; даже откат не отменяет все побочные эффекты и блокировки.
• OFFSET заставляет сервер пропустить предыдущие строки и нестабилен при изменениях набора между страницами.
• N+1 часто скрыт в цикле; оцени число запросов с ростом размера результата.

Seq Scan on orders (rows=980) (actual rows=51230 loops=1)
Rows Removed by Filter: 948770

orders, _ := repo.ListOrders(ctx, 100)
for i, order := range orders {
    orders[i].Customer, _ = repo.GetCustomer(ctx, order.CustomerID)
}