Hatena::Grouppostgresql

PostgreSQL 雑記 このページをアンテナに追加 RSSフィード

2012-04-16PostgreSQL 9.2 - Range Types このエントリーを含むブックマーク このエントリーのブックマークコメント

PostgreSQL 9.2 の新機能 Range Types (範囲型) について調べてみました。

範囲型 = { 下端, 上端 } のペア

範囲型は2つのスカラー値から成るデータ型です。2つのペアで「範囲」や「区間」を表し、これまで2カラム使っていたところを、1カラムで表現できます:

CREATE TABLE reservation (duration_begin timestamptz, duration_end timestamptz);
CREATE TABLE reservation (duration tstzrange);

組み込みの数値型や日時型をペアとする範囲型は予め定義されていますが、CREATE TYPE AS RANGE で独自の範囲型を定義することもできます。浮動小数点型、文字列型の範囲くらいは自分で定義する機会があるかもしれませんね。

int4range, int8range, numrange, daterange, tsrange (timestamp), tstzrange (timestamptz)

物理レイアウトは以下のようになっており、通常6バイトのオーバーヘッドがあります。rangetypid のぶんが気になります。

サイズ名前説明
1vl_lenこの値のサイズ
4rangetypidサブタイプの型
Nlower下端の値
Nupper上端の値
1flags端を含むか, 下限/上限なしか、等

範囲型の作成と、値の取り出し

範囲型の文字列表現は ( ) または [ ] で囲んだ2つのスカラー型です。下端 / 上端は、その端を「含む」か「含まない」かを指定できるため、以下の4パターンがあります:

=# SELECT numrange('[3,8]'), numrange('[3,8)'), numrange('(3,8]'), numrange('(3,8)');
 numrange | numrange | numrange | numrange
----------+----------+----------+----------
 [3,8]    | [3,8)    | (3,8]    | (3,8)

なお、下端 / 上端を別々に与える形式もありますが、この場合は下端は「含む」、上端は「含まない」(以上~未満)になります。また、離散型の範囲型 (int4range, int8range, daterange) の場合は、初期化の際に自動的に「以上~未満」の形式に正規化されました。

=# SELECT numrange(3, 8), int4range('(3,8]');
 numrange | int4range
----------+-----------
 [3,8)    | [4,9)

下端 / 上端の取り出しは lower() / upper() にて、範囲同士の重なりは && 演算子を使います:

=# SELECT lower(r), upper(r), r && numrange(2, 5) AS overlaps FROM numrange(3, 8) AS r;
 lower | upper | overlaps
-------+-------+----------
     3 |     8 | t

インデックスと排他制約

範囲型を使う最大の利点は、排他制約を定義することで「範囲の重なりが無い」制約をデータベース上に表現できることです。以下では、日ごとに価格が変わる商品を定義しています。

=# CREATE TABLE daily_price (item_id integer, duration daterange, price integer);
=# CREATE EXTENSION btree_gist; -- for gist on integer
=# ALTER TABLE daily_price ADD EXCLUDE USING gist (item_id WITH =, duration WITH &&);

うっかり範囲が重なるような更新を行うとエラーになります。商品IDが異なれば範囲が重なっても良いことも表現できています。

=# INSERT INTO daily_price VALUES(1, '[2012-04-16, 2012-04-20]', 10000);
=# INSERT INTO daily_price VALUES(1, '[2012-04-21, 2012-04-30]', 12000);
=# INSERT INTO daily_price VALUES(2, '[2012-04-18, 2012-04-23]', 10000);
→ OK
=# INSERT INTO daily_price VALUES(1, '[2012-04-18, 2012-04-23]', 14000);
ERROR:  conflicting key value violates exclusion constraint "daily_price_item_id_duration_excl"
DETAIL:  Key (item_id, duration)=(1, [2012-04-18,2012-04-24)) conflicts with existing key
         (item_id, duration)=(1, [2012-04-16,2012-04-21)).

範囲型に対してはインデックスを張る場合は gist を使います。なお、排他制約は内部的には gist インデックスを作成するため、上記の定義であれば、そのままインデックスを使った検索ができます:

=# EXPLAIN SELECT * FROM daily_price WHERE item_id = 1 AND duration && '[2012-04-20, 2012-04-23]';
                                              QUERY PLAN
------------------------------------------------------------------------------------------------------
 Index Scan using daily_price_item_id_duration_excl on daily_price  (cost=0.00..8.27 rows=1 width=40)
   Index Cond: ((item_id = 1) AND (duration && '[2012-04-20,2012-04-24)'::daterange))

まとめ

期間の範囲、価格の範囲、面積の範囲などなど、「範囲」を保持するケースは結構多くあると思います。そのときに範囲型を使うと、重なりの検索が自然に表現でき、インデックスを使える利点があります。一方、値ごとに6バイトという見逃せないオーバーヘッドもあります。

使い時としては、そのデータ型にインデックスを張りたいか / 排他制約が必要か、を目安にすることになるでしょう。データサイズは大きくなりますが、インデックスを使えるため検索は速くなります。サイズと性能のトレードオフですね。

参考リンク