パーティションキー以外の列でのプリーニングを実現する方法

概要

分割テーブルにおいて最も価値があるものの一つが、プリーニング（Pruning）です。データベースはクエリ条件に基づいて不要なパーティションを除外し、パフォーマンスを向上させます。 従来はパーティションキーを用いた照会のみでこれが可能と考えられていましたが、巧妙な手法を用いれば、パーティションキー以外の列でもプリーニングを実現できます。

本記事では、その具体的な方法を解説します。

目次

1. テーブルの分割
2. キー列におけるパーティションプリーニング
3. ローカルインデックス
4. グローバルインデックス
5. パーティションキー以外の列でのプリーニングへのアプローチ
6. 最適化器との対話（チェック制約の利用）
7. 外れ値の導入と対処
8. まとめ

1. テーブルの分割

大量のイベントログを保存・管理するシステムの事例を想定します。ユーザー数が多いため、データ量も膨大になります。主に特定の日付範囲を扱うクエリが多いため、**タイムスタンプを基準としてテーブルを分割（Partition）**します。

スキーマとパーティション作成

-- メインテーブルの定義
CREATE TABLE event (
  id         BIGINT GENERATED ALWAYS AS IDENTITY,
  timestamp  TIMESTAMPTZ NOT NULL,
  session_id BIGINT NOT NULL,
  type       TEXT NOT NULL,
  data       JSONB
) PARTITION BY RANGE (timestamp);

-- 2025 年のパーティション作成
CREATE TABLE event_y2025 PARTITION OF event
FOR VALUES FROM ('2025-01-01 UTC') TO ('2026-01-01 UTC');

-- 2026 年のパーティション作成
CREATE TABLE event_y2026 PARTITION OF event
FOR VALUES FROM ('2026-01-01 UTC') TO ('2027-01-01 UTC');

データ生成（例）

テスト用にイベントログを約 5 万件格納します。セッション単位で記録されており、ログイン失敗やパスワードリセットフローが含まれます。

-- （略：大量データの挿入処理）
INSERT INTO event (session_id, timestamp, type, data) ...

2. キー列におけるパーティションプリーニング

**パーティションキー（

timestamp

✅ クイックなケース：時刻でのフィルタリング

SELECT * FROM event
WHERE timestamp >= '2025-12-01 UTC' 
  AND timestamp <  '2026-01-01 UTC';

実行プランの確認: データベースは自動的に不要なパーティションを除外しています。

QUERY PLAN
────────────────────────────────────────────────────────────────────────
 Seq Scan on event_y2025 event      -- ★ 2025 のパーティションのみスキャン
   Filter: (("timestamp" >= ...))
-- 2026 のパーティションは一切参照されていない

❌ スローなケース：セッション ID でのフィルタリング

一方、**パーティションキーではない

session_id

SELECT * FROM event WHERE session_id = 1;

実行プラン:

QUERY PLAN
──────────────────────────────────────────────────────
 Append  (cost=0.00..1060.07 rows=11 width=37)
   -> Seq Scan on event_y2025 ... Filter: (session_id = 1)
   -> Seq Scan on event_y2026 ... Filter: (session_id = 1)
-- ★ 両方のパーティションをスキャンするため、パフォーマンスが落ちる

3. ローカルインデックス

特定のセッションを高速に取得したい場合、ローカルインデックスを作成します。

インデックスの作成と効果

テーブル全体や各パーティションごとに個別にインデックスを作成する方法です。

-- テーブルレベルの（分割された）インデックス
CREATE INDEX event_session_ix ON event(session_id); 
-- 結果として、各パーティションにもインデックスが自動生成される

-- 例：2025 パーティション上のインデックス
-- event_y2025_session_id_idx

効果と限界

ローカルインデックスを使うことで、Seq Scan が Index Scan に変化し、パフォーマンスが向上します。

QUERY PLAN
─────────────────────────────────────────────────────
 Append
   -> Index Scan using event_y2025_session_id_idx on event_y2025 ...
      Index Cond: (session_id = 1)
   -> Index Scan using event_y2026_session_id_idx on event_y2026 ...
      Index Cond: (session_id = 1)

ただし、プリーニング自体は起こっていません。データベースは依然として両方のパーティションにアクセスする必要があります。パーティション数が多くなるほど（例：100 パーティション）、このオーバーヘッドは大きくなります。

重要: ローカルインデックスは「各パーティションに個別のインデックス」という意味合いを持ちます。PostgreSQL では現時点で、分割テーブルに対するグローバルインデックスのサポートはありません（注：更新対応中）。

4. グローバルインデックス

分割テーブルを跨ぐ単一のインデックス（グローバルインデックス）を作成したい場合、現状では PostgreSQL でサポートされていません。 パーティションキー以外の列に対する一意性制約の実装が困難になることも、この制約からの派生した課題です。

5. パーティションキー以外の列でのプリーニング

標準機能のみでは、非パーティションキー列によるフィルタリング時にプリーニングを行うことは難しい状態です。ここで**ドメイン知識（データの特性）**を活用して突破口を開きます。

データの特性とパターン

本事例のデータには以下の明確なパターンがあります：

1. 追加専用（Append-only）: 更新・削除がないため、イベントは不変。
2. 順次生成:

   session_id

   は時間の経過とともに単調増加する。
3. 短期寿命: セッションは数分〜数時間で終了するため、各パーティション内で session ID の重複や大きな飛биは少ない。

パターンの可視化

パーティションごとの Session ID の範囲を確認します。

SELECT tableoid::regclass, MIN(session_id), MAX(session_id)
FROM event 
GROUP BY 1 
ORDER BY 1;

-- 結果
-- event_y2025 | min=1  | max=4320
-- event_y2026 | min=4320| max=10000

発見:

• event_y2025

  は Session ID 1〜4320 に限定されている。

• event_y2026

  は Session ID 4321〜10000 である（※境界処理のため重複あり）。

この相関関係から、**「Session ID が X の行は、特定のパーティションのみを持つ」**という情報を得ることが可能です。

6. 最適化器との対話（チェック制約の利用）

データベースの最適化器には、テーブル統計情報に基づいて判断されますが、確実な情報は制約（Constraints）からしか得られません。 ここにCHECK 制約を活用する裏技があります。

チェック制約を「情報源」として利用する

チェッキリスト（CHECK）はデータを検証するだけでなく、最適化器に対して**「このパーティションには X の範囲のデータしか存在しない」**と保証を与えることが可能です。 これを伝えれば、最適化器は条件に合わないパーティションのスキャンを省略（プリーニング）できます。

チェック制約の設定

各パーティションに Session ID の範囲を強制する CHECK 制約を追加します。

-- 2025 パーティション：ID は 1 から 4320 まで
ALTER TABLE event_y2025 
ADD CONSTRAINT event_y2025_session_id_range
CHECK (session_id BETWEEN 1 AND 4320);

-- 2026 パーティション：ID は 4320 から 10000 まで
ALTER TABLE event_y2026 
ADD CONSTRAINT event_y2026_session_id_range
CHECK (session_id BETWEEN 4320 AND 10000);

プリーニングの実現

セッション ID

1000

SELECT * FROM event WHERE session_id = 1000;

実行プランの結果:

QUERY PLAN
─────────────────────────────────────────────────────
 Index Scan using event_y2025_session_id_idx on event_y2025 ...
   Index Cond: (session_id = 1000)
-- ★ event_y2026 はスキャン対象から完全に除外されました！

仕組み:

• event_y2026

  の CHECK 制約によると、「このパーティションには ID が 4320〜10000 のデータしかない」。
• クエリ条件「session_id = 1000」は、その範囲に含まれないため、最適化器はこれを即座にFalseと判定し、スキャンを跳ね返します（Constraint Exclusion）。

境界値の扱い

境界値（4320）では両方のパーティションを参照する必要がありますが、CHECK 制約がそれを正しく識別してくれます。 同様に、異なる Session ID でも適切にプリーニングされます。

7. 外れ値の導入と対処

現実の世界ではデータにはノイズ（外れ値）があります。 例えば、セッションが長引き、2025 年のセッションが 2026 年まで続いている場合や、Session ID が初期値に戻るなどの異常が発生します。この時、単純な範囲指定は誤ったプリーニングを招きます。

課題：外れ値による範囲の歪み

• 正常時:

  event_y2025

  の MAX は 4320。
• 外れ値出現:

  event_y2026

  に Session ID=1 という古いセッションが存在する。
• 結果:

  event_y2025

  の範囲が「1〜4320」と判断されたままになり、制約違反（事実上）が発生するか、または誤ったプリーニングが行われるリスクがあります。

解決策：PostgreSQL の

multi-minmax

から着想を得る

PostgreSQL には、ページ境界を認識する BRIN インデックス という機能があり、そこでは新しい

multi-minmax

マルチレンジ CHECK 制約の作成

チェック制約内で、複数の範囲を

OR

-- 2025 パーティション（通常のみ）
ALTER TABLE event_y2025 
ADD CONSTRAINT event_y2025_session_id_range
CHECK (session_id BETWEEN 1 AND 4320);

-- 2026 パーティション（外れ値を含むマルチレンジ）
-- 範囲 [1, 1] と [4320, 10000] の両方を許容
ALTER TABLE event_y2026 
ADD CONSTRAINT event_y2026_session_id_range
CHECK (
    session_id BETWEEN 1 AND 1 
    OR 
    session_id BETWEEN 4320 AND 10000
);

これで、Session ID=1 は 2 つのパーティションにまたがることをデータベースが正しく理解します。通常の値（例：6000）については、依然として適切なパーティションのみをスキャンできます。

プリーニングの確認

-- Session ID = 6000 の場合
SELECT * FROM event WHERE session_id = 6000;
-- 結果: event_y2026 のみインデックススキャンを実行（プリーニング成功）

8. まとめ：Gaps and Islands と自動化

この手法では、動的にパーティションごとの Session ID の範囲を特定する必要があります。これは古典的な**「Gaps and Islands（ギャップとアイランド）」問題**の応用です。 SQL で連続した Session ID の範囲（Island）を検出し、チェック制約を自動生成するアプローチが可能です。

-- 簡易的な範疇検出ロジックの例（Gaps and Islands パターン）
WITH detection AS (
  SELECT 
    session_id, 
    session_id - lag(session_id) OVER (ORDER BY session_id) > 1 AS is_gap 
  FROM event_y2026
), gaps AS (
  SELECT 
    session_id, 
    SUM(is_gap::int) OVER (ORDER BY session_id) AS gap_group 
  FROM detection
), ranges AS (
  SELECT MIN(session_id) mn, MAX(session_id) mx FROM gaps GROUP BY gap_group
)
-- 検出された範囲を SQL コマンドに変換して自動生成

結論

• 問題: パーティションキーではない列でのクエリでは、プリーニングが効かない。
• 解決策: データの特性（追加専用、単調増加など）を見抜いて CHECK 制約を定義し、最適化器に「このパーティションには特定のデータしかない」と保証させる。
• 高度な機能: 外れ値にも対応するために、複数の範囲を持つマルチレンジチェック制約を使用する。

適切に設計されたチェック制約は、パーティションキーの選択の自由度を高め、かつパフォーマンスの妥協点を解消する強力な武器となります。