Блог

В чем разница между использованием 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 для фильтрации значений в дополнительном СОЕДИНЕНИИ? Или что происходит?
Я использовал Postgres для этого примера.

Условие and pt.person_id = p.id не относится ни к одному столбцу объединенной таблицы t . Во внутреннем соединении это не имеет особого смысла семантически, и мы можем переместить это условие из ON в WHERE , чтобы сделать запрос более читаемым.

Следовательно, вы правы, что два запроса эквивалентны и должны приводить к одному и тому же плану выполнения. Поскольку это не так, у PostgreSQL, похоже, есть проблема с их оптимизатором.

Во внешнем соединении такое условие in ON может иметь смысл и будет отличаться от WHERE . Я предполагаю, что это причина, по которой оптимизатор находит другой план для ON в целом. Как только он обнаруживает условие в ON нем, он идет другим путем, не обращая внимания на тип соединения (таково мое предположение). Однако я удивлен, что это приводит к лучшему плану; Я бы предпочел ожидать худшего плана.

Это может указывать на то, что статистика таблицы не является актуальной. Пожалуйста, проанализируйте таблицы, чтобы убедиться. Или это может быть больным местом в коде оптимизатора, над которым разработчики PostgreSQL, возможно, захотят поработать.