PostgreSQLのjsonb_array_elementsの動作について

概要
現象
現象の原因の推測

概要

PostgreSQLの大容量データのJson列を参照すると激遅になったよという話。
この話をややこしくしているのはjsonb_array_elementsの挙動一点ですね。

調べていくとjsonb_array_elementsがいなけりゃとてもシンプルな話だなと納得できました。

現象

あるテーブルのカラム（json_data）に大量のJsonデータが格納されている場合に
単純にSELECT文の項目としてjson_dataを参照だけもそれなりにコストがかかります。

※例えばjson_dataには["aaaaa....aaaaという10KBの文字列"]が1000個の計10MB程度がJsonデータが格納

以下のSQLを参照してください。

-- json_dataは["aaaaa....aaaaという1Mbの文字列"]が10個の計10MB程度がJsonデータ
SELECT
    id
    ,json_data -- このカラムが10MB程度情報が入っているので遅い
FROM
    test

そして、ｍこの状況を踏まえて
jsonb_array_elementsでJson配列をSELECTの項目として展開した場合、
激遅になる。というのが今回の話題です。

現象の原因の推測

まぁ考えてみれば当然の話しなのですが、
これは恐らく、jsonb_array_elementsでJson配列からレコードに展開した分だけ、
json_dataの参照を行うからだと思います。

-- json_dataは["aaaaa....aaaaという1Mbの文字列"]が10個の計10MB程度がJsonデータ
SELECT
    id
    ,json_data  -- このカラムが10MB程度情報が入っているので遅い。けど1000件展開されたレコード分参照
    ,jsonb_array_elements(json_data) AS title -- 1000件展開
FROM
    test

jsonb_array_elements(json_data)で展開せずともjson_dataに大容量のデータを保持する
testテーブルのレコード総数が増えれば同様の事象が起こると思われます。

公式ドキュメントとかjsonb_array_elementsの仕様など特になかったですが、
Stack Overflowの「Why I can't aggregate setof jsonb in Postgres?」という記事の以下の一文がとてもしっくり来ました。

原文

A table is a set, so a set returning function is similar to a table. Would you expect to be able to use all rows of a table in the SELECT list? Putting such a function into the select list would be similar to select t1.x1, select t2.x2 from other_table t2 from some_table t1

日本語訳

テーブルはセットであるため、セットを返す関数はテーブルに似ています。
SELECTリストのテーブルのすべての行を使用できると思いますか？選択リストの中にこのような機能を置くことのようになります。
　select t1.x1, select t2.x2 from other_table t2 from some_table t1

そらそうだなぁ。と思った一日でした。

フォローお願いします！

@marine_menta