正規化しないと駄目かな
- 2011/08/05
- aikawa
- PostgreSQL
PostgreSQL8.1を使ったあるシステムでのお話しです。
まずユーザデータのテーブルが有り、そのユーザはID（連番）で管理されています。
そしてユーザが登録するデータテーブルには、データごとにそのデータを共有するユーザのIDが複数登録されています。
これはユーザIDをCSV形式の文字列で格納をしているんですが、条件文でLIKEとORを連発するのでデータ量が多くなると検索が非常に重くなります。
正規化しないと駄目かなと考えたんですが、対象となるテーブルが複数有るので修正に必要な箇所が散在していて、そのうえデータ量が多くなるテーブルは１つか２つしかないので、構造を変えずにどうにかならないかと別の方法を探しました。
結果として一応、postgresql-contribに含まれるtsearch2が、分かち書き形式の全文検索が出来るので良い感じかなと言う事でやってみました。
日本語には対応していませんが、今回は不要なので構いません。
（GINインデックスは対応が8.2からだったのでパスしました）
postgresql-contribは既にインストールされているので、postgresユーザになって、
psql dbname -f /usr/share/pgsql/contrib/tsearch2.sql
次にpsql dbnameでログインして、GRANTを掛けます。
GRANT ALL ON pg_ts_cfg TO PUBLIC;
GRANT ALL ON pg_ts_cfgmap TO PUBLIC;
GRANT ALL ON pg_ts_dict TO PUBLIC;
GRANT ALL ON pg_ts_parser TO PUBLIC;
とりあえずテスト。
CREATE TABLE test_tb(testts tsvector);
CREATE INDEX test_tb_testts ON test_tb USING gist (testts);
何事もなく作成されたので、データを入れてみて、SELECT。
データ中に2と4の含まれるデータを検索します。
SELECT * FROM test_tb WHERE testts @@ to_tsquery('default', '2&4'); -- and検索
SELECT * FROM test_tb WHERE testts @@ to_tsquery('default', '2|4'); -- or検索
EXPLAINすると、INDEXを使用しているようです。
Bitmap Heap Scan on test_tb (cost=4.10..108.72 rows=27 width=32)
Filter: (testts @@ ”’2” & ”4”’::tsquery)
-> Bitmap Index Scan on test_tb_index (cost=0.00..4.10 rows=27 width=0)
Index Cond: (testts @@ ”’2” & ”4”’::tsquery)
これを応用して、対象となるhogeテーブルのcol列にtsvector型に対応させたインデックスを作成します。
一応カンマからスペースに変更して、格納するようにreplaceしています。
CREATE INDEX hoge_col ON hoge USING gist (to_tsvector('default', replace(col,',',' ')));
SELECT * FROM hoge WHERE to_tsvector('default', replace(col,',',' ')) @@ to_tsquery('default', '2|4');
これで一応インデックスは効いているようです。
今までのクエリはインデックスを使わないでそのまま使えるし、インデックススキャンが必要なところは都度対応する事が出来ます。
とはいえ、かなり強引かなと思える方法なので、今後見て行かないとこれでOKかは分かりませんが・・・。
それと、どちらも許容範囲では有りますが、メモリ上に一度乗るとインデックス使わない検索の方が早い事も有ります。
何か他に良い方法は無いものか。
Tags: PostgreSQL
