Help us understand the problem. What is going on with this article?

PostgreSQLでの中間一致検索の性能改善

More than 1 year has passed since last update.

この記事はラクス Advent Calendar 2018の24日目です。

今年、仕事で取り組んだ中間一致検索機能の性能改善について書きたいと思います。

前提条件

  • かなり大量レコードを扱う中間一致検索処理の性能を改善したい
  • 工数と納期の都合によりElasticSearchのようにシステム構成が変わるような大掛かりな変更はできない
  • 同じ理由でデータベース本体のバージョンも避けたい(現状、PostgreSQL9.4を使用)
  • yumでインストール出来て、カスタマイズが不要なものが好ましい
  • 担当しているシステムの特性上、形態素解析は不向きなのでN-gramが前提

という制約のなか検討を行いました。

現状の実装

GINインデックス + tsvector/tsquery というPostgreSQLの標準機能を組み合わせてuni-gramなインデックスを実装をしていました。
かなりのレコード数までは高速に検索できるのですが、レコード数の多い環境では性能が厳しくなってきました。

現状の実装

「Let's postgres」の記事「テキスト検索の方法とインデックス」の「単語単位の検索」の項で紹介されているやり方に近いです。(昔、ズバリこの実装方法が紹介されているサイトがあったのですが現在はリンク切れになってました)

  • tsvector/tsqueryへ変換する関数を登録
CREATE OR REPLACE FUNCTION to_unigram(text) RETURNS tsvector as $$
  SELECT TO_TSVECTOR('simple', ARRAY_TO_STRING(REGEXP_SPLIT_TO_ARRAY($1, E'\\s*'), ' '));
$$ IMMUTABLE LANGUAGE SQL;

CREATE OR REPLACE FUNCTION to_unigram_query(text) RETURNS tsquery as $$
  SELECT TO_TSQUERY(ARRAY_TO_STRING(REGEXP_SPLIT_TO_ARRAY($1, E'\\s*'),'&'));
$$ LANGUAGE SQL;
  • 関数の実行結果
postgres=# select to_unigram('メリークリスマス');
                  to_unigram                   
-----------------------------------------------
 'ク':4 'ス':6,8 'マ':7 'メ':1 'リ':2,5 'ー':3

postgres=# select to_unigram_query('メリークリスマス');
                   to_unigram_query                    
-------------------------------------------------------
 'メ' & 'リ' & 'ー' & 'ク' & 'リ' & 'ス' & 'マ' & 'ス'
-- Ginインデックスを生成
CREATE INDEX unigram_index ON zip USING gin (to_unigram(city));

-- 検索SQL
select * from zip
where to_unigram(city) @@ to_unigram_query('新宿')
and city like '%新宿%';  -- "新"、"宿" が含まれているだけでヒットしてしまうので「新宿」で絞り込む必要がある

実装方法の検討

インデックスを何に置き替えるか検討を行いました。

pg_trgm

PostgreSQLの標準(contrib)であることと、検索の高速性は良さそうでしたが、
基本的に日本語対応されていない1のと、検索ワードが2文字以下だとインデックスが使われないので見送りました

PGroonga

高機能で要件を満たせそうだったのですが、WALベースのストリーミングレプリケーションに対応するにはPostgreSQL9.6以上に上げる必要があるため今回は見送りました2
インデックス作成時間は後述するpg_bigmよりも高速なようなのでPostgreSQLをバージョンアップした後なら候補になるかと思います。

pg_bigm

最終的にpg_bigmを採用しました。良かった点は以下の通りです。

  • 普通のLIKEで書けるので初見に優しく学習コストが必要ない
  • 後で他の実装に乗り換えるとしてもインデックスを破棄するだけなのでコストが高くない
  • ストリーミングレプリケーションが使える
  • rpmパッケージが用意されているので、yumでインストールできる
  • 最新への追随性も問題なし(既にPostgreSQL11対応を謳っている)

まとめると以下のとおりです。本文に書いてませんがインデックス項目の容量制限も気にして調べてました。

インデックス作成方法 インデックスの種類 検索演算子 日本語 容量の制限
現状 1-gram GIN @@ 1MB(tsvectorの限界)
pg_trgm 3-gram GIN/Gist LIKE(~~)、ILIKE(~~*)、~~* ×1 約228MB
pg_bigm 2-gram GIN LIKE 約102MB
PGroonga 可変N-gram pgroonga &@&@~LIKEILIKE なし(text型の限界まで)

性能比較

定番の郵便番号CSV(124,231件)を使って性能比較を行いました。
ただ、12万件程度では差が出にくいので同じデータを繰り返し登録して2000万件までデータを増やして検証しました。

DDL

- DDL
create table zip (
  id     serial primary key,
  gov_code  char(5),
  zip_code_old  char(5),
  zip_code  char(7),
  pref_kana  text,
  city_kana  text,
  town_kana  text,
  pref  text,
  city  text,
  town  text,
  flg_n1  boolean,
  flg_kaza  boolean,
  flg_chome  boolean,
  flg_1n  boolean,
  upd_stat  smallint,
  upd_reason  smallint
);

インデックス生成時間

create index idx_unigram on zip using gin (to_unigram(city));
create index idx_bigram  on zip using gin (city gin_bigm_ops);

CREATE INDEXに掛る時間を比較すると pg_bigm が圧勝でした。
検索の性能改善が目的でしたが副次的に更新処理も速くなりそうです(常にindexを作り直す訳じゃないので、そこまで変わらないかもしれませんが)

create_index.png

インデックスのサイズ

逆にインデックスサイズはpg_bigmを使った方がおよそ1.5倍大きくなりました。(データによって傾向が異なると思いますので参考まで)

index_size.png

検索の性能比較

それぞれ、いろいろなキーワードで検索して平均値を出してみましたが。pg_bigmの方が大分速いという結果になりました。

select * from zip where to_unigram(city) @@ to_unigram_query('新宿') and city like '%新宿%';
select * from zip where city like '%新宿%';

シンプルなSQLで検証したためどちらも速いですが、実際のアプリケーションでは業務要件を満たすため複数キーワードでのAND検索や複数のカラムの検索をかける必要があり、両者の差はもっと大きく開きました。
ちなみに、インデックスを使わない検索でも大体3秒程度でしたので、要件によっては容量も喰いますしインデックスなしもアリと思えたのは自分の中では発見でした。

select.png

さいごに

前提条件を満たし、大きな手間をかけずに中間一致検索の性能を大きく改善することができました。
pg_bigmは導入障障壁が低い割に、効果は大きい良いライブラリだと思います。
同じような前提条件で検討している人の参考にでもなれば幸いです。

<参考にさせて頂いたサイト>
https://blog.longkey1.net/2013/05/08/about-japanese-textsearch-on-postgresql9/
http://shanon-tech.blogspot.com/2012/12/postgresql.html
https://blog.honjala.net/entry/2016/11/19/021057
http://d.hatena.ne.jp/nuko_yokohama/20130406/1365255398
https://qiita.com/fujii_masao/items/87f1d94ff4d350a718aa
https://www.clear-code.com/blog/2015/5/25.html
https://pgroonga.github.io/ja/reference/pgroonga-versus-pg-bigm.html
https://www.sraoss.co.jp/technology/postgresql/3rdparty/pg_bigm.php
https://www.slideshare.net/hadoopxnttdata/postgresqlpgbigm-mysqlpostgresql
https://www.slideshare.net/hadoopxnttdata/pgtrgm
https://www.slideshare.net/masahikosawada98/pgbigm-66639588?next_slideshow=1


  1. ヘッダーファイルを書きかえてリビルドするば対応可能とのこと 

  2. PostgreSQL9.5以下でも別のストリームレプリケーションの実装を使えば大丈夫とのこと 

Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
Comments
No comments
Sign up for free and join this conversation.
If you already have a Qiita account
Why do not you register as a user and use Qiita more conveniently?
You need to log in to use this function. Qiita can be used more conveniently after logging in.
You seem to be reading articles frequently this month. Qiita can be used more conveniently after logging in.
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
ユーザーは見つかりませんでした