Posted at

PostgreSqlでJSONのテキスト要素にindex全文検索

More than 1 year has passed since last update.

PostgreSQLのJSON要素に全文検索(中間一致検索、LIKE "%word%")する方法です。

今回の目標は以下のSQLにインデックスを使わせることです。valはJSONカラムです。

select * from test where val->>'name' like '%vcr%';

中間検索にインデックスを使うにはpg_trgmモジュールが必要です。

最初にcontribモジュールをインストールします。

そのためには前々回のインストール方法で使った

PostgreSQLのソースディレクトリが残ってる必要があります。

そのディレクトリに移動し以下を実行します。

make -C contrib

make -C contrib install

これで同ディレクトリにcontribディレクトリができたので、そこに移動し、さらにそこにあるpg_trgmディレクトリに移動します。そこでmake installすれば入ります。

cd contrib/

cd pg_trgm/
make install

つぎにインデックスを仕込みます。JSONカラムの作成は前回を見てください。

今回は以下のようなJSONのname項目を対象とします

 {"j": {"k": {"val": 77}, "val": 83}, "val": 0, "name": "lnrqzxvcrh"}

こっからpsqlで操作です。

まずpg_trgmモジュールを有効にします。

create extension pg_trgm; 

これで中間検索用のインデックスが使用可能になりました。さっそく作成します。なお対象のカラム名はvalです。valのname項目をテキストでアクセスするのでval->>'name'になります。またtrgmを有効にするためgist_trgm_opsをつけます。

create index namegist on test USING gist((val->>'name') gist_trgm_ops); 

ではindexが使われるか見てみましょう。ちなみに10万レコードです。

test=# explain analyze select * from test where val->>'name' like '%vcr%';

QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on test (cost=1878.29..19720.28 rows=40000 width=110) (actual time=69.950..70.776 rows=479 loops=1)
Recheck Cond: ((val ->> 'name'::text) ~~ '%vcr%'::text)
Heap Blocks: exact=476
-> Bitmap Index Scan on namegist (cost=0.00..1868.29 rows=40000 width=0) (actual time=69.858..69.858 rows=479 loops=1)
Index Cond: ((val ->> 'name'::text) ~~ '%vcr%'::text)
Planning time: 0.132 ms
Execution time: 70.871 ms
(7 rows)

test=#

使われてますね。やりました!

ちなみにない場合はSeqスキャンです。

では最後に検索結果をみてお別れしましょう。

test=# select * from test where val->>'name' like '%vcr%';

val
-----------------------------------------------------------------------
{"j": {"k": {"val": 0}, "val": 0}, "val": 0, "name": "fcqdvcrlxl"}
{"j": {"k": {"val": 67}, "val": 1}, "val": 0, "name": "ovcrldhvde"}
{"j": {"k": {"val": 36}, "val": 38}, "val": 0, "name": "kvcrklisnc"}
{"j": {"k": {"val": 32}, "val": 65}, "val": 0, "name": "apfhfhvcrz"}
{"j": {"k": {"val": 14}, "val": 71}, "val": 0, "name": "nyiwymfvcr"}
{"j": {"k": {"val": 81}, "val": 74}, "val": 0, "name": "vcryasriqw"}
{"j": {"k": {"val": 87}, "val": 75}, "val": 0, "name": "tsqyvcrwlr"}
{"j": {"k": {"val": 77}, "val": 83}, "val": 0, "name": "lnrqzxvcrh"}
{"j": {"k": {"val": 49}, "val": 96}, "val": 0, "name": "ovcrouzuwk"}
{"j": {"k": {"val": 93}, "val": 35}, "val": 1, "name": "xovszjvvcr"}
{"j": {"k": {"val": 34}, "val": 50}, "val": 1, "name": "gtwvcrvqyo"}
{"j": {"k": {"val": 2}, "val": 68}, "val": 1, "name": "csovcrxeui"}
{"j": {"k": {"val": 15}, "val": 20}, "val": 2, "name": "rvcrkxvvda"}
{"j": {"k": {"val": 67}, "val": 22}, "val": 2, "name": "yiedvcrrcv"}
{"j": {"k": {"val": 31}, "val": 26}, "val": 2, "name": "kdvcraoxpy"}