卷呀卷，Hybrid 混合查询学习--哪个库是小趴菜

CREATE EXTENSION IF NOT EXISTS vector;
CREATE TABLE documents (
  id serial PRIMARY KEY,
  content text,
  content_tsv tsvector GENERATED ALWAYS AS (to_tsvector('english', content)) STORED,
  embedding vector(384)  -- 假设用 OpenAI 的 ada-002，384维
);
CREATE INDEX ON documents USING GIN(content_tsv);     -- 全文索引
CREATE INDEX ON documents USING hnsw (embedding vector_cosine_ops);  -- 向量近似搜索
-- 向量搜索结果
WITH vector_results AS (
  SELECT id, 1 / (1 + embedding <#> '[...]'::vector) AS score
  FROM documents
  ORDER BY embedding <#> '[...]'::vector
  LIMIT 10
),
-- 全文搜索结果
text_results AS (
  SELECT id, ts_rank(content_tsv, plainto_tsquery('postgresql')) AS score
  FROM documents
  WHERE content_tsv @@ plainto_tsquery('postgresql')
  ORDER BY score DESC
  LIMIT 10
),
-- 合并并重排序
combined AS (
  SELECT id, score, 'vector' AS source FROM vector_results
  UNION ALL
  SELECT id, score, 'text' AS source FROM text_results
)
SELECT id, SUM(score) AS hybrid_score
FROM combined
GROUP BY id
ORDER BY hybrid_score DESC
LIMIT 10;

{
  "_id": 1,
"title": "PostgreSQL Hybrid Table",
"content": "Hybrid tables connect PostgreSQL with external systems.",
"embedding": [0.123, 0.234, ..., 0.456]
}

{
"$search": {
    "compound": {
      "should": [
        {
          "text": {
            "query": "postgresql hybrid",
            "path": "content"
          }
        },
        {
          "knnBeta": {
            "vector": [...],
            "path": "embedding",
            "k": 10
          }
        }
      ]
    }
  }
}