Postgres查询对比：单关联查询和先取排序再IN查询哪个更优

结论 #

方案2整体性能优于方案1，另外还有一种无需修改表结构的更优实现方案。

两个方案的优劣对比 #

方案1缺点 #

• 存在大量无效关联计算：不管ITEM记录是教材还是杂志类型，都要做4次左连接匹配，空匹配占比达到50%，数据量越大性能损耗越明显。
• 额外计算开销高：多处使用COALESCE函数做字段合并，会增加CPU消耗，也会干扰Postgres优化器生成最优执行计划。
• 维护性差：后续新增其他条目类型时，需要新增左连接和COALESCE逻辑，SQL会越来越臃肿。

方案2优点 #

• 首次查询仅扫描ITEM单表，只要itemId字段加了索引，即使过滤千万级记录速度也极快。
• 后续两次查询都是同类型表的内连接，没有无效匹配，ID为主键/索引时查询效率极高。
• 应用层仅需按首次查询的ID顺序拼接结果，计算开销极低。
• 缺点是需要3次数据库网络请求，数据量很大时会有少量网络往返开销。

无需修改表结构的更优方案 #

直接用CTE + UNION ALL在数据库层完成所有逻辑，仅需一次请求即可拿到排序好的结果，性能比方案2更高：

WITH sorted_item AS (
    -- 先拿到过滤后且排好序的条目基础信息
    SELECT id, type, ranking
    FROM ITEM
    WHERE itemId IN (1, 2, 3, 4)
)
-- 合并教材数据
SELECT
    s.ranking,
    t.title,
    t.total_pages,
    td.price,
    td.availability,
    td.supplier,
    NULL AS edition
FROM sorted_item s
INNER JOIN TEXTBOOK t ON s.id = t.id AND s.type = 'textbook' -- 替换为实际的type字段枚举值
INNER JOIN TEXTBOOK_DATA td ON t.id = td.id

UNION ALL

-- 合并杂志数据
SELECT
    s.ranking,
    m.title,
    NULL AS total_pages,
    md.price,
    NULL AS availability,
    md.supplier,
    m.edition
FROM sorted_item s
INNER JOIN MAGAZINE m ON s.id = m.id AND s.type = 'magazine' -- 替换为实际的type字段枚举值
INNER JOIN MAGAZINE_DATA md ON m.id = md.id

-- 最终按排名排序
ORDER BY ranking;

该方案的优势：

• 仅需一次数据库请求，没有额外网络往返开销
• 所有关联都是精准匹配，没有无效的左连接计算，执行效率极高
• 返回结果已经按ranking排好序，无需应用层二次处理，可直接渲染UI
• 后续新增其他条目类型只需新增对应的UNION ALL分支，维护成本低

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