使用 UNNEST 将 Postgres INSERT 性能提高 50%

COPY 命令通常比 INSERT 更快，但许多开发者仍然偏好 INSERT，因为它更灵活，支持如 upserts（INSERT ... ON CONFLICT）和返回插入行等功能。

我们都知道并喜欢的多记录 INSERT 变体，它使用一个 VALUES 子句，后面跟着批次中每一行的元组。 这些查询看起来很长，但也很容易理解。

INSERT INTO sensors (sensorid, ts, value)
VALUES 
  ($1, $2, $3), 
  ($4, $5, $6), 
   ..., 
  ($2998, $2999, $3000);

在另一方面，我们有 UNNEST 变体，使用 SELECT 查询，每列一个数组，并在执行时使用 UNNEST 函数将它们转换为行。

INSERT INTO sensors (ts, sensorid, value) 
  SELECT * 
  FROM unnest(
    $1::timestamptz[], 
    $2::text[], 
    $3::float8[]
)

Postgres 文档( Postgres documentation)将 UNNEST 描述为 "将多个数组（可能是不同数据类型的数组）扩展为一组行 "的函数。 这其实是有道理的，它基本上是将一系列数组扁平化为一个行集，就像 INSERT ... VALUES 查询中的一样。

其中一个主要区别是，第一个变量在查询中包含 batch_size * num_columns 值，而 UNNEST 变量只包含 num_columns 数组（每个数组在扁平化后都包含 batch_size 记录）。 这一点稍后会很重要，请注意！

作者测试了使用不同批次大小（1000、5000、10000条记录）的 INSERT 查询。

结果显示 INSERT .. UNNEST 在数据库层面上比 INSERT .. VALUES 快52.97%，尤其是在查询规划时间上节省了很多。执行时间两种方法相似，但由于 UNNEST 函数需要额外的工作，所以稍微慢一些。

毫无疑问，就数据库性能而言，INSERT .. UNNEST 在批量插入方面优于 INSERT .. VALUES。 通过最大限度地减少规划开销，UNNEST 可以带来近乎神奇的速度提升，使其成为对摄取速度要求极高的情况下的最佳选择。 需要注意的一点是，语言开销和网络延迟通常也会影响应用程序的总运行时间，但数据库的运行时间还是会减少，这总是件好事。

将 INSERT .. UNNEST 视为介于传统 INSERT .. VALUES 和 COPY 之间的性能提升技巧，它在保留 SQL INSERT 语句的灵活性和可组合性的同时，为批量插入提供了显著的速度提升。