PostgreSQL中WHERE与JOIN执行顺序及CTE写法性能疑问

PostgreSQL 是否会自动在 JOIN 前执行仅依赖A表的WHERE过滤？ #

会。PostgreSQL 查询优化器原生支持谓词下推优化，针对你给出的SQL：

select * from A join B
on A.externalKey = B.externalKey
where A.id in (1, 2, 3)

优化器生成执行计划时，会自动识别A.id in (1,2,3)的过滤条件只依赖A表字段，不需要等两表全量关联后再判断，会直接把这个过滤逻辑下推到A表的扫描阶段：先从A表筛出id符合条件的3条记录，再拿这3条记录和B表做关联，完全不会出现“先把两张大表全量JOIN再过滤”的低效执行逻辑。
你可以直接用EXPLAIN命令查看实际执行计划，能看到A表的扫描节点上会直接带上Filter: (id = ANY ('{1,2,3}'::integer[]))的过滤条件，该节点返回的预估行数就是3左右，后续才是和B表的关联节点。

CTE写法是否效率更高、是不是场景最优解？ #

这个要分PostgreSQL版本看：

• PostgreSQL 12 之前的版本中，普通CTE默认是优化屏障，优化器不会将CTE内部逻辑和外层查询合并优化。如果把A表的过滤逻辑写在CTE内部，确实会固定先执行CTE内的过滤再做关联，但和原生SQL自动谓词下推的执行逻辑、效率没有本质差异，不会比原生写法更快。
• PostgreSQL 12 及之后的版本中，普通CTE默认支持内联优化，优化器会自动把CTE内的逻辑合并到外层查询做等价改写，这种情况下CTE写法和直接把过滤条件写在外层WHERE的执行计划完全一致，没有效率差别。

只有CTE被声明为MATERIALIZED、是递归CTE、或是内部包含带RETURNING的DML语句时，CTE才会作为独立执行节点，不参与外层的谓词下推等优化。

这个场景下你不需要刻意改写为CTE语法，直接写原生JOIN加WHERE的写法就可以，优化器会自动生成最优执行计划。如果想要确认优化是否生效，执行EXPLAIN ANALYZE查看执行计划，确认A表的过滤动作在关联节点之前、关联时A表侧的输入行数为3即可。

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