У меня есть следующая таблица:
create table foo (id serial primary key, ts date, value int);
Уже заполнено 10к записей. Теперь я пытаюсь найти способ эффективно обновлять несколько строк за один раз, поэтому я написал этот код для сравнения двух подходов (ссылки: здесь для первого метода, здесь для второго):
import asyncpg
import asyncio
import time
async def main():
c = await asyncpg.connect(user='foo', password='bar', database='foodb', host='192.168.100.1')
# prepare data
arr = []
arr2 = [[], []]
for i in range(10001):
row = (i+1, i)
arr.append(row)
arr2[0].append(row[0])
arr2[1].append(row[1])
# using FROM VALUES
values = ",".join([ f"{i}" for i in arr ])
q = f"UPDATE foo SET value = v.value FROM(VALUES {values}) AS v(id, value) WHERE v.id = foo.id;"
start_time = time.time()
await c.execute(q)
print(f"from values: {time.time() - start_time}")
# using UNNEST
q = "UPDATE foo SET value = v.value FROM ( SELECT * FROM UNNEST($1::int[], $2::int[])) AS v(id, value) WHERE foo.id = v.id;"
start_time = time.time()
await c.execute(q, *arr2)
print(f"unnest: {time.time() - start_time}")
if __name__ == '__main__':
asyncio.run(main())
Первый метод последовательно намного быстрее, чем второй.
Что мне не нравится в первом методе, так это то, что (насколько я могу судить - исправления приветствуются) его нельзя использовать с $x
заполнителями для части VALUES
, поэтому приходится вручную строить строку (что также небезопасно). я думаю, с точки зрения SQL-инъекций).
Итак, мои вопросы:
VALUES
строить динамически и использовать с заполнителями?UNNEST
действительно медленнее или я что-то не так делаю?Это postgres 14.2, python 3.9.2, asyncpg 0.27.0.
Я не профилировал UNNEST
производительность, но что касается вашего 1-го вопроса (относительно $x
phaceolders) con.executemany — правильный выбор для нескольких обновлений:
q = "UPDATE foo SET value = $2 WHERE id = $1;"
await c.executemany(q, arr)
Это интересно, у меня сложилось впечатление, что executemany
в asyncpg выполняется несколько операторов вместо одного? ОБНОВЛЕНИЕ: здесь они упоминают об улучшении, но, насколько я понимаю, он все еще выполняет несколько операторов.
@person, вы спрашивали о заполнителях - это позволяет вам это. И это атомарная операция, что означает, что либо все выполнения выполняются успешно, либо вообще ничего.
Я спрашивал о заполнителях при использовании VALUES FROM
, но все равно спасибо, я проверю executemany
.
Профилирование executemany
работает лучше, чем UNNEST
, но все же медленнее, чем метод UPDATE FROM VALUES
. Спасибо!
Когда вы не можете связать массивы, моя работа заключается в динамическом создании токенов привязки, а не всей подстановки...
Грубый и готовый пример...
arr = []
for i in range(10001):
arr.append(i+1)
arr.append(i)
tokens = ",".join([ "(?,?)" for i in range(10001) ]
q = f"UPDATE foo SET value = v.value FROM(VALUES {tokens}) AS v(id, value) WHERE v.id = foo.id;"
await c.execute(q, *arr)
Хорошо, так как asyncpg использует пронумерованные заполнители, код должен быть больше похож на tokens = ",".join([ f"(${i+1}::int,${i+2}::int)" for i in range(0, 20002, 2) ])
, но идея ясна. Попытка этого кажется лучше, чем другие методы, но все же немного медленнее, чем первый (возможно, из-за накладных расходов на наличие 20 тыс. заполнителей...)
@persons Является ли приведение типов ::int
строго обязательным? У вас его нет в вашем первом примере (я давно не играл с pg, а потом это было с PSYCOpg2)
Судя по всему так и есть, иначе вы получите ошибку: asyncpg.exceptions.UndefinedFunctionError: operator does not exist: text = integer HINT: No operator matches the given name and argument types. You might need to add explicit type casts.
. Кажется, это дает некоторую предысторию.
Я могу воспроизвести это в v14, но только при включенном JIT и только в более старой компиляции. Я предполагаю, что это связано с тем, что в более новых компиляциях используется более новая версия некоторой библиотеки, зависящей от llvm (но обе сообщают об одной и той же версии самого llvm). Медленный запрос - это не сам запрос UNNEST, а скорее запрос самоанализа, который использует asyncpg, по-видимому, для определения типов аргументов UNNEST. Каким бы ни было изменение, я не знаю, как отменить его для дальнейшего тестирования.
Поскольку я не могу заставить медлительность воспроизводиться в новых компиляциях, тот факт, что v15 быстрее, неоднозначен; либо что-то было исправлено в v15, либо просто потому, что он был скомпилирован совсем недавно.
Поскольку я думаю, что включение jit по умолчанию было ошибкой (разработчиков PostgreSQL) в первую очередь, я бы сказал, что вам следует просто отключить jit и покончить с этим.
Действительно, использование set jit=off;
в соединении непосредственно перед запуском обновлений снижает все время в целом и, в частности, значительно приближает время для двух исходных методов до такой степени, что UNNEST
теперь находится на одном уровне с другим. Спасибо!
Я не могу воспроизвести это, получая одинаковое время для каждого. Например, 0,048 для первого и 0,052 для второго. Какую версию программного обеспечения (и PostgreSQL, и Python, и модули) вы используете?