Почему UNNEST быстрее VALUES в PostgreSQL: замеры по этапам

В прошлой статье я сравнил массовую вставку через VALUES и UNNEST. UNNEST оказался быстрее, но моё объяснение через дорогой разбор запроса было неполным: pgx переиспользует prepared statement. Я разложил время INSERT по этапам: основная разница оказалась в bind и расходах за пределами execute, а не в самой записи строк.

Ниже разберу эти этапы по исходникам PostgreSQL и отдельно покажу замеры UPDATE и UPSERT. У них выигрыш другой, и объяснить его только числом параметров уже нельзя.

Есть два запроса

Наша тестовая таблица будет состоять из трёх столбцов:

CREATE TABLE events (
    id    bigint PRIMARY KEY,
    kind  text NOT NULL,
    value float8 NOT NULL
);

Вариант с VALUES на 10 000 строк из трёх колонок содержит 30 000 параметров:

INSERT INTO events (id, kind, value)
VALUES ($1, $2, $3), ($4, $5, $6), ... ($29998, $29999, $30000)

А вариант с массивами содержит три параметра при любом размере пакета:

INSERT INTO events (id, kind, value)
SELECT * FROM unnest($1::bigint[], $2::text[], $3::float8[])

Данных в обоих случаях одинаково. У обоих по 30 000 значений. Меняется только форма, в которой они попадают на сервер.

Где PostgreSQL тратит время

При выполнении параметризованного запроса по расширенному протоколу PostgreSQL обрабатывает сообщения Parse, Bind и Execute. У каждого своя работа:

1. Parse. Сервер разбирает текст SQL и строит дерево запроса.
2. Bind. Сервер декодирует параметры и получает план. Если плана нет в кэше, то на этом же этапе запускается планирование.
3. Execute. Executor вставляет строки и обновляет индекс.

Плюс работа клиента: собрать SQL, закодировать аргументы, передать байты.

Дальше посмотрим исходники PostgreSQL 17.9: что каждый этап делает с нашими двумя запросами.

Parse. В transformInsertStmt для многострочного VALUES есть цикл по всем строкам. Каждая строка проходит transformExpressionList (analyze.c:799). 10 000 строк дают 10 000 итераций и 30 000 выражений с параметрами. При фиксированном числе колонок размер этой части запроса растёт как O(N). У параметризованного unnest число аргументов и форма SQL не зависят от размера пакета. Это не делает всю обработку O(1): массивы всё равно нужно передать, декодировать и перебрать. В simple protocol значения подставляются в SQL литералами, поэтому текст запроса с массивами тоже растёт.

Bind. Число параметров читается из сообщения как двухбайтовое целое (postgres.c:1724). Отсюда, кстати, лимит в 65 535 параметров. Затем цикл по каждому параметру (postgres.c:1819): поиск функции приёма для типа и её вызов (postgres.c:1943). Для VALUES это 30 000 вызовов. Для unnest три, каждый декодирует целый массив за один проход. В конце bind вызывается GetCachedPlan (postgres.c:2023): он получает подходящий план, при необходимости строит новый. Сам вызов ещё не означает, что планирование происходит каждый раз.

Планирование. Планировщик прогоняет списки VALUES целиком через preprocess_expression. В комментариях к коду явно написано: «Preprocess the values lists fully» (planner.c:1000).

Execute. array_unnest перебирает элементы массива через array_iter_next и отдаёт их по одному (arrayfuncs.c:6320). Работа с данными никуда не исчезает. То есть для запроса с VALUES каждое значение ещё до выполнения проходит разбор и привязку как отдельный элемент запроса.

Это гипотеза о том, где должна быть разница. Теперь замеры.

Как замерял

• PostgreSQL 17.9 в Docker, данные в tmpfs. Go 1.26.8, pgx v5.10.0. AMD Ryzen 5 PRO 4650U, клиент и сервер на одной машине.
• Каждый прогон вставляет 300 000 строк пакетами по 100, 1 000 и 10 000 строк. Соединение свежее, перед прогоном выполняется TRUNCATE. Из пяти повторов выбирается прогон с медианным общим временем; остальные показатели в строке относятся к этому же прогону.
• Время планирования и выполнения берётся из pg_stat_statements с pg_stat_statements.track_planning = on. Всё остальное считается как разница с общим временем на клиенте.
• Отдельный прогон с log_min_duration_statement = 0. В этом режиме PostgreSQL пишет в лог длительность parse, bind и execute отдельно (документация).

Данные в tmpfs занижают стоимость дисковой фиксации по сравнению с устойчивым хранилищем. Это локальный стенд без конкурирующей нагрузки, не модель production. Реальные показатели нужно проверять на вашей схеме, оборудовании и нагрузке. Логирование каждого этапа тоже добавляет накладные расходы, поэтому две серии замеров ниже не стоит сравнивать как один эксперимент.

Скачать код стенда и сохранённые результаты. В архиве есть README с командами запуска, Docker Compose с закреплённым образом, Go-код, проверки фактов и скрипт разбора логов. В results.md сохранён исходный INSERT-замер: отдельные результаты пяти повторов и сырые логи для него не входят в комплект, поэтому независимо пересчитать исходную серию нельзя. В mutations-results.md сохранены все пять повторов новой серии UPDATE и UPSERT от 29 октября; её условия описаны ниже.

Способы вставки:

• simple protocol: pgx подставляет значения литералами в текст SQL, сервер каждый раз разбирает запрос заново.
• prepared: режим pgx по умолчанию (QueryExecModeCacheStatement). Запрос подготавливается один раз на соединение, дальше только bind и execute.
• prepared, custom plan: то же с plan_cache_mode = force_custom_plan, план строится при каждом вызове.
• COPY: conn.CopyFrom для сравнения.

Ядро замера на Go:

// Режим выполнения pgx передаётся первым аргументом Exec.
func insertUnnest(mode pgx.QueryExecMode) func(context.Context, *pgx.Conn, []int64, []string, []float64) error {
	return func(ctx context.Context, conn *pgx.Conn, ids []int64, kinds []string, vals []float64) error {
		_, err := conn.Exec(ctx, unnestSQL, mode, ids, kinds, vals)
		return err
	}
}

// После прогона: время планирования и выполнения только для вставок.
err = conn.QueryRow(ctx, `
	SELECT sum(total_plan_time), sum(total_exec_time), sum(calls)
	FROM pg_stat_statements WHERE query LIKE 'INSERT INTO events%'`).Scan(&planMs, &execMs, &calls)

Результат: этапы на одном пакете

Размер пакета 10 000 строк, 20 вставок с prepared statements. Ниже средние длительности bind и execute по серверному логу. Parse произошёл один раз на соединение и показан отдельно. Общее время на клиенте усреднено по 20 пакетам и включает долю первоначальной подготовки.

Три наблюдения.

Время execute близкое: около 31 мс в обоих случаях. В этом замере основная экономия не в записи строк и обновлении индекса.

Среднее время bind у VALUES в 7,6 раза больше. Самый быстрый bind занял 17,8 мс: даже короткие вызовы остаются заметно дороже, чем у UNNEST. Это согласуется с гипотезой о накладных расходах на большое число параметров, но чистую стоимость их декодирования этот замер не выделяет. В bind входит и другая работа, включая получение плана.

За пределами измеренных серверных этапов остаётся около 29 мс у VALUES и около 8 мс у UNNEST. Здесь parse учтён один раз на 20 пакетов, а не вычтен целиком из каждого пакета. Остаток включает клиентскую работу, передачу и другие затраты вне этих таймеров; это не отдельный профиль клиента. В стенде VALUES заново собирает SQL-строку и отдельные аргументы на каждом вызове. Переиспользование готовой строки может сократить эту часть разницы, но в приведённой серии такой вариант не измерялся.

Результаты для 300 000 строк

Более широкий бенчмарк. Время в таблице указано в миллисекундах на 300 000 строк. «Остальное» включает parse, приём параметров, клиента и сеть.

Пакет 10 000 строк

Пакет 1 000 строк

Пакет 100 строк

Время выполнения у VALUES и UNNEST близкое. В prepared-режиме наибольшая часть разницы приходится на «Остальное». На маленьких пакетах доля планирования, особенно с принудительным custom plan, уже заметнее.

UNNEST с prepared быстрее VALUES с prepared в 1,6 раза на пакетах по 10 000 строк, в 1,3 раза на 1 000 и в 1,2 раза на 100. Без подготовки разрыв больше: VALUES в простом протоколе на 10 000 строк в 2,1 раза медленнее UNNEST в том же режиме. Каждый вызов заново разбирает 30 000 литералов.

Почему планирование мало влияет на этот INSERT

По умолчанию prepared statement первые пять раз выполняется с индивидуальным планом. Потом PostgreSQL сравнивает их среднюю стоимость, включая цену планирования, со стоимостью общего плана. Если общий дешевле, дальше используется он (plancache.c:1074, документация PREPARE).

В замере это видно по pg_prepared_statements: на 300 вызовов пакетами по 1 000 строк вышло 5 индивидуальных планов и 295 общих. У простого INSERT ... VALUES выбирать почти не из чего, план по сути один и тот же. Поэтому PostgreSQL быстро переходит на общий план и больше не планирует.

Если принудительно планировать каждый раз (force_custom_plan), цена становится видна. На пакете 10 000 строк один план VALUES стоит около 3,9 мс. Это 116 мс на 300 000 строк из общих 1 372. Заметно, но меньше 10%. Здесь принудительный режим нужен для диагностики: он отделяет случай с повторным планированием от поведения по умолчанию. Сам по себе prepared statement не гарантирует переход на общий план.

Массовый UPDATE через UNNEST

Для обновления массивы можно развернуть в строки и соединить их с events по первичному ключу:

UPDATE events AS e
SET kind = src.kind,
    value = src.value
FROM unnest($1::bigint[], $2::text[], $3::float8[])
    AS src(id, kind, value)
WHERE e.id = src.id;

Эквивалент для двух строк через VALUES:

UPDATE events AS e
SET kind = src.kind,
    value = src.value
FROM (VALUES
    ($1::bigint, $2::text, $3::float8),
    ($4::bigint, $5::text, $6::float8)
) AS src(id, kind, value)
WHERE e.id = src.id;

Здесь снова три параметра с массивами против трёх параметров на каждую строку. Типы у VALUES указаны явно: в наборе строк источника нет контекста целевых столбцов, как в INSERT. Вызов из pgx остаётся таким же: conn.Exec(ctx, updateSQL, ids, kinds, vals).

Обновятся только существующие строки с совпавшим id; отсутствующие ключи не вставятся. Повтор id в пакете опасен: PostgreSQL обновит целевую строку один раз, но выбор строки источника не гарантирован. Это ограничение UPDATE ... FROM, одинаковое для VALUES и UNNEST (документация UPDATE). Перед отправкой пакета ключи нужно сделать уникальными по выбранному правилу: например, отклонять дубли или оставлять последнее значение в приложении.

В отличие от простой вставки, здесь планировщик выбирает способ соединения с существующей таблицей. Размер пакета, статистика и выбор общего или индивидуального плана могут менять стоимость выполнения. Поэтому близкое время execute из INSERT-бенчмарка нельзя автоматически ожидать от UPDATE.

UPSERT через UNNEST

Для вставки с обновлением при конфликте к исходному запросу добавляется ON CONFLICT:

INSERT INTO events AS e (id, kind, value)
SELECT src.id, src.kind, src.value
FROM unnest($1::bigint[], $2::text[], $3::float8[])
    AS src(id, kind, value)
ON CONFLICT (id) DO UPDATE
SET kind = EXCLUDED.kind,
    value = EXCLUDED.value;

В нашей таблице конфликт определяется первичным ключом id. EXCLUDED содержит предлагаемую для вставки строку: её значения заменят kind и value существующей записи. Для сравнения через VALUES используется тот же ON CONFLICT, меняется только источник строк.

У UPSERT другое поведение при дублях внутри пакета. Если один statement пытается повторно изменить одну строку через ON CONFLICT DO UPDATE, PostgreSQL возвращает ошибку 21000 (cardinality violation). Это происходит и для уже существующего ключа, и для нового ключа, который повторился в пакете. Такой запрос не реализует правило «последний победил»: сначала нужно убрать дубли (документация INSERT).

Если повторная запись тех же значений не нужна, в запрос можно добавить условие:

-- После SET в ON CONFLICT DO UPDATE:
WHERE (e.kind, e.value)
    IS DISTINCT FROM (EXCLUDED.kind, EXCLUDED.value);

Оно пропускает обновление совпадающих значений, но конфликтующая строка всё равно блокируется. Если добавить RETURNING, пропущенные этим условием строки не попадут в результат (документация INSERT). В замере ниже этого условия нет: все существующие строки получают новые значения.

Для обоих сценариев массивы должны иметь одинаковую длину и соответствовать друг другу по позициям. Короткий массив дополняется NULL, а не вызывает ошибку длины (документация UNNEST). В нашей схеме это может закончиться нарушением NOT NULL; в схеме с nullable-столбцами — незаметно записать NULL. Длины стоит проверять до Exec. Три пустых массива дают ноль строк и ничего не меняют. Форма запроса постоянная, но объём передаваемых данных продолжает расти: пакеты всё равно нужно ограничивать.

Отдельные замеры UPDATE и UPSERT

Эту серию я запустил отдельно 29 октября 2026 года: PostgreSQL 17.9 из того же Docker Compose, Go 1.26.9, pgx v5.10.0, тот же Ryzen 5 PRO 4650U и tmpfs. Абсолютные времена не стоит напрямую сравнивать с исходной INSERT-серией. Здесь сравниваются только prepared VALUES и UNNEST с plan_cache_mode = auto, без конкурирующих клиентов.

Каждый прогон обрабатывает 300 000 строк. Для UPDATE все ключи заранее есть в таблице. Для UPSERT подготовлены три состояния: нет существующих ключей, существуют только чётные ключи (50% конфликтов в каждом полном пакете), существуют все ключи. В исходных строках kind = 'old', value = -1, поэтому обновления действительно меняют значения. Повторов ключей внутри пакета нет.

Перед каждым повтором — свежее соединение, TRUNCATE, загрузка исходных строк, ANALYZE и сброс статистики. Подготовка и проверка итогового содержимого не входят в замер. Каждый пакет выполняется отдельным Exec вне явной транзакции. SQL и аргументы VALUES собираются внутри таймера, как в исходном INSERT-замере.

Ниже полное клиентское время в миллисекундах на 300 000 строк. Из пяти повторов выбран прогон с медианным временем. Сначала выполнялись пять повторов VALUES, затем пять UNNEST; порядок не перемешивался случайным образом. Все повторы и серверные показатели сохранены в архиве в mutations-results.md. Воспроизведение: go run . -mutations -rows 300000 -repeat 5.

На пакете 10 000 строк UPDATE через UNNEST быстрее в 1,61 раза, UPSERT — в 1,11–1,17 раза в зависимости от доли конфликтов. На пакете 100 строк разница между медианами составляет всего несколько процентов; эта серия не даёт оснований обещать такой небольшой выигрыш на другой нагрузке.

Для UPDATE на 10 000 строк отличается и серверное выполнение: 2 095 мс у VALUES против 1 576 мс у UNNEST. Остаток полного времени — 700 и 198 мс, планирование — 67 и 2 мс. Поэтому здесь нельзя объяснить весь выигрыш только количеством параметров при bind. В выбранном прогоне VALUES получил 19 общих и 11 индивидуальных планов, UNNEST — 25 и 5. Без сравнения самих планов и отдельного профиля точную причину разницы execute этот замер не выделяет.

У UPSERT на большом пакете основная часть времени приходится на выполнение: при 100% конфликтов 5 505 мс у VALUES и 5 130 мс у UNNEST. Экономия на форме запроса сохраняется, но на фоне обработки существующих строк она даёт меньшую долю общего времени. Конкурентные блокировки, триггеры и дополнительные индексы в этой серии не проверялись. Для неё не собиралась отдельная разбивка parse/bind/execute по логам: «Остальное» по-прежнему объединяет несколько видов затрат.

Что удалось проверить

Здесь важно разделить выводы INSERT-бенчмарка и поведение самих SQL-запросов.

Отдельно про бенчмарк Timescale, на который часто ссылаются. В статье Boosting Postgres INSERT Performance by 2x With UNNEST (James Blackwood-Sewell, ноябрь 2024) суммарное время планирования и выполнения на пакетах по 1 000 строк падает с 2,19 до 1,03 с. Автор связывает выигрыш в основном с планированием. Мой замер этого не воспроизводит: планирование и выполнение у меня отличаются мало. Условия разные. У них pgbench со статическими SQL-файлами, без индекса, версия PostgreSQL не указана. Я их тест не запускал, поэтому называю это расхождением, а не опровержением.

Ещё одна поправка, уже к себе. В прошлой статье я писал, что на больших пакетах PostgreSQL «тратит заметное время на разбор» VALUES. С prepared statements в pgx запрос разбирается один раз на соединение. Дальше платят за bind и за работу клиента.

И одно предупреждение. pg_stat_statements не видит ни разбор, ни приём параметров. Если сравнивать VALUES и UNNEST только по total_plan_time и total_exec_time, большая часть разницы просто не попадёт в отчёт.

Итог

В INSERT-серии UNNEST оказался быстрее VALUES. На пакете 10 000 строк те же 30 000 значений приходят тремя параметрами с массивами. Форма параметризованного SQL не растёт с размером пакета, а при bind сервер обрабатывает три массива вместо 30 000 отдельных параметров. Работа с самими значениями не исчезает. С prepared statements разбор нужен один раз на соединение; основная разница в этой серии остаётся в bind и расходах за пределами execute. Само выполнение INSERT у двух способов заняло близкое время.

Для UPDATE картина другая: на большом пакете отличается и execute. У UPSERT выигрыш меньше и зависит от доли конфликтов. Эти результаты нельзя свести к одному объяснению или общему коэффициенту ускорения.

Практические выводы:

• В этом INSERT-бенчмарке на пакетах по 10 000 строк UNNEST в 1,6 раза быстрее подготовленного VALUES. UPDATE и UPSERT проверены отдельной серией выше: их результат зависит от соединения с таблицей и доли конфликтов. Единого коэффициента ускорения для всех операций нет.
• Для чистой вставки COPY оказался быстрее на пакетах по 10 000 строк. На меньших пакетах в этом стенде преимущества перед UNNEST не было.
• Если остаётесь на VALUES, держите размер пакета постоянным. Тогда pgx переиспользует prepared statement, и повторный разбор SQL не нужен.
• При самостоятельных замерах смотрите на время клиента целиком или включайте log_min_duration_statement. Данных только от pg_stat_statements будет мало.

Я бы не менял все вставки на UNNEST по результатам этого стенда. Сначала стоит понять, какую долю времени операции занимает работа с базой. Если она заметна, сравнить варианты на своей схеме и нагрузке, с теми же требованиями к конфликтам и атомарности. И уже по этому замеру решать, стоит ли менять код.