この実験では、PostgreSQL で全文検索を実装する方法を学びます。全文検索は、自然言語のドキュメントから特定の単語やフレーズを検索する機能を提供します。まず、articles テーブルを作成し、サンプルデータを追加します。次に、処理済みテキストを格納するための特殊なデータ型である tsvector 型の search_vector 列を追加します。検索を高速化するためのインデックスを作成し、その後 tsquery を使用してクエリを実行します。最後に、ソーステキストが変更されたときに検索データを自動的に更新するトリガーを設定し、検索インデックスを最新の状態に保ちます。
このステップでは、PostgreSQL データベースに接続し、articles テーブルを作成して、いくつかのサンプルデータを挿入します。このテーブルは、全文検索の実装とテストの基盤となります。
まず、ターミナルを開き、postgres ユーザーとして PostgreSQL 対話型シェル (psql) に接続します。この実験の以降のすべての SQL コマンドは、このシェル内で実行されます。
sudo -u postgres psqlこれで、postgres=# のような PostgreSQL プロンプトが表示されます。
次に、articles テーブルを作成します。このテーブルは、各記事の ID、タイトル、および本文を格納します。
CREATE TABLE articles (
id SERIAL PRIMARY KEY,
title VARCHAR(255) NOT NULL,
content TEXT
);次に、articles テーブルに 3 つのサンプル記事を挿入します。このデータは、次のステップでの検索クエリに使用されます。
INSERT INTO articles (title, content) VALUES
('PostgreSQL Tutorial', 'This is a comprehensive tutorial on PostgreSQL.'),
('Full Text Search in PostgreSQL', 'Learn how to implement full text search using TSVECTOR in PostgreSQL.'),
('PostgreSQL Performance Tuning', 'Tips and tricks to improve the performance of your PostgreSQL database.');データが正しく挿入されたことを確認するには、SELECT クエリを実行してテーブル内のすべてのレコードを表示します。
SELECT * FROM articles;以下のような出力で、挿入した 3 行が表示されるはずです。
id | title | content
----+----------------------------------+-----------------------------------------------------------------------
1 | PostgreSQL Tutorial | This is a comprehensive tutorial on PostgreSQL.
2 | Full Text Search in PostgreSQL | Learn how to implement full text search using TSVECTOR in PostgreSQL.
3 | PostgreSQL Performance Tuning | Tips and tricks to improve the performance of your PostgreSQL database.
(3 rows)全文検索を実行するには、tsvector 型の列が必要です。tsvector 値は、同じ単語の異なるバリアントをマージするように正規化された、個別の単語（レキシム）のソート済みリストです。このステップでは、tsvector 列を追加し、検索クエリを高速化するためにインデックスを作成します。
まず、articles テーブルに TSVECTOR 型の search_vector という新しい列を追加します。
ALTER TABLE articles ADD COLUMN search_vector TSVECTOR;次に、各記事の title と content を tsvector に変換して、この新しい列を投入します。この変換には to_tsvector 関数が使用されます。この関数は、一般的な単語（ストップワード）を削除し、単語をそのルート形式（ステミング）に削減することでテキストを処理します。
UPDATE articles
SET search_vector = to_tsvector('english', title || ' ' || content);このコマンドでは、'english' がテキスト検索設定を指定し、|| はタイトルとコンテンツを結合するための連結演算子です。
全文検索を大幅に高速化するために、search_vector 列に GIN（Generalized Inverted Index）を作成します。GIN インデックスは、tsvector のような複合値をインデックス化するのに非常に効果的です。
CREATE INDEX articles_search_idx ON articles USING GIN (search_vector);\d コマンドを使用してテーブル構造を確認することで、列とインデックスが追加されたことを検証できます。
\d articles出力には、search_vector 列と articles_search_idx GIN インデックスが含まれているはずです。
Table "public.articles"
Column | Type | Collation | Nullable | Default
---------------+------------------------+-----------+----------+-----------------------------------------
id | integer | | not null | nextval('articles_id_seq'::regclass)
title | character varying(255) | | not null |
content | text | | |
search_vector | tsvector | | |
Indexes:
"articles_pkey" PRIMARY KEY, btree (id)
"articles_search_idx" gin (search_vector)tsvector 列と GIN インデックスが配置されたので、効率的な全文検索を実行できるようになりました。クエリは、テキストクエリを表す tsquery データ型を使用して構築されます。@@ 演算子は、tsquery を tsvector に一致させるために使用されます。
まず、search という単語を含む記事を検索しましょう。to_tsquery 関数は、検索文字列を tsquery に変換します。
SELECT title, content FROM articles WHERE search_vector @@ to_tsquery('english', 'search');このクエリは、「Full Text Search」に関する記事を返します。
title | content
----------------------------------+-----------------------------------------------------------------------
Full Text Search in PostgreSQL | Learn how to implement full text search using TSVECTOR in PostgreSQL.
(1 row)クエリ内に演算子を使用することもできます。& (AND) 演算子は、指定されたすべての用語を含むドキュメントを見つけます。PostgreSQL と performance の両方を含む記事を検索してみましょう。
SELECT title FROM articles WHERE search_vector @@ to_tsquery('english', 'PostgreSQL & performance');これは、パフォーマンスチューニングに関する記事を返します。
title
-------------------------------
PostgreSQL Performance Tuning
(1 row)| (OR) 演算子は、指定された用語の少なくとも 1 つを含むドキュメントを見つけます。tutorial または tuning のいずれかを含む記事を検索してみましょう。
SELECT title FROM articles WHERE search_vector @@ to_tsquery('english', 'tutorial | tuning');このクエリは 2 つの記事を返します。
title
-------------------------------
PostgreSQL Tutorial
PostgreSQL Performance Tuning
(2 rows)現在、search_vector 列は静的です。記事のタイトルまたはコンテンツを更新しても、search_vector は自動的に変更を反映しません。これを解決するために、articles テーブルに対する INSERT または UPDATE 操作の前に search_vector 列を自動的に更新するトリガーを作成できます。
まず、トリガーによって実行される関数を定義します。この関数は、挿入または更新された新しい行の search_vector を更新します。
CREATE OR REPLACE FUNCTION articles_tsvector_update() RETURNS TRIGGER AS $$
BEGIN
NEW.search_vector :=
to_tsvector('pg_catalog.english', NEW.title || ' ' || NEW.content);
RETURN NEW;
END
$$ LANGUAGE plpgsql;関数内の NEW 変数は、挿入または更新されている行を参照します。
次に、トリガー自体を作成します。このトリガーは、各行が挿入または更新される前に articles_tsvector_update 関数を呼び出します。
CREATE TRIGGER tsvectorupdate BEFORE INSERT OR UPDATE
ON articles FOR EACH ROW EXECUTE FUNCTION articles_tsvector_update();次に、トリガーをテストしましょう。まず、データベースインデックスに関する新しい記事を挿入します。
INSERT INTO articles (title, content) VALUES ('Database Indexing', 'Learn about B-Tree and GIN indexes.');トリガーにより、この新しい記事の search_vector は自動的に投入されました。indexes という単語で検索することで、これを検証できます。
SELECT title FROM articles WHERE search_vector @@ to_tsquery('indexes');新しい記事が結果に表示されるはずです。
title
---------------------
Database Indexing
(1 row)これにより、トリガーが正しく機能し、全文検索インデックスがデータと同期した状態に保たれていることが確認できます。
この実験では、PostgreSQL で全文検索を実装するための基本を学びました。テーブルを作成し、データを投入し、tsvector 列を追加して全文検索用に設定しました。to_tsvector 関数を使用してテキストを処理する方法と、GIN インデックスを使用して検索パフォーマンスを最適化する方法を学びました。また、さまざまな演算子を使用して tsquery で検索を実行する練習もしました。最後に、検索インデックスをデータと同期させ続けるプロセスを自動化するためのトリガーを実装しました。これは、信頼性の高い検索システムを維持するための重要なステップです。
