高見 直輝
takam****@orega*****
2016年 1月 27日 (水) 09:57:22 JST
高見です。
まず、本件と並行している
[groonga-dev,03867] Re: PGRNファイルが開けない？
に対する返信を21日に行ったのですが、ログファイル添付によるサイズ制限超過
で承認待ちの状態になりました。
先ほど添付ファイルをZip圧縮して再送しましたが、ウィルススキャンなどに引っ
かかる可能性があるのでご注意ください。
> >> > 当方の現状からすると、インデックスの追加又は再作成はハードルが高いので
> >> > TEXT列に対して完全一致が出来るようになると助かるのですが、今後、可能にす
> >> > る予定はありますでしょうか？
> >>
> >> 前述の通り、サイズ制限があるためサポートする予定はありません。
> >> （うまい落とし所が見つかればサポートするかもしれませんが。。。）
> >
> > 文字列系の関数はTEXT型を使用するのが殆どなので、将来的には何らかの回避手
> > 段を用意しておいた方が良いかもしれません。
>
> 実は、PostgreSQLの内部的にはvarcharもtextも同じ構造のようで、
> text型の値に対してpgroonga.varchar_opsを指定すると動いたりし
> ます。
以下の構文で間違いないでしょうか？
CREATE INDEX インデックス名 ON テーブル名 USING pgroonga
(lower(カラム名) pgroonga.varchar_ops);
動作することは確認したのですが、念のため。
> ただ、4KiBを超えると動かなくなるのは変わらないので、4KiBを超
> える文字列をインデックスを使って完全一致検索したいなら
> PostgreSQL標準のbtreeインデックスを使うのがよいと思います。
> （あるいは前後に「%」を使わないLIKE。）
>
> ただ、4KiBを超える文字列のインデックスはその分だけサイズが大
> きくなると思うのであまり実用的な気はしませんが。。。
私が問題視していたのが
varchar型でも文字列関数を使用したインデックスだと等価条件指定ができない
なので、上記pgroonga.varchar_opsという回避手段があるなら問題ありません。
> >> 代わりにLIKEを使うのはいかがでしょうか？
> >>
> >> lower(pathcombine(rootdir,path)) LIKE lower('\\st\\新しいフォルダー')
> >>
> >> とすると、すでにある全文検索用のインデックスを使いながら完全
> >> 一致検索を実現します。完全一致用のインデックスを使うよりも処
> >> 理量が増えるので遅くなりますが、サイズ制限はありません。
> >>
> >> （クエリーの前後に「%」をつけていないことがポイントです。）
> >
> > この方法で上手くいきました。
> > ありがとうございます。
>
> よかったです！
>
> > インデックスを下記のもので再作成しても状況は変わりませんでした。
> > CREATE INDEX TEST_TABLE_path ON TEST_TABLE USING pgroonga (lower(path)) WITH (tokenizer='TokenBigramSplitSymbolAlphaDigit', normalizer='');
> > EXPLAIN の結果を見ても、％％やLIKEではこのインデックスが使用されているの
> > に、＠＠では使用されていません。
> > 検索結果は％％と＠＠で同じ値が返ってきています。
>
> ↓を試してみたんですが、再現しませんでした。
> なにが違うんでしょうか。。。
検証ありがとうございます。
テーブル名を変えて下記SQLを実行したところ、下記と同じ結果になりました。
問題が発生しているテーブルでは相変わらずの状態なので、テーブルに登録され
ているデータの内容が原因のようです。
※総数2万レコード、このうち1万5千程度が『\st\新しいフォルダー』で始まる。
％％と＠＠でインデックスを使用するかどうかの境界が異なる、ということでしょ
うか？
> ----
> SET pgroonga.log_level = debug;
>
> DROP TABLE IF EXISTS TEST_TABLE;
> CREATE TABLE TEST_TABLE (
> path text
> );
>
> CREATE INDEX TEST_TABLE_path ON TEST_TABLE USING pgroonga (lower(path))
> WITH (tokenizer='TokenBigramSplitSymbolAlphaDigit', normalizer='');
>
> INSERT INTO TEST_TABLE VALUES
> ('\st\新しいフォルダー');
> INSERT INTO TEST_TABLE VALUES
> ('\st\新しいフォルダー\desktop.ini');
> INSERT INTO TEST_TABLE VALUES
> ('\st\00201-00300');
>
> SET enable_seqscan = off;
> SET enable_indexscan = on;
> SET enable_bitmapscan = off;
>
> EXPLAIN ANALYZE VERBOSE
> SELECT path
> FROM TEST_TABLE
> WHERE lower(path) @@ lower('\\st\\新しいフォルダー');
> -- QUERY PLAN
> -- ------------------------------------------------------------------------------------------------------------------------------------
> -- Index Scan using test_table_path on public.test_table (cost=0.14..8.16 rows=1 width=32) (actual time=1.107..1.133 rows=2 loops=1)
> -- Output: path
> -- Index Cond: (lower(test_table.path) @@ '\\st\\新しいフォルダー'::text)
> -- Planning time: 0.154 ms
> -- Execution time: 1.256 ms
> -- (5 rows)
> -- ↑「Index Scan」になっている
> ----
>
> ログは次の通りです。
>
> ----
> 2016-01-26 22:39:36.477273|i| [object][search][index][key][exact] <Lexicon17750_0.index>
> 2016-01-26 22:39:36.477330|i| grn_ii_sel > (\st\新しいフォルダー)
> 2016-01-26 22:39:36.477420|i| n=11 (\st\新しいフォルダー)
> 2016-01-26 22:39:36.477499|i| exact: 2
> 2016-01-26 22:39:36.477519|i| hits=2
> ----
>
> なお、次のように2件ヒットします。
>
> ----
> path
> ----------------------------------
> \st\新しいフォルダー
> \st\新しいフォルダー\desktop.ini
> (2 rows)
> ----
-----------------------------
高見 直輝 <takam****@orega*****>
株式会社オレガ
TEL:03-3267-0150
FAX:03-3267-0180