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

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

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

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

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

最初にcontribモジュールをインストールします。
そのためには[前々回](http://qiita.com/kazusa-qooq/items/783beba7b9e361241a7f)のインストール方法で使った
PostgreSQLのソースディレクトリが残ってる必要があります。
そのディレクトリに移動し以下を実行します。

```
make -C contrib
make -C contrib install
```

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

```
cd contrib/
cd pg_trgm/
make install
```


つぎにインデックスを仕込みます。JSONカラムの作成は[前回](http://qiita.com/kazusa-qooq/items/fc5215faa344a3a739b2)を見てください。

今回は以下のような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が使われるか見てみましょう。ちなみに１０万レコードです。

```
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"}
略
```