PostgreSQLでパーティションキー以外のカラムでクエリする際にパーティションプルーニングを実現する方法

パーティションテーブルの最も価値のある機能の1つは、プルーニング、つまりクエリ述語に基づいてデータベースがパーティション全体を排除できる能力です。従来の考え方では、プルーニングはパーティションキーでクエリする場合にのみ達成できるとされています。これは、適切なキーを選択することを非常に困難にします。しかし、データがある特定のパターンに従う場合、いくつかの clever なトリックを使用することで、パーティションキー以外のカラムでフィルタリングする際にもプルーニングを達成できます。この記事では、パーティションキー以外のカラムでフィルタリングする際にパーティションプルーニングを達成する方法を実演します。 abstrakt design による画像 目次 テーブルパーティション キーカラムのパーティションプルーニング ローカルインデックス グローバルインデックス パーティションキー以外のカラムでのプルーニング オプティマイザとの対話 constraint_exclusion パラメータ 外れ値の処理 外れ値 ギャップとアイランド 背景 最終的な考察 テーブルパーティション 多くのユーザーがいる人気のウェブサイトを運営していると想像してください。プロダクトチームはシステムの使用状況に関する洞察を得たいと考えているため、イベントをログに記録し始めます。イベントにコンテキストを与えるために、それらをセッションにグループ化し、時間、タイプ、および一部のデータをデータベーステーブルに保存します。 db=# 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); CREATE TABLE 多くのユーザーがいるため、多くのイベントが予想されます。ほとんどのクエリはデータのサブセットのみを使用し、通常は特定の期間です。そのため、タイムスタンプに基づいて年ごとにパーティションを作成します。 db=# CREATE TABLE event_y2025 PARTITION OF event FOR VALUES FROM ('2025-01-01 UTC') TO ('2026-01-01 UTC'); CREATE TABLE db=# CREATE TABLE event_y2026 PARTITION OF event FOR VALUES FROM ('2026-01-01 UTC') TO ('2027-01-01 UTC'); CREATE TABLE これで、2つのパーティションができました。1つは2025年のイベント用、もう1つは2026年のイベント用です。セッションは次のようになります。 INSERT INTO event (session_id, timestamp, type, data) VALUES (1, '2025-12-28 15:00:00 UTC', 'view', '{"page": "/login"}'), (1, '2025-12-28 15:00:06 UTC', 'click', '{"selector": "#login"}'), (1, '2025-12-28 15:00:07 UTC', 'login_failed', '{"attempt": 1}'), (1, '2025-12-28 15:00:10 UTC', 'click', '{"selector": "#forgot-password"}'), (1, '2025-12-28 15:00:17 UTC', 'view', '{"page": "/reset-password"}'), (1, '2025-12-28 15:00:23 UTC', 'click', '{"selector": "#reset-password"}'); このセッションでは、ユーザーはシステムにログインしようとして失敗し、パスワードのリセットを要求しました。 より多くのデータの生成 例をより現実的にするために、もう少しデータを作成しましょう。 WITH sessions AS ( SELECT n AS session_id, '2025-12-28 23:59:56 UTC'::timestamptz + interval '1 minute' * n as started_at FROM generate_series(2, 10000) AS t(n) ) INSERT INTO event (session_id, timestamp, type, data) SELECT session_id, started_at + interval '1 second' * n, (array['view', 'click', 'login_failed', 'logged_in'])[ceil(random() * 3)] as type, '{}'::jsonb as data FROM sessions, generate_series(1, 5) as n ORDER BY 1, 2; INSERT 0 49995 これで、両方のパーティションにわたって約50Kのイベントがテーブルに格納されました。 キーカラムのパーティションプルーニング テーブルのパーティションキーはタイムスタンプなので、タイムスタンプでフィルタリングするクエリはパーティションプルーニングの恩恵を受けることができます。例えば、2025年12月のイベントをクエリします。 db=# EXPLAIN SELECT * FROM event WHERE timestamp >= '2025-12-01 UTC' AND timestamp < '2026-01-01 UTC'; QUERY PLAN ------------------------------------------------------------------------------------------------ Seq Scan on event_y2025 event Filter: (("timestamp" >= '2025-12-01 00:00:00+00'::timestamp with time zone) AND ("timestamp" < '2026-01-01 00:00:00+00'::timestamp with time zone)) データベースは2025年のパーティションのみをスキャンする必要があることを賢く判断しました。2026年のパーティションはアクセスされませんでした。これがパーティションプルーニングです。 別の一般的なクエリは、特定のセッションのすべてのイベントを見つけることです。 db=# EXPLAIN SELECT * FROM event WHERE session_id = 1; QUERY PLAN ------------------------------------------------------ Append (cost=0.00..1060.07 rows=11 width=37) -> Seq Scan on event_y2025 event_1 Filter: (session_id = 1) -> Seq Scan on event_y2026 event_2 Filter: (session_id = 1) 今回は、データベースはすべてのパーティションにアクセスしました。パーティションプルーニングは使用されませんでした。このクエリでは、データベースはパーティションを排除する方法がないため、一致するイベントを探すためにすべてのパーティションをスキャンするしかありませんでした。ここでパーティションは少し厄介になります。一方ではプルーニングを達成したいが、他方では他の、おそらく非常に一般的なクエリで痛みを伴う妥協をする必要があります。 ローカルインデックス 特定のセッションのイベントを取得することはかなり一般的であるため、高速である必要があります。データベースで物事を高速にするには、インデックスを作成すればよいですよね？ db=# CREATE INDEX event_session_ix ON event(session_id); CREATE INDEX これはセッションIDにインデックスを作成します。インデックスが配置されたら、セッション1のイベントを取得します。 db=# EXPLAIN SELECT * FROM event WHERE session_id = 1; QUERY PLAN ------------------------------------------------------------------------- Append (cost=0.29..16.82 rows=11 width=37) -> Index Scan using event_y2025_session_id_idx on event_y2025 event_1 Index Cond: (session_id = 1) -> Index Scan using event_y2026_session_id_idx on event_y2026 event_2 Index Cond: (session_id = 1) データベースは再びすべてのパーティションを訪問する必要がありました。唯一の違いは、今回は各パーティションのインデックスを使用したことです。インデックスの使用はパーティション全体をスキャンするよりも高速ですが、データベースは依然としてすべてのパーティションをスキャンすることを余儀なくされています。現在、パーティションは2つしかありませんが、テーブルに100個のパーティションがあった場合、このクエリは100個のテーブルをクエリするようなものになります！このタイプのインデックスはローカルインデックスと呼ばれます。なぜなら、各パーティションに個別のインデックスを作成するからです。 db=# \di event_* List of indexes Schema │ Name │ Type │ Owner │ Table ────────┼───────────────────────┼───────┼───────┼──────── public │ event_session_ix │ index │ haki │ event public │ event_y2025_session_id_idx │ index │ haki │ event_y2025 public │ event_y2026_session_id_idx │ index │ haki │ event_y2026 (3 rows) ローカルインデックスは、パーティションキー以外のカラムで頻繁にフィルタリングする場合に役立ちます。 グローバルインデックス パーティションテーブルにインデックスを作成する別の方法は、複数のパーティションにまたがる単一のインデックスを作成することです。これはグローバルインデックスと呼ばれます。残念ながら、バージョン19以降、PostgreSQLはパーティションテーブルに対するグローバルインデックスをサポートしていません。この件については2009年から議論が続いており、pgsql-hackers メーリングリストで最新情報を確認できます。 グローバルインデックス グローバルインデックスがないことのもう1つの問題点は、パーティションキー以外のものに対する一意性を強制することが困難になることです。これはこの記事の範囲外です。 パーティションキー以外のカラムでのプルーニング イベントテーブルはタイムスタンプでパーティション化されているため、タイムスタンプによるクエリはパーティションプルーニングの恩恵を受けることができます。しかし、セッションIDでクエリしたい状況は依然として数多くあります。単一セッションからのイベントは複数のパーティションにまたがる可能性があり、データベースは現在、関連性のないものを排除する方法がありません。ローカルインデックスは一部の痛みを軽減しますが、データベースは依然としてすべてのパーティションを訪問する必要があり、これはうまくスケーリングしない可能性があります。 この時点で、データベースが標準でできることの限界に達し、ドメイン知識とデータの知識、つまりデータがどのように使用され、どのように保存されているかを知る必要があります。 イベントテーブルは追記専用です。テーブルへの更新はなく、イベントは不変です。 セッションIDはシーケンシャルに生成されます。セッションIDは時間とともに増加します。 セッションは短命です。通常のセッションは通常、数分または数時間よりも長くはなりません。 このパターンは役立つ可能性があります！パターンを視覚化するために、タイムスタンプとセッションIDをプロットします。 タイムスタンプとセッションIDのプロット セッションIDはタイムスタンプと強く相関しています。これは、明確な区別を特定することが可能であることを意味します。