Range Typeでパーティショニング
きっかけ
6/23に日本PostgreSQLユーザ会の夏セミナーで PostgreSQL 9.2 新機能の話をしてきました。会場から「範囲型を使うとレンジパーティションの定義や管理が楽に出来ますか？」という質問があり「できますよ〜」と簡単に答えてしまったのですが、本当に出来るか不安になったので検証してみました。
確認
まずは、親テーブルと子テーブルを作成し、子テーブルには範囲型演算子を使ってパーティション用のチェック制約を定義します。
postgres=# CREATE TABLE parent (id int NOT NULL, name text); CREATE TABLE postgres=# \d parent Table "public.parent" Column | Type | Modifiers --------+---------+----------- id | integer | not null name | text | postgres=# CREATE TABLE child0 () INHERITS (parent); CREATE TABLE postgres=# ALTER TABLE child0 ADD CONSTRAINT child0_id_check postgres-# CHECK('[0,10000)'::int4range @> id); ALTER TABLE postgres=# CREATE TABLE child1 () INHERITS (parent); CREATE TABLE postgres=# ALTER TABLE child1 ADD CONSTRAINT child1_id_check postgres-# CHECK('[10000,20000)'::int4range @> id); ALTER TABLE postgres=# \d child0 Table "public.child0" Column | Type | Modifiers --------+---------+----------- id | integer | not null name | text | Check constraints: "child0_id_check" CHECK ('[0,10000)'::int4range @> id) Inherits: parent
チェック制約がきちんときいているか確認します。child0は0以上10000未満のidのみを許容するはずです。
postgres=# INSERT INTO child0 VALUES(9999, 'foo'); INSERT 0 1 postgres=# INSERT INTO child0 VALUES(10000, 'foo'); ERROR: new row for relation "child0" violates check constraint "child0_id_check" DETAIL: Failing row contains (10000, foo). postgres=#
よさそうですね。さて、それでは検索してみましょう。「id=10000」という条件をつければ、CONSTRAINT EXCLUSION(制約による除外) が効いて child1 テーブルだけ検索するはず…
postgres=# EXPLAIN SELECT * FROM parent WHERE id = 10000; QUERY PLAN --------------------------------------------------------------------------- Result (cost=0.00..50.75 rows=13 width=36) -> Append (cost=0.00..50.75 rows=13 width=36) -> Seq Scan on parent (cost=0.00..0.00 rows=1 width=36) Filter: (id = 10000) -> Seq Scan on child0 parent (cost=0.00..25.38 rows=6 width=36) Filter: (id = 10000) -> Seq Scan on child1 parent (cost=0.00..25.38 rows=6 width=36) Filter: (id = 10000) (8 rows) postgres=#
おや？全テーブルにスキャンが発生していますね(Seq Scan なのはインデックスを一切作っていないためで、今回の確認とは別の問題です)。それでは、一般的な大小比較の書き方でチェック制約をつけてみます。
postgres=# ALTER TABLE child0 ADD CONSTRAINT child0_id_check2 postgres-# CHECK (id >= 0 AND id < 10000); ALTER TABLE postgres=# ALTER TABLE child1 ADD CONSTRAINT child1_id_check2 postgres-# CHECK (id >= 10000 AND id < 20000); ALTER TABLE postgres=# EXPLAIN SELECT * FROM parent WHERE id = 10000; QUERY PLAN --------------------------------------------------------------------------- Result (cost=0.00..25.38 rows=7 width=36) -> Append (cost=0.00..25.38 rows=7 width=36) -> Seq Scan on parent (cost=0.00..0.00 rows=1 width=36) Filter: (id = 10000) -> Seq Scan on child1 parent (cost=0.00..25.38 rows=6 width=36) Filter: (id = 10000) (6 rows) postgres=#
今度は、きちんと CONSTRAINT EXCLUSION が効いて child0 テーブルへのスキャンが省略されています。どうやら、範囲型でのチェック制約定義は CONSTRAINT EXCLUSION では無視されてしまうようです。ドキュメントをよく読んでみると、以下の記述が見つかりました。
5.9.6. 警告
パーティション制約を簡単にしておいてください。そうしないとプランナは、パーティションを使う必要がないことを立証できません。 前述の例で示したとおり、リスト分割のために簡単な等号条件を使用してください。また範囲分割のために簡単な範囲テストを使用してください。 手っ取り早い良い方法は、パーティショニングの制約がパーティション列とB-treeインデックス作成可能な演算子を用いた定数の比較のみを含んでいることです。
どうやら、範囲型に要素を含むかを判定する演算子の「@>/<@」は「B-treeインデックス作成可能」ではないということで CONSTRAINT EXCLUSION が効いていないようです。ソースを少し読んでみたところ、get_btree_test_op()という関数で CONSTRAINT EXCLUSION の対象とするかを判定しているので、設定変更などでの回避は難しそうです。
