4 ms·
I'm now curious (but too lazy to setup and test it) how the following query would fare: SELECT meal_items.*, employee_markouts.employee_id, customer_orders.c
by balfirevic 6y ago
I'm now curious (but too lazy to setup and test it) how the following query would fare:
SELECT meal_items.*, employee_markouts.employee_id, customer_orders.customer_id
FROM meal_items
LEFT JOIN employee_markouts ON employee_markouts.meal_item_id = meal_items.id
LEFT JOIN employees ON employees.id = employee_markouts.employee_id
LEFT JOIN customer_order_items ON customer_order_items.meal_item_id = meal_items.id
LEFT JOIN customer_orders ON customer_orders.id = customer_order_items.customer_order_id
LEFT JOIN customers ON customers.id = customer_orders.customer_id
WHERE (employees.store_id = 250 AND
employee_markouts.created >= '2021-02-03' AND
employee_markouts.created < '2021-02-04') OR
(customers.store_id = 250 AND
customer_orders.created >= '2021-02-03' AND
customer_orders.created < '2021-02-04')
- knuthsat 6y agoOR really messes things up. UNION instead makes things fast.
- balfirevic 6y agoOh, I wouldn't presume that it will be faster than the union but I wonder how it compares to the original query in the article (the one with OR).
- Albert_Camus 6y agoAuthor here. This doesn't give the correct results. It produces meal_items that have both customer_id and employee_id. Here's an excerpt (the full result set is thousands of rows, as opposed to the expected 45): id |label |price|employee_id|customer_id| ---|------------|-----|-----------|-----------| ... 344|Poke | 4.18| 3772| 13204| 344|Poke | 4.18| 3313| 13204| 344|Poke | 4.18| 2320| 13204| 344|Poke | 4.18| 632| 13204| 344|Poke | 4.18| 4264| 13204| 344|Poke | 4.18| 699| 13204| 344|Poke | 4.18| 1070| 13204| 344|Poke | 4.18| 3022| 13204| 344|Poke | 4.18| 1501| 13204| 344|Poke | 4.18| 808| 13204| 344|Poke | 4.18| 2793| 13204| 344|Poke | 4.18| 1660| 13204| 344|Poke | 4.18| 932| 13204| ... To be clear, there are ways to write this query without UNION that have both good performance and give the correct results, but they're very fiddly and harder to reason about that just writing the two comparatively simple queries and then mashing the results together.
- balfirevic 6y ago> Author here. This doesn't give the correct results. Right you are! :) Should have given it more thought before posting. Thanks for jumping in.
- gfody 6y agothis is probably how I'd write it (assuming pg properly pushes the predicates down to the subquery in the lateral join) select mi.*, x.employee_id, x.customer_id from meal_items mi join lateral ( select e.store_id, em.created, em.employee_id, customer_id = null from employee_markouts em join employees e on e.id = em.employee_id where em.meal_item_id = mi.id union all select c.store_id, co.created, employee_id = null, co.customer_id from customer_order_items coi join customer_orders co on co.id = coi.customer_order_id join customers c on c.id = co.customer_id where coi.meal_item_id = mi.id) x where x.store_id = 250 and x.created between '2021-02-03' and '2021-02-03' order by mi.id