概要
PostgreSQL 9.2で搭載され、9.3で強化されたJSON型の使い勝手とパフォーマンス、ハマりどころを調査した
ついでに比較対象として、hstoreも調べてあります
環境: Docker PostgreSQL 9.3.5 | Core i7 / 4GB memory / Ubuntu 14.04
調査内容:
- COPYによるデータロード
- 検索(ステートメント、パフォーマンス)
準備
基データは、日本郵政の郵便番号データ全国版を使用
$ wc -l < KEN_ALL.CSV
123721
テーブル作成とデータロード
CREATE TABLE ZIP_RAW (
ID INTEGER PRIMARY KEY,
IDX INTEGER,
ZIPCODE VARCHAR(7),
PREF VARCHAR(200),
CITY VARCHAR(200),
ST VARCHAR(200)
);
CREATE TABLE ZIP_JSON (
ID INTEGER PRIMARY KEY,
DATA JSON
);
CREATE EXTENSION HSTORE;
CREATE TABLE ZIP_HSTORE (
ID INTEGER PRIMARY KEY,
DATA HSTORE
);
COPYによるデータロード
JSON型(hstore型含む)カラムにも、COPYによるデータロードが使用可能です
ただし、JSONデータ内のカンマ文字の取り扱いが面倒なため、フィールドセパレータをTAB(\t)等にして、COPY時に指定するのが現状のベストプラクティスと思われます
e.g.)
※フィールドセパレータをTABとしています
0 {"idx":0,"zipcode":"0600000","pref":"北海道","city":"札幌市中央区","street":"以下に掲載がない場合"}
1 {"idx":1,"zipcode":"0640941","pref":"北海道","city":"札幌市中央区","street":"旭ケ丘"}
2 {"idx":2,"zipcode":"0600041","pref":"北海道","city":"札幌市中央区","street":"大通東"}
※フィールドセパレータをTABとしています
0 "idx"=>0, "zipcode"=>"0600000", "pref"=>"北海道", "city"=>"札幌市中央区", "street"=>"以下に掲載がない場合"
1 "idx"=>1, "zipcode"=>"0640941", "pref"=>"北海道", "city"=>"札幌市中央区", "street"=>"旭ケ丘"
2 "idx"=>2, "zipcode"=>"0600041", "pref"=>"北海道", "city"=>"札幌市中央区", "street"=>"大通東"
COPY
psql# COPY ZIP_JSON FROM '/tmp/json.txt' DELIMITER ' ';
psql# COPY ZIP_HSTORE FROM '/tmp/hash.txt' DELIMITER ' ';
※TerminalからTAB文字を入力するには、Ctrl+v の後、TABキーで入力できます
KEN_ALL.CSVからCOPY用データ生成するためのrubyコード
=begin
$ ruby conv.rb [-f tsv|json|hash]
=end
require "csv"
require "json"
require "optparse"
opts = ARGV.getopts("f:")
format = opts["f"].downcase.to_sym rescue :tsv
SEPT = "\t"
idx = 0
open("KEN_ALL.CSV", "r+:Shift_JIS").each do |line|
CSV.parse(line.encode(Encoding.default_external)) do |r|
data = {"idx" => idx, "zipcode" => r[2], "pref" => r[6], "city" => r[7], "street" => r[8]}
line = case format
when :tsv
[idx, data.values].flatten.to_csv(col_sep:SEPT)
when :json
[idx, data.to_json].join(SEPT)
when :hash
[idx, data.inspect.sub(/^{/, '').sub(/}$/, '')].join(SEPT)
end
puts line
idx += 1
end
end
※idxはJSON内の数値データ検索用として加えています
検索 (ステートメント、パフォーマンス)
SELECTステートメント、WHERE句
DBカラム
SELECT * FROM ZIP_RAW WHERE ZIPCODE = '1010021';
SELECT * FROM ZIP_RAW WHERE CITY LIKE '千代田区%';
SELECT * FROM ZIP_RAW WHERE IDX < 10;
JSON
SELECT * FROM ZIP_JSON WHERE DATA->>'zipcode' = '1010021';
SELECT * FROM ZIP_JSON WHERE DATA->>'city' LIKE '千代田区%';
SELECT * FROM ZIP_JSON WHERE cast(DATA->>'idx' AS INTEGER) < 10;
hstore
SELECT * FROM ZIP_HSTORE WHERE DATA->'zipcode' = '1010021';
SELECT * FROM ZIP_HSTORE WHERE DATA->'city' LIKE '千代田区%';
SELECT * FROM ZIP_HSTORE WHERE cast(DATA->'idx' AS INTEGER) < 10;
パフォーマンス
※SELECT * FROM ZIP_*を対象に下記条件を設定。\timingを表示するようにし、5回計測中、最大最小を以外の3回の平均
※単位 ms
条件 | DBカラム | JSON | hstore |
----------------------------------------------|---------:|-------:|-------:|
文字列::完全一致
(CITY = '江東区') | 14.9 | 137.9 | 29.0 |
文字列::前方一致
(CITY LIKE '千代田区%') | 18.7 | 139.3 | 34.5 |
文字列::中間一致
(CITY LIKE '%中央%') | 23.0 | 138.7 | 31.2 |
文字列::後方一致
(CITY LIKE '%港区') | 17.9 | 140.0 | 35.3 |
文字列::否定
(CITY <> '新宿区') | 111.2 | 228.5 | 131.6 |
数値::一致
(IDX = 1001) | 10.3 | 140.0 | 35.0 |
数値::範囲一致
(IDX < 100) | 12.3 | 40.3 | 144.5 |
数値::否定
(IDX <> 12345) | 119.5 | 237.0 | 153.6 |
インデックス使用時のパフォーマンス
CREATE INDEX RI0 ON ZIP_RAW (IDX);
CREATE INDEX RI1 ON ZIP_RAW (CITY);
CREATE INDEX JI0 ON ZIP_JSON ( CAST(DATA->>'idx' AS INTEGER) );
CREATE INDEX JI1 ON ZIP_JSON ( (DATA->>'city') );
CREATE INDEX HI0 ON ZIP_HSTORE ( CAST(DATA->'idx' AS INTEGER) );
CREATE INDEX HI1 ON ZIP_HSTORE ( (DATA->'city') );
※上記INDEX作成後環境において、SELECT * FROM ZIP_*を対象に下記条件を設定。\timingを表示するようにし、5回計測中、最大最小を以外の3回の平均
※単位 ms
条件 | DBカラム | JSON | hstore |
----------------------------------------------|---------:|-------:|-------:|
文字列::完全一致
(CITY = '江東区') | 0.8 | 0.4 | 0.4 |
文字列::前方一致
(CITY LIKE '千代田区%') | 19.3 | 140.7 | 32.2 |
文字列::中間一致
(CITY LIKE '%中央%') | 16.1 | 140.0 | 32.3 |
文字列::後方一致
(CITY LIKE '%港区') | 16.7 | 142.0 | 37.0 |
文字列::否定
(CITY <> '新宿区') | 113.7 | 231.5 | 139.9 |
数値::一致
(IDX = 1001) | 0.1 | 0.1 | 0.1 |
数値::範囲一致
(IDX < 100) | 0.2 | 0.4 | 0.4 |
数値::否定
(IDX <> 12345) | 125.6 | 239.5 | 147.5 |
考察
まとめ参照のこと
ハマりどころ
キーはcase sensetive
JSON/hstoreキーはDBカラムと違い、case sensetiveです。data->>'IDX'とdata->>'idx'は別扱いです。
キー存在検証はしてくれないため、WHERE条件ではエラーが発生しません。
一番ハマるのは、CREATE INDEX時です。explainでINDEXが使われているか確認しましょう。
e.g.) このようなデータに対して
ZIP_JSON:
id | data
----+-------------------------------------------------
0 | {"idx":0,"zipcode":"0600000","pref":"北海道",...
DATA->>'ZIPCODE' = '0600000'と検索してもデータは見つかりません
※BAD case
development_db=# SELECT * FROM ZIP_JSON WHERE DATA->>'ZIPCODE' = '0600000';
id | data
----+------
(0 rows)
DATA->>'zipcode'として探すと見つかります
development_db=# SELECT * FROM ZIP_JSON WHERE DATA->>'zipcode' = '0600000';
id | data
----+-------------------------------------------------
0 | {"idx":0,"zipcode":"0600000","pref":"北海道",...
(1 row)
JSONオペレータ指定方法
->>オペレータは( )でくくる必要がでてくるケースがあります
development_db=# CREATE INDEX IDX ON ZIP_JSON_I ( DATA->>'zipcode' );
ERROR: syntax error at or near "->>"
LINE 1: CREATE INDEX IDX ON ZIP_JSON_I ( DATA->>'ZIPCODE' );
※これだと失敗する
development_db=# CREATE INDEX IDX ON ZIP_JSON_I ( (DATA->>'zipcode') );
CREATE INDEX
※これで成功
まとめ
PostgreSQL 9.3におけるJSON型は「使える型」言える
使用に際しての注意点:
- 検索対象となることが確定しているなら、INDEXを作成は必須。ネイティブのDBカラムと同等の検索性能が得られる
- INDEXが使われなかった場合の検索性能の劣化は、DBカラムを1とするとhstore=1/2、JSON=1/10となる。また、JSON/hstoreは、格納データに比例して劣化するものと推測される
- よって、JSON/hstoreにおける文字列データの検索は、完全一致を前提とした設計を検討する必要がある
- JSON/hstoreにおけるの数値データに対する検索やINDEX作成は、
CASTを使用して使う必要があり、若干扱いづらい。しかし、範囲検索が可能で、文字列データに対するLIKEよりは検索性能が高い - DATEはJSON仕様に無いため、文字列型で格納せざるを得なく、INDEXの恩恵は受けづらい
加えて、hstoreはobsoluteとなる可能性があるようなので、見極めがが大切
ref:
- 郵便番号データの説明 - 日本郵便
- PostgreSQL 9.3 の JSON サポートについて（長いよッ） - Qiita
- SIOS "OSSよろず" ブログ出張所: PostgreSQL 9.3 の JSON データのインデックス
- PostgreSQLのJSON型とhstore型のパフォーマンス — still deeper
あとがき
Oyster Tableで牡蠣食べました。美味しいですね