PostgreSQLでJSON/JSONB/HSTOREフィールドテスト

はじめに

• PostgreSQLにはJSON、JSONB、HSTOREというカラム型が存在する
  • 通常データの列カラムは固定の列数となっているため、いくつあるかわからないデータを格納するために、data1,data2・・・などの「使うか使わないかわからない」列を準備しておく必要があった
  • こういった「列数は可変」のデータをカバーするために前述の型が存在する
• これらの型を使うと、検索方法が変わり速度も変化するのか、について調査を行った

環境構築

前提

• 本内容は、PostgreSQL JSON型についての使い勝手とパフォーマンス、時々ハマりどころの調査を元に、実測時間は実行計画で行ったものとなる
  • 前述のサイトでは、実際に時間計測して平均値を取ってあった。
  • また、細かな部分については本書より上記サイトの方がはるかに良いので、そちらを参考のこと
    • というか、ほとんどの部分はまるっと使ってしまっている
• ローカルにPostgreSQL 9.3以降がインストールされているものとする
• ユーザ、パスワードは以下手順ではpostgres/postgresとして記載している
• 元となる郵便番号データは、郵便局・郵便番号データダウンロードより入手したものを、こちらPostgreSQL JSON型についての使い勝手とパフォーマンス、時々ハマりどころの調査を参考にてデータを作った。

データベースとテーブルの準備

• 以下をDosプロンプトで貼り付け
• 最後から4行の取り込み部分は、DELIMITER部分の空白はタブである必要がある。

shell {.line-numbers}

psql -h localhost -p 5432 -U postgres postgres
postgres
CREATE DATABASE testdb;
\q
psql -h localhost -p 5432 -U postgres testdb
postgres
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 TABLE ZIP_JSONB (
  ID INTEGER PRIMARY KEY,
  DATA JSONB
);
CREATE EXTENSION HSTORE;
CREATE TABLE ZIP_HSTORE (
  ID INTEGER PRIMARY KEY,
  DATA HSTORE
);

テーブルデータの読み込み

• 以下は、1行ずつ貼り付け、''；の部分に、TABキーでTABを入れること（コピー＆ペーストではタブが入らないため）

SQL {.line-numbers}

\COPY ZIP_RAW FROM 'ken_all.tsv' DELIMITER '';
\COPY ZIP_JSON FROM 'ken_all.json' DELIMITER '';
\COPY ZIP_JSONB FROM 'ken_all.json' DELIMITER '';
\COPY ZIP_HSTORE FROM 'ken_all.hash' DELIMITER '';

ファイルの構成

• ken_all.tsv
• タブ区切り文字列

txt {.line-numbers}

0	0	0600000	北海道	札幌市中央区	以下に掲載がない場合
1	1	0640941	北海道	札幌市中央区	旭ケ丘
2	2	0600041	北海道	札幌市中央区	大通東

• ken_all.json
• JSON形式にしたもの

json {.line-numbers}

0	{"idx":0,"zipcode":"0600000","pref":"北海道","city":"札幌市中央区","street":"以下に掲載がない場合"}
1	{"idx":1,"zipcode":"0640941","pref":"北海道","city":"札幌市中央区","street":"旭ケ丘"}
2	{"idx":2,"zipcode":"0600041","pref":"北海道","city":"札幌市中央区","street":"大通東"}

• ken_all.hash
• hstore形式にしたもの

text {.line-numbers}

0	"idx"=>0, "zipcode"=>"0600000", "pref"=>"北海道", "city"=>"札幌市中央区", "street"=>"以下に掲載がない場合"
1	"idx"=>1, "zipcode"=>"0640941", "pref"=>"北海道", "city"=>"札幌市中央区", "street"=>"旭ケ丘"
2	"idx"=>2, "zipcode"=>"0600041", "pref"=>"北海道", "city"=>"札幌市中央区", "street"=>"大通東"

確認方法

• 確認は以下SQLを実行して行った

sql {.line-numbers}

EXPLAIN ANALYZE SELECT * FROM ZIP_RAW WHERE ZIPCODE = '1010021';
EXPLAIN ANALYZE SELECT * FROM ZIP_RAW WHERE ZIPCODE <> '1010021';
EXPLAIN ANALYZE SELECT * FROM ZIP_RAW WHERE CITY LIKE '千代田区%';
EXPLAIN ANALYZE SELECT * FROM ZIP_RAW WHERE CITY LIKE '%中央%';
EXPLAIN ANALYZE SELECT * FROM ZIP_RAW WHERE CITY LIKE '%港区';
EXPLAIN ANALYZE SELECT * FROM ZIP_RAW WHERE IDX = 10;
EXPLAIN ANALYZE SELECT * FROM ZIP_RAW WHERE IDX <> 10;
EXPLAIN ANALYZE SELECT * FROM ZIP_RAW WHERE IDX < 10;

EXPLAIN ANALYZE SELECT * FROM ZIP_JSON WHERE DATA->>'zipcode' = '1010021';
EXPLAIN ANALYZE SELECT * FROM ZIP_JSON WHERE DATA->>'zipcode' <> '1010021';
EXPLAIN ANALYZE SELECT * FROM ZIP_JSON WHERE DATA->>'city' LIKE '千代田区%';
EXPLAIN ANALYZE SELECT * FROM ZIP_JSON WHERE DATA->>'city' LIKE '%中央%';
EXPLAIN ANALYZE SELECT * FROM ZIP_JSON WHERE DATA->>'city' LIKE '港区%';
EXPLAIN ANALYZE SELECT * FROM ZIP_JSON WHERE cast(DATA->>'idx' AS INTEGER) = 10;
EXPLAIN ANALYZE SELECT * FROM ZIP_JSON WHERE cast(DATA->>'idx' AS INTEGER) <> 10;
EXPLAIN ANALYZE SELECT * FROM ZIP_JSON WHERE cast(DATA->>'idx' AS INTEGER) < 10;

EXPLAIN ANALYZE SELECT * FROM ZIP_JSONB WHERE DATA->>'zipcode' = '1010021';
EXPLAIN ANALYZE SELECT * FROM ZIP_JSONB WHERE DATA->>'zipcode' <> '1010021';
EXPLAIN ANALYZE SELECT * FROM ZIP_JSONB WHERE DATA->>'city' LIKE '千代田区%';
EXPLAIN ANALYZE SELECT * FROM ZIP_JSONB WHERE DATA->>'city' LIKE '%中央%';
EXPLAIN ANALYZE SELECT * FROM ZIP_JSONB WHERE DATA->>'city' LIKE '%港区';
EXPLAIN ANALYZE SELECT * FROM ZIP_JSONB WHERE cast(DATA->>'idx' AS INTEGER) = 10;
EXPLAIN ANALYZE SELECT * FROM ZIP_JSONB WHERE cast(DATA->>'idx' AS INTEGER) <> 10;
EXPLAIN ANALYZE SELECT * FROM ZIP_JSONB WHERE cast(DATA->>'idx' AS INTEGER) < 10;

EXPLAIN ANALYZE SELECT * FROM ZIP_HSTORE WHERE DATA->'zipcode' = '1010021';
EXPLAIN ANALYZE SELECT * FROM ZIP_HSTORE WHERE DATA->'zipcode' <> '1010021';
EXPLAIN ANALYZE SELECT * FROM ZIP_HSTORE WHERE DATA->'city' LIKE '千代田区%';
EXPLAIN ANALYZE SELECT * FROM ZIP_HSTORE WHERE DATA->'city' LIKE '%中央%';
EXPLAIN ANALYZE SELECT * FROM ZIP_HSTORE WHERE DATA->'city' LIKE '%港区';
EXPLAIN ANALYZE SELECT * FROM ZIP_HSTORE WHERE cast(DATA->'idx' AS INTEGER) = 10;
EXPLAIN ANALYZE SELECT * FROM ZIP_HSTORE WHERE cast(DATA->'idx' AS INTEGER) <> 10;
EXPLAIN ANALYZE SELECT * FROM ZIP_HSTORE WHERE cast(DATA->'idx' AS INTEGER) < 10;

速度の確認(INDEXなし)

Execution Time(INDEXなし)

• 数値の単位はms

条件	DBカラム	JSON	JSONB	HSTORE
文字列::完全一致 ZIPCODE='1010021'	6.817	130.844	16.197	19.718
文字列::否定 ZIPCODE<>'1010021'	11.218	140.090	19.780	21.135
文字列::前方一致 CITY LIKE '千代田区%'	8.903	144.195	15.783	16.218
文字列::中間一致 CITY LIKE '%中央%'	9.616	123.995	18.519	17.710
文字列::後方一致 CITY LIKE '%港区'	10.312	90.486	16.944	14.903
数値::一致 IDX=10	4.974	177.400	39.760	36.331
数値::否定 IDX<>10	9.150	190.363	60.093	40.193
数値::範囲一致 IDX<10	5.237	215.587	40.855	33.807

速度の確認(INDEXあり)

CREATE INDEX

SQL {.line-numbers}

CREATE INDEX RI0 ON ZIP_RAW (IDX);
CREATE INDEX RI1 ON ZIP_RAW (CITY);

CREATE INDEX JIB0 ON ZIP_JSON ( CAST(DATA->>'idx' AS INTEGER) );
CREATE INDEX JIB1 ON ZIP_JSON ( (DATA->>'city') );

CREATE INDEX JI0 ON ZIP_JSONB ( CAST(DATA->>'idx' AS INTEGER) );
CREATE INDEX JI1 ON ZIP_JSONB ( (DATA->>'city') );

CREATE INDEX HI0 ON ZIP_HSTORE ( CAST(DATA->'idx' AS INTEGER) );
CREATE INDEX HI1 ON ZIP_HSTORE ( (DATA->'city') );

Execution Time(INDEXあり)

• 数値の単位はms

条件	DBカラム	JSON	JSONB	HSTORE
文字列::完全一致 ZIPCODE='1010021'	8.032	151.275	24.027	26.231
文字列::否定 ZIPCODE<>'1010021'	11.899	146.686	21.083	23.307
文字列::前方一致 CITY LIKE '千代田区%'	0.118	0.469	16.790	0.187
文字列::中間一致 CITY LIKE '%中央%'	10.168	98.757	18.963	17.807
文字列::後方一致 CITY LIKE '%港区'	9.796	1.002	16.780	0.544
数値::一致 IDX=10	0.030	0.028	0.054	0.090
数値::否定 IDX<>10	11.972	214.642	44.073	51.779
数値::範囲一致 IDX<10	0.015	0.022	48.033	0.033

INDEXの有無による検索速度の結果について

DBカラム/JSON/HSTORE型

• INDEXによって大きく検索速度の向上が期待できる

JSONB型

• JSONを解析してバイナリ化して格納するため、INDEXによる改善はするが他の列型よりも期待はずれ
• そもそも速度が早いため、INDEXは不要と判断する

データの挿入と検索について

サンプルのテーブル

• サンプルテーブルは以下の定義

sql {.line-numbers}

CREATE TABLE json_sample(
  no serial not null,
  json_data json,
  jsonb_data jsonb,

  primary key(no)
);

データ挿入

挿入方法1：直接JSONを入れる

sql {.line-numbers}

INSERT INTO json_sample(
  json_data,
  jsonb_data
)VALUES(
  '{"name" : "田中29", "age" : 29}',
  '{"name" : "田中30", "age" : 30}'
);

挿入方法２:PostgreSQLの関数を利用する

sql {.line-numbers}

INSERT INTO json_sample(
  json_data,
  jsonb_data
)VALUES(
  json_build_object('name', '中田17', 'age', '17'),
  json_build_object('name', '中田18', 'age', '18')
);

データ検索

検索

• 特定カラムを指定する場合は、項目名の指定方法に面倒さがある

sql {.line-numbers}

SELECT jsonb_data->>'name' age
 FROM json_sample WHERE jsonb_data->>'name' = '中田18';

条件に複数の項目を入れる場合

sql {.line-numbers}

SELECT jsonb_data->>'name' age
 FROM json_sample WHERE jsonb_data@>'{"name": "中田18", "age": "18"}';

JSONカラムをテーブル扱いする関数

• 理解がしやすいため、これが一番良いかも？

sql {.line-numbers}

SELECT profiles.name, profiles.age
FROM json_sample,
 jsonb_to_record(jsonb_data) as profiles(name text, age text);

• 以下で比較してみた

EXPLAIN ANALYZE
SELECT profiles.name, profiles.age
FROM json_sample,
 jsonb_to_record(jsonb_data) as profiles(name text, age text);

EXPLAIN ANALYZE
SELECT jsonb_data->>'name', jsonb_data->>'age'
FROM json_sample;

jsonb_to_record使用	4.030
不使用	0.022

となり、やはり変換をかますため、速度の低下が200倍も起こってしまう

結論

• 列数が定まっていないカラムを使いたい場合は問題ないと判断した