PostgreSQL JSON型、JSONB型の違いとINDEXの設定
PostgreSQLにはJSONを格納するためのJSON型、JSONB型の2つのデータ型があります。違いはなんだろうと思いドキュメントを見てみると
8.14. JSONデータ型
json型とjsonb型というデータ型は、ほとんど 同一の入力値セットを受け入れます。
現実的に主要な違いは効率です。
jsonデータ型は入力テキストの正確なコピーで格納し、処理関数を実行するたびに再解析する必要があります。
jsonbデータ型では、分解されたバイナリ形式で格納されます。
格納するときには変換のオーバーヘッドのため少し遅くなりますが、処理するときには、全く再解析が必要とされないので大幅に高速化されます。
また jsonb型の重要な利点はインデックスをサポートしていることです。
JSON型は、JSON文字列をそのまま格納。
JSONB型は、JSON文字列を解析後に格納という違いがあるようです。
JSONB型にはインデックスが設定できるようなので、両者の違いとインデックスの作成を試してみます。
サンプルテーブル
比較のため、2つのテーブルを作成しました。
CREATE TABLE json_test (doc json);
CREATE TABLE jsonb_test (doc jsonb);
データ型の違うdoc列を持つテーブルです。
こんなプログラムで各々100万件のデータを登録しました。
- import psycopg2
- import psycopg2.extras
- con = psycopg2.connect(
- host = '192.168.11.102',
- port = 5432,
- database='sample',
- user='pgadmin',
- password='P@ssw0rd')
- cur = con.cursor()
- for i in range(1, 1000001):
- cur.execute("INSERT INTO json_test(doc) VALUES (%s)", [psycopg2.extras.Json({'id': i, 'value':'value1-%d' % (i)})])
- if i % 10000 == 0:
- print(i)
- con.commit()
- con.commit()
- cur.close()
- con.close()
検索速度
idが1のデータを検索してみます。
SELECT * FROM json_test where doc->>'id' = '1';
SELECT * FROM jsonb_test where doc->>'id' = '1';
json_testの処理速度は0:00:00.221。
jsonb_testの0:00:00.59でした。
フルスキャンだとJSONB型のほうが4倍程度高速です。
各々、実行プランはこのようになりました。
# EXPLAIN SELECT * FROM json_test where doc->'id' = '1';
QUERY PLAN
--------------------------------------------------------------------------------
Gather (cost=1000.00..16941.27 rows=4960 width=40)
Workers Planned: 2
-> Parallel Seq Scan on json_test (cost=0.00..15445.27 rows=2067 width=40)
Filter: ((doc ->> 'id'::text) = '1'::text)
# EXPLAIN SELECT * FROM jsonb_test where doc->>'id' = '1';
QUERY PLAN
---------------------------------------------------------------------------------
Gather (cost=1000.00..18050.00 rows=5000 width=51)
Workers Planned: 2
-> Parallel Seq Scan on jsonb_test (cost=0.00..16550.00 rows=2083 width=51)
Filter: ((doc ->> 'id'::text) = '1'::text)
@>演算子
インデックスの作成の前に、デフォルトの状態でインデックスを作成した場合は
doc->'id'
という問い合わせだとインデックスが使われないようです。
@>演算子を使って検索してみます。
# SELECT * FROM jsonb_test where doc @> '{"id":1}';
json型も同様にと思ったら、エラーになります。
# SELECT * FROM json_test where doc @> '{"id":1}';
ERROR: 演算子が存在しません: json @> unknown
行 1: SELECT * FROM json_test where doc @> '{"id":1}';
^
HINT: 指定した名称と引数の型に合う演算子がありません。明示的な型キャストが必要かもしれません。
一旦jsonb型に変換してやる必要があります。
SELECT * FROM json_test where doc::jsonb @> '{"id":1}';
使用できる演算子もデータ型により異なりますね。
実行プランはこのようになりました。
# EXPLAIN SELECT * FROM json_test where doc::jsonb @> '{"id":1}';
QUERY PLAN
--------------------------------------------------------------------------------
Gather (cost=1000.00..18470.65 rows=9920 width=40)
Workers Planned: 2
-> Parallel Seq Scan on json_test (cost=0.00..16478.65 rows=4133 width=40)
Filter: ((doc)::jsonb @> '{"id": 1}'::jsonb)
# EXPLAIN SELECT * FROM jsonb_test where doc @> '{"id":1}';
QUERY PLAN
-------------------------------------------------------------------------------
Gather (cost=1000.00..16518.33 rows=100 width=51)
Workers Planned: 2
-> Parallel Seq Scan on jsonb_test (cost=0.00..15508.33 rows=42 width=51)
Filter: (doc @> '{"id": 1}'::jsonb)
インデックスの作成
インデックスを作成してみます。
CREATE INDEX idx_doc ON jsonb_test USING GIN (doc);
検索速度は0:00:00.59から0:00:00.05となりました。
ちゃんとインデックスが使われていますね。
# EXPLAIN SELECT * FROM jsonb_test where doc @> '{"id":1}';
QUERY PLAN
-------------------------------------------------------------------------
Bitmap Heap Scan on jsonb_test (cost=36.77..408.47 rows=100 width=51)
Recheck Cond: (doc @> '{"id": 1}'::jsonb)
-> Bitmap Index Scan on idx_doc (cost=0.00..36.75 rows=100 width=0)
Index Cond: (doc @> '{"id": 1}'::jsonb)
ドキュメントの記載通り、以下のクエリーではインデックスは使われませんでした。
# EXPLAIN SELECT * FROM jsonb_test where doc->'id' = '1';
QUERY PLAN
Gather (cost=1000.00..18050.00 rows=5000 width=51)
Workers Planned: 2
-> Parallel Seq Scan on jsonb_test (cost=0.00..16550.00 rows=2083 width=51)
Filter: ((doc -> 'id'::text) = '1'::jsonb)
- 関連記事
コメント