PostgreSQL 全文検索インデックス pg_bigm を使ってみる | SEの道標
PostgreSQL

PostgreSQL 全文検索インデックス pg_bigm を使ってみる

全文検索インデックスとは

PostgreSQL がデフォルトで使用するインデックスは B-tree ですが、B-tree は文字列での検索や並び替えを行う場合、前方一致しか使えません。

B-tree インデックスが張られたカラムで部分一致や後方一致を実行しようとしてもインデックスは使われずシーケンシャルスキャン (全行探索) になります。

部分一致や後方一致、類似度検索をしたい場合は全文検索インデックスを使う必要がありますが、

  • PostgreSQL で利用可能
  • 日本語に特化

という条件では pg_bigm や PGroonga を利用することができます。

簡単な比較表を以下に示します。

メリットデメリット
pg_bigm (2-gram)Index サイズが小さい

日本語 2 文字の検索だと有利

精度が劣る
PGroonga (N-gram)精度が優るIndex サイズが大きい

pg_bigm のインストール

pg_bigm (読み方: ぴーじーばいぐらむ) は全文検索インデックスである GIN インデックスを使うための拡張ツールです。

今回は pg_bigm を RedHat 系の RockyLinux 9 に、rpm パッケージを使ってインストールします。

まずは pg_bigm をインストールしますが、前提条件として、RedHat 標準リポジトリの PostgreSQL ではうまくインストールできず、PGDG 版を使うとすんなりインストールができました。

PostgreSQL (PGDG 版) と pg_bigm のインストール

# dnf install https://download.postgresql.org/pub/repos/yum/reporpms/EL-9-x86_64/pgdg-redhat-repo-latest.noarch.rpm
# dnf install postgresql15-server
# dnf install https://github.com/pgbigm/pg_bigm/releases/download/v1.2-20200228/pg_bigm-1.2.20200228-1.pg15.el9.x86_64.rpm

DB の初期セットアップ

initdb により初期セットアップします。

# PGSETUP_INITDB_OPTIONS="--encoding=UTF-8 --no-locale" /usr/pgsql-15/bin/postgresql-15-setup initdb

今回は検証用のため認証はガバガバにします。

# cat << EOF > /var/lib/pgsql/15/data/pg_hba.conf 
local   all           all                   trust
host    all           all   127.0.0.1/32    trust
local   replication   all                   trust
EOF

設定追加

/var/lib/pgsql/15/data/postgresql.conf に pg_bigm の設定を追加します。

shared_preload_libraries = 'pg_bigm'

起動

PostgreSQL を起動します。

# pg_ctl start

EXTENSION 追加

psql -U postgres -d mydatabase でログインし、以下コマンドで pg_bigm を有効化します。

mydatabase=# CREATE EXTENSION pg_bigm;

確認は以下コマンドでできます。

mydatabase=# SELECT * FROM pg_extension;
  oid  | extname | extowner | extnamespace | extrelocatable | extversion | extconfig | extcondition
-------+---------+----------+--------------+----------------+------------+-----------+--------------
 13540 | plpgsql |       10 |           11 | f              | 1.0        |           |
 24624 | pg_bigm |       10 |         2200 | t              | 1.2        |           |
(2 行)

カラムにインデックスを追加

以下はテーブル "my_table" の "my_column" に、大文字小文字の区別無しの全文検索インデックスを付与する例です。インデックス名は my_index_name としていますが、任意です。

mydatabase=# CREATE INDEX my_index_name ON my_table USING gin (UPPER(my_column) gin_bigm_ops);

大文字小文字の区別無しにするには UPPER で対象の列を囲みます。

動作確認方法

例えば Django 等の ORM を利用しており生の SQL が分からない場合は以下を参考に、実際に使われる SQL SELECT 文を確認します。

PostgreSQL で SQL 実行履歴を確認する
システム全体で全ての SQL 実行履歴をログに出力する方法 postgresql...
milestone-of-se.nesuke.com
2024.06.21

その SQL 文の前に EXPLAIN ANALYZE を付け、実行します。実行結果で Bitmap Heap Scan が出てくれば成功です。

                                                        QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on my_table  (cost=14.63..136.67 rows=81 width=196) (actual time=0.101..0.103 rows=0 loops=1)
   Recheck Cond: (upper(my_column) ~~ '%TEST%'::text)
   Rows Removed by Index Recheck: 1
   Filter: (-- 省略 --)
   Heap Blocks: exact=1
   ->  Bitmap Index Scan on my_index_name  (cost=0.00..14.61 rows=81 width=0) (actual time=0.085..0.085 rows=1 loops=1)
         Index Cond: (upper(my_table) ~~ '%TEST%'::text)
 Planning Time: 0.887 ms
 Execution Time: 0.197 ms
(9 行)

もし　Seq Scan が出てきた場合は失敗です。シーケンシャルスキャンになってしまっています。設定が違う可能性もありますが、単純にデータが少ないため実行計画で「インデックスを使わないほうが有利」という判断がされた可能性もあります。

コメント

タイトルとURLをコピーしました