4 ms·
I suspect query #2 would have performed better if they used a COALESCE instead of an OR condition. One of the most important optimization lessons you can learn
by zeroimpl 6y ago
I suspect query #2 would have performed better if they used a COALESCE instead of an OR condition. One of the most important optimization lessons you can learn is not to use OR for any join conditions.
Eg instead of this:
ON (customer_order_items.meal_item_id = meal_items.id
OR employee_markouts.meal_item_id = meal_items.id
)
Do this:
ON meal_items.id = COALESCE( employee_markouts.meal_item_id, customer_order_items.meal_item_id )
The latter can optimize properly. Probably still slower than the UNION, but would be more in line with expected performance. It might be more useful too in certain cases, such as if building a view.