Postgre SQLの日本語全文検索エンジンクエリ文

 

  PostgreSQLの日本語全文検索に関するクエリ文を以下にまとめます。

1. テキスト検索設定の作成

```sql
-- 日本語用のテキスト検索設定を作成
CREATE TEXT SEARCH CONFIGURATION japanese (COPY = pg_catalog.simple);

-- 形態素解析用のパーサを指定（pg_bigmなど別途インストールが必要な場合も）
CREATE TEXT SEARCH DICTIONARY japanese_ispell (
    TEMPLATE = ispell,
    DictFile = japanese,
    AffFile = japanese,
    StopWords = japanese
);

-- 設定に辞書を割り当て
ALTER TEXT SEARCH CONFIGURATION japanese
    ALTER MAPPING FOR word, asciiword, numword, hword, hword_part, hword_asciipart, hword_numpart
    WITH japanese_ispell, simple;
```

2. 全文検索インデックスの作成

```sql
-- tsvector型のカラムを追加
ALTER TABLE documents ADD COLUMN document_tsv tsvector;

-- GINインデックスの作成
CREATE INDEX documents_tsv_idx ON documents USING GIN(document_tsv);

-- インデックスを更新する関数
CREATE OR REPLACE FUNCTION documents_tsv_trigger() RETURNS trigger AS $$
begin
    new.document_tsv := to_tsvector('japanese', coalesce(new.title, '') || ' ' || coalesce(new.content, ''));
    return new;
end
$$ LANGUAGE plpgsql;

-- トリガーの作成
CREATE TRIGGER tsvectorupdate BEFORE INSERT OR UPDATE
ON documents FOR EACH ROW EXECUTE FUNCTION documents_tsv_trigger();
```

3. 基本的な検索クエリ

```sql
-- 単純な全文検索
SELECT * FROM documents 
WHERE document_tsv @@ to_tsquery('japanese', '検索語');

-- 複数語の検索（AND条件）
SELECT * FROM documents 
WHERE document_tsv @@ to_tsquery('japanese', '検索 & 語');

-- 複数語の検索（OR条件）
SELECT * FROM documents 
WHERE document_tsv @@ to_tsquery('japanese', '検索 | 語');

-- フレーズ検索
SELECT * FROM documents 
WHERE document_tsv @@ phraseto_tsquery('japanese', '全文検索');

-- 類似度スコア付き検索
SELECT *, 
       ts_rank(document_tsv, to_tsquery('japanese', '検索語')) as rank
FROM documents 
WHERE document_tsv @@ to_tsquery('japanese', '検索語')
ORDER BY rank DESC;
```

4. 高度な検索クエリ

```sql
-- 重み付け検索（タイトルを重視）
SELECT *,
       ts_rank('{0.1, 0.2, 0.4, 1.0}', 
               setweight(to_tsvector('japanese', title), 'A') || 
               setweight(to_tsvector('japanese', content), 'B'),
               to_tsquery('japanese', '検索語')) as rank
FROM documents 
ORDER BY rank DESC;

-- 前方一致検索（pg_bigm使用時）
SELECT * FROM documents 
WHERE document_tsv @@ to_tsquery('japanese', '検索:*');

-- ハイライト表示
SELECT title,
       ts_headline('japanese', content, 
                   to_tsquery('japanese', '検索語'),
                   'StartSel=, StopSel=') as highlighted_content
FROM documents 
WHERE document_tsv @@ to_tsquery('japanese', '検索語');
```

5. 統計情報の取得

```sql
-- 文書内の語句統計
SELECT * FROM ts_stat('SELECT document_tsv FROM documents');

-- 特定の語句の出現頻度
SELECT word, ndoc, nentry
FROM ts_stat('SELECT document_tsv FROM documents')
WHERE word = '検索';
```

6. 設定確認クエリ

```sql
-- 利用可能なテキスト検索設定の確認
SELECT * FROM pg_ts_config;

-- テキスト検索設定のマッピング確認
SELECT * FROM pg_ts_config_map 
WHERE mapcfg = (SELECT oid FROM pg_ts_config WHERE cfgname = 'japanese');

-- インデックスサイズ確認
SELECT pg_size_pretty(pg_total_relation_size('documents_tsv_idx'));
```

7. メンテナンスクエリ

```sql
-- インデックスの再構築
REINDEX INDEX documents_tsv_idx;

-- 統計情報の更新
ANALYZE documents;

-- インデックス使用状況の確認
EXPLAIN ANALYZE 
SELECT * FROM documents 
WHERE document_tsv @@ to_tsquery('japanese', '検索語');
```

注意点

1. pg_bigmやtextsearch_jaなどの日本語対応モジュールのインストールが必要な場合があります
2. 日本語辞書の設定とメンテナンスが重要です
3. インデックスの更新タイミングを考慮する必要があります
4. 文字コードはUTF-8を使用してください

これらのクエリは基本的なものなので、実際の環境に合わせて調整してください。