PostgreSQL中如何将用户表与地址表中ID最大的行关联？

你的原查询确实存在潜在性能问题：当目标用户的地址数量很大时，数据库需要先筛选出该用户的所有地址，再全量排序后取第一条，会产生不必要的中间数据集。下面提供几种更高效的写法，同时建议配合合适的索引进一步提升性能。

方案一：子查询定位最大地址ID #

通过子查询直接获取目标用户对应的最大地址ID，再关联查询，避免全量排序：

SELECT u.id, u.username, u.trader_id, a.id, a.street, a.trader_id
FROM "user" u
JOIN address a ON a.trader_id = u.trader_id
WHERE u.id = 6
AND a.id = (
    SELECT MAX(id) FROM address WHERE trader_id = u.trader_id
);

这种写法的核心是利用MAX(id)快速定位最新地址，数据库可以通过索引直接找到目标行，无需处理所有地址数据。

方案二：使用LATERAL JOIN（PostgreSQL专属） #

LATERAL JOIN可以针对每个用户单独执行子查询，只返回该用户的最新地址，逻辑清晰且高效：

SELECT u.id, u.username, u.trader_id, a.id, a.street, a.trader_id
FROM "user" u
JOIN LATERAL (
    SELECT id, street, trader_id
    FROM address
    WHERE trader_id = u.trader_id
    ORDER BY id DESC
    LIMIT 1
) a ON true
WHERE u.id = 6;

如果需要兼容用户无地址的场景，可将JOIN改为LEFT JOIN，此时地址字段会返回NULL。

方案三：窗口函数标记最新地址 #

适合批量获取多个用户最新地址的场景，单个用户查询也适用：

SELECT u.id, u.username, u.trader_id, a.id, a.street, a.trader_id
FROM "user" u
JOIN (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY trader_id ORDER BY id DESC) AS rn
    FROM address
) a ON a.trader_id = u.trader_id AND a.rn = 1
WHERE u.id = 6;

窗口函数会按trader_id分组，给每个用户的地址按ID降序编号，rn=1即为最新地址。

关键优化：添加复合索引 #

无论采用哪种写法，给address表创建(trader_id, id DESC)的复合索引是提升性能的关键：

CREATE INDEX idx_address_trader_id_id ON address (trader_id, id DESC);

该索引可以让数据库直接定位到每个trader_id对应的最大ID地址行，无需扫描全表或大量数据。

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