PostgreSQL用BM25全文検索拡張がv1.0.0でプロダクション化—Timescale開発

-- テーブル作成
CREATE TABLE documents (
  id bigserial PRIMARY KEY,
  content text
);

-- インデックス作成
CREATE INDEX docs_idx ON documents 
  USING bm25(content) 
  WITH (text_config = 'english');

-- 検索実行（<@> オペレータ使用）
SELECT * FROM documents 
  ORDER BY content <@> 'database system' 
  LIMIT 5;

SELECT * FROM documents 
  WHERE content <@> to_bm25query('database system', 'docs_idx') < -1.0
  ORDER BY content <@> 'database system'
  LIMIT 10;

CREATE INDEX docs_idx ON documents 
  USING bm25(content) 
  WITH (text_config = 'english');

CREATE INDEX ON documents (category_id);
SELECT * FROM documents 
  WHERE category_id = 123
  ORDER BY content <@> 'search terms' 
  LIMIT 10;

SELECT * FROM documents 
  WHERE content <@> to_bm25query('search terms', 'docs_idx') < -5.0
  ORDER BY content <@> 'search terms'
  LIMIT 10;

-- 並列ワーカー数を指定（デフォルト2）
SET max_parallel_maintenance_workers = 4;
SET maintenance_work_mem = '256MB';  -- 並列構築には最低64MB必須

-- インデックス構築
CREATE INDEX docs_idx ON documents 
  USING bm25(content) 
  WITH (text_config = 'english');

-- BM25で候補を取得、サブクエリでフレーズ検索
SELECT * FROM (
  SELECT *, content <@> 'database system' AS score 
  FROM documents 
  ORDER BY score 
  LIMIT 100  -- 過度にフェッチしてフィルタ損失に備える
) sub 
WHERE content ILIKE '%database system%' 
ORDER BY score 
LIMIT 10;

ALTER TABLE documents 
  ADD COLUMN search_text text 
  GENERATED ALWAYS AS (
    COALESCE(title, '') || ' ' || COALESCE(content, '')
  ) STORED;

CREATE INDEX ON documents USING bm25(search_text) 
  WITH (text_config = 'english');

-- 単一パーティション検索（推奨）：スコア正確
SELECT * FROM docs 
  WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01'
  ORDER BY content <@> 'search terms' 
  LIMIT 10;

-- クロスパーティション検索：パーティション別に独立計算
SELECT * FROM docs 
  ORDER BY content <@> 'search terms' 
  LIMIT 10;

CREATE TABLE articles (
  id serial PRIMARY KEY,
  title text,
  content text,
  category text,
  published_at timestamp
);

CREATE INDEX articles_content_idx ON articles 
  USING bm25(content) 
  WITH (text_config = 'english');

CREATE INDEX ON articles (category);  -- プリフィルタリング用

-- テストデータ
INSERT INTO articles (title, content, category) VALUES
  ('Database Systems', 
   'PostgreSQL is a powerful relational database system', 
   'technology'),
  ('Search Technology', 
   'Full text search enables finding relevant documents quickly', 
   'technology'),
  ('Information Retrieval', 
   'BM25 is a ranking function used in search engines', 
   'academic');

-- 検索例：テクノロジーカテゴリから"database search"に関連する記事
SELECT 
  title, 
  content <@> 'database search' AS relevance_score
FROM articles 
WHERE category = 'technology'
ORDER BY relevance_score
LIMIT 5;

EXPLAIN SELECT * FROM documents 
  ORDER BY content <@> 'database system' 
  LIMIT 5;

SET enable_seqscan = off;

SELECT 
  schemaname, 
  tablename, 
  indexname, 
  idx_scan, 
  idx_tup_read, 
  idx_tup_fetch
FROM pg_stat_user_indexes 
WHERE indexrelid::regclass::text ~ 'pg_textsearch';