PostgreSQLのJSONのIndexの張り方

PostgreSQLのJSON、jsonb型についてみていきます。
まずはテーブルつくりましょう。

test=# create table test ( val jsonb );

そしてデータを１０万件ほどぶち込みました。
インポート用のデータファイル形式は1行ごとにjson書いたmongodbでmson形式って呼ばれてるやつです。

インポートデータ

{"val": 0, "j": {"val": 0,"k": {"val": 0}}}
{"val": 0, "j": {"val": 0,"k": {"val": 1}}}
・
・
・
{"val": 99, "j": {"val": 99,"k": {"val": 99}}}

インポート

test=# copy test ( val ) from '/home/ubuntu/workspace/user/mson.txt';

10秒弱で終わりました。早いですね。あ、cloud9上です。

さて検索してみます。jのvalが10のものを探してみましょう。 @>演算子でJSONにそれを含むものを検索できます。今回は'{ "j": { "val": 10 }}'::jsonbを含むレコードを検索しています。

test=# select * from test where val @> '{ "j": { "val": 10 }}'::jsonb;
   id   |                       val                       
--------+-------------------------------------------------
   1001 | {"j": {"k": {"val": 0}, "val": 10}, "val": 0}
   1002 | {"j": {"k": {"val": 1}, "val": 10}, "val": 0}
   1003 | {"j": {"k": {"val": 2}, "val": 10}, "val": 0}
   1004 | {"j": {"k": {"val": 3}, "val": 10}, "val": 0}
   1005 | {"j": {"k": {"val": 4}, "val": 10}, "val": 0}
   1006 | {"j": {"k": {"val": 5}, "val": 10}, "val": 0}
   1007 | {"j": {"k": {"val": 6}, "val": 10}, "val": 0}
   1008 | {"j": {"k": {"val": 7}, "val": 10}, "val": 0}
   1009 | {"j": {"k": {"val": 8}, "val": 10}, "val": 0}
   1010 | {"j": {"k": {"val": 9}, "val": 10}, "val": 0}
   1011 | {"j": {"k": {"val": 10}, "val": 10}, "val": 0}
   1012 | {"j": {"k": {"val": 11}, "val": 10}, "val": 0}
   1013 | {"j": {"k": {"val": 12}, "val": 10}, "val": 0}
   1014 | {"j": {"k": {"val": 13}, "val": 10}, "val": 0}
   1015 | {"j": {"k": {"val": 14}, "val": 10}, "val": 0}
・
・
・
test=# 

インデックスなくても結構はやかったです。体感１秒くらい。
explainは以下。当然フルスキャンですね。

test=# explain select * from test where val @> '{ "j": { "val": 10 }}'::jsonb;
                         QUERY PLAN                         
------------------------------------------------------------
 Seq Scan on test  (cost=0.00..27885.00 rows=1000 width=92)
   Filter: (val @> '{"j": {"val": 10}}'::jsonb)
(2 rows)

ではインデックスをつけてみます。個々のJSON要素ではなくvalカラムに直接指定するだけで全体に張られるようです。

test=# CREATE INDEX idxgin ON test USING gin (val);
CREATE INDEX
test=# 

張るのに3秒くらいかかりましたね。
ではexplainを見てみましょう。

test=# explain select * from test where val @> '{ "j": { "val": 10 }}'::jsonb;
                                QUERY PLAN                                
--------------------------------------------------------------------------
 Bitmap Heap Scan on test  (cost=151.75..3310.69 rows=1000 width=92)
   Recheck Cond: (val @> '{"j": {"val": 10}}'::jsonb)
   ->  Bitmap Index Scan on idxgin  (cost=0.00..151.50 rows=1000 width=0)
         Index Cond: (val @> '{"j": {"val": 10}}'::jsonb)
(4 rows)

indexが使われてますね。実際実行するとほぼ待ちなしでした。

今回テストにつかったJSONはかなり小さいので、大きくなるとどうでしょうね？今回はこのへんで。