PostgreSQLで全文検索をしてみよう

# １．はじめに
だいぶ前に書いたものが下書きに残っていたので投稿。
以前、全文検索について調べていた時に調査したものを備忘録として残します。

本稿はPostgreSQLの全文検索のやり方をまとめています。

# ２．前準備
### 2.1. 環境について
　OS：CentOS7.9
　DB：PostgreSQL9.6（develも必要。バージョンも合わせること）
　　※gccやcmakeが必要になりますので事前に入れてください。
　　　その他、エラーが出たら必要に応じてインストールしてください。

### 2.2. pgroongaに必要なモジュールのインストール
pgroongaをインストールする際に必要になります。gitからダウンロードしましょう。

```:msgpack-cのインストール
# git clone -b c_master https://github.com/msgpack/msgpack-c.git
# cd msgpack-c/
# cmake .
# make
# make install
```
```:groongaのインストール
# yum install -y https://packages.groonga.org/centos/7/groonga-release-latest.noarch.rpm
# yum install -y --enablerepo=epel groonga
# yum install -y groonga-devel
```

# ３．PostgreSQLの全文検索モジュールのインストール

### 3.1. pgroongaのインストール
```:pgroongaのインストール
# wget https://packages.groonga.org/source/pgroonga/pgroonga-2.2.8.tar.gz
# tar xvf pgroonga-2.2.8.tar.gz
# cd pgroonga-2.2.8

# echo 'export PKG_CONFIG_PATH=/usr/local/lib/pkgconfig/:/usr/lib64/pkgconfig/' >> ~/.bashrc

# make HAVE_MSGPACK=1
# make
# make install
```
エラーになる場合は以下のことを確認してください。

①pg_configコマンドが有効であること。
　有効である場合は以下のような情報が表示されます。

```:正しい場合はこんな感じ
[root@2e9dcb1b3a5d pgroonga-2.2.8]# pg_config
BINDIR = /usr/pgsql-9.6/bin
DOCDIR = /usr/pgsql-9.6/doc
HTMLDIR = /usr/pgsql-9.6/doc/html
INCLUDEDIR = /usr/pgsql-9.6/include
PKGINCLUDEDIR = /usr/pgsql-9.6/include
INCLUDEDIR-SERVER = /usr/pgsql-9.6/include/server
LIBDIR = /usr/pgsql-9.6/lib
PKGLIBDIR = /usr/pgsql-9.6/lib
LOCALEDIR = /usr/pgsql-9.6/share/locale
MANDIR = /usr/pgsql-9.6/share/man
SHAREDIR = /usr/pgsql-9.6/share
SYSCONFDIR = /etc/sysconfig/pgsql
PGXS = /usr/pgsql-9.6/lib/pgxs/src/makefiles/pgxs.mk
CONFIGURE = '--enable-rpath' '--prefix=/usr/pgsql-9.6' '--includedir=/usr/pgsql-9.6/include' '--mandir=/usr/pgsql-9.6/share/man' '--datadir=/usr/pgsql-9.6/share' '--libdir=/usr/pgsql-9.6/lib' '--with-perl' '--with-python' '--with-tcl' '--with-tclconfig=/usr/lib64' '--with-openssl' '--with-pam' '--with-gssapi' '--with-includes=/usr/include' '--with-libraries=/usr/lib64' '--enable-nls' '--enable-dtrace' '--with-uuid=e2fs' '--with-libxml' '--with-libxslt' '--with-ldap' '--with-selinux' '--with-systemd' '--with-system-tzdata=/usr/share/zoneinfo' '--sysconfdir=/etc/sysconfig/pgsql' '--docdir=/usr/pgsql-9.6/doc' '--htmldir=/usr/pgsql-9.6/doc/html' 'CFLAGS=-O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector-strong --param=ssp-buffer-size=4 -grecord-gcc-switches -m64 -mtune=generic' 'LDFLAGS=-Wl,--as-needed'
CC = gcc
CPPFLAGS = -D_GNU_SOURCE -I/usr/include/libxml2 -I/usr/include
CFLAGS = -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector-strong --param=ssp-buffer-size=4 -grecord-gcc-switches -m64 -mtune=generic
CFLAGS_SL = -fPIC
LDFLAGS = -Wl,--as-needed -L/usr/lib64 -Wl,--as-needed -Wl,-rpath,'/usr/pgsql-9.6/lib',--enable-new-dtags
LDFLAGS_EX =
LDFLAGS_SL =
LIBS = -lpgcommon -lpgport -lselinux -lxslt -lxml2 -lpam -lssl -lcrypto -lgssapi_krb5 -lz -lreadline -lrt -lcrypt -ldl -lm
VERSION = PostgreSQL 9.6.20　　　　#ここ結構重要。サーバーのバージョンと異なる（9.2等）場合はdevelのバージョンを合わせてください。
```

②pkg-config --list-allにgroongaが含まれていること。
　存在しない場合、項番3.1.「pgroongaのインストール」の手順を見直してみてください。

### 3.2. DB作成
全文検索用のDBを作成します。
既存のDBを全文検索化したい場合は飛ばしていただいて結構です。

```SQL:DB作成
create database dev2
    template template0 
    encoding 'utf-8'
    lc_collate 'ja_JP.utf-8'
    lc_ctype 'ja_JP.utf-8';
```
※DB名やオーナー等は自由に変えていただいて問題ありません。ご自身の環境に合わせてください。

### 3.3. pgroongaの有効化
全文検索したいデータベースへログインして以下のSQLを発行します。


```SQL:pgroonga有効化
CREATE EXTENSION pgroonga;
```

以上でDB側の全文検索の準備完了は完了です。


# ４．テーブル準備

今回は[住所.jp](http://jusyo.jp/index.html)さんにいいデータがそろっていましたので、こちらを使っていきます。

### 4.1. テーブル作成

住所データを格納するためのテーブルを作成するため、以下のSQLを発行します。
カラム名はそのまま直訳しました。
address_cd(住所CD)がプライマリーキーなので設定します。これは今回使わないのでなくてもいいです。
カラムの属性はすべてtextで作成します。全文検索は基本的にtext型が対象になります。

```SQL:住所テーブル作成
create table address (
	 address_cd				text
	,prefecture_cd			text
	,city_cd				text
	,town_cd				text
	,postal_code			text
	,office_flag			text
	,discontinued_flag		text
	,prefecture				text
	,prefecture_kana		text
	,city_town_village		text
	,city_town_village_kana	text
	,town_area				text
	,town_area_kana			text
	,town_area_supplement	text
	,kyoto_street_name		text
	,aza_chome				text
	,aza_chome_kana			text
	,supplement				text
	,office_name			text
	,office_name_kana		text
	,office_address			text
	,new_address_cd			text
	,constraint index_address primary key(
		address_cd
	)
);
```

### 4.2. CSVインポート

住所.jpからダウンロードしたCSV「zenkoku.csv」をインポートします。
中身はsjisだったのでencodingにsjisを指定します。

```SQL:住所データ（zenkoku.csv）インポート
copy address from '/tmp/zenkoku.csv' delimiter ',' csv header encoding 'sjis';
```

### 4.3. インデックス作成

事業者名が扱いやすいので、これに対してインデックスを作成します。

```SQL:全文検索インデックス作成
create index pgroonga_index_address_office_name
on address
using pgroonga (office_name);
```

### 4.4. 検証

インデックスを作成したカラムに対して「会社」という単語で全文検索してみます。
pgroonga_scoreはスコアです。よく全文検索系のアプリにあるTerm Frequencyと呼ばれるものです。何個そのワードが含まれていたかを表します。
「&@」は全文検索用演算子になります。他には「&@~」「LIKE」「ILIKE」があります。詳しく知りたい場合は[こちら](https://pgroonga.github.io/ja/tutorial/)をご覧ください。

```SQL:検証
select
	  pgroonga_score(tableoid, ctid) AS score 
	 ,*
from address
where office_name &@ '会社'
order by 1 desc
```
![sql.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/783185/b2c62f56-4fb4-1afa-8670-864076b35241.png)

以上で全文検索の紹介を終わります。