PostgreSQL: PostgreSQL全文検索エンジン

SELECT * FROM ts_debug('english', 'The red shoes are beautiful');

 alias | descriptor | token  |  dictionaries  | dictionary | lexemes 
-------+------------+--------+----------------+------------+---------
 ascii | word       | The    | {english_stem}  | english_stem| {}
 ascii | word       | red    | {english_stem}  | english_stem| {red}
 ascii | word       | shoes  | {english_stem}  | english_stem| {shoe}
 ascii | word       | are    | {english_stem}  | english_stem| {}
 ascii | word       | beautiful | {english_stem}| english_stem| {beauti}

SELECT to_tsvector('english', 'The red shoes are beautiful shoes');

'beauti':5 'red':2 'shoe':3,6

SELECT to_tsquery('english', 'red & shoe');
-- 'red' & 'shoe'

SELECT to_tsquery('english', 'red | blue');
-- 'red' | 'blue'

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

SELECT plainto_tsquery('english', 'red shoes');
-- 'red' & 'shoe'（両方の語を含むドキュメントに一致）

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

SELECT phraseto_tsquery('english', 'red shoes');
-- 'red' <-> 'shoe'（'red'が'shoe'の直前に来るドキュメントに一致）

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

SELECT websearch_to_tsquery('english', '"red shoes" -broken OR new');
-- 'red' <-> 'shoe' & !'broken' | 'new'

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

CREATE TABLE catalog (
  product_id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  description TEXT,
  search_vector tsvector
);

INSERT INTO catalog (name, description, search_vector) VALUES
  ('Red Running Shoes', 'Lightweight red shoes for running', 
   to_tsvector('english', 'Lightweight red shoes for running')),
  ('Blue Casual Shoes', 'Comfortable blue shoes for daily wear',
   to_tsvector('english', 'Comfortable blue shoes for daily wear')),
  ('Red Dress', 'Elegant red dress for special occasions',
   to_tsvector('english', 'Elegant red dress for special occasions'));

-- "red shoes"を検索
SELECT name, description
FROM catalog
WHERE search_vector @@ plainto_tsquery('english', 'red shoes');

INSERT 0 1

SELECT name,
       ts_rank(search_vector, plainto_tsquery('english', 'red shoes')) AS rank
FROM catalog
WHERE search_vector @@ plainto_tsquery('english', 'red shoes')
ORDER BY rank DESC;

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

ALTER TABLE catalog ADD COLUMN title_vector tsvector;

UPDATE catalog SET title_vector = to_tsvector('english', name);

-- 重みを設定：title=D(default)、body=A(highest)
SELECT name,
       ts_rank(
         setweight(title_vector, 'A') || setweight(search_vector, 'B'),
         plainto_tsquery('english', 'red shoes')
       ) AS rank
FROM catalog
WHERE setweight(title_vector, 'A') || setweight(search_vector, 'B')
      @@ plainto_tsquery('english', 'red shoes')
ORDER BY rank DESC;

UPDATE 3

SELECT name,
       ts_headline('english', description, plainto_tsquery('english', 'red shoes')) AS highlighted
FROM catalog
WHERE search_vector @@ plainto_tsquery('english', 'red shoes');

 name              | highlighted
-------------------+-------------------------------------------------
 Red Running Shoes | Lightweight <b>red</b> <b>shoes</b> for running

SELECT ts_headline(
  'english',
  description,
  plainto_tsquery('english', 'red shoes'),
  'StartSel=<em>,StopSel=</em>,MaxWords=10,MinWords=5'
) AS highlighted
FROM catalog
WHERE search_vector @@ plainto_tsquery('english', 'red shoes');

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

CREATE INDEX idx_catalog_desc_fts
ON catalog USING gin (to_tsvector('english', description));

-- クエリは同じ設定を使用する必要あり
SELECT name FROM catalog
WHERE to_tsvector('english', description) @@ plainto_tsquery('english', 'red shoes');

CREATE TABLE

CREATE INDEX idx_catalog_search_fts ON catalog USING gin (search_vector);

-- クエリは格納列を使用
SELECT name FROM catalog
WHERE search_vector @@ plainto_tsquery('english', 'red shoes');

CREATE TABLE

CREATE FUNCTION catalog_search_vector_update() RETURNS trigger AS $$
BEGIN
  NEW.search_vector :=
    setweight(to_tsvector('english', COALESCE(NEW.name, '')), 'A') ||
    setweight(to_tsvector('english', COALESCE(NEW.description, '')), 'B');
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_catalog_search
BEFORE INSERT OR UPDATE OF name, description ON catalog
FOR EACH ROW EXECUTE FUNCTION catalog_search_vector_update();

INSERT 0 1

SELECT to_tsvector('simple', 'The red shoes');
-- 'red':2 'shoes':3 'the':1（'the'を保持、ステミングなし）

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

-- アプリケーション層で中国語テキストをスペースで事前分割
INSERT INTO catalog (name, description, search_vector)
VALUES (
  'Red Running Shoes',
  'light red running shoes for jogging',
  to_tsvector('simple', 'light red running shoes for jogging')
);

-- 分割されたクエリで検索
SELECT name FROM catalog
WHERE search_vector @@ to_tsquery('simple', 'red & shoes');

INSERT 0 1

CREATE TABLE articles (
  article_id SERIAL PRIMARY KEY,
  title TEXT NOT NULL,
  content TEXT,
  fts_en tsvector,
  fts_zh tsvector
);

-- 英語内容にはenglish設定、中国語にはsimpleを使用
UPDATE articles SET
  fts_en = to_tsvector('english', COALESCE(title, '') || ' ' || COALESCE(content, '')),
  fts_zh = to_tsvector('simple', COALESCE(title, '') || ' ' || COALESCE(content, ''));

-- 英語検索
SELECT title FROM articles
WHERE fts_en @@ plainto_tsquery('english', 'database optimization');

-- 中国語検索（アプリケーション層で分割）
SELECT title FROM articles
WHERE fts_zh @@ to_tsquery('simple', 'data & optimization');

UPDATE 3

SELECT word, ndoc, nentry
FROM ts_stat(
  'SELECT search_vector FROM catalog'
)
ORDER BY nentry DESC
LIMIT 10;

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

SELECT cfgname FROM pg_ts_config;

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

SELECT dictname, dictnamespace FROM pg_ts_dict;

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

-- テストデータを作成
INSERT INTO catalog (name, description, search_vector)
SELECT
  'Product ' || i,
  'High quality product number ' || i || ' with great features and amazing design',
  to_tsvector('english', 'High quality product number ' || i || ' with great features and amazing design')
FROM generate_series(1, 100000) AS i;

-- LIKEクエリ（遅い、先頭ワイルドカードではインデックスサポートなし）
EXPLAIN ANALYZE
SELECT name FROM catalog WHERE description LIKE '%great features%';

-- GINインデックス付き全文検索（高速）
CREATE INDEX idx_catalog_fts ON catalog USING gin (search_vector);
EXPLAIN ANALYZE
SELECT name FROM catalog
WHERE search_vector @@ plainto_tsquery('english', 'great features');

INSERT 0 1

-- ステップ1: 事前計算tsvector付きの商品テーブルを作成
CREATE TABLE shop_products (
  product_id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  category TEXT NOT NULL,
  description TEXT,
  price NUMERIC(10,2) NOT NULL,
  search_doc tsvector GENERATED ALWAYS AS (
    setweight(to_tsvector('english', COALESCE(name, '')), 'A') ||
    setweight(to_tsvector('english', COALESCE(category, '')), 'B') ||
    setweight(to_tsvector('english', COALESCE(description, '')), 'C')
  ) STORED
);

-- ステップ2: サンプルデータを挿入
INSERT INTO shop_products (name, category, description, price) VALUES
  ('Red Running Shoes', 'Shoes', 'Lightweight red running shoes with cushioned sole', 89.99),
  ('Blue Casual Shoes', 'Shoes', 'Comfortable blue shoes for everyday casual wear', 59.99),
  ('Red Leather Dress Shoes', 'Shoes', 'Premium red leather dress shoes for formal events', 149.99),
  ('Red Silk Dress', 'Dresses', 'Elegant red silk dress for special occasions', 199.99),
  ('Running Watch', 'Accessories', 'GPS running watch with heart rate monitor', 249.99),
  ('Running Shorts', 'Clothing', 'Breathable running shorts with pockets', 39.99);

-- ステップ3: 生成列にGINインデックスを作成
CREATE INDEX idx_shop_products_search ON shop_products USING gin (search_doc);

-- ステップ4: ランキング、ハイライト表示、ページネーション付き検索関数
CREATE FUNCTION search_products(
  p_query TEXT,
  p_limit INT DEFAULT 20,
  p_offset INT DEFAULT 0
) RETURNS TABLE (
  product_id INT,
  name TEXT,
  category TEXT,
  price NUMERIC,
  rank REAL,
  headline TEXT
) AS $$
BEGIN
  RETURN QUERY
  SELECT
    s.product_id,
    s.name,
    s.category,
    s.price,
    ts_rank(s.search_doc, websearch_to_tsquery('english', p_query)) AS rank,
    ts_headline(
      'english',
      COALESCE(s.name, '') || '. ' || COALESCE(s.description, ''),
      websearch_to_tsquery('english', p_query),
      'StartSel=<mark>,StopSel=</mark>,MaxWords=20,MinWords=5'
    ) AS headline
  FROM shop_products s
  WHERE s.search_doc @@ websearch_to_tsquery('english', p_query)
  ORDER BY rank DESC
  LIMIT p_limit
  OFFSET p_offset;
END;
$$ LANGUAGE plpgsql;

-- ステップ5: 検索をテスト
SELECT * FROM search_products('red shoes');
SELECT * FROM search_products('"running shoes"');
SELECT * FROM search_products('running -shoes');

-- ステップ6: カテゴリフィルター付き検索
SELECT name, price,
       ts_rank(search_doc, plainto_tsquery('english', 'red')) AS rank
FROM shop_products
WHERE search_doc @@ plainto_tsquery('english', 'red')
  AND category = 'Shoes'
ORDER BY rank DESC;