前の節の例では、単純な文字列定数を使った全文検索照合を説明しました。この節では、テーブルのデータを検索する方法、そしてインデックスを使う方法を示します。
インデックスがなくても全文検索をすることは可能です。body
フィールド中のfriend
という単語を含む行のtitle
を印刷する単純な問い合わせは次のようになります。
SELECT title FROM pgweb WHERE to_tsvector('english', body) @@ to_tsquery('english', 'friend');
同時に、これは、friends
、friendly
のように、関連する単語を見つけ出します。これらはすべて同じ正規化された語彙素に帰結するからです。
上の問い合わせはenglish
設定を使って文字列をパースして正規化することを指定しています。別の方法としては、設定パラメータを省略することができます。
SELECT title FROM pgweb WHERE to_tsvector(body) @@ to_tsquery('friend');
この問い合わせはdefault_text_search_configで設定された設定を使用します。
もっと複雑な例として、create
とtable
をtitle
またはbody
に含む文書のうち新しい順に10個選ぶというものを示します。
SELECT title FROM pgweb WHERE to_tsvector(title || ' ' || body) @@ to_tsquery('create & table') ORDER BY last_mod_date DESC LIMIT 10;
細かいことですが、この例では、二つのうち一つのフィールドにNULL
を含む行を探すために必要なcoalesce
関数の呼び出しを省略しています。
これらの問い合わせはインデックスなしでも動きますが、たまに実行する一時的な問い合わせ用を除くと、たいていの用途には遅すぎます。 実用上は、インデックスを作成することが必要なのが普通です。
テキスト検索を高速化するために、GINインデックス(12.9. GINおよびGiSTインデックス種類)を作ることができます。
CREATE INDEX pgweb_idx ON pgweb USING GIN (to_tsvector('english', body));
2引数バージョンのto_tsvector
を使っていることに注意してください。設定名を指定するテキスト検索関数だけが、式インデックス(11.7. 式に対するインデックス)で使えます。これは、インデックス内容が、default_text_search_configの影響を受けないためです。もし影響を受けるとすると、異なるテキスト検索設定で作られたtsvector
を持つエントリの間でインデックス内容が首尾一貫しなくなるからです。そして、どのエントリがどのようにして作られたのか、推測する方法はないでしょう。そのようなインデックスを正しくダンプ、リストアするのは不可能でしょう。
上記のインデックスでは、2引数バージョンのto_tsvector
が使われているので、同じ設定名の2引数バージョンのto_tsvector
を使う問い合わせ参照だけがそのインデックスを使います。すなわち、WHERE to_tsvector('english', body) @@ 'a & b'
はインデックスが使えますが、WHERE to_tsvector(body) @@ 'a & b'
は使えません。これにより、インデックスエントリを作ったときの設定と、同じ設定のときだけインデックスが使われることが保証されます。
他の列によって設定名が指定されたより複雑な式インデックスを作ることができます。例えば、
CREATE INDEX pgweb_idx ON pgweb USING GIN (to_tsvector(config_name, body));
ここで、config_name
はpgweb
テーブルの列です。
これによって、各々のインデックスエントリで使用された設定を記録しつつ、同じインデックスの中で異なる設定を混在させることができます。
これは、例えば文書の集まりが異なる言語の文書を含む場合に有用です。
繰り返しになりますが、インデックスを使うよう考慮されている問い合わせは、合致するように書かれなければなりません。例えば、WHERE to_tsvector(config_name, body) @@ 'a & b'
。
インデックスには、列を連結することさえできます。
CREATE INDEX pgweb_idx ON pgweb USING GIN (to_tsvector('english', title || ' ' || body));
別の方法として、to_tsvector
の出力を保持する別のtsvector
列を作る方法があります。この例では、title
とbody
を連結、coalesce
を使って、一つのフィールドがNULL
であっても他のフィールドがインデックス付けされることを保証しています。
ALTER TABLE pgweb ADD COLUMN textsearchable_index_col tsvector; UPDATE pgweb SET textsearchable_index_col = to_tsvector('english', coalesce(title,'') || ' ' || coalesce(body,''));
そして、GINインデックスを作って検索速度を上げます。
CREATE INDEX textsearch_idx ON pgweb USING GIN (textsearchable_index_col);
これで、高速全文検索を実行する準備ができました。
SELECT title FROM pgweb WHERE textsearchable_index_col @@ to_tsquery('create & table') ORDER BY last_mod_date DESC LIMIT 10;
tsvector
形式を保存するために別の列を使う場合、title
あるいはbody
が変更されたらtsvector
列を最新の状態にいつでも維持するためにトリガを作る必要があります。12.4.3. 自動更新のためのトリガにその方法が説明されています。
別列方式が式インデックスに勝る点の一つは、インデックスを使うために問い合わせの中でテキスト検索設定を明示的に指定する必要がないことです。上の例で示したように、問い合わせはdefault_text_search_config
に依存できます。もう一つの利点は、インデックスの合致を検証するためにto_tsvector
を再実行する必要がないのでより高速だという事です。(この点はGINインデックスを使うときよりも、GiSTインデックスを使う場合に重要です。12.9. GINおよびGiSTインデックス種類参照。)しかしながら、式インデックス方式はセットアップがより容易で、tsvector
表現を明示的に保存する必要がないので、ディスクスペースの消費が少ないです。