News for hackers. Stuff that matters.
Re:Speeding up SQL queries by orders of magnitude using UNION by gfody on Saturday March 20, 2021 @09:42PM (#26527469)
this 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

There are no comments on this comment yet.