Posted at

PostgreSQLのJSONのIndexの張り方

More than 1 year has passed since last update.

PostgreSQLのJSON、jsonb型についてみていきます。

まずはテーブルつくりましょう。


test=# create table test ( val jsonb );

そしてデータを10万件ほどぶち込みました。

インポート用のデータファイル形式は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=#

インデックスなくても結構はやかったです。体感1秒くらい。

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はかなり小さいので、大きくなるとどうでしょうね?今回はこのへんで。