SQL パフォーマンス最適化 — Anti Join・ソート消去の応用

SELECT   category,
         COUNT(*)                                              AS total_cnt,
         COUNT(*)    FILTER (WHERE status = 'completed')     AS completed_cnt,
         COALESCE(SUM(amount) FILTER (WHERE status = 'completed'), 0)
                                                               AS completed_amt,
         ROUND(100.0 * COUNT(*) FILTER (WHERE status = 'completed')
               / NULLIF(COUNT(*), 0), 1)                   AS completed_rate
FROM     orders
GROUP BY category
ORDER BY category;   -- テーブル走査はこの1回だけ

/*
  実行順序:
  1. orders を先頭から1回だけ走査（Seq Scan）
  2. 行を読みながら category でグループ分け（HashAggregate）
  3. 各グループ内で、行ごとに FILTER 条件を判定し
  4. 全行読了後、グループごとに rate を集約値同士の式として計算
  5. category 昇順に整列して返却
  */

CREATE INDEX idx_articles_cat_pub
  ON articles (category, published_at DESC);  -- 等値(category) → ソート列(published_at)

-- 1ページ目
SELECT   article_id, title, published_at
FROM     articles
WHERE    category = 'tech'          -- index 先頭列で tech 区間へ直行
ORDER BY published_at DESC           -- 区間内は整列済み → Sort 不要
LIMIT    3;

-- 2ページ目（keyset: 前ページ最終行の published_at を境界に）
SELECT   article_id, title, published_at
FROM     articles
WHERE    category = 'tech'
  AND    published_at < DATE '2026-06-02'  -- 続きの位置へ index で直接シーク
ORDER BY published_at DESC
LIMIT    3;

/*
  実行順序（1ページ目）:
  1. idx_articles_cat_pub 内で category = 'tech' の区間先頭
  2. 区間内を index 順に1エントリずつ読み、ヒープから title 等を取得
  3. 3行返した時点で LIMIT が走査を打ち切り（Sort 工程は存在しない）
  */

SELECT log_id, message, created_at
FROM (
  (SELECT log_id, message, created_at
   FROM   logs_202605
   ORDER BY created_at DESC LIMIT 3)  -- ブランチ内 Top-N: index で3件のみ読む
  UNION ALL                            -- 排他ソース → 重複排除コストゼロ
  (SELECT log_id, message, created_at
   FROM   logs_202606
   ORDER BY created_at DESC LIMIT 3)  -- 同上（括弧でブランチに ORDER/LIMIT を付与）
) u
ORDER BY created_at DESC               -- 並べ直す対象は高々 6行
LIMIT  3;

/*
  実行順序:
  1. logs_202605 の created_at index 先頭から3件だけ読む（Index Scan + Limit）
  2. logs_202606 も同様に3件だけ読む
  3. 2本の整列済みストリームを Append（条件が揃えば Merge Append）で連結
  4. 高々6行を created_at 降順に整列し、先頭3件で打ち切り
  → 結果: 3行 × 3列（600万行のうち実際に読むのは index 6エントリ + ヒープ6行）
*/

SELECT   s.region,
         COUNT(*) FILTER (WHERE s.status = 'paid')               AS paid_cnt,
         COALESCE(SUM(s.amount) FILTER (WHERE s.status = 'paid'), 0) AS paid_amt,
         COUNT(*) FILTER (WHERE s.status = 'refund')             AS refund_cnt
FROM (
  SELECT region, status, amount, customer_id FROM sales_202605
  UNION ALL                          -- 排他な月別ソース → 重複排除コストゼロ
  SELECT region, status, amount, customer_id FROM sales_202606
) s
WHERE    NOT EXISTS (                -- 行単位の除外は集計前（WHERE）・NULL 安全
           SELECT 1 FROM blocked_accounts b
           WHERE  b.customer_id = s.customer_id
         )
GROUP BY s.region
HAVING   COUNT(*) FILTER (WHERE s.status = 'paid') >= 2  -- グループの選別は集計後（HAVING）
ORDER BY paid_amt DESC;

/*
  実行順序:
  1. sales_202605 / sales_202606 を各1回走査し Append で連結
  2. blocked_accounts を1回走査してハッシュ表 {999} を構築（NULL 行は照合に無関係）
  3. 連結ストリームの各行をハッシュ表へ照合し、一致行を除外（Hash Anti Join）
  4. region でグループ化し、行ごとに FILTER 条件を判定して各集約を同時更新
  5. グループ確定後、HAVING（paid_cnt >= 2）で north を脱落させる
  6. paid_amt 降順に整列して返却
  → 結果: 2行 × 4列（各テーブル走査は1回ずつ・重複排除なし・NULL に壊されない）
*/