PostgreSQL ON vs WHERE при объединении таблиц?
У меня есть 2 таблицы customer и coupons, клиент может иметь или не иметь назначенный reward_id, поэтому это столбец с нулевым значением. У покупателя может быть много купонов, и купон принадлежит покупателю.
+-------------+------------+
| coupons | customers |
+-------------+------------+
| id | id |
| customer_id | first_name |
| code | reward_id |
+-------------+------------+
customer_id column is indexed
Я хотел бы объединить 2 таблицы.
Моя попытка:
select c.*, cust.id as cust_id, cust.first_name as cust_name
from coupons c
join customer cust
on c.customer_id = cust.id and cust.reward_id is not null
Однако я думаю, что у reward_id нет индекса, поэтому мне следует переместить cust.reward_id is not null в предложение where:
select c.*, cust.id as cust_id, cust.first_name as cust_name
from coupons c
join customer cust
on c.customer_id = cust.id
where cust.reward_id is not null
Интересно, будет ли вторая попытка более эффективной, чем первая.
@stickybit, будет ли второй запрос лучшим выбором, если reward_id проиндексирован?
Подробно обсуждается, какова будет разница с точки зрения объединений и объединений, где dba.stackexchange.com/a/3481
@Rizwan спасибо за ваш комментарий, но мой вопрос немного отличается, он фокусируется на дополнительном условии в предложении ON по сравнению с дополнительным условием в предложении WHERE, и оба запроса используют JOIN
@tkhuynh. . . У них должен быть одинаковый план выполнения.
Оба результата приведут к одному и тому же плану выполнения.
@GordonLinoff, значит, разницы в производительности нет, я прав? Я провел тест по двум запросам, и я вижу, что второй выполняется немного быстрее.
@tkhuynh. . . Это может быть связано с эффектами кеширования. Или, если разница действительно небольшая (скажем, <5%), тогда просто случайное отклонение.
Это легко найти часто задаваемые вопросы и ясно, что так .. Прежде чем рассматривать возможность публикации, пожалуйста, прочтите руководство и погуглите любое сообщение об ошибке или множество ясных, кратких и точных формулировок вашего вопроса / проблемы / цели, с указанием и без ваших конкретных строк / имен и сайта: stackoverflow.com & теги; прочитал много ответов. Если вы публикуете вопрос, используйте одну фразу в качестве заголовка. Отразите свое исследование. См. Как спросить и тексты курсора со стрелкой голосования.
Отвечает ли это на ваш вопрос? SQL JOIN - предложение WHERE по сравнению с предложением ON
Изучите основы реляционной оптимизации / реализации запросов SQL. Они тривиально отображаются в одну и ту же реализацию с помощью простейшего механизма запросов. Также было бы полезно подумать о том, почему вы думаете, что у вас есть причина считать свои предположения оправданными. (Лучше, когда вы говорите «интересно», но «интересно» - это не вопрос. Однако «эффективный» ничего особенного не означает.)
Ответы 2
Будет лучше, если вы сами увидите план выполнения. Добавьте EXPLAIN ANALYZE перед оператором select и выполните оба, чтобы увидеть различия.
Вот как:
EXPLAIN ANALYZE select ...
Что оно делает? Он фактически выполняет оператор select и возвращает вам план выполнения, который был выбран оптимизатором запросов. Без ключевого слова ANALYZE он будет оценивать только план выполнения без фактического выполнения оператора в фоновом режиме.
База данных не будет использовать два индекса одновременно, поэтому наличие индекса на customer(id) сделает невозможным использование индекса на customer(reward_id). Это условие будет фактически рассматриваться как условие фильтрации, что является правильным поведением.
Вы можете поэкспериментировать с производительностью частичного индекса, созданного как таковой: customer(id) where reward_id is not null. Это уменьшит размер индекса, поскольку будет хранить только те идентификаторы клиентов, которым назначен reward_id.
Обычно мне нравится отделять логику отношений / соединения от применяемых условий, и я сам помещаю их в предложение WHERE, потому что оно там более наглядно и его легче читать в будущем, если будут какие-либо другие изменения.
Я предлагаю вам лично убедиться в возможном приросте производительности, потому что он зависит от объема данных и возможной низкой мощности для reward_id. Например, если в большинстве строк этот столбец заполнен значением, это не будет иметь большого значения, поскольку размер индекса (нормальный или частичный) будет почти таким же.
Я использовал EXPLAIN ANALYZE, чтобы увидеть производительность двух запросов, и заметил, что при большом количестве записей второй запрос работает лучше. Однако я все еще хочу подтвердить, будет ли лучше переносить cust.reward_id is not null в WHERE.
Во внутреннем соединении PostgreSQL условие фильтра, помещенное в предложение ON или предложение WHERE, не влияет на результат или производительность запроса.
Вот руководство, которое исследует эту тему более подробно: https://app.pluralsight.com/guides/using-on-versus-where-clauses-to-combine-and-filter-data-in-postgresql-joins
Теоретически да, но на практике оптимизатор возвращает разные планы. Базовый пример здесь: stackoverflow.com/questions/65673841/… Любое понимание, которое вы могли бы получить, было бы признательно.
Создайте недостающий индекс вместо того, чтобы перемещать условия. Оптимизатор все равно переместит их, если увидит в этом выгоду.