😹 👩🏿‍🎨 🍔 PostgreSQL 8.3でのTsearch2の使用 🤵🏻 👨🏼‍⚖️ 👲🏽

サイト検索を実装する場合、多くの人がLIKE演算子または正規表現を使用してデータベースを照会する必要があります。
PostgreSQL 8.3のリリースでは、全文検索モジュールtsearch2がシステムのコアに統合され（これはすでにHabréで記述されています）、それにより、より機能的なデータベースで検索を実装できます。原則として、上記の記事ではこのモジュールの使用例を紹介していますが、実際のプロジェクトで全文検索を実装した経験を共有したいと思います。

そのため、「title」、「metaKeywords」、「metaDescription」、「content」というフィールドを含むテーブル「news」があります。
テーブルのすべてのフィールドで単語を検索する全文検索を実装する必要があり、各フィールドには独自の絶対優先順位があります。

UTF8エンコーディングでデータベースを作成し、その中にテストテーブルを作成しましょう。

CREATE DATABASE "tsearch2"
WITH ENCODING = 'UTF8' ;
CREATE TABLE "ニュース"
（
「NewsId」シリアルNOT NULL 、
「タイトル」 Varchar （1024） NOT NULL 、
「MetaKeywords」 Varchar （2048）、
「MetaDescription」 Varchar （1024）、
「コンテンツ」テキストNOT NULL 、
主キー（ "newsId"）
）;

次に、このテーブルにレコードを追加します。

「ニュース」への挿入（「タイトル」、「メタキーワード」、「メタ説明」、「コンテンツ」）
VALUES （ 'Test News' 、 'News、Article' 、 'Test news for Search' 、 'Hello world' ）;

次に、全文検索構成を作成する必要があります。

テキスト検索辞書の作成mydict_russian_ispell（
TEMPLATE = ispell、
DictFile =ロシア語、
AffFile =ロシア語、
ストップワード=ロシア語
）;

テキスト検索設定の作成public .mydict_ru（PARSER = default ）;
テキスト検索設定に関するコメントpublic .mydict_ru IS 'conf。 for mydict ru ' ;

ALTER TEXT SEARCH CONFIGURATION mydict_ru ADDマッピング
メール、url、url_path、 host 、 file 、version、
sfloat、 float 、 int 、uint、
numword、hword_numpart、numhword
WITH simple;

ALTER TEXT SEARCH CONFIGURATION mydict_ru ADDマッピング
FORワード、hword_part、hword
WITH mydict_russian_ispell;

このスクリプトでは、 ispell辞書mydict_russian_ispellを作成しました 。次に、辞書の使用規則を示すmydict_ru構成を作成しました。
最初のリクエストでは、デフォルトのディレクトリ（\ share \ tsearch_data \）にあるpostgresにあるファイルへのリンクを使用することをお伝えします。
辞書を作成するには、russian.affix、russian.stop、russian.dictの3つのファイルが必要でした。最初は特定の言語の単語の語尾の説明を含み、2番目はストップワードのリストを含み、最後は単語自体を含みます。ファイルはベース自体と同じエンコーディングである必要があることを覚えておくことが重要です。私たちの場合-UTF8。

検索を実装するとき、データベースのフィールドを参照するのではなく、テーブルのコンテンツに関する情報を含む特別なインデックスを参照します。

ALTER TABLE "news" ADD COLUMN fts_news tsvector;
UPDATE "ニュース" SET fts_news =
setweight（合体（to_tsvector（ 'mydict_ru' 、 "title"）、 '' ）、 'A' ）||
setweight（合体（to_tsvector（ 'mydict_ru' 、 "metaKeywords"）、 '' ）、 'B' ）||
setweight（合体（to_tsvector（ 'mydict_ru' 、 "metaDescription"）、 '' ）、 'C' ）||
setweight（合体（to_tsvector（ 'mydict_ru' 、 "content"）、 '' ）、 'D' ）;

CREATE INDEX news_fts_idx ON "news" USING gist（fts_news）;

「ニュース」を分析する真空;

このスクリプトを使用して、テーブルに別のtsvectorフィールドを作成しました。このフィールドには、テーブルの4つのフィールドに関する情報が含まれています。「タイトル」フィールドは最高の優先度A 、「コンテンツ」フィールドは最低のD 次に、GISTインデックスを作成し、インデックスを更新しました。
テストレコードの結果、テーブルは次のtsvectorに対応します-'search '：8C 'article'：4B 'news'：2A、3B、6C 'test'：1A、5C 。

さあ、検索のテストの時間です。次のリクエストを実行します。

選択
「NewsId」、
ts_headline（ 'mydict_ru' 、 "title"、q）を "title" として、
ランク
FROM （
選択
「NewsId」、
「タイトル」、
q
ランクとしての ts_rank（fts_news、q）
FROM “ news”、plainto_tsquery（ 'mydict_ru' 、 'news' ）q
WHERE fts_news @@ q
ORDER BYランクDESC
） AS foo;

その結果、行Test Newsを取得します。見つかった単語の強調表示は、 ts_headline関数によって実装されます。クエリのランキング-ts_rank関数。値で結果をソートします。

そしてもちろん、「news」テーブルの「fts_news」フィールドを更新するトリガーを作成することを忘れてはなりません。

関数「createNewsFTS」の作成または置換（） RETURNS 「トリガー」 AS
DECLARE bUpdateブール値。
開始
bUpdate = false;
IF（TG_OP = ' ' INSERT ' '）その後
bUpdate：= true;
ELSEIF（TG_OP = ' ' UPDATE ' '）その後
NEW.title！= OLD.titleまたはNEW.content！= OLD.contentまたはNEW。 "MetaKeywords"！= OLD。 "MetaKeywords"またはNEW。 "MetaDescription"！= OLD。 "MetaDescription"その後
bUpdate：= true;
終了IF;
終了IF;

bUpdate = TRUE THENの場合
レイズ通知 ' ' UPDATE ' ';
new.fts_news：= setweight（coalesce（to_tsvector（ ' ' mydict_ru ' '、new.title）、 ' ' ' '）、 ' ' A ' '）||
setweight（合体（to_tsvector（ ' ' mydict_ru ' '、new。 "metaKeywords"）、 ' ' ' '）、 ' ' B ' '）||
setweight（合体（to_tsvector（ ' ' mydict_ru ' '、new。 "metaDescription"）、 ' ' ' '）、 ' ' C ' '）||
setweight（coalesce（to_tsvector（ ' ' mydict_ru ' '、new.content）、 ' ' ' '）、 ' ' D ' '）;
終了IF;
新しいリターン;
終了
' LANGUAGE ' plpgsql '揮発性;

CREATE TRIGGER "newsFTSTrigger"
「ニュース」の挿入または更新前
各行の実行手順 "updateNewsFTS"（）;

UPD

性能

コメントでパフォーマンス情報を提供するように求められたため、2つのタイプの検索（tsearchと正規表現検索）を比較することにしました。
検索は、4つのテーブル（合計で約5400エントリ）のデータを組み合わせて、VIEWによって実行されます。

tsearchを使用したクエリ：

選択
「Id」
「タイプ」、
ts_headline（ 'mydict_ru' 、 "title"、q）を "title" として、
（ CASE WHEN trim （ "foreword"）= '' THEN ts_headline（ 'mydict_ru' 、 "content"、q）の場合
ELSE ts_headline（ 'mydict_ru' 、“ foreword”、q） END ） as “ body”、
「ResourceTypes」、
ランク
FROM （
選択
「Id」
「タイプ」、
「タイトル」、
まえがき
「コンテンツ」、
「ResourceTypes」、
q
ts_rank（fts_vector、q）ランクとして
FROM "getSearchItems"、plainto_tsquery（ 'mydict_ru' 、 'physics' ）q
WHERE fts_vector @@ q
ORDER BYランクDESC
） AS foo;

私のラップトップでのそのようなリクエストの平均実行時間は2.35秒です。結果には821エントリが含まれます。

正規表現を使用し、ランキング結果の類似度を実装するクエリ：

選択 *
、（ SELECT 位置（ lower （ 'physics' ） in lower （ " search "。 "Title"）） as "pos"
「pos」として SELECT 0 を除く）「titlePosition」として
、（位置を選択（下位（ '物理' ）下位（ " 検索 "。 "メタキーワード"）））として "pos"
「pos」としての SELECT 0 を除く）「metaKeywordsPosition」として
、（ SELECT 位置（ lower （ 'physics' ） in lower （ " search "。 "MetaDescription"）） as "pos"
「metaDescriptionPosition」として「pos」として SELECT 0 を除く
、（ SELECT 位置（ lower （ 'physics' ） in lower （ " search "。 "Foreword"）） as "pos"
「pos」としての SELECT 0 を除く）「forewordPosition」として
、（ SELECT 位置（ lower （ 'physics' ） in lower （ " search "。 "Content"）） as "pos"
SELECT 0 を「pos」として、「contentPosition」として
FROM （
選択
「Id」
「タイプ」、
「タイトル」、
「MetaKeywords」、
「MetaDescription」、
まえがき
「コンテンツ」
FROM "getSearchItems"
WHERE （ lower （ "title"）〜lower （ '（。*）' || 'physics' || '（。*）' ）
or lower （ "metaKeywords"）〜lower（ '（。*）' || 'physics' || '（。*）' ）
または low（ "metaDescription"）〜lower（ '（。*）' || 'physics' || '（。*）' ）
or lower （ "foreword"）〜lower（ '（。*）' || 'physics' || '（。*）' ）
または lower （ "content"）〜lower （ '（。*）' || 'physics' || '（。*）' ））
）「検索」として
ORDER BY "タイプ" ASC
、「TitlePosition」 ASC
、「MetaKeywordsPosition」 ASC
、「MetaDescriptionPosition」 ASC
、「ForewordPosition」 ASC
、「ContentPosition」 ASC 。

私のラップトップでのこのようなリクエストの平均実行時間は1.5秒です。結果には567レコードが含まれます。
したがって、tsearchを使用すると、「単純な」検索の実行時間の1.5倍のランタイムが得られましたが、 物理学のさまざまな形式を含み、テンプレートへの出力の準備ができているレコードが約1.5倍増えました。

PS

この検索の実装は、データベース内のロシア語の全文検索を提供します。
この構成では、英単語は索引付けされませんでした。これを修正するには、別の検索構成を作成する必要があります。自分で使用した構成を提供しました。
これまでのところ、最も簡単で最もアクセスしやすい方法は、英語のispell辞書を追加して英語の単語のインデックスを作成するこの種の設定です。

テキスト検索辞書の作成english_ispell（
TEMPLATE = ispell、
DictFile =英語、
AffFile =英語、
StopWords =英語
）;

ALTER TEXT SEARCH CONFIGURATION mydict_ru ADDマッピング
asciiword
WITH english_ispell;

PostgreSQL 8.3でのTsearch2の使用

性能

PS

More articles: