Asyncpg: ОБНОВЛЕНИЕ с UNNEST намного медленнее, чем использование FROM VALUES

У меня есть следующая таблица:

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 действительно медленнее или я что-то не так делаю?

Я не могу воспроизвести это, получая одинаковое время для каждого. Например, 0,048 для первого и 0,052 для второго. Какую версию программного обеспечения (и PostgreSQL, и Python, и модули) вы используете?

jjanes 15.01.2023 17:39

Это postgres 14.2, python 3.9.2, asyncpg 0.27.0.

persson 15.01.2023 19:49
Почему в Python есть оператор "pass"?
Почему в Python есть оператор "pass"?
Оператор pass в Python - это простая концепция, которую могут быстро освоить даже новички без опыта программирования.
Некоторые методы, о которых вы не знали, что они существуют в Python
Некоторые методы, о которых вы не знали, что они существуют в Python
Python - самый известный и самый простой в изучении язык в наши дни. Имея широкий спектр применения в области машинного обучения, Data Science,...
Основы Python Часть I
Основы Python Часть I
Вы когда-нибудь задумывались, почему в программах на Python вы видите приведенный ниже код?
LeetCode - 1579. Удаление максимального числа ребер для сохранения полной проходимости графа
LeetCode - 1579. Удаление максимального числа ребер для сохранения полной проходимости графа
Алиса и Боб имеют неориентированный граф из n узлов и трех типов ребер:
Оптимизация кода с помощью тернарного оператора Python
Оптимизация кода с помощью тернарного оператора Python
И последнее, что мы хотели бы показать вам, прежде чем двигаться дальше, это
Советы по эффективной веб-разработке с помощью Python
Советы по эффективной веб-разработке с помощью Python
Как веб-разработчик, Python может стать мощным инструментом для создания эффективных и масштабируемых веб-приложений.
0
2
64
3
Перейти к ответу Данный вопрос помечен как решенный

Ответы 3

Я не профилировал UNNEST производительность, но что касается вашего 1-го вопроса (относительно $x phaceolders) con.executemany — правильный выбор для нескольких обновлений:

q = "UPDATE foo SET value = $2 WHERE id = $1;"
await c.executemany(q, arr)

Это интересно, у меня сложилось впечатление, что executemany в asyncpg выполняется несколько операторов вместо одного? ОБНОВЛЕНИЕ: здесь они упоминают об улучшении, но, насколько я понимаю, он все еще выполняет несколько операторов.

persson 14.01.2023 23:13

@person, вы спрашивали о заполнителях - это позволяет вам это. И это атомарная операция, что означает, что либо все выполнения выполняются успешно, либо вообще ничего.

RomanPerekhrest 14.01.2023 23:18

Я спрашивал о заполнителях при использовании VALUES FROM, но все равно спасибо, я проверю executemany.

persson 14.01.2023 23:20

Профилирование executemany работает лучше, чем UNNEST, но все же медленнее, чем метод UPDATE FROM VALUES. Спасибо!

persson 14.01.2023 23:23

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

Грубый и готовый пример...

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 тыс. заполнителей...)

persson 14.01.2023 23:36

@persons Является ли приведение типов ::int строго обязательным? У вас его нет в вашем первом примере (я давно не играл с pg, а потом это было с PSYCOpg2)

MatBailie 15.01.2023 00:12

Судя по всему так и есть, иначе вы получите ошибку: 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.. Кажется, это дает некоторую предысторию.

persson 15.01.2023 12:38
Ответ принят как подходящий

Я могу воспроизвести это в v14, но только при включенном JIT и только в более старой компиляции. Я предполагаю, что это связано с тем, что в более новых компиляциях используется более новая версия некоторой библиотеки, зависящей от llvm (но обе сообщают об одной и той же версии самого llvm). Медленный запрос - это не сам запрос UNNEST, а скорее запрос самоанализа, который использует asyncpg, по-видимому, для определения типов аргументов UNNEST. Каким бы ни было изменение, я не знаю, как отменить его для дальнейшего тестирования.

Поскольку я не могу заставить медлительность воспроизводиться в новых компиляциях, тот факт, что v15 быстрее, неоднозначен; либо что-то было исправлено в v15, либо просто потому, что он был скомпилирован совсем недавно.

Поскольку я думаю, что включение jit по умолчанию было ошибкой (разработчиков PostgreSQL) в первую очередь, я бы сказал, что вам следует просто отключить jit и покончить с этим.

Действительно, использование set jit=off; в соединении непосредственно перед запуском обновлений снижает все время в целом и, в частности, значительно приближает время для двух исходных методов до такой степени, что UNNEST теперь находится на одном уровне с другим. Спасибо!

persson 16.01.2023 13:13

Другие вопросы по теме