# 1. 数据库设计
CREATE TABLE fgedu_cms_articles (
id SERIAL PRIMARY KEY,
title VARCHAR(255) NOT NULL,
content TEXT NOT NULL,
author VARCHAR(100) NOT NULL,
category VARCHAR(100) NOT NULL,
tags VARCHAR(255),
status VARCHAR(20) DEFAULT ‘published’,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
search_vector tsvector
);

# 2. 创建索引
CREATE INDEX idx_fgedu_cms_articles_search_vector ON fgedu_cms_articles USING GIN (search_vector);
CREATE INDEX idx_fgedu_cms_articles_category ON fgedu_cms_articles (category);
CREATE INDEX idx_fgedu_cms_articles_status ON fgedu_cms_articles (status);

# 3. 创建触发器
CREATE OR REPLACE FUNCTION fgedu_cms_articles_search_vector_update()
RETURNS TRIGGER AS $$
BEGIN
NEW.search_vector :=
setweight(to_tsvector(‘english’, NEW.title), ‘A’) ||
setweight(to_tsvector(‘english’, NEW.content), ‘B’) ||
setweight(to_tsvector(‘english’, NEW.author), ‘C’) ||
setweight(to_tsvector(‘english’, NEW.tags), ‘D’);
NEW.updated_at := CURRENT_TIMESTAMP;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER fgedu_cms_articles_search_vector_update
BEFORE INSERT OR UPDATE ON fgedu_cms_articles
FOR EACH ROW EXECUTE FUNCTION fgedu_cms_articles_search_vector_update();

# 4. 插入测试数据
INSERT INTO fgedu_cms_articles (title, content, author, category, tags)
VALUES
(‘PostgreSQL全文检索实战’, ‘本文介绍PostgreSQL全文检索的实战应用…’, ‘风哥’, ‘数据库’, ‘PostgreSQL,全文检索,数据库’),
(‘PostgreSQL性能优化技巧’, ‘本文介绍PostgreSQL性能优化的各种技巧…’, ‘风哥’, ‘数据库’, ‘PostgreSQL,性能优化,数据库’),
(‘PostgreSQL备份与恢复策略’, ‘本文介绍PostgreSQL备份与恢复的最佳策略…’, ‘风哥’, ‘数据库’, ‘PostgreSQL,备份,恢复’),
(‘MySQL与PostgreSQL对比’, ‘本文对比MySQL与PostgreSQL的优缺点…’, ‘风哥’, ‘数据库’, ‘MySQL,PostgreSQL,对比’),
(‘PostgreSQL高可用架构’, ‘本文介绍PostgreSQL高可用架构的设计…’, ‘风哥’, ‘数据库’, ‘PostgreSQL,高可用,架构’);

# 5. 搜索示例
— 基本搜索
SELECT id, title, author, category
FROM fgedu_cms_articles
WHERE search_vector @@ to_tsquery(‘english’, ‘PostgreSQL’)
AND status = ‘published’
ORDER BY created_at DESC;

— 高级搜索（带排名）
SELECT id, title, author, category,
ts_rank(search_vector, to_tsquery(‘english’, ‘PostgreSQL & 性能’)) AS rank
FROM fgedu_cms_articles
WHERE search_vector @@ to_tsquery(‘english’, ‘PostgreSQL & 性能’)
AND status = ‘published’
ORDER BY rank DESC, created_at DESC;

— 按类别搜索
SELECT id, title, author, category
FROM fgedu_cms_articles
WHERE search_vector @@ to_tsquery(‘english’, ‘备份’)
AND category = ‘数据库’
AND status = ‘published’
ORDER BY created_at DESC;

— 前缀搜索
SELECT id, title, author, category
FROM fgedu_cms_articles
WHERE search_vector @@ to_tsquery(‘english’, ‘Postg:*’)
AND status = ‘published’
ORDER BY created_at DESC;

# 1. 数据库设计
CREATE TABLE fgedu_products (
id SERIAL PRIMARY KEY,
sku VARCHAR(50) UNIQUE NOT NULL,
name VARCHAR(255) NOT NULL,
description TEXT NOT NULL,
price DECIMAL(10, 2) NOT NULL,
category VARCHAR(100) NOT NULL,
brand VARCHAR(100) NOT NULL,
tags VARCHAR(255),
stock INTEGER NOT NULL,
status VARCHAR(20) DEFAULT ‘active’,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
search_vector tsvector
);

# 2. 创建索引
CREATE INDEX idx_fgedu_products_search_vector ON fgedu_products USING GIN (search_vector);
CREATE INDEX idx_fgedu_products_category ON fgedu_products (category);
CREATE INDEX idx_fgedu_products_brand ON fgedu_products (brand);
CREATE INDEX idx_fgedu_products_price ON fgedu_products (price);
CREATE INDEX idx_fgedu_products_status ON fgedu_products (status);

# 3. 创建触发器
CREATE OR REPLACE FUNCTION fgedu_products_search_vector_update()
RETURNS TRIGGER AS $$
BEGIN
NEW.search_vector :=
setweight(to_tsvector(‘english’, NEW.name), ‘A’) ||
setweight(to_tsvector(‘english’, NEW.description), ‘B’) ||
setweight(to_tsvector(‘english’, NEW.brand), ‘C’) ||
setweight(to_tsvector(‘english’, NEW.tags), ‘D’);
NEW.updated_at := CURRENT_TIMESTAMP;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER fgedu_products_search_vector_update
BEFORE INSERT OR UPDATE ON fgedu_products
FOR EACH ROW EXECUTE FUNCTION fgedu_products_search_vector_update();

# 4. 插入测试数据
INSERT INTO fgedu_products (sku, name, description, price, category, brand, tags, stock)
VALUES
(‘PROD001’, ‘Smartphone’, ‘Latest smartphone with advanced features…’, 999.99, ‘Electronics’, ‘Apple’, ‘smartphone,electronics,Apple’, 100),
(‘PROD002’, ‘Laptop’, ‘High performance laptop for professionals…’, 1299.99, ‘Electronics’, ‘Dell’, ‘laptop,electronics,Dell’, 50),
(‘PROD003’, ‘T-Shirt’, ‘Comfortable cotton t-shirt…’, 29.99, ‘Clothing’, ‘Nike’, ‘t-shirt,clothing,Nike’, 200),
(‘PROD004’, ‘Running Shoes’, ‘Lightweight running shoes for athletes…’, 129.99, ‘Shoes’, ‘Nike’, ‘running shoes,sports,Nike’, 150),
(‘PROD005’, ‘Smartwatch’, ‘Fitness tracking smartwatch…’, 299.99, ‘Electronics’, ‘Apple’, ‘smartwatch,fitness,Apple’, 75);

# 5. 搜索示例
— 基本产品搜索
SELECT id, sku, name, price, brand
FROM fgedu_products
WHERE search_vector @@ to_tsquery(‘english’, ‘smartphone’)
AND status = ‘active’
AND stock > 0
ORDER BY price ASC;

— 高级搜索（带排名和过滤）
SELECT id, sku, name, price, brand,
ts_rank(search_vector, to_tsquery(‘english’, ‘Apple & smart’)) AS rank
FROM fgedu_products
WHERE search_vector @@ to_tsquery(‘english’, ‘Apple & smart’)
AND status = ‘active’
AND stock > 0
AND price < 500 ORDER BY rank DESC, price ASC; -- 按类别搜索 SELECT id, sku, name, price, brand FROM fgedu_products WHERE search_vector @@ to_tsquery('english', 'running') AND category = 'Shoes' AND status = 'active' AND stock > 0
ORDER BY price ASC;

— 前缀搜索
SELECT id, sku, name, price, brand
FROM fgedu_products
WHERE search_vector @@ to_tsquery(‘english’, ‘lap:*’)
AND status = ‘active’
AND stock > 0
ORDER BY price ASC;

# 1. 选择合适的索引类型
— GIN索引（推荐）
CREATE INDEX idx_fgedu_articles_search_vector ON fgedu_articles USING GIN (search_vector);

— GiST索引（适用于更新频繁的场景）
CREATE INDEX idx_fgedu_articles_search_vector_gist ON fgedu_articles USING GiST (search_vector);

# 2. 索引维护
— 定期重建索引
REINDEX INDEX idx_fgedu_articles_search_vector;

— 分析表以更新统计信息
ANALYZE fgedu_articles;

# 3. 部分索引
— 只为活跃数据创建索引
CREATE INDEX idx_fgedu_articles_search_vector_active ON fgedu_articles USING GIN (search_vector)
WHERE status = ‘active’;

# 4. 复合索引
— 结合其他条件创建复合索引
CREATE INDEX idx_fgedu_articles_category_search ON fgedu_articles (category) INCLUDE (search_vector);

# 5. 表达式索引
— 直接基于表达式创建索引
CREATE INDEX idx_fgedu_articles_search_expr ON fgedu_articles USING GIN (
to_tsvector(‘english’, title || ‘ ‘ || content)
);

# 1. 简化查询
— 避免复杂的查询表达式
— 好的查询
SELECT * FROM fgedu_articles WHERE search_vector @@ to_tsquery(‘english’, ‘PostgreSQL’);

— 不好的查询
SELECT * FROM fgedu_articles WHERE search_vector @@ to_tsquery(‘english’, ‘PostgreSQL & (performance | optimization) & !backup’);

# 2. 使用适当的查询操作符
— 使用&（AND）、|（OR）、!（NOT）操作符
— 合理组合查询条件

# 3. 限制结果集
— 使用LIMIT限制返回结果
SELECT * FROM fgedu_articles
WHERE search_vector @@ to_tsquery(‘english’, ‘PostgreSQL’)
LIMIT 10;

# 4. 利用排名进行排序
— 使用ts_rank进行相关性排序
SELECT * FROM fgedu_articles
WHERE search_vector @@ to_tsquery(‘english’, ‘PostgreSQL’)
ORDER BY ts_rank(search_vector, to_tsquery(‘english’, ‘PostgreSQL’)) DESC
LIMIT 10;

# 5. 避免全表扫描
— 确保查询使用索引
— 检查执行计划
EXPLAIN ANALYZE SELECT * FROM fgedu_articles WHERE search_vector @@ to_tsquery(‘english’, ‘PostgreSQL’);

# 6. 使用物化视图
— 对于频繁的复杂查询，使用物化视图
CREATE MATERIALIZED VIEW fgedu_articles_search AS
SELECT id, title, content, search_vector
FROM fgedu_articles
WHERE status = ‘active’;

— 创建索引
CREATE INDEX idx_fgedu_articles_search_mv ON fgedu_articles_search USING GIN (search_vector);

— 定期刷新物化视图
REFRESH MATERIALIZED VIEW fgedu_articles_search;

# 1. 压缩存储
— PostgreSQL会自动压缩tsvector数据
— 可以通过pg_column_size查看存储大小
SELECT id, title, pg_column_size(search_vector) AS size
FROM fgedu_articles
ORDER BY size DESC;

# 2. 分区表
— 对于大量数据，使用分区表
CREATE TABLE fgedu_articles_partitioned (
id SERIAL,
title VARCHAR(255) NOT NULL,
content TEXT NOT NULL,
search_vector tsvector,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)
PARTITION BY RANGE (created_at);

— 创建分区
CREATE TABLE fgedu_articles_2026_q1 PARTITION OF fgedu_articles_partitioned
FOR VALUES FROM (‘2026-01-01’) TO (‘2026-04-01’);

CREATE TABLE fgedu_articles_2026_q2 PARTITION OF fgedu_articles_partitioned
FOR VALUES FROM (‘2026-04-01’) TO (‘2026-07-01’);

— 在每个分区上创建索引
CREATE INDEX idx_fgedu_articles_2026_q1_search ON fgedu_articles_2026_q1 USING GIN (search_vector);
CREATE INDEX idx_fgedu_articles_2026_q2_search ON fgedu_articles_2026_q2 USING GIN (search_vector);

# 3. 批量更新
— 对于大量数据的更新，使用批量操作
— 避免单行更新的开销
UPDATE fgedu_articles
SET search_vector = to_tsvector(‘english’, title || ‘ ‘ || content)
WHERE id BETWEEN 1 AND 1000;

# 4. 减少不必要的字段
— 只存储必要的文本数据
— 避免在search_vector中包含无关内容

# 2. 搜索性能慢
# 问题：搜索查询执行缓慢
# 解决方法：
– 创建适当的索引（GIN索引）
– 优化查询语句
– 增加内存配置
– 使用物化视图

# 3. 索引膨胀
# 问题：全文检索索引占用空间过大
# 解决方法：
– 定期重建索引
– 使用部分索引
– 优化tsvector的大小

# 4. 中文分词问题
# 问题：中文搜索效果不佳
# 解决方法：
– 安装pg_jieba或zhparser扩展
– 配置中文文本搜索配置
– 调整分词参数

# 5. 内存使用过高
# 问题：全文检索查询使用大量内存
# 解决方法：
– 限制结果集大小
– 优化查询语句
– 调整work_mem参数
– 使用游标处理大结果集

# 1. 监控索引大小
SELECT indexrelid::regclass AS index_name,
pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_fgedu_indexes
WHERE indexrelid::regclass::text LIKE ‘%search_vector%’
ORDER BY pg_relation_size(indexrelid) DESC;

# 2. 监控查询性能
SELECT query, calls, total_exec_time, mean_exec_time
FROM pg_stat_statements
WHERE query LIKE ‘%@@%’ OR query LIKE ‘%to_tsquery%’
ORDER BY total_exec_time DESC
LIMIT 10;

# 3. 监控索引使用情况
SELECT indexrelid::regclass AS index_name,
relid::regclass AS table_name,
idx_scan,
idx_tup_read,
idx_tup_fetch
FROM pg_stat_fgedu_indexes
JOIN pg_stat_fgedu_tables ON pg_stat_fgedu_indexes.relid = pg_stat_fgedu_tables.relid
WHERE indexrelid::regclass::text LIKE ‘%search_vector%’
ORDER BY idx_scan DESC;

# 4. 监控tsvector大小
SELECT avg(pg_column_size(search_vector)) AS avg_size,
max(pg_column_size(search_vector)) AS max_size,
min(pg_column_size(search_vector)) AS min_size
FROM fgedu_articles;

# 5. 监控分词性能
— 使用EXPLAIN分析分词性能
EXPLAIN ANALYZE SELECT to_tsvector(‘english’, title || ‘ ‘ || content) FROM fgedu_articles;