SQL パフォーマンス最適化 — LAG/LEAD・キーセットの応用

SELECT   product_id,
         recorded_at,
         price,
         price - LAG(price) OVER (PARTITION BY product_id        -- 商品で区画
                                  ORDER BY recorded_at) AS diff  -- 1つ手前の price を取得
FROM     price_history
ORDER BY product_id, recorded_at;

-- ✗ 自己 LEFT JOIN + 相関 MAX(recorded_at < ...) で前日を取る
--      → 行数ぶんの MAX 探索 + JOIN によるファンアウトリスク + コードが膨張

-- 補足: 「移動平均」が欲しいなら同じ仕組みで以下のように書ける
-- AVG(price) OVER (PARTITION BY product_id ORDER BY recorded_at
--                  ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS ma7

/*
  実行順序:
  1. FROM: price_history を1回走査
  2. ウィンドウ: PARTITION BY product_id で区画化、ORDER BY recorded_at で整列
     → index (product_id, recorded_at) の並びと一致 → ソート工程なし
     → 各行で LAG(price) = 同区画の直前行の price を取得（区画先頭は NULL）
  3. SELECT: price - LAG(price) で差分を計算
  4. ORDER BY: 出力用の並び（既に整列済みなので追加コストなし）
  → 結果: 行数そのまま、差分付き（走査1回・探索ゼロ）
*/

CREATE INDEX idx_tasks_pri_due_id                        -- index も向きまで揃えると、ORDER BY のソート工程が消える
  ON tasks (priority DESC, due_date ASC, task_id DESC);

SELECT   task_id, title, priority, due_date
FROM     tasks
WHERE    priority < 2                                                     -- ① 優先度が下がる
   OR    (priority = 2 AND due_date > DATE '2026-06-07')                  -- ② 同優先度・期限後ろ
   OR    (priority = 2 AND due_date = DATE '2026-06-07' AND task_id < 4)  -- ③ 同優先度・同期限・id 小
ORDER BY priority DESC, due_date ASC, task_id DESC                        -- index の並びと完全一致
LIMIT    2;

-- ✗ 行値比較 (priority, due_date, task_id) < (2, '2026-06-07', 4)
--      → 辞書式は全列同じ向き前提のため、ASC 列が混じった ORDER BY とは意味が一致しない

/*
  実行順序:
  1. FROM: tasks を対象
  2. WHERE: OR 展開を index 上の範囲条件として評価
  3. ORDER BY priority DESC, due_date ASC, task_id DESC  → 追加ソートなし
  4. LIMIT: 2件読んだら打ち切り（Top-N 即時終了）
  */

SELECT   c.cat_id, c.name AS category,
         p.product_id, p.product_name, p.sold_at
FROM     categories c
JOIN LATERAL (
  SELECT   product_id, name AS product_name, sold_at
  FROM     products
  WHERE    category_id = c.cat_id                     -- ① 左の c.cat_id を参照（相関）
  ORDER BY sold_at DESC, product_id DESC             -- ② index と同じ並び（タイブレーク付き）
  LIMIT    3                                          -- ③ 各カテゴリで3件で打ち切り
) p ON TRUE                                           -- LATERAL は左の各行に対し右を独立評価
ORDER BY c.cat_id, p.sold_at DESC;

-- △ ROW_NUMBER 版（products 1000万行を全件走査して番号付け）:
--   WITH ranked AS (SELECT *, ROW_NUMBER() OVER (PARTITION BY category_id
--                   ORDER BY sold_at DESC) AS rn FROM products)
--   SELECT ... FROM categories JOIN ranked ON ... WHERE rn <= 3;

/*
  実行順序:
  1. FROM: categories を走査（10行）
  2. JOIN LATERAL: c の各行に対し、右のサブクエリを c.cat_id を埋めて評価
  3. 結合: c × 各カテゴリの3件 を組み合わせる（10カテゴリ × 最大3 = 最大30行）
  4. ORDER BY: 出力用の並び（c.cat_id 昇順 × sold_at 降順）
  */

CREATE INDEX idx_tickets_open_sort                          -- ① 部分 index で 'open' の少数派だけを並び順込みで索引化（基礎編Q5 + 応用Q8）
  ON tickets (priority DESC, due_date ASC, ticket_id DESC)
  WHERE status = 'open';
CREATE INDEX idx_tickets_open_email_lower                   -- ② 部分 + 式 index でメール検索を大文字小文字無視 + Sargable（基礎編Q5 + 応用Q6）
  ON tickets (LOWER(customer_email))
  WHERE status = 'open';
CREATE INDEX idx_replies_ticket_recent                      -- ③ replies の最新応答取得用（応用LATERAL を効かせる）
  ON replies (ticket_id, replied_at DESC, reply_id DESC);

SELECT   t.ticket_id, t.subject, t.priority, t.due_date,
         lr.last_replied_at, lr.last_message
FROM     tickets t
JOIN LATERAL (                                                              -- ④ 各チケットの最新応答1件（応用Q9）
  SELECT   replied_at AS last_replied_at, body AS last_message
  FROM     replies
  WHERE    ticket_id = t.ticket_id
  ORDER BY replied_at DESC, reply_id DESC
  LIMIT    1
) lr ON TRUE
WHERE    t.status = 'open'                                                  -- ① 部分 index 含意（文字列同士・基礎編Q6）
  AND    LOWER(t.customer_email) = LOWER('Alice@Example.com')                -- ② 式 index で Sargable（応用Q6）
  AND    (                                                                    -- ③ DESC/ASC 混在キーセット（応用Q8）
            t.priority < 2
         OR (t.priority = 2 AND t.due_date > DATE '2026-06-09')
         OR (t.priority = 2 AND t.due_date = DATE '2026-06-09' AND t.ticket_id < 104)
         )
ORDER BY t.priority DESC, t.due_date ASC, t.ticket_id DESC                    -- 並び用 index と完全一致 → Sort なし
LIMIT    2;

/*
  実行順序:
  1. FROM tickets   → status='open' で部分 index
  2. 絞り込み           → 式 index で LOWER(email) を等価検索
  3. キーセット (OR 展開)  → 次ページ候補に縮小
  4. ORDER BY       → 並び用 index と一致しソートなし
  5. LATERAL        → チケットごとに最新返信を1件シーク
  6. LIMIT 2        → 2件で打ち切り

  プランナへの「3つのゼロ」シグナル:
    - Seq Scan ゼロ: 部分 index + 式 index + キーセット index が3層で守る
    - Sort ゼロ: 並び用 index が ORDER BY と方向まで完全一致
    - 大 loops の SubPlan ゼロ: LATERAL は index 1探索 × 2行のみ
*/