Postgres执行Inner Join的内部机制及当前查询优化合理性探讨

Postgres 处理 Inner Join 主要依赖三种算法，优化器会根据数据规模、索引情况自动选择最优方案：

• Nested Loop Join（嵌套循环连接）
  逻辑最简单：拿小表的每一行，去大表（或大表的索引）中查找匹配行。适合小表关联大表且关联列有索引的场景，单次查找成本低；但如果两个表都很大，循环次数过多会导致性能骤降。

• Merge Join（合并连接）
  你的执行计划采用的就是这种，它的前提是两个数据集按关联列有序：

  1. 分别从两个表的索引中读取有序数据（你的索引本身就是按property_id排序的，无需额外排序）；
  2. 用双指针遍历两个有序数据集，像归并排序那样匹配相同property_id的行并输出。
     这种算法的优势是仅需遍历一次数据，且刚好匹配你查询的order by ps.property_id需求，省去了额外排序步骤。

• Hash Join（哈希连接）
  先将较小的表构建成以关联列为键的哈希表，再遍历大表的每一行，用关联列去哈希表中查找匹配项。适合两个表都很大且无有序索引的场景，当构建哈希表的成本低于排序时，优化器会选择这种方式。

从执行计划来看，当前的优化已经非常到位，理由如下：

1. Index Only Scan 最大化效率
   两个表都使用了Index Only Scan——这意味着Postgres无需回表读取原表数据，直接从索引中就能获取所需的property_id和location_id，是索引扫描中效率最高的类型，说明你的索引设计完全匹配查询需求。

2. Merge Join 完美适配排序需求
   查询要求按property_id升序输出，而Merge Join依赖的有序数据集刚好满足该要求，避免了额外的Sort操作，节省了大量排序成本。

3. 成本估算无明显偏差
   执行计划中的成本（cost）和行数（rows）估算与数据规模匹配，说明统计信息准确，优化器的选择合理。

如果想进一步压榨性能，可以尝试以下操作：

• 更新统计信息：执行ANALYZE property_static; ANALYZE property_location;，确保优化器的估算更精准；
• 查看实际执行情况：用EXPLAIN ANALYZE替代EXPLAIN，对比预估成本与实际执行时间，排查是否有异常的IO或CPU消耗；
• 关闭无用JIT：当前JIT的Inlining和Optimization均为false，若JIT带来的开销大于收益，可通过SET jit = off;关闭当前会话的JIT功能。

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