pg_bigmとpg_trgmの併存
Fujii先生からのツッコミ
昨日、pg_bigmの検証のエントリを書いた後、作者(か或いは作者に近い立場と思われる)のFujii Masaoさんから、以下のコメントをもらった。
@nuko_yokohama BTW you can easily register pg_bigm and pg_trgm to the same database at the same time by modifying sql file slightly.
ふむ。そういうことならSQLファイルを修正して、pg_bigmとpg_trgmを同一データベース上にインストールできるようにしてみよう。
通常の運用では不要だとは思うけど。
なぜ併存できないのか
とりあえず何で併存できないのか、実際に2つのEXTENSIONを突っ込んで挙動を見るのが一番手っ取り早い。
ということでやってみた。
[ぬこ＠横浜] psql gmtest psql (9.2.4) Type "help" for help. gmtest=# CREATE EXTENSION pg_trgm ; CREATE EXTENSION gmtest=# CREATE EXTENSION pg_bigm ; ERROR: operator class "gin_trgm_ops" for access method "gin" already exists STATEMENT: CREATE EXTENSION pg_bigm ; gmtest=#
ふーむ。GIN用の演算子クラス名が重複しているのが原因なのか。
ソースフォルダ上にある pg_bigm--1.0.sql の中を見てみるとGIN演算子クラスとして gin_bigm_ops と gin_trgm_ops の両方が定義されている。gin_trgm_ops のほうはコメントにあるように
support also gin_trgm_ops for backward-compatibility
下位互換性のための演算子クラスなので、これを使うつもりがなければ、定義から削除してしまえば良いのではないか。
複数EXTENSION併存の実証実験
ということで gin_trgm_ops のOPERATOR CLASS を削除し、その後に make install で再インストールする。
[ぬこ＠横浜] make USE_PGXS=1 install
/bin/mkdir -p '/home/harada/pgsql-9.2.4/lib'
/bin/mkdir -p '/home/harada/pgsql-9.2.4/share/extension'
/bin/mkdir -p '/home/harada/pgsql-9.2.4/share/extension'
/bin/sh /home/harada/pgsql-9.2.4/lib/pgxs/src/makefiles/../../config/install-sh -c -m 755 pg_bigm.so '/home/harada/pgsql-9.2.4/lib/pg_bigm.so'
/bin/sh /home/harada/pgsql-9.2.4/lib/pgxs/src/makefiles/../../config/install-sh -c -m 644 ./pg_bigm.control '/home/harada/pgsql-9.2.4/share/extension/'
/bin/sh /home/harada/pgsql-9.2.4/lib/pgxs/src/makefiles/../../config/install-sh -c -m 644 ./pg_bigm--1.0.sql '/home/harada/pgsql-9.2.4/share/extension/'
[ぬこ＠横浜] psql gmtest
psql (9.2.4)
Type "help" for help.
gmtest=# CREATE EXTENSION pg_bigm ;
CREATE EXTENSION
gmtest=# \dx
List of installed extensions
Name | Version | Schema | Description
---------+---------+------------+-------------------------------------------------------------------
pg_bigm | 1.0 | public | text index searching based on bigrams
pg_trgm | 1.0 | public | text similarity measurement and index searching based on trigrams
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
(3 rows)同一データベースにpg_trgmとpg_bigmが併存できた。
本当に両方使えるのかどうか、両方の演算子クラスを使ったインデクスを両方登録し、両方使えるのかを検証してみる。このために、pg_plan_hintをインストールしておく。pg_hint_planについてはFujii氏のブログに詳しい解説があるのでそっちをみてくだちい。
とりあえず、また「走れメロス」をサンプルにして、2種類のインデクスを生成する。
gmtest=# CREATE INDEX test_trgm ON test USING gin (data gin_trgm_ops);
CREATE INDEX
gmtest=# \d test
Table "public.test"
Column | Type | Modifiers
--------+---------+---------------------------------------------------
id | integer | not null default nextval('test_id_seq'::regclass)
data | text |
Indexes:
"test_bigm" gin (data gin_bigm_ops)
"test_trgm" gin (data gin_trgm_ops)
gmtest=# 両方のインデクスができた。
検索してみる。とはいえ普通にクエリを発行するとTom Laneというかプランナの御心のままになる。
gmtest=# EXPLAIN SELECT id, data FROM test WHERE data LIKE '%セリヌンティウス%' LIMIT 3;
QUERY PLAN
-----------------------------------------------------------------------------
Limit (cost=52.08..53.62 rows=3 width=349)
-> Bitmap Heap Scan on test (cost=52.08..57.20 rows=10 width=349)
Recheck Cond: (data ~~ '%セリヌンティウス%'::text)
-> Bitmap Index Scan on idx_tr (cost=0.00..52.08 rows=10 width=0)
Index Cond: (data ~~ '%セリヌンティウス%'::text)
(5 rows)今回はpg_trgmの演算子クラスを使ったGINインデクスが選択されるようだ。
で、pg_plan_hitの機能を使ってインデクスを制御してみようとしたが・・・
gmtest=# \d test
Table "public.test"
Column | Type | Modifiers
--------+---------+---------------------------------------------------
id | integer | not null default nextval('test_id_seq'::regclass)
data | text |
Indexes:
"idx_bi" gin (data gin_bigm_ops)
"idx_tr" gin (data gin_trgm_ops)
gmtest=# /*+ IndexScan(test idx_bi) */ EXPLAIN SELECT id, data FROM test WHERE data LIKE '%セリヌンティウス%' LIMIT 3;
QUERY PLAN
---------------------------------------------------------------------------------
Limit (cost=10000000000.00..10000000001.84 rows=3 width=349)
-> Seq Scan on test (cost=10000000000.00..10000000006.12 rows=10 width=349)
Filter: (data ~~ '%セリヌンティウス%'::text)
(3 rows)
gmtest=# /*+ IndexScan(test idx_tr) */ EXPLAIN SELECT id, data FROM test WHERE data LIKE '%セリヌンティウス%' LIMIT 3;
QUERY PLAN
---------------------------------------------------------------------------------
Limit (cost=10000000000.00..10000000001.84 rows=3 width=349)
-> Seq Scan on test (cost=10000000000.00..10000000006.12 rows=10 width=349)
Filter: (data ~~ '%セリヌンティウス%'::text)
(3 rows)え？なんでHINT句指定が無視されてるんだ・・・？
インデクスが両方生成できてもこれじゃ意味ないじゃないか・・・。
起動時にpg_hint_planの読み込みはされているはずなのに。どういうことなの。
追記
ビール飲みながら検証とかしちゃイカンなあ・・・ｗ
早速、Fujii Masaoさんからツッコミが。
HINTの指定が間違っていただけだった。
IndexScanではなくBitmapScanを指定することできちんと制御できた・・・。阿呆か俺はｗ
gmtest=# /*+ BitmapScan(test idx_bi) */ EXPLAIN SELECT id, data FROM test WHERE data LIKE '%セリヌンティウス%' LIMIT 3;
QUERY PLAN
-----------------------------------------------------------------------------
Limit (cost=60.08..61.62 rows=3 width=349)
-> Bitmap Heap Scan on test (cost=60.08..65.20 rows=10 width=349)
Recheck Cond: (data ~~ '%セリヌンティウス%'::text)
-> Bitmap Index Scan on idx_bi (cost=0.00..60.08 rows=10 width=0)
Index Cond: (data ~~ '%セリヌンティウス%'::text)
(5 rows)
gmtest=# /*+ BitmapScan(test idx_tr) */ EXPLAIN SELECT id, data FROM test WHERE data LIKE '%セリヌンティウス%' LIMIT 3;
QUERY PLAN
-----------------------------------------------------------------------------
Limit (cost=52.08..53.62 rows=3 width=349)
-> Bitmap Heap Scan on test (cost=52.08..57.20 rows=10 width=349)
Recheck Cond: (data ~~ '%セリヌンティウス%'::text)
-> Bitmap Index Scan on idx_tr (cost=0.00..52.08 rows=10 width=0)
Index Cond: (data ~~ '%セリヌンティウス%'::text)
(5 rows)
gmtest=# 当初の目的は達成できた。めでたしめでたし。これで眠れる。
