PostgreSQL中如何高效合并两查询字段并获取去重值？

背景信息 #

数据表结构 #

\d tx_out
                                        Table "public.tx_out"
       Column        |       Type        | Collation | Nullable |              Default
---------------------+-------------------+-----------+----------+------------------------------------
 id                  | bigint            |           | not null | nextval('tx_out_id_seq'::regclass)
 tx_id               | bigint            |           | not null |
 index               | txindex           |           | not null |
 address             | character varying |           | not null |
 address_raw         | bytea             |           | not null |
 address_has_script  | boolean           |           | not null |
 payment_cred        | hash28type        |           |          |
 stake_address_id    | bigint            |           |          |
 value               | lovelace          |           | not null |
 data_hash           | hash32type        |           |          |
 inline_datum_id     | bigint            |           |          |
 reference_script_id | bigint            |           |          |

\d tx_in
                               Table "public.tx_in"
    Column    |  Type   | Collation | Nullable |              Default
--------------+---------+-----------+----------+-----------------------------------
 id           | bigint  |           | not null | nextval('tx_in_id_seq'::regclass)
 tx_in_id     | bigint  |           | not null |
 tx_out_id    | bigint  |           | not null |
 tx_out_index | txindex |           | not null |
 redeemer_id  | bigint  |           |          |

原查询与需求 #

需要从关联查询中获取tx_id和tx_in_id的去重值，原关联查询逻辑为：

SELECT tx_id, tx_in_id FROM tx_out
LEFT JOIN tx_in ON tx_out_id = tx_id AND tx_out_index = index
WHERE address = ANY($1)

已知条件：

• tx_id 和 tx_in_id 类型相同
• 两列都可能存在重复值
• tx_in_id 可为NULL（LEFT JOIN导致）
• 两列的值可能互相重复

原实现的问题 #

原方案通过CTE拆分后合并，会导致主关联查询被执行两次（source CTE会被combined中的两个子查询分别扫描）：

WITH source AS (
  SELECT tx_id, tx_in_id FROM tx_out
  LEFT JOIN tx_in ON tx_out_id = tx_id AND tx_out_index = index
  WHERE address = ANY($1)
),
combined AS (
  SELECT tx_id AS id FROM source
  UNION ALL
  SELECT tx_in_id AS id FROM source WHERE tx_in_id IS NOT NULL
)
SELECT DISTINCT id FROM combined ORDER BY id;

优化后的实现方案 #

以下两种方案都只执行一次主关联查询，避免重复扫描，性能更优：

方案1：使用LATERAL VALUES展开列 #

SELECT DISTINCT v.id
FROM tx_out
LEFT JOIN tx_in ON tx_out_id = tx_id AND tx_out_index = index
CROSS JOIN LATERAL (
    VALUES (tx_id), (tx_in_id)
) AS v(id)
WHERE address = ANY($1)
AND v.id IS NOT NULL
ORDER BY id;

方案2：使用数组展开（unnest） #

SELECT DISTINCT unnest(ARRAY[tx_id, tx_in_id]) AS id
FROM tx_out
LEFT JOIN tx_in ON tx_out_id = tx_id AND tx_out_index = index
WHERE address = ANY($1)
AND unnest(ARRAY[tx_id, tx_in_id]) IS NOT NULL
ORDER BY id;

优化说明 #

两种方案都将每行的tx_id和tx_in_id转换成两行数据，仅对主关联查询的结果集扫描一次，之后通过DISTINCT去重并排序，彻底避免了原方案中重复执行主查询的问题，在数据量较大时性能提升明显。

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