概要
PostgreSQLでjsonb型カラムを使用した際、SQLのSELECT句でそのカラムをどのように取得するかで処理速度がだいぶ変わった。類似の事例をwebで記事を検索しても中々ヒットしなかったため、備忘録として残しておく。
経緯
以前、DBにPostgreSQLを使用しているあるプロジェクトに参画したことがあった。そのプロジェクトでは一部テーブルのカラムはjsonb型として定義されていた。
※PostgreSQLではカラムの型としてjson型、又はjsonb型という定義が用意されており、json文字列を用いて1つのカラムの中にデータ構造を持たせたうえで複数のデータを保持することができる。
上記テーブルからデータを取得する際は以下のようなSQLを用いていた。
-- このようなテーブルがあったとして
CREATE TABLE test
(
id int PRIMARY KEY,
col_json jsonb
)
-- col_jsonカラムにはこんなjsonデータが入っていたとして
{
"key1": "value1",
"key2": "value2",
"key3": "value3",
...
}
-- こんな感じに取得データを指定していた
SELECT
col_json->>'key1',
col_json->>'key2',
col_json->>'key3',
...
FROM test
当該プロジェクトでは上記のSQLのSELECT句にjson内部の200項目程を指定しており、そのSQLは数百万あるレコードから1分程で結果を返していたのだが、ある改修案件で取得する項目数を1000項目程まで増やしたところ、 5分以上経っても結果を返さなくなった。
当該処理は割と処理時間を考慮しなければならない処理だったため、速度改善が必要であった。
当時の自分は「単に取得カラムを増やすだけでこんなに取得速度に影響が出るものなのか？」と疑問に思ったため、まずは検証することにした。
検証してみた
検証条件
- OS：Windows 10のDocker上で動いているUbuntu20.04。
- PostgreSQL：Windows 10のDocker上で動いているもの。バージョン17.4。
- Python3.8で上記DBに接続し、SQLを投げて結果が返るまでの時間を計測する。SQLは5回投げてその平均をとるものとする。
- テーブルには1万行のレコードが格納されているものとし、その全レコードが取得対象であるものとする。
検証に使用したテーブル
create table test
(
id int PRIMARY KEY,
col1 varchar(50),
col2 varchar(50),
col3 varchar(50),
（中略）
col499 varchar(50),
col500 varchar(50),
col_json jsonb
);
実行SQL
1. jsonb型カラムの内部の項目を100, 300, 500項目取得するもの
SELECT
col_json->>'key1',
col_json->>'key2',
...
FROM test
2. （比較対象として）jsonb型以外のカラムを100, 300, 500個取得するもの
SELECT
col1,
col2,
...
FROM test
検証結果（単位はミリ秒）
| 平均 | |
|---|---|
| 1. jsonb型カラム 100項目 | 9051.8 |
| 1. jsonb型カラム 300項目 | 12696.1 |
| 1. jsonb型カラム 500項目 | 22160.1 |
| 2. jsonb型カラム以外 100個 | 94.7 |
| 2. jsonb型カラム以外 300個 | 253.4 |
| 2. jsonb型カラム以外 500個 | 403.3 |
上記結果から以下のことが判明した。
- 基本的に、SELECT句に指定する数が多くなるほど処理時間は長くなる。
-
- のjsonb型カラムの内部項目を取得する時間は、2. のjsonb型以外のカラムを指定した場合よりも明らかに長い。
「SELECT句に指定する数が多いほど処理時間は長くなる」というのは感覚的には理解できる。が、jsonb型カラムから取得する時間が、jsonb型カラム以外から取得するよりも長くなるというのは何故だろうか？
この疑問を解消するため色々調査したところ、PostgreSQLには TOAST という仕組みが存在することを知った。
https://www.postgresql.jp/document/16/html/storage-toast.html
要はjsonb型やtext型など、1レコード1カラムにおけるバイトサイズがある程度大きくなるような型については、DB内で 圧縮して 保持する仕組みである。
で、どうもこの圧縮が曲者のようで、SELECT句にjsonb型カラム内の項目を指定した場合、実は指定した数だけ毎回解凍処理が走るようなのである。
実際以下のように、jsonb型カラム内の項目をSELECT句で1個1個指定するのではなく1カラムとしてまとめて取得するようにしたり、jsonb型カラムの解凍後データをメモリ上に保持しておくようなSQLを実行した場合、処理速度が劇的に改善した。
3. jsonb_path_query関数を用いて100, 300, 500項目を1カラムとして取得するもの
SELECT
-- 100項目の場合
jsonb_path_query(col_json, '$.keyvalue() ? (@.key like_regex "^key[1-9]$|^key[1-9][0-9]$|^key100$") .value')
-- 300項目の場合
jsonb_path_query(col_json, '$.keyvalue() ? (@.key like_regex "^key[1-9]$|^key[1-9][0-9]$|^key[1-3][0-9][0-9]$") .value')
-- 500項目の場合
jsonb_path_query(col_json, '$.keyvalue() ? (@.key like_regex "^key[1-9]$|^key[1-9][0-9]$|^key[1-5][0-9][0-9]$") .value')
FROM test
4. 解凍後データを一時的にメモリ上に保持して100, 300, 500項目取得するもの
SELECT
t.tmp_json->>'key1',
t.tmp_json->>'key2',
（略）
FROM (
SELECT
--解凍後のデータを一時的にメモリ上に保持するためのダミー演算
col_json #- '{aaa}' as tmp_json
FROM test
) as t
上記SQLの結果（単位はミリ秒）
| 平均 | |
|---|---|
| 3. jsonb_path_query関数使用 100項目 | 1.5 |
| 3. jsonb_path_query関数使用 300項目 | 1.3 |
| 3. jsonb_path_query関数使用 500項目 | 1.4 |
| 4. 解凍後データをメモリ保持 100個 | 1.4 |
| 4. 解凍後データをメモリ保持 300個 | 1.5 |
| 4. 解凍後データをメモリ保持 500個 | 1.4 |
解凍処理が1回しか走っていないため、取得項目を増やしても処理速度はほぼ一定となっているのがわかる。
その他の解決策
前述のとおり、SELECT句の個数に比例した処理速度の鈍化はPostgreSQLのデータ圧縮処理によって生じたものである。
であれば、そもそも圧縮を行わなければSELECT時の速度鈍化は発生しないはずであり、PostgreSQLにはその為のオプションも用意されている。
以下のSQLで指定テーブル・カラムのデータ圧縮を行わない形式に変更できる。
ALTER TABLE {テーブル名} ALTER COLUMN {カラム名} SET STORAGE EXTERNAL;
上記SQLを検証に用いたjsonb型カラムに適用したのち、データを登録しなおした上で処理速度を検証すると以下のようになった。
検証結果（単位はミリ秒）
| 平均 | |
|---|---|
| 1. jsonb型カラム 100項目 | 6633.8 |
| 1. jsonb型カラム 300項目 | 6812.0 |
| 1. jsonb型カラム 500項目 | 11677.4 |
前述した検証結果と比べると処理速度がおよそ半分になっていることがわかる。（それでもjsonb型カラム以外から取得するものと比べるとだいぶ遅いが）
また、「データの圧縮を行わない」ということは当然それだけディスク容量を圧迫することになるため、jsonb型カラムからの取得で性能が問題になった場合は、基本的にはSQLを見直すべきだろう。
結論
PostgreSQLのjsonb型カラムを用いていて、かつ処理速度を考慮する必要がある場合は、SELECT句におけるjsonb型カラムの記述方法を工夫しようというお話でした。
検証に使用したソースコードはこちら