unnest配合lateral是postgresql最高效可控的数组展开方式，必须显式使用以支持外层字段引用和条件过滤，避免隐式cross join导致的性能退化。
直接用 UNNEST 配合 LATERAL 是 PostgreSQL 中最高效、可控的数组展开方式，但必须配合明确的上下文引用和过滤逻辑，否则容易退化成全表扫描或爆炸式中间行。
UNNEST 必须搭配 LATERAL 才能安全用于关联展开
很多人写 SELECT id, UNNEST(tags) FROM posts 能跑通，但这只是“碰巧能用”，不是“正确用法”。它隐式触发了 CROSS JOIN 语义，无法在展开时引用外层字段做条件过滤，也无法与其它表做有效 JOIN。一旦你需要按用户查其标签、按订单查其商品明细，就必须显式写出 LATERAL。
-
LATERAL让子查询能读取外层表的列（如p.user_id），这是动态展开的前提 - PostgreSQL 10+ 虽支持隐式
LATERAL，但显式写出可避免执行计划歧义，也方便加注释或调试 - 错误写法：
SELECT p.id, t.tag FROM posts p, UNNEST(p.tags) AS t(tag)—— 等价于CROSS JOIN，语义模糊且不可控 - 正确写法：
SELECT p.id, t.tag FROM posts p LATERAL UNNEST(p.tags) AS t(tag)
大数组展开前务必截断或预过滤
单个 tags 字段存几百甚至上千个元素时，UNNEST 会生成等量中间行，不仅内存暴涨，还可能拖垮整个查询的并行度和排序性能。这不是函数慢，是数据量失控。
- 用
WITH ORDINALITY+LIMIT控制单行展开上限：LATERAL (SELECT elem FROM UNNEST(p.tags) WITH ORDINALITY AS t(elem, n) WHERE n - 如果数组本身来自聚合（如
array_agg），优先在聚合层加LIMIT或DISTINCT，比展开后再去重更省资源 - 对 JSONB 数组字段（如
details->'items'），避免反复调用jsonb_array_elements()；应先用details->'items'提取一次，再LATERAL UNNEST(ARRAY(...))
IN 列表场景下，优先用 UNNEST + JOIN 替代硬拼字符串
后端传入 ID 列表（如 [101, 102, 105]）时，拼成 WHERE id IN (101, 102, 105) 看似简单，但当列表超百项、或嵌套在复杂查询中时，优化器常放弃使用索引，改走顺序扫描。换成数组传参 + UNNEST 关联，能让执行计划回归“两表 JOIN”模式，更容易触发 Index Scan 和并行执行。
- 应用层传参用
ARRAY[101,102,105]，SQL 写成：SELECT * FROM orders o JOIN UNNEST($1) AS v(id) ON o.id = v.id - 若需保留原始顺序，加
WITH ORDINALITY并按序号排序：JOIN UNNEST($1) WITH ORDINALITY AS v(id, idx) - 注意：
UNNEST不自动去重，若输入数组含重复 ID，结果也会重复；必要时加DISTINCT ON (v.id)或外层GROUP BY
标量子查询里别用 UNNEST，除非你真需要多行返回
在 SELECT 子句中写 (SELECT UNNEST(p.tags)) 是非法的 —— 标量子查询只能返回单值，而 UNNEST 返回多行。这类错误常出现在想“把数组转成逗号字符串”却误用展开函数的场景。
- 要转字符串，用
array_to_string(p.tags, ',')，不是UNNEST - 要映射码值（如
{1,3}→{'高','低'}），用array_map_dict(p.tags, '{"1":"高","3":"低"}'::jsonb)这类纯 SQL 函数，避免LATERAL引入额外开销 - 真要在
SELECT列里展开多行？那说明你该重构查询结构，把展开逻辑移到FROM层，用LATERAL+JOIN显式表达意图
最关键的细节往往藏在执行计划里：每次加完 LATERAL UNNEST，一定要 EXPLAIN ANALYZE 看是否真的用了 Index Scan，以及中间行数是否可控。数组展开不是“写出来就快”，而是“控制住膨胀才快”。
