Hybrid search

Hybrid search

Merge the results from both query methods to perform multi-channel recall.

WITH t AS (
SELECT chunk, embedding <-> polar_ai.ai_text_embedding('PolarDB有哪些产品系列')::vector(1536) as dist
FROM t_chunk
ORDER by dist ASC
limit 5 ),
t2 as (
  SELECT chunk, v <=> to_tsquery('jiebacfg', 'PolarDB|PostgreSQL版|快速') as rank
FROM t_chunk 
WHERE v @@ to_tsquery('jiebacfg', 'PolarDB|PostgreSQL版|快速')
ORDER by rank ASC
LIMIT 5
)
SELECT * FROM t
UNION ALL
SELECT * FROM t2;

Because the distance calculation methods for these two search types are different, their scores cannot be directly compared. To solve this, you can use Reciprocal Rank Fusion (RRF) to combine and re-rank the results. RRF merges multiple result sets from different search methods into a single list. It does not require tuning and produces high-quality results even when the relevance metrics of the different methods are not correlated. The basic steps are as follows:

1. Collect ranked lists

   Multiple retrievers (each representing a recall channel) generate separate ranked lists of results for a given query.

2. Fuse ranks

   RRF uses a simple scoring function to combine the ranks from each list. The RRF score for each document is calculated using the following formula:

   $$RRF\_score(d)=\sum _{i=1}^N\frac{1}{k+ran{k}_i(d)}$$

   Where $$N$$ is the number of different recall paths, $$ran{k}_i(d)$$ is retriever $$i$$'s rank for document $$d$$, and $$k$$ is a smoothing parameter, typically set to 60.

3. Re-rank results

   Re-rank the documents based on their combined RRF scores to produce the final result list.

In this query, if you are not satisfied with the result order, adjust the parameter $$k$$ to change it. The system combines results from a full-text search and a vector search. Based on the $$k$$ parameter passed in the query, the system retrieves the $$topK$$ results from each search. It then scores each returned document using the formula $$\frac{1}{k+rank(i)}$$. In this formula, $$rank(i)$$ is the document's rank at position $$i$$. If a document from the full-text $$topK$$ results does not appear in the vector search $$topK$$ results, it receives a single score. The same applies to documents that appear only in the vector search results. If a document appears in the $$topK$$ result sets of both searches, their scores are added together.

-- 密集向量召回
WITH t1 as 
(
SELECT chunk, embedding <-> polar_ai.ai_text_embedding('PolarDB有哪些产品系列')::vector(1536) as dist
FROM t_chunk
ORDER by dist ASC
limit 5
),
t2 as (
SELECT ROW_NUMBER() OVER (ORDER BY dist ASC) AS row_num,
chunk
FROM t1
),
-- 稀疏向量召回
t3 as 
(
  SELECT chunk, v <=> to_tsquery('jiebacfg', 'PolarDB|PostgreSQL版|快速') as rank
  FROM t_chunk 
  WHERE v @@ to_tsquery('jiebacfg', 'PolarDB|PostgreSQL版|快速')
  ORDER by rank ASC
  LIMIT 5
),
t4 as (  
SELECT ROW_NUMBER() OVER (ORDER BY rank DESC) AS row_num,
chunk
FROM t3
),
-- Calculate RRF scores separately
t5 AS (
SELECT 1.0/(60+row_num) as score, chunk FROM t2
UNION ALL 
SELECT 1.0/(60+row_num), chunk FROM t4
)
-- 评分进行合并
SELECT sum(score) as score, chunk
FROM t5
GROUP BY chunk
ORDER BY score DESC;

Apply weights

You can also assign different weights to each result set. For example, you can assign a weight of 0.8 to the dense search results and 0.2 to the sparse search results.

-- 密集向量召回
WITH t1 as 
(
SELECT chunk, embedding <-> polar_ai.ai_text_embedding('PolarDB有哪些产品系列')::vector(1536) as dist
FROM t_chunk
ORDER by dist ASC
limit 5
),
t2 as (
SELECT ROW_NUMBER() OVER (ORDER BY dist ASC) AS row_num,
chunk
FROM t1
),
-- 稀疏向量召回
t3 as 
(
  SELECT chunk, v <=> to_tsquery('jiebacfg', 'PolarDB|PostgreSQL版|快速') as rank
  FROM t_chunk 
  WHERE v @@ to_tsquery('jiebacfg', 'PolarDB|PostgreSQL版|快速')
  ORDER by rank ASC
  LIMIT 5
),
t4 as (  
SELECT ROW_NUMBER() OVER (ORDER BY rank DESC) AS row_num,
chunk
FROM t3
),
-- Calculate RRF scores separately with weights 0.8 and 0.2
t5 as (
SELECT (1.0/(60+row_num)) * 0.8 as score , chunk FROM t2
UNION ALL 
SELECT (1.0/(60+row_num)) * 0.2, chunk FROM t4
)
-- 评分进行合并
SELECT sum(score) as score, chunk
FROM t5
GROUP BY chunk
ORDER BY score DESC;

Dense search

Performs a search based only on vectors, where a smaller distance indicates higher semantic similarity.

SELECT chunk, embedding <-> polar_ai.ai_text_embedding('PolarDB有哪些产品系列')::vector(1536) as dist
FROM t_chunk
ORDER by dist ASC
limit 5;

Sparse search

Performs a search based only on full-text matching, where a smaller rank value indicates higher relevance.

SELECT chunk, v <=> to_tsquery('jiebacfg', 'PolarDB|PostgreSQL版|快速') as rank
FROM t_chunk 
WHERE v @@ to_tsquery('jiebacfg', 'PolarDB|PostgreSQL版|快速')
ORDER by rank ASC
LIMIT 5;