PostgreSQL子查询优化：关联外部查询提升大表查询效率

我有两张表：users（约400条记录）和orders（超10万条记录），需要通过email字段关联，统计每个用户的已完成（status = 'COMPLETED'）订单数量。

现有查询与痛点 #

最初的查询写法：

SELECT sub_1.num, u.id FROM users AS u,
(SELECT cust_email AS email, COUNT(purchaseid) AS num
    FROM orders AS o
    WHERE o.status = 'COMPLETED'
    GROUP BY cust_email) sub_1
WHERE u.email = sub_1.email
ORDER BY createdate DESC NULLS LAST

虽然能得到结果，但orders表数据量极大，希望子查询仅处理users表中存在的邮箱对应的订单，减少无效计算。

尝试过在子查询中直接关联users表：

SELECT sub_1.num, u.id FROM users AS u,
(SELECT cust_email AS email, COUNT(purchaseid) AS num
    FROM orders AS o, users AS u
    WHERE o.status = 'COMPLETED'
    and o.cust_email = u.email
    GROUP BY cust_email) sub_1
WHERE u.email = sub_1.email
ORDER BY createdate DESC NULLS LAST

这种写法确实能提速，但当外部查询不是简单全量查询users时，该方案不适用。同时发现第一个查询速度比预期快，想了解PostgreSQL的优化原理，以及这类子查询的最优实现方式。

PostgreSQL自动优化的原理 #

PostgreSQL的查询优化器会对查询进行重写和执行规划，它能识别第一个查询中子查询与外部users表的关联关系，自动将查询转换为更高效的执行逻辑：

• 优化器会优先扫描数据量极小的users表，获取所有邮箱后，再用这些邮箱去orders表中筛选匹配的已完成订单，最后分组统计。
• 这种优化属于关联子查询优化，核心是利用小表的结果过滤大表，避免对orders表进行全量扫描。

最优实现方式 #

方式1：JOIN+GROUP BY（直观高效） #

直接用JOIN关联两张表后分组统计，优化器能自动选择最优执行计划，同时适配复杂的外部查询条件：

SELECT u.id, COUNT(o.purchaseid) AS num
FROM users u
JOIN orders o ON u.email = o.cust_email
WHERE o.status = 'COMPLETED'
GROUP BY u.id, u.createdate
ORDER BY u.createdate DESC NULLS LAST

如果需要包含无订单的用户（统计数量为0），可以改用LEFT JOIN：

SELECT u.id, COALESCE(COUNT(o.purchaseid), 0) AS num
FROM users u
LEFT JOIN orders o ON u.email = o.cust_email AND o.status = 'COMPLETED'
GROUP BY u.id, u.createdate
ORDER BY u.createdate DESC NULLS LAST

方式2：关联子查询（适配复杂过滤） #

当外部查询有复杂的users表过滤条件时，关联子查询能让优化器精准利用过滤结果，仅统计符合条件的用户订单：

SELECT u.id,
       (SELECT COUNT(purchaseid)
        FROM orders o
        WHERE o.cust_email = u.email
          AND o.status = 'COMPLETED') AS num
FROM users u
-- 可在此添加任意复杂的users表过滤条件，例如：WHERE u.country = 'CN'
ORDER BY u.createdate DESC NULLS LAST

方式3：IN子句明确过滤 #

如果需要手动明确控制过滤逻辑，可使用IN子句，优化器同样会高效执行：

SELECT u.id, o_stats.num
FROM users u
JOIN (
    SELECT cust_email AS email, COUNT(purchaseid) AS num
    FROM orders
    WHERE status = 'COMPLETED'
      AND cust_email IN (SELECT email FROM users)
    GROUP BY cust_email
) o_stats ON u.email = o_stats.email
ORDER BY u.createdate DESC NULLS LAST

性能优化补充 #

• 给orders表创建cust_email和status的联合索引：CREATE INDEX idx_orders_cust_email_status ON orders(cust_email, status);，能大幅提升筛选和分组速度。
• 确保users表的email字段有唯一索引（主键或唯一约束），避免关联时出现重复数据。

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