SQL パフォーマンス最適化 — 複合INDEX・JOIN前集約の応用

SELECT order_id, created_at, amount
FROM   orders
WHERE  (created_at, order_id) < ('2024-06-05', 8)  -- 【カーソル】前ページ末尾キーよりソート順で後ろ（タプル比較）
ORDER BY created_at DESC, order_id DESC            -- 一意キーまで含めて順序を完全に固定
LIMIT  3;                                          -- 1ページ分だけ取得して早期終了

/*
  実行順序:
  1. INDEX を直後の位置まで二分探索   → OFFSET の読み捨てなし
  2. インデックス順に3行だけ読んで打ち切り  → LIMIT 3
  3. SELECT 列を返却          → 列を返す

  タプル比較の展開（等価な条件）:
  (created_at, order_id) < ('2024-06-05', 8)
  ⇔  created_at <  '2024-06-05'
   OR (created_at = '2024-06-05' AND order_id < 8)

  プラン比較（概念）:
  ✗ LIMIT 3 OFFSET 3 … 先頭から6行読んで3行捨てる（深いページほど悪化）
  ✓ Keyset          … カーソル位置から3行読むだけ（常に O(limit)）
*/

WITH ranked AS (
  SELECT category, order_id, amount,
         ROW_NUMBER() OVER (
           PARTITION BY category           -- カテゴリごとに独立した採番空間を作る
           ORDER BY amount DESC, order_id  -- 金額降順 + 一意キーで順位を決定的に
         ) AS rn
  FROM orders
)
SELECT category, order_id, amount, rn
FROM   ranked
WHERE  rn <= 2                             -- ウィンドウ関数はWHEREで直接使えないため外側で絞る
ORDER BY category, rn;

/*
  実行順序:
  1. FROM orders                          → 9行を1パスで読込
  2. PARTITION BY category                → カテゴリごとに区画化
  3. 各区画内 ORDER BY amount DESC, order_id  → 区画内ソート
  4. ROW_NUMBER() で連番付与                   → rn を付与
  5. 外側 WHERE rn で上位抽出                    → 各区画の上位2行だけ通過
  6. ORDER BY category, rn                → 最終並べ替え

  タイブレークの効果（Games）:
    id=7 (2500) と id=8 (2500) は同額
    → ORDER BY amount DESC, order_id により id=7 が rn=1、id=8 が rn=2 に確定
*/

WITH order_agg AS (
  SELECT   customer_id,
           COUNT(*)    AS order_count,   -- JOIN前に子テーブル側で件数を確定
           SUM(amount) AS total_amount   -- 合計も同じ1パスで計算
  FROM     orders
  GROUP BY customer_id                  -- 6行 → 2行に畳んでから結合へ
)
SELECT c.customer_id,
       c.name,
       COALESCE(a.order_count, 0)  AS order_count,  -- 注文0件の顧客は NULL → 0 に補正
       COALESCE(a.total_amount, 0) AS total_amount
FROM   customers c
LEFT JOIN order_agg a                  -- LEFT JOIN で全顧客を保持（Bobを落とさない）
       ON a.customer_id = c.customer_id
ORDER BY c.customer_id;

/*
  実行順序:
  1. CTE order_agg                       → customer_id で集約
  2. FROM customers LEFT JOIN order_agg  → 集約結果を結合（未一致はNULL）
  3. SELECT で COALESCE                   → NULL を 0 へ変換
  4. ORDER BY customer_id                → 整列

  プラン比較（概念）:
  ✗ JOINしてから GROUP BY → 結合中間結果が膨張（orders全行が参加）
  ✓ 集約してから JOIN     → 結合に参加するのは「顧客数」行だけ
*/

WITH RECURSIVE tree AS (
  SELECT id, name, parent_id, 0 AS depth  -- 【シード】起点 Electronics（depth=0）
  FROM   categories
  WHERE  id = 1

  UNION ALL  -- 重複排除不要のため UNION ALL（UNIONより軽い）

  SELECT c.id, c.name, c.parent_id, t.depth + 1
  FROM   categories c
  JOIN   tree t ON c.parent_id = t.id      -- 直前世代の行の「子」だけを探索（要 parent_id index）
  WHERE  t.depth < 2                         -- 【深さ制限】暴走・データ異常時の安全弁
)
SELECT   id, name, depth
FROM     tree
ORDER BY depth, id;

/*
  実行順序（再帰の世代ごと）:
  1. シード実行      → 起点を作業テーブルに置く
  2. 再帰1回目      → parent_id の子を探索し追加
  3. 再帰2回目      → さらに子を探索し追加
  4. 再帰3回目      → ガードで停止（新規0件）
  5. 外側 SELECT  → 累積結果を depth, id で整列
  */