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'  (matches docs with BOTH terms)

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

SELECT phraseto_tsquery('english', 'red shoes');
-- 'red' <-> 'shoe'  (matches docs where 'red' is immediately before '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'));

-- Search for "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);

-- Set weights: 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));

-- Query must use same config
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);

-- Query uses stored column
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  (keeps 'the', no stemming)

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

-- Pre-segment Chinese text with spaces at application layer
INSERT INTO catalog (name, description, search_vector)
VALUES (
  '红色跑鞋',
  '轻 量 红色 跑鞋 适合 跑步',
  to_tsvector('simple', '轻 量 红色 跑鞋 适合 跑步')
);

-- Search with segmented query
SELECT name FROM catalog
WHERE search_vector @@ to_tsquery('simple', '红色 & 跑鞋');

INSERT 0 1

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

-- English config for English content, simple for Chinese
UPDATE articles SET
  fts_en = to_tsvector('english', COALESCE(title, '') || ' ' || COALESCE(content, '')),
  fts_zh = to_tsvector('simple', COALESCE(title, '') || ' ' || COALESCE(content, ''));

-- Search English
SELECT title FROM articles
WHERE fts_en @@ plainto_tsquery('english', 'database optimization');

-- Search Chinese (application-segmented)
SELECT title FROM articles
WHERE fts_zh @@ to_tsquery('simple', '数据 & 优化');

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)

-- Create test data
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 query (slow, no index support for leading wildcard)
EXPLAIN ANALYZE
SELECT name FROM catalog WHERE description LIKE '%great features%';

-- Full-text search with GIN index (fast)
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

-- Step 1: Create products table with pre-computed 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
);

-- Step 2: Insert sample data
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);

-- Step 3: Create GIN index on generated column
CREATE INDEX idx_shop_products_search ON shop_products USING gin (search_doc);

-- Step 4: Search function with ranking, highlighting and pagination
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;

-- Step 5: Test search
SELECT * FROM search_products('red shoes');
SELECT * FROM search_products('"running shoes"');
SELECT * FROM search_products('running -shoes');

-- Step 6: Search with category filter
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;