PostgreSQL高效查询两表关联下指定节点的不重复直接邻居

场景说明 #

• 数据库内存在两张表：nodes存储节点属性，edges存储节点间的连接关系，edges.source_id、edges.target_id均为关联nodes.id的外键
• 查询目标：传入任意节点ID子集，返回其所有不重复直接邻居的完整nodes表记录
• 直接邻居判定规则：若某节点与任意指定ID共同出现在edges表的同一行（不管是作为source_id还是target_id），即为指定节点的直接邻居，结果不含传入的查询节点本身
• 测试场景：传入查询ID为1、5，预期返回id为2、3、4的三条节点记录

表结构定义 #

CREATE TABLE nodes (
  id INT,
  prop_1 VARCHAR(64),
  prop_2 VARCHAR(64)
);

CREATE TABLE edges (
  source_id INT,
  target_id INT
);

测试数据 #

INSERT INTO nodes (id, prop_1, prop_2) VALUES 
(1, 'lorem', 'ipsum'),
(2, 'dolor', 'sit'),
(3, 'conseteteur', 'sadipiscing'),
(4, 'elitr', 'sed'),
(5, 'diam', 'nonumy');

INSERT INTO edges (source_id, target_id) VALUES 
(1, 2),
(1, 3),
(1, 4),
(3, 4),
(2, 4),
(4, 5);

1. 正确返回目标结果的SQL写法 #

核心逻辑是先聚合所有关联的邻居ID并提前去重，再关联节点表取完整属性，从根源避免冗余数据生成：

SELECT n.*
FROM nodes n
JOIN (
    -- 匹配指定节点作为起点时指向的邻居
    SELECT target_id AS neighbor_id FROM edges WHERE source_id IN (1,5)
    UNION
    -- 匹配指定节点作为终点时关联的起点邻居
    SELECT source_id AS neighbor_id FROM edges WHERE target_id IN (1,5)
) t ON n.id = t.neighbor_id;

语法说明：UNION操作会自动合并两个结果集并去除重复ID，无需额外加DISTINCT；查询仅返回nodes表字段，不会产生多余列，执行结果完全匹配预期。
如果数据量较大，可以给edges.source_id、edges.target_id分别建索引，查询性能会进一步提升。

2. 原有写法的效率缺陷 #

原查询写法存在三个明显的性能和逻辑问题：

• 隐式生成笛卡尔积：FROM nodes, sources, targets属于无前置关联条件的隐式交叉连接，会先生成三张表行数乘积的全量中间结果集，边表数据量稍大时，中间无效数据会指数级膨胀，占用大量计算和存储资源。
• 过滤逻辑低效：WHERE后用OR拼接两个匹配条件，在笛卡尔积基础上做全量过滤，无法有效利用索引，扫描成本极高。
• 冗余去重开销：交叉连接会生成大量重复的节点匹配记录，最后靠SELECT DISTINCT *做全局去重，排序去重的计算成本远高于在ID聚合阶段提前去重的成本。
  此外原写法用*选择所有字段，会把两个CTE中的ID列也一并返回，直接导致结果出现多余列。

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