Asyncpg中UNNEST批量UPDATE远慢于FROM VALUES的问题咨询

表结构 #

create table foo (id serial primary key, ts date, value int);

该表已填充10000条记录，为寻找高效的批量更新方法，对比了两种方案，测试代码如下：

测试代码 #

import asyncpg
import asyncio
import time

async def main():
    c = await asyncpg.connect(user='foo', password='bar', database='foodb', host='192.168.100.1')

    # 准备数据
    arr = []
    arr2 = [[], []]
    for i in range(10001):
        row = (i+1, i)
        arr.append(row)
        arr2[0].append(row[0])
        arr2[1].append(row[1])


    # 使用 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}")

    # 使用 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())

测试发现FROM VALUES方法的执行速度始终远快于UNNEST方法，但前者需要手动拼接字符串，存在SQL注入风险，因此有两个疑问：

1. 是否可以动态构建VALUES并使用占位符？
2. UNNEST确实更慢还是我的使用方式有误？

问题解答 #

1. 可以动态构建带占位符的VALUES子句 #

完全可以，无需手动拼接字符串构造VALUES内容，通过以下两种安全方式实现：

方案一：行数组结合unnest

将待更新的行数据打包成行类型数组，用unnest展开，既安全又避免字符串拼接：

UPDATE foo 
SET value = v.value 
FROM unnest($1::foo[]) AS v(id, value) 
WHERE foo.id = v.id;

对应Python代码直接传递行列表即可：

q = "UPDATE foo SET value = v.value FROM unnest($1::foo[]) AS v(id, value) WHERE foo.id = v.id;"
await c.execute(q, arr)

方案二：动态生成有序占位符

针对N行数据，生成($1,$2),($3,$4),...的占位符模板，再扁平化数据列表传入：

# 生成占位符模板
placeholders = ",".join([f"(${2*i+1}, ${2*i+2})" for i in range(len(arr))])
# 扁平化数据
flat_values = [val for row in arr for val in row]
q = f"UPDATE foo SET value = v.value FROM(VALUES {placeholders}) AS v(id, value) WHERE v.id = foo.id;"
await c.execute(q, *flat_values)

这种方式完全使用占位符，无SQL注入风险，性能和手动拼接版本接近。

2. UNNEST性能差异的原因 #

你的UNNEST写法本身没有错误，但性能不如VALUES的核心原因有两点：

• 执行计划开销：UNNEST($1::int[], $2::int[])需要分别展开两个数组再做关联，相比VALUES直接构造内存临时表，多了数组关联的计算开销。
• 数据传递方式：传递两个单独数组时，数据库需要额外处理数组匹配和列映射工作，效率低于直接传递行数据。

如果要优化UNNEST版本性能，可改用上面提到的行数组+unnest写法，性能会接近VALUES版本，同时保留占位符的安全性。

内容的提问来源于stack exchange，提问作者persson