# 已经存在了不需要创建了
# CREATE EXTENSION IF NOT EXISTS pgml;
CREATE EXTENSION IF NOT EXISTS vector;

\dx

CREATE TABLE articles (
    id SERIAL PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    summary TEXT,
    publication_date DATE,
    summary_embedding VECTOR(384),
    category VARCHAR(100),
    tags TEXT[],
    author VARCHAR(100),
    view_count INTEGER DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 为常用查询字段创建索引
CREATE INDEX idx_articles_title ON articles(title);
CREATE INDEX idx_articles_category ON articles(category);
CREATE INDEX idx_articles_publication_date ON articles(publication_date);
CREATE INDEX idx_articles_created_at ON articles(created_at);

-- 为向量字段创建索引（后续步骤中会详细说明）
-- CREATE INDEX idx_articles_embedding ON articles USING ivfflat (summary_embedding vector_cosine_ops) WITH (lists = 100);

INSERT INTO articles (title, summary, publication_date, category, tags, author) VALUES
('2024 年最佳无线耳机', '最佳无线耳机评测，重点关注音质、电池续航时间和降噪效果。', '2024-01-15', '电子产品', ARRAY['耳机', '音频', '评测'], '张三'),
('如何选择完美的智能手机', '根据摄像头、性能和预算选择智能手机的指南。', '2024-02-10', '电子产品', ARRAY['手机', '购买指南', '性能'], '李四'),
('适合各种地形的跑鞋', '公路跑、越野跑和马拉松跑顶级跑鞋对比。', '2024-03-05', '运动装备', ARRAY['跑鞋', '运动', '评测'], '王五'),
('2024 年值得关注的智能家居设备', '探索最新的智能家居设备，包括扬声器和恒温器。', '2024-04-20', '智能家居', ARRAY['智能家居', '物联网', '趋势'], '赵六'),
('最适合远程办公的笔记本电脑', '面向专业人士的具有长电池续航时间和快速处理器的笔记本电脑评测。', '2024-05-12', '电子产品', ARRAY['笔记本', '办公', '性能'], '钱七'),
('可穿戴技术的崛起', '智能手表和健身追踪器如何塑造消费趋势。', '2024-06-08', '可穿戴设备', ARRAY['智能手表', '健身', '趋势'], '孙八'),
('家用咖啡师的咖啡机', '可编程功能和时尚设计的咖啡机汇总。', '2024-07-14', '家电', ARRAY['咖啡机', '家电', '评测'], '周九'),
('竞技游戏显示器', '高刷新率和低延迟的顶级游戏显示器。', '2024-08-19', '游戏设备', ARRAY['显示器', '游戏', '性能'], '吴十'),
('环保时尚趋势', '引领市场的可持续鞋服品牌。', '2024-09-03', '时尚', ARRAY['环保', '时尚', '品牌'], '郑十一'),
('户外探险的蓝牙音箱', '防水设计和强劲音质的便携式音箱。', '2024-10-11', '音频设备', ARRAY['音箱', '户外', '防水'], '王十二'),
('5G技术及其影响', '5G 如何改变智能手机和物联网设备。', '2024-11-25', '技术趋势', ARRAY['5G', '物联网', '智能手机'], '李十三'),
('健康烹饪的最佳空气炸锅', '大容量智能空气炸锅评测。', '2024-12-07', '家电', ARRAY['空气炸锅', '烹饪', '智能'], '张十四'),
('无线充电详解', '深入了解手机和耳机的无线充电技术。', '2025-01-09', '技术', ARRAY['无线充电', '技术', '手机'], '王十五'),
('内容创作者的顶级相机', '视频和摄影方面无反相机和单反相机的比较。', '2025-02-14', '摄影设备', ARRAY['相机', '摄影', '创作'], '李十六'),
('家庭健身装备', '家庭健身必备的鞋子和追踪器等装备。', '2025-03-22', '健身', ARRAY['健身', '装备', '家庭'], '张十七'),
('电子商务技术的未来', '人工智能和增强现实技术正在塑造在线购物体验。', '2025-04-10', '电商', ARRAY['电商', 'AI', 'AR'], '王十八'),
('家庭影院便携式投影仪', '支持 4K 且易于设置的紧凑型投影仪。', '2025-04-28', '影音设备', ARRAY['投影仪', '4K', '家庭影院'], '李十九'),
('智能厨房电器', '智能烤箱和搅拌机等创新电器。', '2025-05-03', '家电', ARRAY['智能家电', '厨房', '创新'], '张二十'),
('降噪耳机指南', '具有卓越降噪功能的最佳旅行和工作耳机。', '2025-05-10', '音频设备', ARRAY['降噪', '耳机', '旅行'], '王二一'),
('可持续科技产品', '从充电器到耳机的环保科技产品。', '2025-05-14', '环保', ARRAY['环保', '科技', '可持续'], '李二二');

-- 查看所有文章
SELECT * FROM articles;

-- 按分类统计文章数量
SELECT category, COUNT(*) as article_count
FROM articles
GROUP BY category
ORDER BY article_count DESC;

-- 查看标签分布
SELECT unnest(tags) as tag, COUNT(*) as count
FROM articles
GROUP BY tag
ORDER BY count DESC;

-- 查看作者贡献
SELECT author, COUNT(*) as article_count
FROM articles
GROUP BY author
ORDER BY article_count DESC;

-- 检查是否有缺失的摘要
SELECT id, title
FROM articles
WHERE summary IS NULL OR summary = '';

-- 检查日期范围
SELECT MIN(publication_date) as earliest_date,
       MAX(publication_date) as latest_date
FROM articles;

-- 检查标题长度
SELECT id, title, LENGTH(title) as title_length
FROM articles
ORDER BY title_length DESC
LIMIT 5;

UPDATE articles
SET summary_embedding = pgml.embed('sentence-transformers/all-MiniLM-L6-v2', summary);

-- 分批处理，避免内存问题
WITH batch AS (
  SELECT id, summary
  FROM articles
  WHERE summary_embedding IS NULL
  LIMIT 10
)
UPDATE articles a
SET summary_embedding = pgml.embed('sentence-transformers/all-MiniLM-L6-v2', b.summary)
FROM batch b
WHERE a.id = b.id;

-- 检查已生成嵌入的文章数量
SELECT count(*) FROM articles WHERE summary_embedding IS NOT NULL;

-- 查看处理进度
SELECT
  COUNT(*) as total_articles,
  COUNT(CASE WHEN summary_embedding IS NOT NULL THEN 1 END) as embedded_articles,
  COUNT(CASE WHEN summary_embedding IS NULL THEN 1 END) as pending_articles,
  ROUND(COUNT(CASE WHEN summary_embedding IS NOT NULL THEN 1 END) * 100.0 / COUNT(*), 2) as progress_percentage
FROM articles;

-- 验证嵌入是否成功生成
SELECT id, title, summary_embedding IS NOT NULL AS has_embedding
FROM articles
LIMIT 5;

-- 检查嵌入向量维度
SELECT
    id,
    title,
    vector_dims(summary_embedding) AS embedding_dimension,
    summary_embedding IS NOT NULL AS has_embedding
FROM articles
WHERE summary_embedding IS NOT NULL
LIMIT 5;

-- 计算嵌入向量的统计信息
SELECT
    COUNT(*) AS vector_count,
    MIN(vector_dims(summary_embedding)) AS min_dimension,
    MAX(vector_dims(summary_embedding)) AS max_dimension,
    AVG(vector_dims(summary_embedding)) AS avg_dimension
FROM articles
WHERE summary_embedding IS NOT NULL;

-- 检查是否有异常向量
SELECT
    id,
    title,
    vector_dims(summary_embedding) AS dimension
FROM articles
WHERE summary_embedding IS NOT NULL
  AND vector_dims(summary_embedding) != 384;

-- 创建错误记录表
CREATE TABLE embedding_errors (
  id SERIAL PRIMARY KEY,
  article_id INTEGER,
  error_message TEXT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 重新生成失败的嵌入
UPDATE articles
SET summary_embedding = pgml.embed('sentence-transformers/all-MiniLM-L6-v2', summary)
WHERE summary_embedding IS NULL
  AND id IN (
    SELECT article_id FROM embedding_errors
  );

-- 记录成功情况
DELETE FROM embedding_errors
WHERE article_id IN (
  SELECT id FROM articles WHERE summary_embedding IS NOT NULL
);

-- 使用 BAAI/bge-small-en-v1.5 模型（支持中文）
UPDATE articles
SET summary_embedding = pgml.embed('BAAI/bge-small-en-v1.5', summary)
WHERE summary_embedding IS NULL;

-- 或者使用更高质量的模型
UPDATE articles
SET summary_embedding = pgml.embed('sentence-transformers/all-mpnet-base-v2', summary)
WHERE summary_embedding IS NULL;

-- 查看已缓存的模型
$ docker exec -it pgml_postgres ls -lh /var/lib/postgresql/.cache/huggingface/hub
total 4.0K
drwx------ 1 postgres postgres 54 Nov 22 12:57 models--mixedbread-ai--mxbai-embed-large-v1
drwx------ 1 postgres postgres 54 Nov 22 13:28 models--sentence-transformers--all-MiniLM-L6-v2
-rw------- 1 postgres postgres  1 Nov 22 12:56 version.txt

-- 清理缓存（谨慎使用）
$ docker exec -it pgml_postgres rm -rf /var/lib/postgresql/.cache/huggingface/*

-- 查找与"无线技术趋势"相关的文章
SELECT id, title, summary, publication_date
FROM articles
ORDER BY summary_embedding <=> pgml.embed('sentence-transformers/all-MiniLM-L6-v2', 'wireless technology trends')::vector
LIMIT 3;

-- 查找相似文章并显示相似度分数
SELECT
  id,
  title,
  summary,
  publication_date,
  1 - (summary_embedding <=> pgml.embed('sentence-transformers/all-MiniLM-L6-v2', '智能家电技术')::vector) as similarity_score
FROM articles
WHERE summary_embedding IS NOT NULL
ORDER BY similarity_score DESC
LIMIT 5;

-- 结合分类和语义搜索
SELECT
  id,
  title,
  summary,
  category,
  publication_date,
  1 - (summary_embedding <=> pgml.embed('sentence-transformers/all-MiniLM-L6-v2', '智能家居')::vector) as similarity_score
FROM articles
WHERE category IN ('智能家居', '家电')
  AND summary_embedding IS NOT NULL
ORDER BY similarity_score DESC
LIMIT 10;

-- 在特定时间范围内进行语义搜索
SELECT
  id,
  title,
  summary,
  publication_date,
  1 - (summary_embedding <=> pgml.embed('sentence-transformers/all-MiniLM-L6-v2', '最新科技趋势')::vector) as similarity_score
FROM articles
WHERE publication_date BETWEEN '2024-06-01' AND '2025-05-31'
  AND summary_embedding IS NOT NULL
ORDER BY similarity_score DESC
LIMIT 8;

-- 查找包含特定标签的相似文章
SELECT
  a.id,
  a.title,
  a.summary,
  a.tags,
  1 - (a.summary_embedding <=> pgml.embed('sentence-transformers/all-MiniLM-L6-v2', '游戏设备')::vector) as similarity_score
FROM articles a
WHERE a.summary_embedding IS NOT NULL
  AND EXISTS (
    SELECT 1 FROM unnest(a.tags) tag WHERE tag IN ('游戏', '性能', '显示器')
  )
ORDER BY similarity_score DESC
LIMIT 5;

-- 实现搜索结果分页
WITH search_results AS (
  SELECT
    id,
    title,
    summary,
    1 - (summary_embedding <=> pgml.embed('sentence-transformers/all-MiniLM-L6-v2', '环保科技')::vector) as similarity_score,
    ROW_NUMBER() OVER (ORDER BY 1 - (summary_embedding <=> pgml.embed('sentence-transformers/all-MiniLM-L6-v2', '环保科技')::vector) DESC) as row_num
  FROM articles
  WHERE summary_embedding IS NOT NULL
)
SELECT id, title, similarity_score
FROM search_results
WHERE row_num BETWEEN 6 AND 10;  -- 第二页，每页5条结果

-- 过滤低相似度的结果
SELECT
  id,
  title,
  summary,
  1 - (summary_embedding <=> pgml.embed('sentence-transformers/all-MiniLM-L6-v2', '人工智能技术')::vector) as similarity_score
FROM articles
WHERE summary_embedding IS NOT NULL
  AND 1 - (summary_embedding <=> pgml.embed('sentence-transformers/all-MiniLM-L6-v2', '人工智能技术')::vector) > 0.3  -- 只显示相似度大于30%的结果
ORDER BY similarity_score DESC
LIMIT 10;

-- 使用多个关键词进行综合语义搜索
WITH keyword_embeddings AS (
    SELECT
        pgml.embed('sentence-transformers/all-MiniLM-L6-v2', '智能手机')::vector AS embedding1,
        pgml.embed('sentence-transformers/all-MiniLM-L6-v2', '高性能')::vector AS embedding2
)
SELECT
    a.id,
    a.title,
    a.summary,
    (1 - (a.summary_embedding <=> k.embedding1)) +
    (1 - (a.summary_embedding <=> k.embedding2)) AS combined_score
FROM articles a, keyword_embeddings k
WHERE a.summary_embedding IS NOT NULL
ORDER BY combined_score DESC
LIMIT 10;

-- 分析搜索结果分布
WITH search_results AS (
  SELECT
    category,
    1 - (summary_embedding <=> pgml.embed('sentence-transformers/all-MiniLM-L6-v2', '科技创新')::vector) as similarity_score
  FROM articles
  WHERE summary_embedding IS NOT NULL
)
SELECT
  category,
  COUNT(*) as result_count,
  AVG(similarity_score) as avg_similarity,
  MAX(similarity_score) as max_similarity,
  MIN(similarity_score) as min_similarity
FROM search_results
GROUP BY category
ORDER BY result_count DESC;

-- 使用 CTE 优化复杂搜索
WITH query_embedding AS (
  SELECT pgml.embed('sentence-transformers/all-MiniLM-L6-v2', '人工智能和机器学习')::vector as query_vec
)
SELECT
  a.id,
  a.title,
  a.summary,
  1 - (a.summary_embedding <=> q.query_vec) as similarity_score
FROM articles a, query_embedding q
WHERE a.summary_embedding IS NOT NULL
ORDER BY similarity_score DESC
LIMIT 20;

-- 创建 IVFFlat 索引（适合大规模数据集）
CREATE INDEX idx_articles_embedding_ivfflat ON articles
USING ivfflat (summary_embedding vector_cosine_ops)
WITH (lists = 100);

-- 创建 HNSW 索引（适合高精度搜索）
CREATE INDEX idx_articles_embedding_hnsw ON articles
USING hnsw (summary_embedding vector_cosine_ops)
WITH (m = 16, ef_construction = 64);

-- IVFFlat 索引参数说明
-- lists: 聚类数量，通常为数据集大小的平方根
-- 对于 10,000 条记录，lists = 100 是合理的选择

-- HNSW 索引参数说明
-- m: 每个节点的最大连接数，通常 16-64
-- ef_construction: 构建时的搜索范围，通常 32-128
CREATE INDEX idx_articles_embedding_hnsw_optimized ON articles
USING hnsw (summary_embedding vector_cosine_ops)
WITH (m = 32, ef_construction = 128);

-- 查看索引信息
SELECT indexname, indexdef
FROM pg_indexes
WHERE tablename = 'articles';

-- 重建索引
REINDEX INDEX idx_articles_embedding_ivfflat;

-- 删除索引
DROP INDEX idx_articles_embedding_ivfflat;

-- 配置 PostgreSQL 参数
ALTER SYSTEM SET shared_preload_libraries = 'vector';
ALTER SYSTEM SET max_parallel_workers = 4;
ALTER SYSTEM SET max_parallel_workers_per_gather = 2;

-- 重启 PostgreSQL 使配置生效
-- 在容器内执行：pg_ctl restart

-- 查看当前配置
SHOW shared_preload_libraries;
SHOW max_parallel_workers;

-- 启用查询分析
EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)
SELECT id, title
FROM articles
ORDER BY summary_embedding <=> pgml.embed('sentence-transformers/all-MiniLM-L6-v2', '测试查询')::vector
LIMIT 10;

-- 查看查询执行计划
EXPLAIN
SELECT id, title
FROM articles
WHERE category = '电子产品'
ORDER BY summary_embedding <=> pgml.embed('sentence-transformers/all-MiniLM-L6-v2', '智能手机')::vector
LIMIT 5;

-- 调整 PostgreSQL 内存参数
ALTER SYSTEM SET shared_buffers = '256MB';
ALTER SYSTEM SET work_mem = '16MB';
ALTER SYSTEM SET maintenance_work_mem = '64MB';
ALTER SYSTEM SET effective_cache_size = '1GB';

-- 查看内存使用情况
SELECT name, setting, unit
FROM pg_settings
WHERE name IN ('shared_buffers', 'work_mem', 'maintenance_work_mem', 'effective_cache_size');

-- 按时间分区（适用于时间序列数据）
CREATE TABLE articles_partitioned (
    id SERIAL,
    title VARCHAR(255) NOT NULL,
    summary TEXT,
    publication_date DATE,
    summary_embedding VECTOR(384),
    category VARCHAR(100),
    tags TEXT[],
    author VARCHAR(100),
    view_count INTEGER DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) PARTITION BY RANGE (publication_date);

-- 创建分区
CREATE TABLE articles_2024 PARTITION OF articles_partitioned
    FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');

CREATE TABLE articles_2025 PARTITION OF articles_partitioned
    FOR VALUES FROM ('2025-01-01') TO ('2026-01-01');

-- 为分区表创建索引
CREATE INDEX idx_articles_2024_embedding ON articles_2024
USING ivfflat (summary_embedding vector_cosine_ops)
WITH (lists = 50);

-- 检查 PostgreSQL 版本
SELECT version();

-- 检查已安装的扩展
SELECT * FROM pg_available_extensions WHERE name IN ('pgml', 'vector');

-- 如果扩展不存在，尝试手动安装
-- 在容器内执行：
CREATE EXTENSION pgml;
CREATE EXTENSION vector;

-- 检查网络连接
-- 在容器内执行：
ping huggingface.co

-- 使用本地模型或镜像
UPDATE articles
SET summary_embedding = pgml.embed('BAAI/bge-small-en-v1.5', summary)
WHERE summary_embedding IS NULL;

-- 选择合适的索引类型
-- 小数据集（<10,000）：HNSW
-- 大数据集（>10,000）：IVFFlat

-- 定期维护索引
REINDEX INDEX idx_articles_embedding;
ANALYZE articles;

-- 使用 CTE 减少重复计算
WITH query_embedding AS (
  SELECT pgml.embed('model', 'query')::vector as vec
)
SELECT * FROM articles, query_embedding
WHERE summary_embedding <=> vec < 0.5
LIMIT 10;

-- 限制返回结果数量
SELECT * FROM articles
ORDER BY summary_embedding <=> query_vector
LIMIT 100;

-- 定期清理过期数据
DELETE FROM articles
WHERE created_at < CURRENT_DATE - INTERVAL '1 year';

-- 使用分区表管理大数据
CREATE TABLE articles_2024 PARTITION OF articles_partitioned
FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');

-- 创建测试数据
CREATE TABLE test_articles (LIKE articles INCLUDING ALL);

-- 编写测试查询
SELECT * FROM test_articles
WHERE summary_embedding IS NOT NULL
LIMIT 5;

-- 性能测试
EXPLAIN ANALYZE
SELECT * FROM test_articles
ORDER BY summary_embedding <=> query_vector
LIMIT 100;

-- 在容器内检查 PostgreSQL 状态
docker exec -it pgml_postgres bash
su - postgres
pg_isready
psql -c "SELECT version();"

-- 检查配置文件
cat /var/lib/postgresql/data/postgresql.conf | grep listen_addresses
cat /var/lib/postgresql/data/pg_hba.conf

-- 修改 PostgreSQL 配置
docker exec -it pgml_postgres bash
echo "listen_addresses = '*'" >> /var/lib/postgresql/data/postgresql.conf
echo "host all all 0.0.0.0/0 md5" >> /var/lib/postgresql/data/pg_hba.conf

su - postgres
pg_ctl reload
exit
exit

ERROR: could not open extension control file for "pgml": No such file or directory

-- 检查扩展可用性
SELECT * FROM pg_available_extensions WHERE name IN ('pgml', 'vector');

-- 检查 PostgreSQL 版本兼容性
SELECT version();

-- 检查安装路径
docker exec -it pgml_postgres bash
ls -la /usr/lib/postgresql/15/lib/
ls -la /usr/share/postgresql/15/extension/

-- 重新安装扩展（如果支持）
DROP EXTENSION IF EXISTS pgml;
DROP EXTENSION IF EXISTS vector;

-- 确保使用正确的 PostgreSQL 版本
CREATE EXTENSION IF NOT EXISTS pgml VERSION "2.10.0";
CREATE EXTENSION IF NOT EXISTS vector VERSION "0.5.0";

-- 选择合适的索引类型
-- 小数据集（< 10,000 条）：使用精确搜索
CREATE INDEX ON articles USING exact (summary_embedding vector_cosine_ops);

-- 中等数据集（10,000 - 1,000,000 条）：使用 IVFFlat
CREATE INDEX ON articles USING ivfflat (summary_embedding vector_cosine_ops)
WITH (lists = 100);

-- 大数据集（> 1,000,000 条）：使用 HNSW
CREATE INDEX ON articles USING hnsw (summary_embedding vector_cosine_ops)
WITH (m = 16, ef_construction = 64);

-- 部分索引：只为有嵌入的记录创建索引
CREATE INDEX articles_embedding_partial_idx ON articles (summary_embedding vector_cosine_ops)
WHERE summary_embedding IS NOT NULL;

-- 使用 EXPLAIN ANALYZE 分析查询
EXPLAIN ANALYZE
SELECT id, title, summary_embedding <=> pgml.embed('sentence-transformers/all-MiniLM-L6-v2', '查询文本')::vector as distance
FROM articles
ORDER BY distance
LIMIT 10;

-- 批量处理嵌入生成
-- 推荐：批量处理（性能好）
WITH batch AS (
    SELECT id, summary
    FROM articles
    WHERE summary_embedding IS NULL
    LIMIT 1000
)
UPDATE articles a
SET summary_embedding = pgml.embed('sentence-transformers/all-MiniLM-L6-v2', b.summary)
FROM batch b
WHERE a.id = b.id;

-- 使用 CTE 优化复杂查询
WITH search_embedding AS (
    SELECT pgml.embed('sentence-transformers/all-MiniLM-L6-v2', '查询文本')::vector as embedding
),
scored_articles AS (
    SELECT
        a.id,
        a.title,
        a.summary,
        1 - (a.summary_embedding <=> se.embedding) as score
    FROM articles a, search_embedding se
    WHERE a.summary_embedding IS NOT NULL
)
SELECT * FROM scored_articles
WHERE score > 0.5
ORDER BY score DESC
LIMIT 10;

-- 创建只读用户
CREATE USER ml_readonly WITH PASSWORD 'readonly_password';
GRANT CONNECT ON DATABASE postgresml TO ml_readonly;
GRANT USAGE ON SCHEMA public TO ml_readonly;
GRANT SELECT ON articles TO ml_readonly;

-- 创建数据写入用户
CREATE USER ml_writer WITH PASSWORD 'writer_password';
GRANT CONNECT ON DATABASE postgresml TO ml_writer;
GRANT USAGE ON SCHEMA public TO ml_writer;
GRANT SELECT, INSERT, UPDATE ON articles TO ml_writer;

-- 使用行级安全策略
ALTER TABLE articles ENABLE ROW LEVEL SECURITY;

-- 只允许用户看到自己的数据（如果适用）
CREATE POLICY user_articles_policy ON articles
    FOR ALL TO ml_writer
    USING (author = current_user);