-- BAD: subquery in SELECT (correlated subquery - runs for each row)
SELECT
  u.name,
  (SELECT COUNT(*) FROM orders o WHERE o.user_id = u.id) AS order_count
FROM users u
WHERE u.is_active = true;
-- Runs N subqueries for N users. Slow.

-- GOOD: GROUP BY with LEFT JOIN
SELECT
  u.name,
  COUNT(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE u.is_active = true
GROUP BY u.id, u.name;
-- One pass, uses indexes on join key

-- BAD: OR across different columns (hard to use composite index)
SELECT * FROM orders
WHERE user_id = 123
   OR product_id = 456;  -- Seq Scan likely

-- GOOD: UNION ALL (each branch uses its own index)
SELECT * FROM orders WHERE user_id = 123
UNION ALL
SELECT * FROM orders WHERE product_id = 456 AND user_id != 123;

Query Optimization — EXPLAIN, Query Plans

Query Optimization — EXPLAIN, Query Plans

Query Optimization — EXPLAIN, Query Plans