PostgreSQL中嵌套SELECT与JOIN的查询性能及返回结果对比

我在PostgreSQL数据库中存在如下两张表（为简化示例做了结构精简）：

article 表结构与示例数据 #

event 表结构与示例数据 #

查询需求 #

为每一条article记录匹配最多1条eventtype_id=4的event记录，期望返回结果格式如下：

现有如下两个实现查询（Query 1、Query 2），对比两个查询的执行速度、返回结果一致性：

Query1（LEFT JOIN写法） #

SELECT
    a.id AS article_id,
    a.summary AS article_summary,
    evnt.comment AS event_comment
FROM 
    article a
LEFT JOIN
    event evnt ON evnt.article_id = a.id AND evnt.eventtype_id = 4;

Query2（相关子查询写法） #

SELECT
    a.id AS article_id,
    a.summary AS article_summary,
    (
        SELECT
            evnt.comment
        FROM
            event evnt
        WHERE
            evnt.article_id = a.id AND
            evnt.eventtype_id = 4
        LIMIT 1
    ) AS event_comment
FROM 
    article a;

对比结论 #

1. 返回结果一致性 #

二者不保证返回完全一致的结果：
核心差异在于：如果同一个article_id下存在多条eventtype_id=4的event记录，Query1会返回重复的article行——1篇文章关联到几条符合条件的事件，结果里就会出现几行这篇文章的数据，直接违反「每条article最多匹配1条event」的需求。而Query2的子查询带LIMIT 1，不管单篇文章匹配到多少条符合条件的事件，都只会返回1行结果，没有匹配事件时就返回NULL，完全符合需求。
只有当所有article在event表中最多只有1条eventtype_id=4的关联记录时，两个查询返回的行数、字段值才会完全相同。

2. 执行速度对比 #

性能表现需要结合索引和数据分布判断：

• 如果event表建了(article_id, eventtype_id)的联合索引，且绝大多数article都存在匹配的type=4事件：两个查询性能几乎没有差别，PostgreSQL优化器会将相关子查询优化为和JOIN接近的执行逻辑。
• 如果没有上述联合索引，或者大部分article都没有匹配的type=4事件：Query2执行速度更快。Query2的子查询找到第一条符合条件的记录就会终止扫描，不需要遍历所有符合条件的event；而Query1的LEFT JOIN会扫描全部eventtype_id=4的记录做关联，一旦单篇文章对应多条符合条件的event，不仅结果不符合预期，还会产生大量无效扫描，导致结果集膨胀，执行速度明显变慢。

如果要写语义正确、性能稳定的JOIN版本，应该用LATERAL关联配合LIMIT，写法如下，性能和Query2持平，语义也更清晰：

SELECT
    a.id AS article_id,
    a.summary AS article_summary,
    evnt.comment AS event_comment
FROM 
    article a
LEFT JOIN LATERAL (
    SELECT comment FROM event 
    WHERE article_id = a.id AND eventtype_id =4 
    LIMIT 1
) evnt ON true;

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