全文検索

Postgresには、全文検索クエリを処理する関数が組み込まれています。これは、Postgres内の「検索エンジン」のようなものです。

準備

このガイドでは、以下の例をデータとして使用します。

Data

create table books (
  id serial primary key,
  title text,
  author text,
  description text
);

insert into books (title, author, description) 
values 
  ('The Poky Little Puppy','Janette Sebring Lowrey','Puppy is slower than other, bigger animals.'),
  ('The Tale of Peter Rabbit','Beatrix Potter','Rabbit eats some vegetables.'),
  ('Tootle','Gertrude Crampton','Little toy train has big dreams.'),
  ('Green Eggs and Ham','Dr. Seuss','Sam has changing food preferences and eats unusually colored food.'),
  ('Harry Potter and the Goblet of Fire','J.K. Rowling','Fourth year of school starts, big drama ensues.');

SQL

id	title	author	description
1	The Poky Little Puppy	Janette Sebring Lowrey	Puppy is slower than other, bigger animals.
2	The Tale of Peter Rabbit	Beatrix Potter	Rabbit eats some vegetables.
3	Tootle	Gertrude Crampton	Little toy train has big dreams.
4	Green Eggs and Ham	Dr. Seuss	Sam has changing food preferences and eats unusually colored food.
5	Harry Potter and the Goblet of Fire	J.K. Rowling	Fourth year of school starts, big drama ensues.

使用方法

このガイドで次の関数を取り上げます。

to_tsvector()

データを検索可能な「トークン（処理上の一単位）」に変換します。to_tsvector()は「to text search vector（テキスト検索文書に変換）」の略です。例えば、以下のようなものです。

select to_tsvector('green eggs and ham')

-- 'egg':2 'green':1 'ham':4 を返します

これらのトークンをまとめて「ドキュメント」と呼び、Postgresはこれを比較に使用できます。

to_tsquery()

to_tsquery()は「to text search query（テキスト検索問い合わせに変換）」の略で、クエリ文字列をマッチする「トークン」に変換します。

この変換ステップは、キーワードを「曖昧にマッチ」させるために重要です。 例えば、ユーザーが「eggs」を検索して、カラムに「egg」という値があった場合、マッチしたものを返したくなります。

マッチ： @@

@@記号は、全文検索の「マッチ」記号です。この記号は、to_tsvectorの結果とto_tsqueryの結果の間で一致するものを返します。

次の例を見てみましょう。

SQL

select * 
from books 
where title = 'Harry';

JavaScript

const { data, error } = await supabase
  .from('books')
  .select()
  .eq('title', 'Harry')

Dart

final result = await client
  .from('books')
  .select()
  .eq('title', 'Harry')
  .execute();

上の等号記号（=）は、マッチするものに対して非常に「厳密」です。フルテキスト検索では、「Harry Potter」の本をすべて見つけたい場合がありますので、上記の例を書き換えることができます。

SQL

select * 
from books 
where to_tsvector(title) @@ to_tsquery('Harry');

JavaScript

const { data, error } = await supabase
  .from('books')
  .select()
  .textSearch('title', `'Harry'`)

Dart

final result = await client
  .from('books')
  .select()
  .textSearch('title', "'Harry'")
  .execute();

基本的な全文検索クエリ

1つの列を検索

descriptionに「big」という単語が含まれるbooksをすべて検索するには次の通りです。

SQL

select 
  * 
from 
  books
where 
  to_tsvector(description) 
  @@ to_tsquery('big');

JavaScript

const { data, error } = await supabase
  .from('books')
  .select()
  .textSearch('description', `'big'`)

Dart

final result = await client
  .from('books')
  .select()
  .textSearch('description', "'big'")
  .execute();

Data

id	title	author	description
3	Tootle	Gertrude Crampton	Little toy train has big dreams.
5	Harry Potter and the Goblet of Fire	J.K. Rowling	Fourth year of school starts, big drama ensues.

複数の列を検索

descriptionまたはtitleにlittleという単語が含まれるすべてのbooksを検索します。

SQL

select 
  * 
from 
  books
where 
  to_tsvector(description || ' ' || title) -- 列を連結する際には、必ずスペースを入れて区切ってください。
  @@ to_tsquery('little');

Data

id	title	author	description
1	The Poky Little Puppy	Janette Sebring Lowrey	Puppy is slower than other, bigger animals.
3	Tootle	Gertrude Crampton	Little toy train has big dreams.

すべての検索ワードに一致

descriptionにlittleとbigの両方の単語が含まれているbooksをすべて検索するには、&記号を使用します。

SQL

select 
  * 
from 
  books
where 
  to_tsvector(description)
  @@ to_tsquery('little & big'); -- 検索条件のANDに&を使います

JavaScript

const { data, error } = await supabase
  .from('books')
  .select()
  .textSearch('description', `'little' & 'big'`)

Dart

final result = await client
  .from('books')
  .select()
  .textSearch('description', "'little' & 'big'")
  .execute();

Data

id	title	author	description
3	Tootle	Gertrude Crampton	Little toy train has big dreams.

任意の検索語にマッチ

descriptionにlittleまたはbigのいずれかの単語が含まれているbooksをすべて検索するには、｜記号を使用します。

SQL

select 
  * 
from 
  books
where 
  to_tsvector(description) 
  @@ to_tsquery('little | big'); -- 検索条件のORに|を使用します

JavaScript

const { data, error } = await supabase
  .from('books')
  .select()
  .textSearch('description', `'little' | 'big'`)

Dart

final result = await client
  .from('books')
  .select()
  .textSearch('description', "'little' | 'big'")
  .execute();

Data

id	title	author	description
1	The Poky Little Puppy	Janette Sebring Lowrey	Puppy is slower than other, bigger animals.
3	Tootle	Gertrude Crampton	Little toy train has big dreams.

bigを検索すると、bigger（またはbigestなど）という単語を含む結果が表示されることに注目してください。

インデックスを作成

さて、全文検索が動作するようになったので、インデックスを作成してみましょう。これにより、Postgresが事前に文書を「構築」でき、クエリ実行時に文書を作成する必要がなくなります。これにより、クエリの実行がより速くなります。

検索可能な列

titleとdescriptionの列の検索可能なインデックスを格納するために、booksテーブルの中に新しい列ftsを作成しましょう。

Postgresの特別な機能である、生成列を使用して、titleとdescriptionsの列の値が変更されるたびにインデックスが更新されるようにします。

SQL

alter table 
  books 
add column 
  fts tsvector generated always as (to_tsvector('english', description || ' ' || title)) stored;
  
create index books_fts on books using gin (fts); -- インデックスを生成

select id, fts 
from books;

Data

id	fts
1	'anim':7 'bigger':6 'littl':10 'poki':9 'puppi':1,11 'slower':3
2	'eat':2 'peter':8 'rabbit':1,9 'tale':6 'veget':4
3	'big':5 'dream':6 'littl':1 'tootl':7 'toy':2 'train':3
4	'chang':3 'color':9 'eat':7 'egg':12 'food':4,10 'green':11 'ham':14 'prefer':5 'sam':1 'unus':8
5	'big':6 'drama':7 'ensu':8 'fire':15 'fourth':1 'goblet':13 'harri':9 'potter':10 'school':4 'start':5 'year':2

新しいカラムを使った検索

インデックスを作成して追加したので、これまでと同じ手法で検索してみましょう。

SQL

select 
  * 
from 
  books
where 
  fts @@ to_tsquery('little & big');

JavaScript

const { data, error } = await supabase
  .from('books')
  .select()
  .textSearch('fts', `'little' & 'big'`)

Dart

final result = await client
  .from('books')
  .select()
  .textSearch('fts', "'little' & 'big'")
  .execute();

Data

id	title	author	description	fts
3	Tootle	Gertrude Crampton	Little toy train has big dreams.	'big':5 'dream':6 'littl':1 'tootl':7 'toy':2 'train':3

クエリ演算子

次により高度な全文検索クエリに使用できる演算子を紹介します。さらに学ぶには、PostgreSQLのテキスト検索関数と演算子を参照してください。

近接：<->

近接は、一定の「距離」を隔てた用語を検索するのに便利です。 例えば、「big」にマッチした後、「dreams」にマッチする「big dreams」というフレーズを検索する場合に使用します。

SQL

select 
  *
from 
  books
where 
  to_tsvector(description) @@ to_tsquery('big <-> dreams');

JavaScript

const { data, error } = await supabase
  .from('books')
  .select()
  .textSearch('description', `'big' <-> 'dreams'`)

Dart

final result = await client
  .from('books')
  .select()
  .textSearch('description', "'big' <-> 'dreams'")
  .execute();

<->を使って、一定の距離内にある単語を探すこともできます。例えば、yearとschoolを2語以内で検索するには、次のようにします。

SQL

select 
  *
from 
  books
where 
  to_tsvector(description) @@ to_tsquery('year <2> school');

JavaScript

const { data, error } = await supabase
  .from('books')
  .select()
  .textSearch('description', `'year' <2> 'school'`)

Dart

final result = await client
  .from('books')
  .select()
  .textSearch('description', "'year' <2> 'school'")
  .execute();

否定：!

否定は、検索語を含まないフレーズを見つけるために使用できます。例えば、bigという単語を含み、littleという単語を含まないレコードを見つけるには次の通りになります。

SQL

select 
  *
from 
  books
where 
  to_tsvector(description) @@ to_tsquery('big & !little');

JavaScript

const { data, error } = await supabase
  .from('books')
  .select()
  .textSearch('description', `'big' & !'little'`)

Dart

final result = await client
  .from('books')
  .select()
  .textSearch('description', "'big' & !'little'")
  .execute();

リソース

• PostgreSQL：テキスト検索関数と演算子