読者です 読者をやめる 読者になる 読者になる

アラタナエンジニアブログ

aratana Engineer's Blog

【pg_bigm】PostgreSQL で高速全文検索【PGroonga】

こんにちは、開発チームの竹田です。


今年もたけのこのおいしい季節がやってきました。
皆様はどのようなたけのこご飯や、たけのことイカの木の芽和えを賞味しましたか?
ちなみに僕はこちらのたけのこご飯を賞味しました。油揚げがおいしかったです。
Cpicon 簡単!激ウマ!筍ご飯 (炊き込みご飯) by ナウちゃん


たけのこといえばたけのこ掘りですが、初心者がたけのこを見つけるのは難しいものです。
見つけたと思いきや、よく見たら竹の子族だったり、たけのこの里だったり。
広大な山の敷地内において、トリュフ豚ばりに即座に、旬のたけのこを見つけられないものでしょうか。


ということで今回は、広大な山を Wikipedia のデータとみなして、旬のたけのこを見つけたいと思います。
http://dumps.wikimedia.org/jawiki/latest/
まずはこちらから jawiki-latest-abstract.xml という要約のデータを取得してきます。
このデータをごにょごにょして title と abstract を PostgreSQL に突っ込んだものが以下の様な感じになります。
なんか title の頭に軒並み Wikipedia: が付いていたり、
abstract のところがあれなデータもありますが気にしないように自分に言い聞かせます。

title abstract
Wikipedia: 国際天文学連合による惑星の定義 国際天文学連合による惑星の定義(こくさいてんもんがくれんごうによるわくせいのていぎ、IAU definition of planet)は、2006年に国際天文学連合 (IAU) によって定められた。それによると、太陽系内において、惑星は以下を満たす天体である。
Wikipedia: ナザレ派 ナザレ派(なざれは、)は、19世紀初頭のドイツロマン派の画家たちによる、キリスト教美術の誠実性と精神性を取り戻そうとする芸術運動である。ナザレ派という呼び名は、彼らが聖書に忠実な衣服や髪型を好んだことに対する、周囲からの侮蔑的表現に由来する。
Wikipedia: キャプテンハーロック -SPACE PIRATE CAPTAIN HARLOCK- 言語 = 日本語

ちなみに、総レコード数は 963,128 件でした。

How to Find たけのこ

さて、肝心のたけのこの見つけ方ですが、abstract を中間一致で検索することにします。

SELECT * FROM wiki WHERE abstract LIKE '%たけのこ%';
          title          |                                                                                                                                                                                                                                      abstract                                                                                                                                                                                                                                      
-------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Wikipedia: 竹の子族     | 竹の子族(たけのこぞく)とは、野外で独特の派手な衣装でディスコサウンドにあわせて「ステップダンス」を踊るという風俗またその参加者の総称。
 Wikipedia: 竹腰正晴     | 竹腰 正晴(たけのこし まさはる、寛永11年(1634年)- 延宝5119日(1677220日))は、尾張藩の附家老、美濃国今尾藩の第2代当主。
 Wikipedia: 竹腰友正     | 竹腰 友正(たけのこし ともまさ、延宝元年(1673年) - 宝永3419日(1706530日))は、尾張藩の附家老、美濃国今尾藩の第3代当主。官位は従五位下、筑後守。
 Wikipedia: 竹腰正映     | 竹腰 正映(たけのこし まさてる、元禄2年(1689年)- 宝永6118日(1709227日))は、尾張藩の附家老、美濃国今尾藩の第4代当主。初代当主・竹腰正信の三男竹腰正辰の長男。
 Wikipedia: 竹腰正武     | 竹腰 正武(たけのこし まさたけ)は、尾張藩の御附家老、美濃今尾領の第5代領主。石川章長の四男。
 Wikipedia: 竹腰勝起     | 竹腰 勝起(たけのこし かつおき、元文396日(17381018日)- 寛政元年819日(1789107日))は、、尾張藩の附家老、美濃今尾藩の第6代当主。
 Wikipedia: 竹腰睦群     | 竹腰 睦群(たけのこし むつむら宗睦(むねちか/むねよし)より1字を与えられているため、本来は「ちかむら」、「よしむら」が正しい読みと思われる。、明和6年(1769年)- 文化元年1026日(18041127日))は、尾張藩の附家老、美濃今尾藩の第7代当主。
 Wikipedia: 竹腰正定     | 竹腰 正定(たけのこし まささだ(まさやす)、寛政3年(1791年) - 嘉永5129日(1853118日))は、尾張藩の附家老、美濃今尾藩の第8代当主。
 Wikipedia: 竹腰正富     | 竹腰 正富(たけのこし まさとみ、文政元年630日(181881日)- 明治17年(1884年)724日)は、尾張藩の附家老、美濃国今尾藩の第9代当主。
 Wikipedia: 竹腰正旧     | 竹腰 正旧(たけのこし まさもと、旧字体:正舊、嘉永422日(185134日) - 明治43年(1910年)822日)は、尾張藩の附家老、美濃国今尾藩の第10代当主で初代藩主。
 Wikipedia: 竹腰正信     | 竹腰 正信(たけのこし まさのぶ、天正19121日(1591214日)『尾張群書系図部集』 - 正保2430日(1645525日))は、江戸時代初期の武将。山城守。
 Wikipedia: エビイモ     | エビイモ(海老芋)は、サトイモの品種のひとつ。土寄せ等の独特の栽培を施したもので形状は湾曲している。湾曲して表面には横縞がありエビのように見えることが名前の由来とされている。京芋(きょういも)「京芋」の名で呼ばれるサトイモの仲間には、他に「筍芋(たけのこいも)」もある。海老芋よりも大ぶりで直線的な形をしており、主に宮崎県で生産されている。とも呼ばれる。収穫時期は10月上旬~11月中旬(出荷時期は11月上旬~12月中旬)で、京都府を中心に主とし近畿地方で消費されている。
 Wikipedia: たけのこの里 | thumb|250px|たけのこの里
 Wikipedia: 竹腰成方     | 竹腰 成方(たけのこし しげかた、慶長19515日(1614622日)『尾張群書系図部集』 - 貞享5221日(1688322日))は、尾張藩附家老、美濃国今尾領主・竹腰正信の長男。母は大久保忠隣の養女(孫)の春。
 Wikipedia: 今尾陣屋     | 今尾陣屋(いまおじんや)は岐阜県海津市平田町今尾(美濃国安八郡)にあった尾張藩附家老を務めた竹腰(たけのこし)氏の陣屋である。
 Wikipedia: 合馬たけのこ | 合馬たけのこ(おうまたけのこ)とは、日本有数の竹林面積(1,400ha)を誇る福岡県北九州市小倉南区の合馬(おうま)地区で採れる高級筍。地元を管轄するJA北九の地域団体商標でもある。
 Wikipedia: 竹腰正厚     | 竹腰 正厚(たけのこし まさあつ、? - 文久2824日(1862917日))は、美濃国今尾藩の世嗣。
 Wikipedia: 竹腰正辰     | 竹腰 正辰(たけのこし まさたつ)は、美濃国今尾藩の初代当主(幕藩体制下では藩主として認められていない)竹腰正信の三男。正室は樋口信康の娘の立。
(18 rows)

なにやらヒットしましたが、求めているたけのこではありません。

SELECT * FROM wiki WHERE abstract LIKE '%タケノコ%';
            title            |                                                                                   abstract                                                                                   
-----------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Wikipedia: タケノコ         | タケノコ(竹の子、筍、英名:bamboo shoot)は、イネ科タケ亜科タケの若芽を指し、日本や中国などで食材として利用されている。春の季語。
 Wikipedia: カンチク         | カンチク(寒竹)は日本原産の竹の一種だが本来の自生地は不明である。種名の由来は晩秋から冬にかけてタケノコが出ることからであり、耐寒性がある訳ではない。
 Wikipedia: タケノコカワニナ | タケノコカワニナ(筍川蜷)、学名 Stenomelania rufescens は、吸腔目トウガタカワニナ科(トゲカワニナ科とも)に分類される巻貝の一種。南日本の汽水域に生息する塔形の巻貝である。
(3 rows)

求めているたけのこがヒットしたようですが、さらに絞り込みます。

SELECT * FROM wiki WHERE abstract LIKE '%タケノコ%' AND abstract LIKE '%春%';
        title        |                                                             abstract                                                              
---------------------+-----------------------------------------------------------------------------------------------------------------------------------
 Wikipedia: タケノコ | タケノコ(竹の子、筍、英名:bamboo shoot)は、イネ科タケ亜科タケの若芽を指し、日本や中国などで食材として利用されている。春の季語。
(1 row)

うん、これはおいしそうなたけのこだ。


さてさて、前置きが長くなってしまいましたが、ようやく本題に入ります。
先ほどのクエリの実行時間ですが、こんな感じになっています。

EXPLAIN ANALYSE SELECT * FROM wiki WHERE abstract LIKE '%タケノコ%' AND abstract LIKE '%春%';
                                               QUERY PLAN                                               
--------------------------------------------------------------------------------------------------------
 Seq Scan on wiki  (cost=0.00..38038.92 rows=1 width=166) (actual time=11.388..2582.249 rows=1 loops=1)
   Filter: ((abstract ~~ '%タケノコ%'::text) AND (abstract ~~ '%春%'::text))
   Rows Removed by Filter: 963127
 Planning time: 30.092 ms
 Execution time: 2588.401 ms
(5 rows)

お世辞にも早いとは言えません。
例えるなら、たけのこを探しているうちに黄昏時になってしまったような、そんな感じです。

How to find たけのこ quickly [pg_bigm]

そこで、夕餉に間に合わせるために、pg_bigm なるものを使用します。
http://pgbigm.sourceforge.jp/

pg_bigmは、PostgreSQL上で全文検索機能を提供するモジュールです。このモジュールを使うことで、ユーザは全文検索用のインデックスを作成でき、高速に文字列検索を行えるようになります。

なるほど、朝飯前に夕餉のたけのこをゲットできてしまいそうですね。


今回用意した環境はこんな感じです。

こちらの環境に pg_bigm 1.1 をインストールします。

インストール

インストールはこのドキュメントのとおりで問題ないと思います。
http://pgbigm.sourceforge.jp/pg_bigm-1-1.html#install

wget 'http://sourceforge.jp/frs/redir.php?m=iij&f=%2Fpgbigm%2F59914%2Fpg_bigm-1.1-20131122.tar.gz'
tar zxf pg_bigm-1.1-20131122.tar.gz
cd pg_bigm-1.1-20131122
make USE_PGXS=1
make USE_PGXS=1 install

pg_config にパスが通っているので、PG_CONFIG は指定していません。


ドキュメントでは postgres.conf に

shared_preload_libraries = 'pg_bigm'

を設定して PostgreSQL を再起動する手順がありますが、
何を思ったか、試しにスルーしてみても動きました。


最後に対象の DB で CREATE EXTENSION します。

CREATE EXTENSION pg_bigm;
\dx pg_bigm
                                 List of installed extensions
  Name   | Version | Schema |                           Description
---------+---------+--------+------------------------------------------------------------------
 pg_bigm | 1.1     | public | text similarity measurement and index searching based on bigrams
(1 row) 

これで準備完了です。

インデックス作成

今回は abstract を LIKE 検索するので、こんな感じでインデックスを作成します。

CREATE INDEX wiki_index ON wiki USING gin (abstract gin_bigm_ops); 

割と待ちます。

たけのこ ~再び~

インデックスが作成できたので、早速たけのこを探します。

EXPLAIN ANALYSE SELECT * FROM wiki WHERE abstract LIKE '%タケノコ%' AND abstract LIKE '%春%';
                                                     QUERY PLAN                                                     
--------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on wiki  (cost=76.00..80.02 rows=1 width=166) (actual time=1.168..1.168 rows=1 loops=1)
   Recheck Cond: ((abstract ~~ '%タケノコ%'::text) AND (abstract ~~ '%春%'::text))
   Heap Blocks: exact=1
   ->  Bitmap Index Scan on wiki_index  (cost=0.00..76.00 rows=1 width=0) (actual time=1.167..1.167 rows=1 loops=1)
         Index Cond: ((abstract ~~ '%タケノコ%'::text) AND (abstract ~~ '%春%'::text))
 Planning time: 0.264 ms
 Execution time: 1.193 ms
(7 rows)

朝飯前どころか、夢現の状態でたけのこを手中に収めてしまったような感じですね。

How to find たけのこ quickly [PGroonga]

せっかくなので、PGroonga も試してみたいと思います。
https://github.com/pgroonga/pgroonga

PGroongaはPostgreSQLからインデックスとして Groongaを使うための拡張機能です。

http://groonga.org/ja/

Groongaはオープンソースのカラムストア機能付き全文検索エンジンです。Groongaを使うと全文検索機能付き高性能アプリケーションを開発することができます。


今回は Groonga 5.0.2 と PGroonga 0.4.0 をインストールします。

インストール

インストールはこのドキュメントのとおりで問題ないと思います。
Groonga はパッケージインストール、PGroonga はソースインストールしました。
https://github.com/pgroonga/pgroonga#%E3%82%A4%E3%83%B3%E3%82%B9%E3%83%88%E3%83%BC%E3%83%AB
http://groonga.org/ja/docs/install/centos.html#centos-6

Groonga
rpm -ivh http://packages.groonga.org/centos/groonga-release-1.1.0-1.noarch.rpm
yum makecache
yum install -y groonga
PGroonga
yum update yum
yum install groonga-devel
wget http://packages.groonga.org/source/pgroonga/pgroonga-0.4.0.tar.gz
tar zxf pgroonga-0.4.0.tar.gz
cd pgroonga-0.4.0
make
make install

yum が古くて groonga-devel のインストールに失敗したので、yum update yum しています。


最後に対象の DB で CREATE EXTENSION します。

CREATE EXTENSION pgroonga;
\dx pgroonga
                             List of installed extensions
   Name   | Version | Schema |                      Description                       
----------+---------+--------+--------------------------------------------------------
 pgroonga | 0.4.0   | public | CJK-ready fast full-text search index based on Groonga
(1 row)

これで準備完了です。

インデックス作成

今回は abstract を LIKE 検索するので、こんな感じでインデックスを作成します。

CREATE INDEX wiki_index ON wiki USING pgroonga (abstract);

割と待ちます。

たけのこ ~三度~

インデックスが作成できたので、早速たけのこを探します。

EXPLAIN ANALYSE SELECT * FROM wiki WHERE abstract %% 'タケノコ' AND abstract %% '春';
                                                        QUERY PLAN                                                         
---------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on wiki  (cost=2472.18..29675.91 rows=240782 width=166) (actual time=1.517..1.519 rows=1 loops=1)
   Recheck Cond: ((abstract %% 'タケノコ'::text) AND (abstract %% '春'::text))
   Heap Blocks: exact=1
   ->  Bitmap Index Scan on wiki_index  (cost=0.00..2411.98 rows=240782 width=0) (actual time=1.506..1.506 rows=1 loops=1)
         Index Cond: ((abstract %% 'タケノコ'::text) AND (abstract %% '春'::text))
 Planning time: 0.055 ms
 Execution time: 1.577 ms
(7 rows)

朝飯前どころか、夢現の状態でたけのこを手中に収めてしまったような感じですね(2度目)。

終わりに

平均をとったりはしていないですが、pg_bigm の方が早いかなという感じです。
検索条件を変更したら結果も変わってくるのかもしれません(後で試す)。
いずれにしろ、インデックス無しよりは相当早くなるので、導入しない手はないといったところでしょうか。

ちなみに、たけのこは採取してからアクが増加してゆくものなので、
いくら早く見つけたとしても、放置していると泣きを見ます。
油断しないように春の味覚を楽しみましょう。


おしまい