为何带额外JOIN的PostgreSQL查询比归一化数据查询更快？

嘿，这个情况确实有点反直觉！通常内嵌标注因为不需要额外JOIN，查询会更快，但这次你的特定查询里，用独立标注表的版本反而快了近100ms。咱们从两个执行计划的细节里拆解原因：

先看核心执行流程的差异 #

独立标注查询的高效路径 #

在独立标注的查询里，PostgreSQL走了一条很聪明的优化路线：

1. 先扫小表过滤标注：先对极小的users_p表做全表扫描，只留下符合所有标注条件的100条用户ID（耗时才0.05ms，几乎可以忽略）。
2. 物化结果减少重复扫描：把users和locations过滤后的结果（954行）做了Materialize（物化）缓存起来，避免后续重复扫描这两个大表。
3. 用索引快速匹配位置标注：最后通过locations_purpose_index索引扫描locations_p表，每次匹配locations_id只花0.002ms，954次也才不到2ms。

关键的是，标注过滤的工作都在小表或索引查询里完成，大表只需要处理业务条件，成本低很多。

内嵌标注查询的冗余成本 #

而内嵌标注的查询里，所有过滤都堆在了大表的全表扫描里：

1. users表要同时过滤业务条件（国家、年龄）和4个标注位运算条件，虽然最终只留下2行，但每行都要做多次计算。
2. locations表更夸张：要同时过滤年份条件+3个标注位运算条件，每次扫描25万行（200332条被过滤+49668条保留），还得循环2次，累计扫描50万行，每个行都要做3次位运算判断——这部分的耗时直接从独立查询的_{310ms涨到了}414ms，是主要的慢因。

具体原因拆解 #

• 标注过滤的时机太关键：独立查询里，标注过滤是早过滤，用小表先把不符合的用户ID筛掉，后续大表只处理有效数据；而内嵌查询里，标注过滤和业务条件混在一起，大表每行都要额外做多次位运算，累计成本很高。
• 索引救了独立查询的命：locations_p表有专门的索引locations_purpose_index，匹配locations_id的速度远快于在大locations表里逐行做位运算过滤。内嵌查询里没有这个索引优化，只能硬扛全表扫描的计算成本。
• 物化缓存减少重复劳动：独立查询里的Materialize把users+locations的结果缓存起来，和users_p做100次循环Join时不用重复扫描大表，节省了重复计算的时间；内嵌查询没有这个优化，每次循环都要重新扫大表做过滤。

总结 #

这次反直觉的结果，本质是PostgreSQL对两个查询生成的执行计划差异：独立标注的计划选择了用小表早过滤+索引匹配标注+物化缓存的高效路径，而内嵌标注的计划只能在大表全扫里堆所有过滤条件，自然耗时更高。

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