SQL パフォーマンス最適化 — ROW_NUMBER・型変換の基礎

SELECT   user_id, logged_at, device
FROM (
  SELECT user_id, logged_at, device,
         ROW_NUMBER() OVER (PARTITION BY user_id                      -- ユーザーごとに区画
                            ORDER BY logged_at DESC, login_id DESC) AS rn  -- タイブレーク必須
  FROM   logins
) t
WHERE    rn = 1   -- ウィンドウ関数は WHERE に直接書けないため外側で絞る
ORDER BY user_id;

-- 別解（PostgreSQL専用・簡潔）:
-- SELECT DISTINCT ON (user_id) user_id, logged_at, device
-- FROM logins ORDER BY user_id, logged_at DESC, login_id DESC;

/*
  実行順序:
  1. 内側 FROM logins    → 1回走査
  2. ウィンドウ             → user_id で区画化し ROW_NUMBER を付与
  3. 外側 WHERE rn = 1   → 区画ごとに1行
  4. SELECT            → 3列を確定
  5. ORDER BY user_id  → 昇順に整列
  */

SELECT   article_id, title, published_at
FROM     articles
WHERE    (published_at, article_id) < (TIMESTAMP '2026-05-05 13:00', 5)  -- 前ページ最終行のキー
ORDER BY published_at DESC, article_id DESC  -- カーソルと同じ列・同じ向き
LIMIT    2;

-- 前提 index: CREATE INDEX ON articles (published_at, article_id);（逆順走査で DESC に対応）

/*
  実行順序:
  1. FROM articles  → 対象にする
  2. WHERE 行値比較     → index 上のシークに変換
  3. ORDER BY       → index の並びを利用しソートなし
  4. LIMIT          → 2件で打ち切り
  */

SELECT   u.user_id, u.name,
         CASE WHEN EXISTS (
           SELECT 1 FROM orders o WHERE o.user_id = u.user_id  -- 1件見つけたら即打ち切り
         ) THEN 'yes' ELSE 'no' END AS has_order
FROM     users u
ORDER BY u.user_id;

-- ✗: CASE WHEN (SELECT COUNT(*) FROM orders WHERE user_id = u.user_id) > 0 …
--      → ユーザーごとに注文を全件カウント（idx(user_id) の Range Scan を最後まで走り切る）

/*
  実行順序:
  1. FROM users        → 走査
  2. SELECT 内 EXISTS   → idx で1件見つけたら打ち切り（Semi Join）
  3. CASE              → TRUE/FALSE を 'yes'/'no' に変換
  4. ORDER BY user_id  → 昇順
  */

CREATE INDEX idx_tasks_pending_keyset                             -- 部分 index: 'pending' 行だけを担当者ごとの最新順に索引化（Q5 + Q7）
  ON tasks (assignee_id, created_at DESC, task_id DESC)
  WHERE status = 'pending';
CREATE INDEX idx_task_tags_task_tag ON task_tags (task_id, tag);  -- 補助 index: 重要タグの存在確認用（EXISTS が刺さる）

WITH ranked AS (
  SELECT task_id, assignee_id, title, created_at,
         ROW_NUMBER() OVER (PARTITION BY assignee_id                       -- ① 担当者ごとに区画
                            ORDER BY created_at DESC, task_id DESC) AS rn  -- タイブレーク必須
  FROM   tasks
  WHERE  status = 'pending'                                              -- ② 部分 index が含意で選ばれる（文字列同士・Q6）
)
SELECT   task_id, assignee_id, title, created_at
FROM     ranked
WHERE    rn = 1                                                            -- 各担当者の最新1件だけ残す
  AND    (created_at, task_id) < (TIMESTAMP '2026-06-04 11:00', 4)       -- ③ キーセット (Q8)
  AND    EXISTS (                                                          -- ⑤ 存在判定 (Q9)
           SELECT 1 FROM task_tags tt
           WHERE  tt.task_id = ranked.task_id AND tt.tag = 'important'
         )
ORDER BY created_at DESC, task_id DESC                                  -- カーソルと同じ列・同じ向き
LIMIT    2;

/*
  実行順序:
  1. CTE 内 FROM/WHERE  → status='pending' で部分 index を採用
  2. CTE 内 ウィンドウ       → 区画化し ROW_NUMBER を付与（担当者ごとの index 順を利用）
  3. 外側 WHERE rn = 1   → 担当者ごとの先頭行
  4. キーセット行値比較         → 古い側へジャンプ
  5. EXISTS            → important の有無を1件探索（Semi Join）
  6. ORDER BY created_at DESC, task_id DESC  → 残った候補を並べて2件で終了
  */