Что быстрее: where или join?

Начнем с того, что, грубо говоря, «where» и «join» — не то же самое. Предложение where накладывает на выборку некое условие, а join — объединяет данные некоторых таблиц по некоторым условиям. Но если свести задачу к выводу данных из двух (или более) таблиц по условию, то результат будет в обоих случаях окажется идентичным. Сегодня хотелось бы выяснить, какую конструкцию использовать оптимальнее в данном случае.

Для теста буду использовать все те же таблицы из записи Оптимизация запросов PostgreSQL с explain analyze. В таблицу phonebook добавим новое поле phone_number (номер телефона):

ALTER TABLE public.phonebook
    ADD COLUMN phone_number text;

Задание: вывести номера телефонов и имена людей. Для первого теста количество записей в таблице people — 100 000. В таблице phonebook — 50 000. Для второго теста — 1 000 000 в одной, и 900 000 в другой таблице, соответственно. Ключ human_id все так же может быть неуникальным.

Тест 1

explain analyze 
select b.human_id, a.name, b.phone_number
from people a, phonebook b
where a.id = b.human_id

Hash Join  (cost=4063.98..7422.22 rows=50000 width=78) (actual time=53.831..137.790 rows=50000 loops=1)
  Hash Cond: (b.human_id = a.id)
  -  Seq Scan on phonebook b  (cost=0.00..968.00 rows=50000 width=41) (actual time=0.014..6.928 rows=50000 loops=1)
  -  Hash  (cost=1934.99..1934.99 rows=99999 width=41) (actual time=53.208..53.208 rows=100000 loops=1)
        Buckets: 65536  Batches: 4  Memory Usage: 2381kB
        -  Seq Scan on people a  (cost=0.00..1934.99 rows=99999 width=41) (actual time=0.008..20.413 rows=100000 loops=1)
Planning time: 0.247 ms
Execution time: 139.024 ms

explain analyze 
select b.human_id, a.name, b.phone_number
from people a 
inner join phonebook b on a.id = b.human_id

Hash Join  (cost=4063.98..7422.22 rows=50000 width=78) (actual time=36.378..170.474 rows=50000 loops=1)
  Hash Cond: (b.human_id = a.id)
  -  Seq Scan on phonebook b  (cost=0.00..968.00 rows=50000 width=41) (actual time=0.010..10.994 rows=50000 loops=1)
  -  Hash  (cost=1934.99..1934.99 rows=99999 width=41) (actual time=35.775..35.775 rows=100000 loops=1)
        Buckets: 65536  Batches: 4  Memory Usage: 2381kB
        -  Seq Scan on people a  (cost=0.00..1934.99 rows=99999 width=41) (actual time=0.008..13.776 rows=100000 loops=1)
Planning time: 0.178 ms
Execution time: 172.356 ms

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

Тест 2

explain analyze 
select b.human_id, a.name, b.phone_number
from people a, phonebook b
where a.id = b.human_id

Hash Join  (cost=40636.00..94022.49 rows=900000 width=78) (actual time=912.484..2362.599 rows=900000 loops=1)
  Hash Cond: (b.human_id = a.id)
  -  Seq Scan on phonebook b  (cost=0.00..17412.00 rows=900000 width=41) (actual time=0.037..401.818 rows=900000 loops=1)
  -  Hash  (cost=19346.00..19346.00 rows=1000000 width=41) (actual time=909.446..909.446 rows=1000000 loops=1)
        Buckets: 65536  Batches: 32  Memory Usage: 2840kB
        -  Seq Scan on people a  (cost=0.00..19346.00 rows=1000000 width=41) (actual time=0.014..429.818 rows=1000000 loops=1)
Planning time: 28.119 ms
Execution time: 2383.102 ms

explain analyze 
select b.human_id, a.name, b.phone_number
from people a 
inner join phonebook b on a.id = b.human_id

Hash Join  (cost=40636.00..94022.49 rows=900000 width=78) (actual time=469.435..1641.759 rows=900000 loops=1)
  Hash Cond: (b.human_id = a.id)
  - Seq Scan on phonebook b  (cost=0.00..17412.00 rows=900000 width=41) (actual time=0.008..157.313 rows=900000 loops=1)
  -  Hash  (cost=19346.00..19346.00 rows=1000000 width=41) (actual time=467.118..467.118 rows=1000000 loops=1)
        Buckets: 65536  Batches: 32  Memory Usage: 2840kB
        ->  Seq Scan on people a  (cost=0.00..19346.00 rows=1000000 width=41) (actual time=0.009..165.728 rows=1000000 loops=1)
Planning time: 0.186 ms
Execution time: 1660.800 ms

Вывод объединенных данных через inner join по условию работает быстрее, на выборку первой записи на всех этапах построения запроса ему требуется меньше времени, чем запросу по условию where, хотя планы выполнения запросов одинаковые.