Azure Database for PostgreSQLでpg_trgmを利用した日本語全文検索

AzureでPostgreSQLが利用できるようになりました。

セットアップ

Azureの管理画面からPostgreSQLデータベースを作成します。作成する際に、以下の情報を指定します。

  • サーバー名
  • サブスクリプション
  • リソース グループ
  • サーバー管理者ログイン名
  • パスワード
  • パスワードの確認
  • 場所
  • バージョン
  • 価格レベル

作成したデータベースにアクセスするためには、接続元のIPアドレスを登録する必要があります。Azureの管理画面の設定 > 接続のセキュリティから「自分のIPを追加」を押してから「保存」を押して、IPアドレスを登録します。

データベースへの接続

psqlコマンドでPostgreSQLデータベースに接続します。その際、ユーザー名にはusername@hostnameのフォーマットで指定します。ユーザー名のフォーマットが間違っていると、以下のようなエラーが出力されます。

psql: FATAL:  Invalid Username specified. Please check the Username and retry connection. The Username should be in <username@hostname> format.

psqlコマンドでユーザー名、ホスト名を指定します。接続先のデータベースには、最初はpostgresを指定します。

$ psql -U username@hostname -h hostname.postgres.database.azure.com -d postgres

データベースの作成

接続後、新規にデータベースを作成します。

hostname=> create database newdb;

接続先データベースを切り替えます。

hostname=> \c newdb
psql (9.6.4, server 9.6.2)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-SHA384, bits: 256, compression: off)
You are now connected to database "newdb" as user "username@hostname".

全文検索のサポート状況

pg_bigm

pg_bigmを有効にするコマンドを実行すると、以下のようなエラーが出力されます。2017年8月17日現在ではpg_bigmはサポートされていないようです。

hostname=> create extension pg_bigm;
ERROR: extension "pg_bigm" is not supported by Azure Database for PostgreSQL
DETAIL: Installing the extension "pg_bigm" failed, because it is not on the list of extensions supported by Azure Database for PostgreSQL.
HINT: To see the full list of supported extensions run: SELECT * FROM pg_available_extensions;

pg_trgm

利用可能な機能を確認すると、pg_trgmは利用できるようです。

hostname=> select name, default_version from pg_available_extensions where name like '%pg_trgm%';
  name   | default_version 
---------+-----------------
 pg_trgm | 1.3
(1 row)

テーブルとインデックスの作成

テーブルを作成します。

hostname=> create table records (id serial, body text);

pg_trgmを有効にします。

hostname=> create extension pg_trgm;
CREATE EXTENSION

インデックスを作成します。

hostname=> create index records_body on records using gin (body gin_trgm_ops);
CREATE INDEX

サンプルデータの登録と検索

サンプルデータを登録します。今回はパフォーマンスを評価するわけではないので、1件だけ登録します。

hostname=> insert into records (body) values ('吾輩は猫である。名前はまだ無い。');

SQLで検索します。

hostname=> select * from records where body like '%吾輩は猫%';
 id |               body               
----+----------------------------------
  1 | 吾輩は猫である。名前はまだ無い。
(1 row)

正常に検索できました。インデックスが利用されているか確認します。

hostname=> explain select * from records where body like '%吾輩は猫%';
                                 QUERY PLAN                                 
----------------------------------------------------------------------------
 Bitmap Heap Scan on records  (cost=16.00..20.01 rows=1 width=36)
   Recheck Cond: (body ~~ '%吾輩は猫%'::text)
   ->  Bitmap Index Scan on records_body  (cost=0.00..16.00 rows=1 width=0)
         Index Cond: (body ~~ '%吾輩は猫%'::text)
(4 rows)

検索する文字数が2文字以下だとインデックスが利用されず、シーケンシャルスキャンになります。

hostname=> explain select * from records where body like '%猫%';
                        QUERY PLAN                        
----------------------------------------------------------
 Seq Scan on records  (cost=0.00..25.88 rows=51 width=36)
   Filter: (body ~~ '%猫%'::text)

pg_trgmのメカニズムについては、以下のスライドにまとまっていたので、あとで読んでみたいと思います。