遅れてしまいましたが、PostgreSQL Adventカレンダー2014の9日目です。昨年はタグ検索するならPostgreSQLで決まり！でPostgreSQLの特徴でもある配列型を使ったタグ検索を紹介しました。今年はJSONを使ってみたいと思います。
つい先程、PostgreSQL GitリポジトリからPostgreSQL 9.4のソースを取得＆ビルドして記事を書いています。（記事を書くことを忘れていたので大急ぎで書きました）
お店や記事にタグ機能を付けたいことはくあります。PostgreSQLのJSONBなら簡単かつ高速なタグ検索機能を追加できます。Webシステムの強い味方になるはずです。
JSONB型を含むテーブルを作成
昨年書いた通りRDBMSはタグ検索が苦手です。しかし、PostgreSQLはRDBMSですが得意です！GINインデックスを使うと効率的に管理＆検索できます。特にPostgreSQL 9.4からはGINが更に高速化されています。
基本的な構造は配列版と変わりません。
CREATE TABLE shop ( name text, tags jsonb -- 配列版では tags text[] );
JSONBカラムにGINインデックスを作成
JSONB型として作ったtagsカラムにGINインデックスを作成します。
CREATE INDEX shop_tag_idx ON shop USING GIN (tags);
データを挿入してみる
JSONの配列型で新しいデータを挿入してみます。
INSERT INTO shop ( name, tags ) VALUES ( 'PostgreSQLショップ', '[ "RDB", "OSS", "ORDB", "データベース" ]' );
tagsを配列型を使った場合は次のようになります。
INSERT INTO shop (
name,
tags
)
VALUES (
'PostgreSQLショップ',
'{ "RDB", "OSS", "ORDB", "データベース" }'
);
解りづらいかも知れませんが、'{ }’と囲んでいる部分を'[ ]’に変えただけです。たったこれだけでWebアプリと親和性が高いJSONを使ってタグ検索ができるようになります。
検索してみる
単純にSELECTすると以下のようになります。
yohgaki@127 ~=# SELECT * FROM shop; ┌────────────────────┬────────────────────────────────────────┐ │ name │ tags │ ├────────────────────┼────────────────────────────────────────┤ │ PostgreSQLショップ │ ["RDB", "OSS", "ORDB", "データベース"] │ └────────────────────┴────────────────────────────────────────┘ (1 行) 時間: 0.321 ms
JSONB型として保存されていることがわかります。
タグ検索してみる
昨年の配列型の場合は以下のように検索しました。
SELECT * FROM shop WHERE 'OSS' = ANY(tags) AND 'RDB' = ANY(tags);
今回はJSONB型を使っているので違う検索方法になります。JSONB/JSON型には9.15. JSON Functions and Operatorsのオペレータが使えます。マニュアルを見れば色々操作ができることが判ると思います。昨年と同じような感じで JSON配列から指定した要素を含むレコードを検索するには
| ?& | text[] | Do all of these key/element strings exist? | ‘[“a”, “b”]’::jsonb ?& array[‘a’, ‘b’] |
が利用できることが分かります。
yohgaki@127 ~=# SELECT * FROM shop WHERE tags ?& array['OSS', 'RDB']; ┌────────────────────┬────────────────────────────────────────┐ │ name │ tags │ ├────────────────────┼────────────────────────────────────────┤ │ PostgreSQLショップ │ ["RDB", "OSS", "ORDB", "データベース"] │ └────────────────────┴────────────────────────────────────────┘ (1 行) 時間: 0.516 ms
無事検索できました。
インデックスが利用されていること確認
たった一行しかありませんが、インデックスが利用されていることがEXPLAIN ANALYZEで確認できました。（行数を増やすとオプティマイザが働いてSeqScanになりました。もっと増やすとインデックスを使うと思います）
┌─────────────────────────────────────────────────────────────────────────────────────────────────────────────
│ QUERY PLAN
├─────────────────────────────────────────────────────────────────────────────────────────────────────────────
│ Bitmap Heap Scan on shop (cost=16.01..20.02 rows=1 width=64) (actual time=0.016..0.016 rows=1 loops=1)
│ Recheck Cond: (tags ?& '{OSS,RDB}'::text[])
│ Heap Blocks: exact=1
│ -> Bitmap Index Scan on shop_tag_idx (cost=0.00..16.01 rows=1 width=0) (actual time=0.007..0.007 rows=1
│ Index Cond: (tags ?& '{OSS,RDB}'::text[])
│ Planning time: 0.056 ms
│ Execution time: 0.035 ms
└─────────────────────────────────────────────────────────────────────────────────────────────────────────────
(7 行)
JSONB型のインデックスは8.14.4. jsonb Indexingを参照すると使い方が分かります。
まとめ
以前、配列型でタグ検索を実装したので今回もJSON配列で作りました。もちろんオブジェクトでもGINで検索可能です。詳しくはPostgreSQLマニュアルを参照してください。
JSONBを使う場合の注意点は2つです。
- 重複するキーは登録できない
- キーの順序は維持されない
これはJSONBがハッシュを利用して実装されているためです。この制限がどうしても困る場合はJSON型を使いましょう。JSON型を使ってもPostgreSQLには関数インデックスという強力な味方があります。関数インデックスを使えば、テキストとして保存されているJSON型でも高速に検索可能です。
PostgreSQLのマニュアルも参照すると、配列型に較べ更に便利かつ強力になっていることがわかると思います。
PostgreSQLでタグ検索するならJSONBです！
