SQL パフォーマンス最適化 — カバリングINDEX・MVの応用

QUESTION 6

カバリングインデックス — Index Only Scan でテーブルに触れない

INCLUDEIndex Only ScanVisibility MapIndex設計

前提知識 ▼

通常の Index Scan は「index で行の位置を特定 → ヒープ（テーブル本体）へ飛んで値を読む」の2段階です。出力したい列がすべて index 内に含まれていると、ヒープ訪問そのものを省略できる Index Only Scan が選ばれます。PostgreSQL 11+ では INCLUDE 句で「検索キーではないが、index リーフに同梱したい列」を追加できます。

-- ✓ カバリングインデックス：amount は検索キーでなく「同梱」
CREATE INDEX idx_orders_cover
  ON orders (customer_id, created_at) INCLUDE (amount);

-- ✗ (customer_id, created_at) のみ → amount のためヒープ訪問が毎行発生

Visibility Map：Index Only Scan が「本当にヒープを読まずに済む」のは、対象ページが全行可視（all-visible）と記録されている場合だけです。VACUUM が走っていないテーブルでは Heap Fetches が増えて効果が薄れるため、EXPLAIN (ANALYZE) の Heap Fetches: 0 を確認するのが運用上の合格ラインです。

問題 ▼

orders（1000万行）に対し、ダッシュボードから「顧客101の 2024-06-01 以降の注文日と金額の一覧」が毎秒数百回流れます。ヒープ訪問ゼロ（Index Only Scan）で返せるインデックスを定義し、それを活かす SELECT 文を書いてください。

使用テーブル（概念図・8行）

- orders（実体1000万行 / 例として8行）

order_id	customer_id	created_at	amount	note(巨大列)
1	100	2024-05-01	1200	…
2	101	2024-06-10	800	…
3	101	2024-07-22	2000	…
4	101	2024-08-30	3000	…
5	102	2024-04-15	500	…
6	101	2024-09-05	1500	…
7	103	2024-07-01	700	…
8	101	2024-05-20	900	…

期待出力 ▼

created_at	amount
2024-06-10	800
2024-07-22	2000
2024-08-30	3000
2024-09-05	1500

模範解答コード ▼

CREATE INDEX idx_orders_cover          -- カバリングインデックス：等値→範囲 の検索キー + 出力列を INCLUDE で同梱
  ON orders (customer_id, created_at)  -- 検索キー（B-tree の並び順を決める）
  INCLUDE (amount);                    -- 同梱列（リーフにのみ保持・並び順に関与しない）

SELECT   created_at, amount          -- index 内の列だけを SELECT（カバー成立）
FROM     orders
WHERE    customer_id = 101           -- 等値：左端キーで絞り込み
  AND    created_at >= '2024-06-01'  -- 範囲：第2キーで範囲走査
ORDER BY created_at;                 -- index 順そのまま → 追加ソート不要

/*
  実行順序（Index Only Scan プラン）:
  1. B-tree を複合キーで二分探索        → 開始リーフへ
  2. リーフを右に走査                 → index 内でキー＋同梱列を読取
  3. Visibility Map を確認       → all-visible ならヒープ訪問なし
  4. customer_id を外れたら走査終了    → 範囲のみ
  5. index 順 = created_at 昇順  → ソート工程なし
  */

解説（テーブル変化・ポイント） ▼

CREATE INDEX idx_orders_cover
  ON orders (customer_id, created_at)
  INCLUDE (amount);

SELECT   created_at, amount
FROM     orders
WHERE    customer_id = 101
  AND    created_at >= '2024-06-01'
ORDER BY created_at;

LEGEND ▼

データ取得・読込対象

1. テーブルとインデックスの構造 — ヒープと B-tree リーフ ▼

orders + idx (customer_id, created_at) INCLUDE (amount)index リーフには検索キー (customer_id, created_at) と INCLUDE で同梱した amount が並びます。note のような巨大列はヒープにしかありません。SELECT する列が index 内で完結すれば、ヒープを読む必要がなくなります。

1 / 5

リーフ位置	customer_id	created_at	amount(同梱)	ヒープ参照
#1	100	2024-05-01	1200	→ heap p.12
#2	101	2024-05-20	900	→ heap p.3
#3	101	2024-06-10	800	→ heap p.7
#4	101	2024-07-22	2000	→ heap p.18
#5	101	2024-08-30	3000	→ heap p.2
#6	101	2024-09-05	1500	→ heap p.9
#7	102	2024-04-15	500	→ heap p.5
#8	103	2024-07-01	700	→ heap p.14

index リーフは (キー順) に整列済み

学習ポイント

「検索キー」と「同梱列」を分けて設計する：INCLUDE の列はB-tree の並び順に関与しないため、内部ノードを肥大化させず、リーフだけにペイロードとして載ります。検索キーに不要な列をキー側に足すと index 全体が太り、更新コストとキャッシュ効率が悪化します。「絞るための列はキー、返すための列は INCLUDE」が応用編の鉄則です。

Index Only Scan の成立条件は3つ：(1) SELECT・WHERE・ORDER BY のすべての列が index 内にある、(2) プランナがコスト的に有利と判断する、(3) Visibility Map が all-visible。3つ目は運用の問題で、EXPLAIN (ANALYZE) の Heap Fetches が大きい場合は VACUUM（または autovacuum 設定の見直し）が処方箋です。

SELECT * はカバリングを破壊する：基礎編で学んだ「列を指定する」は、応用編では「Index Only Scan を成立させるための前提条件」に昇格します。1列でも index 外の列（特に巨大な note や JSON 列）を SELECT に足した瞬間、全行ヒープ訪問が復活します。API のレスポンス設計と index 設計はセットで考えましょう。

アンチパターン

なんでも INCLUDE に詰め込む：「将来使うかも」で大型列まで INCLUDE すると、リーフページ数が増えて index 走査自体が遅くなり、書込み時の WAL も増えます。INCLUDE は高頻度クエリの出力列に限定し、低頻度クエリは素直にヒープを読ませる方がトータルで安くつきます。

更新が激しいテーブルで Index Only Scan を当てにする：頻繁な UPDATE/DELETE は Visibility Map の all-visible ビットを落とすため、計測時は速いのに本番で Heap Fetches が嵩むことがあります。ホットなテーブルでは「Index Only Scan 前提の設計」より「行を小さく保つ・autovacuum を強める」が先です。

実務コラム：カバリングは「読み取りレプリカの容量」も買っている

Index Only Scan の価値は単発クエリの速度だけではありません。ヒープ訪問が消えるとshared_buffers に載せるべきページが index だけで済むようになり、同じメモリでヒットするデータ量が増えます。つまり高頻度参照系のカバリング index は、キャッシュ効率＝サーバ台数の節約に直結します。一方で index は「テーブルの部分コピー」なので、1本増やすたびに INSERT/UPDATE のコストが増えることも忘れずに。pg_stat_user_indexes の idx_scan を定期的に眺め、使われていない index を落とす「棚卸し」までがカバリング設計です。次の Q7 では、index を活かしたまま「グループごとの Top-N」を JOIN の形で解く LATERAL を学びます。

QUESTION 7

LATERAL JOIN — 「各行ごとの Top-N」を index 直撃で取る

LATERALTop-N per groupIndex Scan相関サブクエリ

前提知識 ▼

LATERAL は「左側の行を参照できるサブクエリを JOIN に置ける」構文です。各顧客の最新N件のような「グループごとの Top-N」を、顧客ごとに index を1回ずつ引く Nested Loop として素直に表現できます。全行に ROW_NUMBER を振ってから捨てるウィンドウ方式と違い、各グループで LIMIT N の時点で走査が止まるのが強みです。

-- 各 c に対して「c の最新2件」だけを index で取るループになる
FROM customers c
CROSS JOIN LATERAL (
  SELECT ... FROM orders o
  WHERE  o.customer_id = c.customer_id   -- 左側 c を参照できる！
  ORDER BY o.created_at DESC LIMIT 2
) recent

使い分けの目安：顧客数が少なく orders が巨大なら LATERAL（グループ数 × Index Scan）、全顧客×全注文を一括処理するバッチなら ウィンドウ関数（1パス全走査）。前提となる index は両者共通で (customer_id, created_at DESC) です。

問題 ▼

customers（3行）と orders（実体1000万行 / 例8行、(customer_id, created_at DESC) に index あり）から、各顧客の最新2注文を取得してください。出力列は name, created_at, amount、name 昇順 → created_at 降順で。注文が無い顧客は出力不要です。

使用テーブル

- customers（3行・駆動表）

customer_id	name
101	Alice
102	Bob
103	Carol

- orders（例8行・(customer_id, created_at DESC) に index）

order_id	customer_id	created_at	amount
1	101	2024-05-01	1200
2	101	2024-06-10	800
3	101	2024-07-22	2000
4	102	2024-08-30	3000
5	102	2024-04-15	500
6	103	2024-09-05	1500
7	103	2024-07-01	700
8	103	2024-05-20	900

期待出力 ▼

name	created_at	amount
Alice	2024-07-22	2000
Alice	2024-06-10	800
Bob	2024-08-30	3000
Bob	2024-04-15	500
Carol	2024-09-05	1500
Carol	2024-07-01	700

模範解答コード ▼

SELECT   c.name,
         recent.created_at,
         recent.amount
FROM     customers c                      -- 外側 = 駆動表（3行）
CROSS JOIN LATERAL (                      -- 各 c の行ごとに評価されるサブクエリ
  SELECT o.created_at, o.amount
  FROM   orders o
  WHERE  o.customer_id = c.customer_id    -- 左側 c を参照（LATERAL の本体）
  ORDER BY o.created_at DESC              -- index 順をそのまま利用
  LIMIT  2                                -- 各顧客2件で走査打ち切り
) recent
ORDER BY c.name, recent.created_at DESC;  -- 出力ソート

/*
  実行順序（Nested Loop + LATERAL）:
  1. customers を1行ずつ走査（駆動表、3行）
  2. 各 c に対し LATERAL サブクエリを実行
  3. 注文0件の顧客は CROSS JOIN LATERAL では行が消える
  4. 6行を name, created_at DESC で並べ替え
  */

解説（テーブル変化・ポイント） ▼

SELECT   c.name, recent.created_at, recent.amount
FROM     customers c
CROSS JOIN LATERAL (
  SELECT o.created_at, o.amount
  FROM   orders o
  WHERE  o.customer_id = c.customer_id
  ORDER BY o.created_at DESC
  LIMIT  2
) recent
ORDER BY c.name, recent.created_at DESC;

LEGEND ▼

データ取得・読込対象

1. 2テーブル読込 — customers と index 済み orders ▼

FROM customers c / idx (customer_id, created_at DESC)orders の index は customer_id ごとに created_at 降順で整列済みです。つまり「各顧客の最新N件」は、index の各グループ先頭から N 行読むだけで取れる形になっています。

1 / 5

src	customer_id	created_at(降順整列)	amount
idx	101	2024-07-22	2000
idx	101	2024-06-10	800
idx	101	2024-05-01	1200
idx	102	2024-08-30	3000
idx	102	2024-04-15	500
idx	103	2024-09-05	1500
idx	103	2024-07-01	700
idx	103	2024-05-20	900

customers=3, orders=8（index 整列済）

学習ポイント

LATERAL は「行ごとの LIMIT」を index に直撃させる：通常の JOIN サブクエリは左側の行を参照できませんが、LATERAL なら WHERE o.customer_id = c.customer_id と書けるため、各行を起点に index (customer_id, created_at DESC) の先頭から N 行だけ読むプランが成立します。「グループごとに止まる走査」は LATERAL でしか書けない最適化です。

方式選択は「対象グループ数 × グループあたり行数」で決める：顧客100人の最新2件 → LATERAL（200行走査）。全1億行から全顧客の最新2件を一括 → ウィンドウ関数1パス。「テーブル全行」と「グループ数×N」のどちらが小さいかだけで判定でき、EXPLAIN を見る前に概算で勝負がつきます。

index の並び順（DESC 込み）まで一致させる：LATERAL 内の ORDER BY created_at DESC LIMIT 2 が Sort なしで終わるのは、index が (customer_id, created_at DESC)（または昇順 index の Backward Scan）で並び順ごと一致しているからです。並びが合わないと「グループ全行を読んでソート」に退化し、LATERAL の利点が消えます。

アンチパターン

駆動表が巨大なのに LATERAL を使う：LATERAL は本質的に Nested Loop なので、外側が100万行あれば100万回サブクエリが走ります。基礎編で学んだ N+1 と同じ構造です。外側が大きい場合は WHERE で先に絞るか、ウィンドウ方式・事前集約へ切り替えるのが正解です。

CROSS と LEFT の取り違え：CROSS JOIN LATERAL はサブクエリが0行の外側行を黙って消します。「注文ゼロの顧客が一覧から消えた」という事故が典型例。全顧客を残す要件なら LEFT JOIN LATERAL (...) ON true と書きます。結合の種類は要件（行を残すか）で決め、性能で決めないこと。

実務コラム：LATERAL は「SQL に for ループを書く」感覚で読む

LATERAL を初見で読み解くコツは、「FROM の左から右へ流れる for ループ」と捉えることです。FROM A CROSS JOIN LATERAL (B using A) は for a in A: for b in B(a) に他なりません。この読み方を覚えると、「直近の為替レートを各取引に付ける」「各センサーの最新値を取る」「各記事の上位コメント3件」など、アプリ側でループ＋クエリ（N+1）になりがちな処理を、index を活かした1本の SQL に畳み込めるようになります。次の Q8 では、1パス走査が得意なウィンドウ関数側を深掘りし、フレーム指定による移動集計を学びます。

QUESTION 8

ウィンドウフレーム — ROWS BETWEEN で移動平均を1パスで計算

ウィンドウ関数ROWS BETWEEN移動平均ROWS vs RANGE

前提知識 ▼

ウィンドウ関数のフレーム句は「各行から見てどの範囲の行を集計対象にするか」を決めます。ROWS BETWEEN 2 PRECEDING AND CURRENT ROW なら「自分と直前2行」、つまり3行移動平均。テーブルはソート済み状態を1パス走査するだけで、自己結合なしに移動集計が完成します。

AVG(sales) OVER (
  ORDER BY sales_date                          -- フレームの並び順
  ROWS BETWEEN 2 PRECEDING AND CURRENT ROW    -- 自分+直前2行 = 3行窓
)

ROWS と RANGE の違い：ROWS は物理的な行数、RANGE は値の範囲（同値は全部仲間）でフレームを切ります。さらに重要な罠：フレーム省略時のデフォルトは RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW。同日データがあると「同値行を全部含む累計」になり、意図とズレます。移動集計は必ず ROWS を明示が鉄則です。

問題 ▼

daily_sales（日次売上）から、日付昇順で「3日移動平均」付きの一覧を取得してください。出力列は sales_date, sales, ma3（移動平均は小数1桁に丸め）。先頭2日は「存在する行だけ」で平均します。

使用テーブル

- daily_sales（6行・sales_date に index）

sales_date	sales
2024-09-01	100
2024-09-02	200
2024-09-03	300
2024-09-04	600
2024-09-05	300
2024-09-06	900

期待出力 ▼

sales_date	sales	ma3
2024-09-01	100	100.0
2024-09-02	200	150.0
2024-09-03	300	200.0
2024-09-04	600	366.7
2024-09-05	300	400.0
2024-09-06	900	600.0

模範解答コード ▼

SELECT sales_date,
       sales,
       ROUND(
         AVG(sales) OVER (
           ORDER BY sales_date                       -- フレームの基準順序
           ROWS BETWEEN 2 PRECEDING AND CURRENT ROW  -- 物理3行窓（必ず ROWS を明示）
         ), 1)                                       -- 小数1桁に丸め
       AS ma3
FROM   daily_sales
ORDER BY sales_date;                                 -- 出力ソート（窓と同順）

/*
  実行順序:
  1. daily_sales を読込     → Index Scan でソート省略
  2. ウィンドウ処理             → 自分＋直前2行のフレームで AVG
  3. SELECT で ROUND を適用  → 列を確定
  4. ORDER BY            → 窓の順序と同じため追加ソート不要
  */

解説（テーブル変化・ポイント） ▼

SELECT sales_date, sales,
  ROUND(
    AVG(sales) OVER (
      ORDER BY sales_date
      ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ), 1) AS ma3
FROM   daily_sales
ORDER BY sales_date;

LEGEND ▼

データ取得・読込対象

1. データ読込 — sales_date 昇順に整列 ▼

FROM daily_sales（index で既ソート）ウィンドウ処理はORDER BY 順に並んだ状態が前提です。sales_date に index があれば Index Scan がそのまま整列済みデータを供給し、Sort ノードが消えます。

1 / 5

sales_date	sales
2024-09-01	100
2024-09-02	200
2024-09-03	300
2024-09-04	600
2024-09-05	300
2024-09-06	900

6行（昇順整列済）

学習ポイント

フレーム句は「窓の形」を決める第3の部品：ウィンドウ関数は PARTITION BY（グループ）→ ORDER BY（並び）→ フレーム（範囲） の3層構造です。基礎編の ROW_NUMBER はフレームを使わない関数でしたが、AVG / SUM / MAX などの集約系をウィンドウで使う瞬間にフレームが効き始めます。「この行から見てどこからどこまで？」を言語化できれば、移動平均・累計・直近最大値はすべて同じ型で書けます。

移動集計は自己結合の O(N×W) を 1パスに潰す：同じ結果を JOIN daily_sales d2 ON d2.sales_date BETWEEN ... で書くと、行ごとに窓幅 W の走査が走ります。ウィンドウフレームはソート済みストリームに対する増分計算として実行されるため、走査は N 行きっかり。データが百万行規模になると差は決定的です。

ORDER BY 列の index がソートを消す：ウィンドウ処理の前段には必ず「PARTITION BY + ORDER BY 順への整列」が必要です。(sales_date) に index があれば Index Scan が整列済み入力を供給し、EXPLAIN から Sort が消えます。巨大データのウィンドウ集計が遅いとき、最初に疑うのは window 関数ではなく手前の Sort です。

アンチパターン

フレーム省略のまま移動平均のつもりになる：AVG(x) OVER (ORDER BY d) は移動平均ではなく累計平均（しかも同値行を巻き込む RANGE）です。レビューで最も見つかりにくいバグの一つで、グラフが「だんだん滑らかになる」不自然な形になったらこれを疑います。集約系 + OVER + ORDER BY を書いたら、フレームを書いたか指差し確認。

欠損日を無視した「3行 = 3日」の思い込み：ROWS は物理行なので、9/3 のデータが欠けていると「3行窓」は実質4日間に跨ります。暦どおりの「3日間」が要件なら、カレンダーテーブルと LEFT JOIN して欠損日を 0 で埋めるか、RANGE BETWEEN INTERVAL '2 days' PRECEDING（対応 DB の場合）を使います。「行の窓」か「時間の窓」かを要件定義で必ず確認。

実務コラム：フレーム指定で書ける「実務4点セット」

フレームを自在に書けると、分析 SQL の頻出パターンがすべて1パスで片付きます。1. 移動平均：ROWS BETWEEN k PRECEDING AND CURRENT ROW。2. 累計：ROWS UNBOUNDED PRECEDING（RANGE デフォルトに頼らず明示）。3. 全体比：SUM(x) OVER ()（フレーム無指定の全パーティション）で構成比。4. 前後比較：LAG / LEAD はフレーム不要のオフセット系。これらを組み合わせれば、BI ツールに頼っていた前処理の大半が SQL 側に寄せられ、転送量と再計算コストが激減します。とはいえ「同じ重い集計を毎回計算する」こと自体が無駄な場面もあります。次の Q9 では、その答えであるマテリアライズドビューを学びます。

QUESTION 9

マテリアライズドビュー — 重い集計は「事前計算して保存」する

MATERIALIZED VIEWREFRESH CONCURRENTLY事前計算鮮度設計

前提知識 ▼

毎回同じ重い集計（月次売上など）を実行するのは無駄です。マテリアライズドビュー（MV）はクエリ結果を実体テーブルとして保存する仕組みで、参照側は計算済みの小さな表を読むだけになります。鮮度は REFRESH MATERIALIZED VIEW のタイミングで制御します。

-- 定義時に一度だけ集計が走り、結果が実体化される
CREATE MATERIALIZED VIEW mv_monthly_sales AS
SELECT DATE_TRUNC('month', created_at) AS month, SUM(amount) AS total
FROM orders GROUP BY 1;

-- 更新：CONCURRENTLY なら参照をブロックしない（一意 index 必須）
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_monthly_sales;

通常 VIEW との違い：VIEW は「クエリの別名」で参照のたびに元テーブルを集計します。MV は「結果のスナップショット」で参照は速いが鮮度が古くなる。「許容できる古さ（例：1時間）」を先に決め、その間隔で REFRESH するのが MV 設計の出発点です。

問題 ▼

orders（1000万行）への月次売上集計がダッシュボードで毎分実行され、DB 負荷の主因になっています。(1) 月次売上 MV を定義し、(2) 参照を止めずに更新できるよう一意 index を付与し、(3) ダッシュボード用の SELECT（month 昇順）を書いてください。

使用テーブル（概念図・6行）

- orders（実体1000万行 / 例として6行）

order_id	created_at	amount
1	2024-07-03	1200
2	2024-07-18	800
3	2024-08-02	2000
4	2024-08-21	3000
5	2024-09-05	1500
6	2024-09-28	700

期待出力 ▼

month	total	order_count
2024-07-01	2000	2
2024-08-01	5000	2
2024-09-01	2200	2

模範解答コード ▼

CREATE MATERIALIZED VIEW mv_monthly_sales AS                          -- (1) 月次集計を実体化：作成時に1000万行の集計が1回だけ走る
SELECT   DATE_TRUNC('month', created_at)::date AS month,
         SUM(amount)   AS total,
         COUNT(*)      AS order_count
FROM     orders
GROUP BY 1;
CREATE UNIQUE INDEX ux_mv_monthly_sales ON mv_monthly_sales (month);  -- (2) CONCURRENTLY 更新の前提：MV 上の一意 index

-- (3) ダッシュボードはこの3行を読むだけ（元1000万行に触れない）
SELECT   month, total, order_count
FROM     mv_monthly_sales
ORDER BY month;
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_monthly_sales;  -- 鮮度更新（cron 等で定期実行 / 参照をブロックしない）

/*
  実行順序:
  [作成時・REFRESH 時のみ]
  1. orders 1000万行を走査し DATE_TRUNC('month') で月キー化
  2. GROUP BY month で SUM / COUNT を集約 → 結果（3行）をディスクに実体化
  3. CONCURRENTLY の場合：新結果を一時領域に作り、
     一意 index (month) を使って差分行だけを置換（参照は非ブロック）
  [ダッシュボード参照時（毎分）]
  4. mv_monthly_sales（3行）を読む → ORDER BY month
  → 結果: 3行 × 3列（集計コストは参照時ゼロ）
*/

解説（テーブル変化・ポイント） ▼

CREATE MATERIALIZED VIEW mv_monthly_sales AS
SELECT DATE_TRUNC('month', created_at)::date AS month,
       SUM(amount) AS total, COUNT(*) AS order_count
FROM   orders
GROUP BY 1;

CREATE UNIQUE INDEX ux_mv_monthly_sales
  ON mv_monthly_sales (month);

SELECT month, total, order_count
FROM   mv_monthly_sales
ORDER BY month;

REFRESH MATERIALIZED VIEW CONCURRENTLY
  mv_monthly_sales;

LEGEND ▼

データ取得・読込対象

1. 元テーブル — 毎分の集計対象だった1000万行 ▼

FROM orders（MV なしでは毎回全走査）MV を作る前は、ダッシュボードの毎分のリクエストごとに1000万行のスキャン + GROUP BY が走っていました。同じ答えを毎回計算し直している状態です。

1 / 5

order_id	created_at	amount
1	2024-07-03	1200
2	2024-07-18	800
3	2024-08-02	2000
4	2024-08-21	3000
5	2024-09-05	1500
6	2024-09-28	700

実体1000万行（例6行・月で色分け）

学習ポイント

MV は「計算コストの支払いタイミング」をずらす道具：参照のたびに払っていた集計コストを、REFRESH 時にまとめ払いする仕組みです。判断基準はシンプルで、「参照頻度 × 集計コスト ≫ 更新頻度 × REFRESH コスト」なら MV の勝ち。毎分参照・1時間鮮度のダッシュボードは典型的な勝ちパターンです。

CONCURRENTLY には一意 index が必須：無停止更新は「新旧の結果を行単位で突合して差分置換する」実装のため、行を一意に特定できる indexが前提です。これを忘れると本番で REFRESH ... CONCURRENTLY がエラーになります。MV を作ったら一意 index までがワンセットと覚えてください。

MV にも index が貼れる：MV は実体テーブルなので、WHERE month >= ... のような参照パターンに合わせて普通の index を追加できます。「巨大ファクトテーブル → MV で集約 → MV に index」という2段ロケットは、BI 系の遅いクエリに対する最も再現性の高い処方箋です。

アンチパターン

鮮度要件を決めずに MV 化する：「MV にしたら金額がリアルタイムに合わない」というクレームは、技術ではなく要件定義の失敗です。導入前に「何分前のデータまで許容か」を業務側と合意し、画面に「最終更新: hh:mm」を表示するのが安全策。リアルタイム必須の数値は MV ではなく増分集計（トリガー / ストリーム）の領域です。

REFRESH の多段連鎖と時間帯衝突：MV の上に MV を重ねると、REFRESH の順序依存が生まれ、1つの遅延が連鎖します。また全 MV を同時刻に REFRESH すると I/O スパイクで本体クエリまで巻き添えに。依存関係を図にし、REFRESH をずらしてスケジュールするのが運用の基本です。

実務コラム：VIEW / MV / 集計テーブルの使い分け

似た道具が3つあります。VIEW＝ロジックの共通化（性能効果なし・常に最新）。MV＝宣言的な事前計算（REFRESH で全量再集計・運用が簡単）。手組みの集計テーブル＝トリガーやバッチで増分更新（最速・最鮮度だが実装と整合性管理が重い）。迷ったら「まず VIEW、遅ければ MV、MV の REFRESH が重すぎたら増分集計」の順に段階導入するのが事故の少ない進め方です。なお REFRESH が重い最大の理由は「全量再集計」なので、元テーブル側を時間で物理分割しておくと走査が激減します — それが次の Q10、パーティショニングです。

QUESTION 10

パーティション・プルーニング — WHERE で「読む区画」ごと減らす

PARTITION BY RANGEPruningパーティションキー関数適用の罠

前提知識 ▼

テーブルパーティショニングは、巨大テーブルをパーティションキー（多くは日付）で物理的に複数の子テーブルに分割する仕組みです。WHERE 句がキーの範囲を特定できると、プランナは該当しない区画をプラン段階で丸ごと除外（Pruning）します。index が「行を絞る」のに対し、Pruning は「読むテーブル自体を絞る」一段上の足切りです。

CREATE TABLE orders (
  order_id bigint, created_at date, amount int, ...
) PARTITION BY RANGE (created_at);            -- パーティションキー

CREATE TABLE orders_2024_08 PARTITION OF orders
  FOR VALUES FROM ('2024-08-01') TO ('2024-09-01');  -- 月単位の区画

Pruning が効く条件：WHERE がパーティションキーに対して素の比較（=, <, >, BETWEEN）であること。DATE_TRUNC(created_at) のようにキー列を関数で包むと範囲が特定できず全区画スキャンになります（Sargable の原則がそのまま適用）。

問題 ▼

月単位 RANGE パーティション化された orders（12区画 × 各約100万行）から、2024-08 の売上合計と件数を取得してください。Pruning が効く WHERE（半開区間 >= / <）で書くこと。出力列は total, order_count。

パーティション構成（概念図）

- orders（親）= 12 の子区画の集合

子テーブル	範囲 (FROM 〜 TO)	行数
orders_2024_07	07-01 〜 08-01	約100万
orders_2024_08	08-01 〜 09-01	約100万
orders_2024_09	09-01 〜 10-01	約100万
…（他9区画）	…	…

- orders_2024_08 内（例として3行）

order_id	created_at	amount
3	2024-08-02	2000
4	2024-08-21	3000
9	2024-08-30	1000

期待出力 ▼

total	order_count
6000	3

模範解答コード ▼

SELECT SUM(amount) AS total,
       COUNT(*)    AS order_count
FROM   orders                      -- 親テーブルに対して普通に書くだけ
WHERE  created_at >= '2024-08-01'  -- キー列は素のまま（関数で包まない）
  AND  created_at <  '2024-09-01';    -- 半開区間：月末日や時刻の取りこぼしなし

/*
  実行順序:
  1. WHERE 範囲と区画定義を照合  → 該当区画のみ採用（Pruning）
  2. 該当区画のみスキャン        → 他区画はプランから除外
  3. SUM / COUNT を集約   → 1行に集計
  */

解説（テーブル変化・ポイント） ▼

SELECT SUM(amount) AS total,
       COUNT(*) AS order_count
FROM   orders
WHERE  created_at >= '2024-08-01'
  AND  created_at <  '2024-09-01';

LEGEND ▼

データ取得・読込対象

1. パーティション構成 — 親は「区画の束」 ▼

orders = 12 partitions BY RANGE (created_at)親テーブル orders は実体を持たず、月ごとの子テーブル12個の集合です。各区画は FOR VALUES FROM ... TO ... の定義範囲をメタデータとして持っており、プランナはこれを見て読む区画を決めます。

1 / 5

子テーブル	定義範囲	行数
orders_2024_06	[06-01, 07-01)	約100万
orders_2024_07	[07-01, 08-01)	約100万
orders_2024_08	[08-01, 09-01)	約100万
orders_2024_09	[09-01, 10-01)	約100万
…（他8区画）	…	…

合計約1200万行 / 12区画

学習ポイント

Pruning は index より手前の「ゼロ段目の絞り込み」：足切りの階層は (0) Pruning で区画を捨てる → (1) index で行を捨てる → (2) フィルタで行を捨てる の順です。上の階層で捨てるほど安く、Pruning はI/O どころかプラン対象からテーブルごと消すため最も強力。時系列の巨大テーブル（ログ・注文・イベント）は、まずキー設計＝日付パーティションを検討します。

半開区間 >= AND < が日付範囲の正解形：BETWEEN '08-01' AND '08-31' は timestamp 型だと 08-31 の 00:00 以降を取りこぼし、月末日のハードコードはうるう月や月の長さでバグります。>= 月初 AND < 翌月初 は型・月長に依存せず、しかも Pruning とも完全に相性が良い万能の書き方です。

運用系の操作が桁違いに軽くなる：「13ヶ月より古いデータを削除」は通常 DELETE（巨大 WAL + VACUUM 地獄）ですが、パーティションなら DETACH PARTITION + DROP でメタデータ操作一発。バックアップ・ANALYZE・REINDEX も区画単位で回せます。パーティショニングの真価は検索性能より運用性能にあると言われる所以です。

アンチパターン

パーティションキーを WHERE に含めないクエリが主流のまま分割する：例えば WHERE customer_id = ? ばかりのワークロードで日付分割すると、毎回全区画を叩く（しかも区画ごとの index を全部探す）ため、未分割より遅くなり得ます。「主要クエリの大半がキーで絞れるか」が分割可否の判定基準です。

区画の作り忘れで INSERT が落ちる：RANGE パーティションは該当区画が存在しないと INSERT がエラーになります（DEFAULT パーティションを置けば落ちないが、肥大化すると Pruning 効果を蝕む）。翌月区画を自動作成するジョブ + 監視までがパーティション運用のワンセットです。

実務コラム：応用編総まとめ — 「速いSELECT」を支える5つの上位装備

応用編この5問を一言で束ねると、「読む量を、より上の階層で減らす」です。1. カバリングINDEX：ヒープ訪問を消す（Index Only Scan）。2. LATERAL：グループごとに走査を打ち切る（Top-N を index 直撃）。3. ウィンドウフレーム：自己結合を1パスに畳む（移動集計）。4. マテリアライズドビュー：計算自体を参照時から追い出す（事前計算 + 鮮度設計）。5. パーティショニング：テーブルごと読まない（Pruning + 運用性能）。基礎編の10原則が「クエリの書き方」だったのに対し、応用編は「データ構造とコストの置き場所の設計」です。EXPLAIN (ANALYZE, BUFFERS) で Heap Fetches / Rows Removed / Partitions Pruned 相当の区画数 を読みながら、この5装備を使い分けられれば、実務のパフォーマンス課題の大半に正面から答えられます。