PostgreSQLのjsonb型のlike検索でインデックスを効かせたい

やりたいこと

PostgreSQLのjsonb型の各キーにある値をlike検索でインデックス効かせたい

調査

現状、上記を「設定1発でOK」といったやり方はなさそう…
ですのでやや強引に以下で実施

方法

jsonb型カラムと同期できるテキスト型カラムを追加し、テキスト型カラムにインデックス設定して検索

注）キー名と検索したい値が被ってると、よけいなレコードも取得しまうので
多少のノイズが検索結果にあっても問題なければ利用できます

例

テーブル作成

CREATE TABLE test (
  name jsonb NULL
);
INSERT INTO test (name) VALUES ('{"key1":"あいうえお"}'), ('{"key2":"かきくけこ"}'),('{"key3":"さしすせそ"}');

中身

SELECT * FROM test;
           name
--------------------------
 {"key1": "あいうえお"}
 {"key2": "かきくけこ"}
 {"key3": "さしすせそ"}

当たり前ですが、これだけではインデックス効かせてlike検索できない

EXPLAIN SELECT * FROM test WHERE name::text like '%きく%';
                      QUERY PLAN
------------------------------------------------------
 Seq Scan on test  (cost=0.00..33.80 rows=1 width=32)
   Filter: ((name)::text ~~ '%きく%'::text)

jsonb型カラムのnameをテキスト型にして更新を追従できるname_textカラム追加

ALTER TABLE test ADD COLUMN name_text TEXT GENERATED ALWAYS AS (name::text) STORED;

中身

SELECT * FROM test;
            name             |          name_text
-----------------------------+-----------------------------
 {"key1": "あいうえお"}       | {"key1": "あいうえお"}
 {"key2": "かきくけこ"}       | {"key2": "かきくけこ"}
 {"key3": "さしすせそ"}       | {"key3": "さしすせそ"}

name_textカラムに全文検索のインデックス設定

CREATE INDEX name_text_idx ON test USING gin (name_text gin_bigm_ops);

インデックス確認

\di
Schema |                  Name                   | Type  |   Owner    |            Table
public | name_text_idx                           | index | abcdetg123 | test

インデックス効かせたlike検索できるようになる

EXPLAIN SELECT * FROM test WHERE name_text like '%きく%';

※レコード数がすくないとスキャンになってしまいますが、数万件などに増やすとおそらく
　インデックスを効かせた検索になると思います！