带CTE的UPDATE查询性能更优原因及无LATERAL替代写法咨询

一、为何带CTE和LATERAL的查询性能更优？ #

两种写法的核心差异在于执行模式的本质不同：

1. 带CTE和LATERAL的写法是批量关联+批量更新：
   • 从执行计划能看到，它先通过Hash Join对test和test2各做一次全表扫描，一次性关联出所有匹配行并转换为JSON，生成CTE数据集后，再和test表关联完成批量更新。整个过程的时间复杂度为O(N+M)（N为test表行数，M为test2表行数），仅需扫描test两次、test2一次，操作次数是线性量级。
2. 嵌套子查询的写法是逐行循环查询：
   • 这种写法会对test表的每一行单独触发一次子查询，去test2表匹配数据并转换JSON。如果test表有几十万行，就会执行几十万次test2查询（无索引时每次都是全表扫），时间复杂度变为O(N*M)，操作次数呈指数级增长，这就是它耗时极长的根本原因。

此外，第一种写法的关联和数据处理仅耗时约4秒，剩余时间是更新操作的IO开销；而第二种写法光是循环查询的开销就会远超这个数值，更不用说后续的更新了。

二、不使用LATERAL的等效查询 #

根据需求（将test2每行数据转JSON后更新到对应test行的json字段），可以通过子查询预先处理test2的JSON转换，再关联test表完成批量更新，无需使用LATERAL：

UPDATE test t
SET json = t2_json.json_data
FROM (
    SELECT test_id, to_json(t2) AS json_data
    FROM test2 t2
) t2_json
WHERE t.id = t2_json.test_id;

补充说明： #

如果存在一个test对应多个test2行的场景（比如你的第二个查询里加了LIMIT 1），可以通过DISTINCT ON指定取每个test_id对应的某一行（例如最新的test2行）：

UPDATE test t
SET json = t2_json.json_data
FROM (
    SELECT DISTINCT ON (test_id) test_id, to_json(t2) AS json_data
    FROM test2 t2
    ORDER BY test_id, id DESC  -- 按test_id分组，取id最大的行
) t2_json
WHERE t.id = t2_json.test_id;

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