PostgreSQL JSON型についての使い勝手とパフォーマンス、時々ハマりどころの調査

  • 18
    いいね
  • 0
    コメント
この記事は最終更新日から1年以上が経過しています。

概要

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.)

json.txt (フィールドセパレータを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":"大通東"}

hash.txt (フィールドセパレータを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コード

conv.rb
=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:

あとがき

Oyster Tableで牡蠣食べました。美味しいですね