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

## はじめに

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

## 環境構築

### 前提

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

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

- 以下を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 |
| ----------------------------------------- | -------- | ------- | ------ | ------ |
| 文字列::完全一致<BR>ZIPCODE='1010021'     | 6.817    | 130.844 | 16.197 | 19.718 |
| 文字列::否定<BR>ZIPCODE<>'1010021'        | 11.218   | 140.090 | 19.780 | 21.135 |
| 文字列::前方一致<BR>CITY LIKE '千代田区%' | 8.903    | 144.195 | 15.783 | 16.218 |
| 文字列::中間一致<BR>CITY LIKE '%中央%'    | 9.616    | 123.995 | 18.519 | 17.710 |
| 文字列::後方一致<BR>CITY LIKE '%港区'     | 10.312   | 90.486  | 16.944 | 14.903 |
| 数値::一致<BR>IDX=10                      | 4.974    | 177.400 | 39.760 | 36.331 |
| 数値::否定<BR>IDX<>10                     | 9.150    | 190.363 | 60.093 | 40.193 |
| 数値::範囲一致<BR>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 |
| ----------------------------------------- | -------- | ------- | ------ | ------ |
| 文字列::完全一致<BR>ZIPCODE='1010021'     | 8.032    | 151.275 | 24.027 | 26.231 |
| 文字列::否定<BR>ZIPCODE<>'1010021'        | 11.899   | 146.686 | 21.083 | 23.307 |
| 文字列::前方一致<BR>CITY LIKE '千代田区%' | 0.118    | 0.469   | 16.790 | 0.187  |
| 文字列::中間一致<BR>CITY LIKE '%中央%'    | 10.168   | 98.757  | 18.963 | 17.807 |
| 文字列::後方一致<BR>CITY LIKE '%港区'     | 9.796    | 1.002   | 16.780 | 0.544  |
| 数値::一致<BR>IDX=10                      | 0.030    | 0.028   | 0.054  | 0.090  |
| 数値::否定<BR>IDX<>10                     | 11.972   | 214.642 | 44.073 | 51.779 |
| 数値::範囲一致<BR>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);
```

- 以下で比較してみた

```sql
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倍も起こってしまう

## 結論

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