Speeding up SQL queries by orders of magnitude using UNION – Foxhound Systems
This post from Foxhound Systems demonstrates how splitting a complex, multi-branch SQL query into simpler queries and merging results with [[UNION]] (or [[UNION ALL]]) can yield orders-of-magnitude speedups. The authors show a diamond-shaped schema where tables such as [[stores]], [[employees]], [[employee_markouts]], [[customers]], [[customer_orders]], and [[meal_items]] create multiple join paths; an intuitive OR-joined query that combined both employee markouts and customer orders ran in ~3,264 ms, whereas two focused queries (one over employee markouts and one over customer orders) ran in ~1.5 ms and ~102 ms respectively. Using UNION ALL to append the two fast queries produced identical results in ~112 ms (and an ordered wrapper kept deterministic ordering), highlighting how query planning and avoiding OR across branches — especially on [[PostgreSQL]]— can dramatically improve performance without added caching.