SQL Performance Tuning — Applied Composite Indexes, Pre-Join

SELECT order_id, created_at, amount
FROM   orders
WHERE  (created_at, order_id) < ('2024-06-05', 8)  -- [Cursor] after the previous page’s last key in sort order (tuple comparison)
ORDER BY created_at DESC, order_id DESC            -- Include the unique key to fully fix the order
LIMIT  3;                                          -- Fetch only one page and stop early

/*
  Logical execution order:
  1. Binary-search the index to the position after the cursor  → no discarded OFFSET rows
  2. Read only 3 rows in index order and stop  → LIMIT 3
  3. Return the SELECT columns  → return the requested columns

  Tuple-comparison expansion (equivalent condition):
  (created_at, order_id) < ('2024-06-05', 8)
  ⇔  created_at <  '2024-06-05'
   OR (created_at = '2024-06-05' AND order_id < 8)

  Plan comparison (conceptual):
  ✗ LIMIT 3 OFFSET 3 … read the first 6 rows and discard 3 (worse on deeper pages)
  ✓ Keyset          … read only 3 rows from the cursor position (always O(limit))
*/

WITH ranked AS (
  SELECT category, order_id, amount,
         ROW_NUMBER() OVER (
           PARTITION BY category           -- Create an independent numbering space for each category
           ORDER BY amount DESC, order_id  -- Amount descending + unique key makes the ranking deterministic
         ) AS rn
  FROM orders
)
SELECT category, order_id, amount, rn
FROM   ranked
WHERE  rn <= 2                             -- Window functions cannot be used directly in WHERE; filter outside
ORDER BY category, rn;

/*
  Logical execution order:
  1. FROM orders                          → read 9 rows in one pass
  2. PARTITION BY category                → divide into category partitions
  3. ORDER BY amount DESC, order_id within each partition  → sort within partitions
  4. ROW_NUMBER() assigns sequential numbers                   → assign rn
  5. Outer WHERE rn filters the top rows                    → only the top 2 rows in each partition pass
  6. ORDER BY category, rn                → final ordering

  Tie-breaker effect (Games):
    id=7 (2500) and id=8 (2500) have the same amount
    → ORDER BY amount DESC, order_id fixes id=7 at rn=1 and id=8 at rn=2
*/

WITH order_agg AS (
  SELECT   customer_id,
           COUNT(*)    AS order_count,   -- Fix the child-table count before the JOIN
           SUM(amount) AS total_amount   -- Compute the total in the same pass
  FROM     orders
  GROUP BY customer_id                  -- Fold 6 rows into 2 before joining
)
SELECT c.customer_id,
       c.name,
       COALESCE(a.order_count, 0)  AS order_count,  -- Customer with no orders: NULL → 0
       COALESCE(a.total_amount, 0) AS total_amount
FROM   customers c
LEFT JOIN order_agg a                  -- LEFT JOIN keeps every customer (does not drop Bob)
       ON a.customer_id = c.customer_id
ORDER BY c.customer_id;

/*
  Logical execution order:
  1. CTE order_agg                       → aggregate by customer_id
  2. FROM customers LEFT JOIN order_agg  → join the aggregate (unmatched side is NULL)
  3. SELECT applies COALESCE              → convert NULL to 0
  4. ORDER BY customer_id                → order the rows

  Plan comparison (conceptual):
  ✗ JOIN then GROUP BY → the join intermediate result fans out (all orders participate)
  ✓ Aggregate then JOIN → only the “customer count” rows participate in the join
*/

WITH RECURSIVE tree AS (
  SELECT id, name, parent_id, 0 AS depth  -- [Seed] Starting Electronics (depth=0)
  FROM   categories
  WHERE  id = 1

  UNION ALL  -- No duplicate removal is needed, so UNION ALL is lighter than UNION

  SELECT c.id, c.name, c.parent_id, t.depth + 1
  FROM   categories c
  JOIN   tree t ON c.parent_id = t.id      -- Explore only children of the immediately preceding generation (needs parent_id index)
  WHERE  t.depth < 2                         -- [Depth limit] Safety valve for runaway queries and bad data
)
SELECT   id, name, depth
FROM     tree
ORDER BY depth, id;

/*
  Logical execution order (generation by generation):
  1. Run the seed      → place the starting row in the working table
  2. First recursion    → find and add children through parent_id
  3. Second recursion   → find and add the next children
  4. Third recursion    → stop at the guard (0 new rows)
  5. Outer SELECT       → order the accumulated result by depth and id
  */