RDSで全文検索プラグインpg_bigmを試してみる手順のご紹介

postgresqlで、多くのデータの中から候補となる選択肢を入力都度返すなどする場合、そのレスポンス速度が重要になってきます。１０万件程度であれば、インデックスがなくとも都度全ての全部のデータをチェックして返す方法でも１秒以内に処理は完結すると思いますが、１０００万件くらいになると、処理時間が１０秒弱かかるようになり、ユーザビリティに影響を与えます。そのような場合、インデックスを作成することで、これまでインデックスが使えなかった日本語のlike検索も高速に結果を返すことができることが確認できたので、その手順と結果をここに紹介します。


## 作業環境

データベースは検証にはRDSのPostgres最新バージョンを利用しました（13.1） 
クライアントからDBへの接続する際の環境（ツール）は、DBeaverを利用する前提としますが、特にこのツールでなくてはならないわけではありません。

## シングルカラムのインデックス作成＆検索

### プラグインの有効化

まず、dbeaverでデータベースに接続し、Extentions > Create new Extension を選択します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/77722/e0ca6e97-09be-c606-fe4c-822b15e78f73.png)



でてきた一覧からpg\_bigmを選択します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/77722/fec69c83-cb3f-dd9d-dba2-f8b149f29240.png)

成功したら、Extensionsの下にpg\_bigmが出てきます。この状態になれば、対象のデータベース内でpg\_bigmを使ったインデックスが作成できます。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/77722/c5cf33f9-20a7-d0e7-543f-3eff715a94fb.png)


プラグインの追加は以下のSQLでもOKです。（こちらの方が簡単？？）

```
-- Excentionを有効化
CREATE EXTENSION pg_bigm;
-- 確認
SELECT * FROM pg_extension;
```

### テーブルの作成

```
CREATE TABLE sample_pg_tools (tool text, description text);
```

### 検証用データ（５０００万件）のデータを作成

```
CREATE TABLE sample_data2 AS
SELECT num id, to_char(num,'FMAAA0000000000') textdata1,to_char(num2,'FMBBB00000000000') textdata2
FROM   generate_series(1,10000000) num, generate_series(1,5) num2
;
INSERT INTO sample_data2 VALUES (9000087, 'けいりのわな','東京のグループ');
INSERT INTO sample_data2 VALUES (9000087, 'ああああ','英単語を覚える必要がある');
INSERT INTO sample_data2 VALUES (9000086, 'こののりはいくつかの単純な単語が単語があるためpg_trgmでは検索にヒットしないワードがでてしまいます','東京のグループ');
INSERT INTO sample_data2 VALUES (9000087, '日本語は数多くの二文字の単語があるためpg_trgmでは検索にヒットしないワードがでてしまいます','東京のグループ');
INSERT INTO sample_data2 VALUES (9000088, '文中のどこかでりんごが含まれるデー','PostgreSQLで2-gramの全文検索を使えるようにするツール');
INSERT INTO sample_data2 VALUES (9000089, 'PostgreSQLでHINT句を使えるようにするツール','PostgreSQLでHINT句を使えるようにするツール');
INSERT INTO sample_data2 VALUES (9000090, 'PostgreDatabase ','aaax');

INSERT INTO sample_data2 VALUES (9000091, 'PostgreDaDatase ','aaaaaa');
```

### データを検索（インデックスなし）

```
select * from sample_data2 where textdata1 like '%りんご%';
```

上記のSQLでも、２回目の実行時は２０秒程度で実行が完了しました。

```
select * from sample_data2 where textdata2 like '%HINT%';
```

上記のSQLでは２４秒かかりました。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/77722/f1e5d63f-f369-ea5a-196f-bce051ceb9da.png)


### インデックス追加

以下のSQLを実行し、textdata1カラムに対してbigmのインデックスを作成します。

```
CREATE INDEX sample_data2_idx1 ON sample_data2 USING gin (textdata1 gin_bigm_ops);
```

インデックスが作成を確認するために、以下のSQLを実行します。

```
SELECT indexname, indexdef
  FROM pg_indexes
 WHERE tablename = 'sample_data2';
```

インデックスが作成されたかどうかは画面からでも確認できます。

![](https://toyotsu-digital123.atlassian.net/wiki/download/attachments/886734849/image-20211218-154312.png?api=v2)

### データを検索（インデックスありカラム）

先ほどと同じSQLでtextdata1を検索してみる。

```
select * from sample_data2 where textdata1 like '%りんご%';
```

50ms くらい。インデックスが効いてる。

### データを検索（インデックスなしカラム）

```
select * from sample_data2 where textdata2 like '%HINT%';
```

先ほどど同じ、22秒くらい

## 複数カラムのインデックス作成と効果確認

### 複数カラム検索（インデックスなし）

textdata1とtextdata2をORで検索

```
select * from sample_data2 where textdata1 like '%りんご%' or textdata2 like '%HINT%';
```

![](https://toyotsu-digital123.atlassian.net/wiki/download/attachments/886734849/image-20211218-162206.png?api=v2)

22秒くらい。インデックス効いていない

### 複数カラムのインデックス作成

両方統合したインデックスを作成

```
-- 複数カラムインデックス作成
CREATE INDEX sample_data2_idx_multi ON sample_data2 USING gin (textdata1 gin_bigm_ops,textdata2 gin_bigm_ops);
```

作成に４分かかった

### 複数カラム検索

textdata1とtextdata2をORで検索

```
select * from sample_data2 where textdata1 like '%りんご%' or textdata2 like '%HINT%';
```

今度は80msで結果が返ってくるようになった。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/77722/7281ab6e-c04d-b058-25b0-cc4da6911520.png)


一文字含むデータを検索した場合でも早い

```
select * from sample_data2 where textdata1 like '%り%' or textdata2 like '%単語%';
```

インデックス使われていると実行計画こんな感じになる

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/77722/0ac60072-b5c2-f1d7-362f-2ade3082a552.png)


### 類似検索機能（多分英語のみ）

こうやって検索すると類似のものを抽出してくれる。でも日本語はうまく動かなさそう

```
select * from sample_data2 where textdata1 =% 'Boss cool' or textdata2 =% 'siulary';
```

## 結論

pg\_bigram使うと、大きなテーブルであっても高速にlike検索ができるため素晴らしいと思います。

ただし、インデックスを作ることにより、データサイズが倍程度になります。五十万件程度のデータであれば、インデックス使わなくても早いので、データが百万件以上になるようなテーブルに絞って適用するのが良さそうです。