PostgreSQL, SQL, Базы данных

Что быстрее: 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
where
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
inner join

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

Тест 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
where
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

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

Что быстрее: where или join?: 2 комментария

  1. Доброе утро! Хотелось бы вас поправить. Выражение from people a, phonebook b в вашем запросе является ничем иным, как соединением. У вас два одинаковых запроса, просто в первом случае более упрощенный синтаксис.
    Время выполнения лучше брать как среднее из нескольких тестов, количество тестов зависит от размера выборки и таблиц. Здесь вы фактически выполнили один и тот же запрос, и во второй раз время уменьшилось, так как часть данных попала в кэш.

    1. Добрый день, Элина! Спасибо за ваш комментарий. Вообще изначально эти два способа используются для разных целей. Join — для объединения по условию, а where — для фильтрации данных. Однако, когда условие фильтрации where — совпадение ключей разных таблиц, то получается одно и то же. План выполнения при этом одинаков. Стало любопытно, что по цифрам 🙂 Ваш комментарий и мой пост не противоречат друг другу.

Оставьте своё мнение...

Этот сайт использует Akismet для борьбы со спамом. Узнайте как обрабатываются ваши данные комментариев.