SQL: ON vs. WHERE in sub JOIN

user_7409499 0
Viewed 79

What is the difference between using ON and WHERE in a sub join when using an outer reference?

Consider these two SQL statements as an example (looking for 10 persons with not closed tasks, using person_task with a many-to-many relationship):

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

They produce the same result but the statement with ON is considerably faster.

Here the corresponding EXPLAIN (ANALYZE) statements:

-- 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

Should therefore always the ON statement be used for filtering values in a sub JOIN? Or what is going on? I have used Postgres for this example.

select join postgresql sql performance
1 Answers

The condition and pt.person_id = p.id doesn't refer to any column of the joined table t. In an inner join this doesn't make much sense semantically and we can move this condition from ON to WHERE to get the query more readable.

You are right, hence, that the two queries are equivalent and should result in the same execution plan. As this is not the case, PostgreSQL seems to have a problem here with their optimizer.

In an outer join such a condition in ON can make sense and would be different from WHERE. I assume that this is the reason for the optimizer finding a different plan for ON in general. Once it detects the condition in ON it goes another route, oblivious of the join type (so my assumption). I am surprised though, that this leads to a better plan; I'd rather expect a worse plan.

This may indicate that the table's statistics are not up-to-date. Please analyze the tables to make sure. Or it may be a sore spot in the optimizer code PostgreSQL developers might want to work on.

user_22707620
Related