PostgreSQL嵌套循环连接：并发插入是否会影响查询结果？

核心结论 #

• 其他查询可以在嵌套循环连接执行期间插入数据，是否影响结果完全取决于当前事务的隔离级别以及连接类型。
• 普通嵌套循环和物化嵌套循环在并发插入场景下，确实可能产生结果差异。

1. 并发插入的可能性 #

PostgreSQL基于多版本并发控制（MVCC）机制运行，默认隔离级别为READ COMMITTED。在该级别下，其他事务的插入操作只要完成提交，后续的查询就能读取到新数据。无论执行的是普通还是物化嵌套循环，连接执行期间其他事务都可以正常对内表或外表执行插入操作——数据库不会因当前查询的连接操作阻塞写入。

2. 普通嵌套循环连接的行为 #

普通嵌套循环会为外表的每一行元组重新扫描内表：

• 在READ COMMITTED隔离级别下，每次扫描内表时，都会读取当前事务启动后所有已提交的数据。
• 如果在循环执行过程中，其他事务提交了针对内表的插入操作，后续的内表扫描就会包含这些新插入的元组。
• 最终的连接结果会包含这些新数据与外表元组匹配的部分。

3. 物化嵌套循环连接的行为 #

物化嵌套循环会一次性扫描内表并将数据加载到内存，之后所有外表元组的匹配都基于这份内存中的快照：

• 内表的物化操作发生在连接执行的初期，之后不管其他事务插入多少数据，都不会影响内存中的这份快照。
• 最终的连接结果只会包含物化时刻内表已有的数据，完全不会包含后续插入的新数据。

4. 结果差异的场景举例 #

假设内表初始有10条数据，外表有5条数据，执行连接时：

• 普通嵌套循环：执行到第3个外表元组时，其他事务插入了1条与第4、5个外表元组匹配的内表数据。最终结果会包含这两条新的匹配记录。
• 物化嵌套循环：内表物化时只有初始10条数据，后续插入的新数据不会被纳入匹配，最终结果里没有这两条新记录。

隔离级别的影响 #

如果事务使用REPEATABLE READ或更高隔离级别：

• 普通嵌套循环的每次内表扫描都会看到事务启动时的快照，不会看到后续其他事务提交的插入数据。
• 物化嵌套循环的行为和READ COMMITTED下一致，因为物化也是基于事务启动时的快照。
• 这种情况下两种连接类型的结果不会有差异。

内容的提问来源于stack exchange，提问作者Kim Beob Woo