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

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

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

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

[pastacode lang=»sql» manual=»ALTER%20TABLE%20public.phonebook%0A%20%20%20%20ADD%20COLUMN%20phone_number%20text%3B» message=»» highlight=»» provider=»manual»/]

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

Тест 1

[pastacode lang=»sql» manual=»explain%20analyze%20%0Aselect%20b.human_id%2C%20a.name%2C%20b.phone_number%0Afrom%20people%20a%2C%20phonebook%20b%0Awhere%20a.id%20%3D%20b.human_id%0A%0AHash%20Join%20%20(cost%3D4063.98..7422.22%20rows%3D50000%20width%3D78)%20(actual%20time%3D53.831..137.790%20rows%3D50000%20loops%3D1)%0A%20%20Hash%20Cond%3A%20(b.human_id%20%3D%20a.id)%0A%20%20-%3E%20%20Seq%20Scan%20on%20phonebook%20b%20%20(cost%3D0.00..968.00%20rows%3D50000%20width%3D41)%20(actual%20time%3D0.014..6.928%20rows%3D50000%20loops%3D1)%0A%20%20-%3E%20%20Hash%20%20(cost%3D1934.99..1934.99%20rows%3D99999%20width%3D41)%20(actual%20time%3D53.208..53.208%20rows%3D100000%20loops%3D1)%0A%20%20%20%20%20%20%20%20Buckets%3A%2065536%20%20Batches%3A%204%20%20Memory%20Usage%3A%202381kB%0A%20%20%20%20%20%20%20%20-%3E%20%20Seq%20Scan%20on%20people%20a%20%20(cost%3D0.00..1934.99%20rows%3D99999%20width%3D41)%20(actual%20time%3D0.008..20.413%20rows%3D100000%20loops%3D1)%0APlanning%20time%3A%200.247%20ms%0AExecution%20time%3A%20139.024%20ms» message=»where» highlight=»» provider=»manual»/]

[pastacode lang=»sql» manual=»explain%20analyze%20%0Aselect%20b.human_id%2C%20a.name%2C%20b.phone_number%0Afrom%20people%20a%20%0Ainner%20join%20phonebook%20b%20on%20a.id%20%3D%20b.human_id%0A%0AHash%20Join%20%20(cost%3D4063.98..7422.22%20rows%3D50000%20width%3D78)%20(actual%20time%3D36.378..170.474%20rows%3D50000%20loops%3D1)%0A%20%20Hash%20Cond%3A%20(b.human_id%20%3D%20a.id)%0A%20%20-%3E%20%20Seq%20Scan%20on%20phonebook%20b%20%20(cost%3D0.00..968.00%20rows%3D50000%20width%3D41)%20(actual%20time%3D0.010..10.994%20rows%3D50000%20loops%3D1)%0A%20%20-%3E%20%20Hash%20%20(cost%3D1934.99..1934.99%20rows%3D99999%20width%3D41)%20(actual%20time%3D35.775..35.775%20rows%3D100000%20loops%3D1)%0A%20%20%20%20%20%20%20%20Buckets%3A%2065536%20%20Batches%3A%204%20%20Memory%20Usage%3A%202381kB%0A%20%20%20%20%20%20%20%20-%3E%20%20Seq%20Scan%20on%20people%20a%20%20(cost%3D0.00..1934.99%20rows%3D99999%20width%3D41)%20(actual%20time%3D0.008..13.776%20rows%3D100000%20loops%3D1)%0APlanning%20time%3A%200.178%20ms%0AExecution%20time%3A%20172.356%20ms» message=»inner join» highlight=»» provider=»manual»/]

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

Тест 2

[pastacode lang=»sql» manual=»explain%20analyze%20%0Aselect%20b.human_id%2C%20a.name%2C%20b.phone_number%0Afrom%20people%20a%2C%20phonebook%20b%0Awhere%20a.id%20%3D%20b.human_id%0A%0AHash%20Join%20%20(cost%3D40636.00..94022.49%20rows%3D900000%20width%3D78)%20(actual%20time%3D912.484..2362.599%20rows%3D900000%20loops%3D1)%0A%20%20Hash%20Cond%3A%20(b.human_id%20%3D%20a.id)%0A%20%20-%3E%20%20Seq%20Scan%20on%20phonebook%20b%20%20(cost%3D0.00..17412.00%20rows%3D900000%20width%3D41)%20(actual%20time%3D0.037..401.818%20rows%3D900000%20loops%3D1)%0A%20%20-%3E%20%20Hash%20%20(cost%3D19346.00..19346.00%20rows%3D1000000%20width%3D41)%20(actual%20time%3D909.446..909.446%20rows%3D1000000%20loops%3D1)%0A%20%20%20%20%20%20%20%20Buckets%3A%2065536%20%20Batches%3A%2032%20%20Memory%20Usage%3A%202840kB%0A%20%20%20%20%20%20%20%20-%3E%20%20Seq%20Scan%20on%20people%20a%20%20(cost%3D0.00..19346.00%20rows%3D1000000%20width%3D41)%20(actual%20time%3D0.014..429.818%20rows%3D1000000%20loops%3D1)%0APlanning%20time%3A%2028.119%20ms%0AExecution%20time%3A%202383.102%20ms» message=»where» highlight=»» provider=»manual»/]

[pastacode lang=»sql» manual=»explain%20analyze%20%0Aselect%20b.human_id%2C%20a.name%2C%20b.phone_number%0Afrom%20people%20a%20%0Ainner%20join%20phonebook%20b%20on%20a.id%20%3D%20b.human_id%0A%0AHash%20Join%20%20(cost%3D40636.00..94022.49%20rows%3D900000%20width%3D78)%20(actual%20time%3D469.435..1641.759%20rows%3D900000%20loops%3D1)%0A%20%20Hash%20Cond%3A%20(b.human_id%20%3D%20a.id)%0A%20%20-%3E%20%20Seq%20Scan%20on%20phonebook%20b%20%20(cost%3D0.00..17412.00%20rows%3D900000%20width%3D41)%20(actual%20time%3D0.008..157.313%20rows%3D900000%20loops%3D1)%0A%20%20-%3E%20%20Hash%20%20(cost%3D19346.00..19346.00%20rows%3D1000000%20width%3D41)%20(actual%20time%3D467.118..467.118%20rows%3D1000000%20loops%3D1)%0A%20%20%20%20%20%20%20%20Buckets%3A%2065536%20%20Batches%3A%2032%20%20Memory%20Usage%3A%202840kB%0A%20%20%20%20%20%20%20%20-%3E%20%20Seq%20Scan%20on%20people%20a%20%20(cost%3D0.00..19346.00%20rows%3D1000000%20width%3D41)%20(actual%20time%3D0.009..165.728%20rows%3D1000000%20loops%3D1)%0APlanning%20time%3A%200.186%20ms%0AExecution%20time%3A%201660.800%20ms» message=»inner join» highlight=»» provider=»manual»/]

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

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

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

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

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

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