postgreSQL: ON vs. WHERE in sub JOIN

Question

当使用外部引用时，在子连接中使用ON和WHERE有什么区别?

考虑以下两个SQL语句作为示例(查找10个没有关闭任务的人，使用具有多对多关系的person_task):

select p.name
from person p
where exists (
select 1
from person_task pt
join task t on pt.task_id = t.id 
and t.state <> 'closed'
and pt.person_id = p.id -- ON
)
limit 10

---

select p.name
from person p
where exists (
select 1
from person_task pt
join task t on pt.task_id = t.id and t.state <> 'closed'
where pt.person_id = p.id -- WHERE
)
limit 10

它们产生相同的结果，但是带有ON的语句要快得多。

下面是相应的EXPLAIN (ANALYZE)语句:

-- USING ON
Limit  (cost=0.00..270.98 rows=10 width=8) (actual time=10.412..60.876 rows=10 loops=1)
->  Seq Scan on person p  (cost=0.00..28947484.16 rows=1068266 width=8) (actual time=10.411..60.869 rows=10 loops=1)
Filter: (SubPlan 1)
Rows Removed by Filter: 68
SubPlan 1
->  Nested Loop  (cost=1.00..20257.91 rows=1632 width=0) (actual time=0.778..0.778 rows=0 loops=78)
->  Index Scan using person_taskx1 on person_task pt  (cost=0.56..6551.27 rows=1632 width=8) (actual time=0.633..0.633 rows=0 loops=78)
Index Cond: (id = p.id)
->  Index Scan using taskxpk on task t  (cost=0.44..8.40 rows=1 width=8) (actual time=1.121..1.121 rows=1 loops=10)
Index Cond: (id = pt.task_id)
Filter: (state <> 'open')
Planning Time: 0.466 ms
Execution Time: 60.920 ms

---

-- USING WHERE
Limit  (cost=2818814.57..2841563.37 rows=10 width=8) (actual time=29.075..6884.259 rows=10 loops=1)
->  Merge Semi Join  (cost=2818814.57..59308642.64 rows=24832 width=8) (actual time=29.075..6884.251 rows=10 loops=1)
Merge Cond: (p.id = pt.person_id)
->  Index Scan using personxpk on person p  (cost=0.43..1440340.27 rows=2136533 width=16) (actual time=0.003..0.168 rows=18 loops=1)
->  Gather Merge  (cost=1001.03..57357094.42 rows=40517669 width=8) (actual time=9.441..6881.180 rows=23747 loops=1)
Workers Planned: 2
Workers Launched: 2
->  Nested Loop  (cost=1.00..52679350.05 rows=16882362 width=8) (actual time=1.862..4207.577 rows=7938 loops=3)
->  Parallel Index Scan using person_taskx1 on person_task pt  (cost=0.56..25848782.35 rows=16882362 width=16) (actual time=1.344..1807.664 rows=7938 loops=3)
->  Index Scan using taskxpk on task t  (cost=0.44..1.59 rows=1 width=8) (actual time=0.301..0.301 rows=1 loops=23814)
Index Cond: (id = pt.task_id)
Filter: (state <> 'open')
Planning Time: 0.430 ms
Execution Time: 6884.349 ms

因此应该总是使用ON语句来过滤子JOIN中的值吗?或者发生了什么?我在这个例子中使用了Postgres。

Answer 1

条件and pt.person_id = p.id不涉及连接表t的任何列。在内连接中，这在语义上没有多大意义，我们可以将这个条件从ON移到WHERE，以使查询更具可读性。

您是对的，因此，这两个查询是等价的，应该产生相同的执行计划。由于不是这样，PostgreSQL的优化器似乎有问题。

在外部连接中，ON中的这种条件是有意义的，并且与WHERE不同。我认为这是优化器通常为ON找到不同计划的原因。一旦它检测到ON中的条件，它就会走另一条路线，忽略连接类型(这是我的假设)。但令我惊讶的是，这导致了一个更好的计划;我宁愿期待一个更糟糕的计划。

这可能表明表的统计信息不是最新的。请分析一下表格来确认一下。或者它可能是PostgreSQL开发人员想要改进的优化器代码中的痛点。