✔ PASS Search & Retrieval task_052_Q52_reciprocal_rank_fusion_rrf Scaled: 1M+ Records Ordered: Yes

Reciprocal Rank Fusion (RRF) Hybrid Search (BM25 + Dense Vectors)

Candidate Latency (1M Rows)

515.60 ms

(1.0x slower than golden)

Golden Latency (1M Rows)

508.51 ms

DuckDB in-memory engine

Underlying Data Volume

1,000,000 rows

38.1 MiB across 1 table

Evaluated Model

gemini-3.8-flash-low

Gen Latency: 14.3s

✔ Correctness Verified on 1M+ Records

Candidate SQL output matches ground truth golden query across all multiset rows and numeric tolerances (tol=1e-3). Zero data skew or anti-patterns detected on full dataset scale.

1. Analytical Significance & What It Means

Scenario & Frontier Trap

Business Scenario & Problem

Hybrid search combines sparse keyword search (great for exact names and acronyms) with dense vector search (great for synonyms and concepts). RRF merges both rank lists using: RRF_score = 1/(60 + rank_bm25) + 1/(60 + rank_vec).

⚡ The Frontier Trap (Why LLMs Fail)

Adding raw BM25 scores (range 0 to 50+) directly to cosine similarity scores (range 0 to 1) allows BM25 to completely drown out vector relevance.

Prompt / User Request Given to Model

Hybrid search combines sparse keyword search (great for exact names and acronyms) with dense vector search (great for synonyms and concepts). RRF merges both rank lists using: RRF_score = 1/(60 + rank_bm25) + 1/(60 + rank_vec).

Execute the required analytical SQL calculation in DuckDB.
Expected output columns: doc_id, title, rank_lexical, rank_vector, rrf_score.
Results should be ordered as specified.

2. Underlying Tables, Row Counts & Data Volume

1 Tables (1,000,000 rows • 38.1 MiB)

Production-scale fixtures loaded into the isolated DuckDB sandbox with at least 1,000,000 records per table:

📊 corpus_docs 1,000,000 rows 38.1 MiB Data Volume

doc_id (INTEGER) title (VARCHAR) lexical_score (DOUBLE) vec (FLOAT[2])

Data Preview (showing sample rows from 1,000,000 total rows):

#	doc_id	title	lexical_score	vec
1	1	DuckDB Vector Search Guide	12.5	[0.95 0.05]
2	2	Postgres HNSW Extension	14.2	[0.7 0.3]
3	3	Deep Learning Embeddings	2.1	[0.92 0.1 ]
4	3	DuckDB Vector Search Guide	9.47	[0.8492 0.0731]
5	4	Postgres HNSW Extension	4.45	[0.8186 0.0276]
6	5	Deep Learning Embeddings	8.16	[0.8412 0.0971]
7	6	DuckDB Vector Search Guide	12.89	[0.8701 0.0937]
8	7	Postgres HNSW Extension	5.89	[0.8273 0.0483]
9	8	Deep Learning Embeddings	16.96	[0.895 0.1401]
10	9	DuckDB Vector Search Guide	0.01	[0.7817 0.0886]
11	10	Postgres HNSW Extension	0.01	[0.7847 0.1242]
12	11	Deep Learning Embeddings	9.35	[0.8485 0.0449]
13	12	DuckDB Vector Search Guide	3.18	[0.8108 0.1152]
14	13	Postgres HNSW Extension	10.41	[0.855 0.0738]
15	14	Deep Learning Embeddings	8.49	[0.8432 0.1226]

3. Graphical Query Execution Plan & Cost Bottlenecks

Physical Operator Tree (DuckDB 2.0 Profiler)

Visual flowchart of the physical execution pipeline. Operators with high relative costs (≥ 20% of query runtime or heavy latency) are visually highlighted:

Pipeline Latency Distribution (Total Plan Time: 1018.18 ms) Nodes consuming ≥ 20% of query runtime are highlighted with glowing borders

⏳ ORDER_BY 🔥 19.3% Cost

Latency: 196.87 ms

Rows In/Out: 1,000,002

Data Vol: 41.96 MB

📐 PROJECTION 🔥 8.0% Cost

Latency: 81.73 ms

Rows In/Out: 1,000,002

Data Vol: 41.97 MB

📁 Projections: ['doc_id', 'title',...

⚡ HASH_JOIN 🔥 20.2% Cost

Latency: 205.23 ms

Rows In/Out: 1,000,002

Data Vol: 34.34 MB

📐 PROJECTION 0.0%

Latency: 0.32 ms

Rows In/Out: 1,000,000

Data Vol: 19.07 MB

📁 Projections: ['#0', '#1', '#2']

🪟 WINDOW 🔥 26.2% Cost

Latency: 266.32 ms

Rows In/Out: 1,000,000

Data Vol: 19.07 MB

📁 Projections: row_number() OVER (...

🔍 TABLE_SCAN 0.3%

Latency: 2.70 ms

Rows In/Out: 1,000,000

Data Vol: 11.44 MB

📁 memory.main.corpus_docs

📐 PROJECTION 0.0%

Latency: 0.19 ms

Rows In/Out: 1,000,000

Data Vol: 34.33 MB

📁 Projections: ['#0', '#1', '#2', ...

🪟 WINDOW 🔥 24.9% Cost

Latency: 253.72 ms

Rows In/Out: 1,000,000

Data Vol: 34.33 MB

📁 Projections: row_number() OVER (...

🔍 TABLE_SCAN 1.1%

Latency: 11.10 ms

Rows In/Out: 1,000,000

Data Vol: 26.70 MB

📁 memory.main.corpus_docs

Operator	Cost %	Latency	Processed Rows	Data Volume	Details / Table	Status
WINDOW	26.2%	266.32 ms	1,000,000	19.07 MB	Projections: row_number() OVER (ORDER BY array_c	🔥 BOTTLENECK
WINDOW	24.9%	253.72 ms	1,000,000	34.33 MB	Projections: row_number() OVER (ORDER BY lexical	🔥 BOTTLENECK
HASH_JOIN	20.2%	205.23 ms	1,000,002	34.34 MB	-	🔥 BOTTLENECK
ORDER_BY	19.3%	196.87 ms	1,000,002	41.96 MB	-	🔥 BOTTLENECK
PROJECTION	8.0%	81.73 ms	1,000,002	41.97 MB	Projections: ['doc_id', 'title', 'rank_lexical',	🔥 BOTTLENECK
TABLE_SCAN	1.1%	11.10 ms	1,000,000	26.70 MB	memory.main.corpus_docs	Normal
TABLE_SCAN	0.3%	2.70 ms	1,000,000	11.44 MB	memory.main.corpus_docs	Normal
PROJECTION	0.0%	0.32 ms	1,000,000	19.07 MB	Projections: ['#0', '#1', '#2']	Normal
PROJECTION	0.0%	0.19 ms	1,000,000	34.33 MB	Projections: ['#0', '#1', '#2', '#3']	Normal

4. Queries Executed (Candidate vs Golden Ground Truth)

Beautified & Syntax Highlighted

-- Hybrid RRF rank fusion between BM25 lexical score and dense vector cosine similarity
WITH ranked_lexical AS
  (SELECT doc_id,
          title,
          row_number() OVER (
                             ORDER BY lexical_score DESC) AS rank_lexical
   FROM corpus_docs),
     ranked_vector AS
  (SELECT doc_id,
          title,
          row_number() OVER (
                             ORDER BY array_cosine_similarity(vec, [1.0, 0.0]::FLOAT[2]) DESC) AS rank_vector
   FROM corpus_docs)
SELECT l.doc_id,
       l.title,
       l.rank_lexical,
       v.rank_vector, -- Standard RRF formula with k = 60
 round((1.0 / (60.0 + l.rank_lexical)) + (1.0 / (60.0 + v.rank_vector)), 6) AS rrf_score
FROM ranked_lexical l
JOIN ranked_vector v ON l.doc_id = v.doc_id
ORDER BY rrf_score DESC;

-- Hybrid RRF rank fusion between BM25 lexical score and dense vector cosine similarity
 WITH ranked_lexical AS
  (SELECT doc_id,
          title,
          row_number() OVER (
                             ORDER BY lexical_score DESC) AS rank_lexical
   FROM corpus_docs),
      ranked_vector AS
  (SELECT doc_id,
          title,
          row_number() OVER (
                             ORDER BY array_cosine_similarity(vec, [1.0, 0.0]::FLOAT[2]) DESC) AS rank_vector
   FROM corpus_docs)
SELECT l.doc_id,
       l.title,
       l.rank_lexical,
       v.rank_vector, -- Standard RRF formula with k = 60
 round((1.0 / (60.0 + l.rank_lexical)) + (1.0 / (60.0 + v.rank_vector)), 6) AS rrf_score
FROM ranked_lexical l
JOIN ranked_vector v ON l.doc_id = v.doc_id
ORDER BY rrf_score DESC;

CREATE TABLE corpus_docs (doc_id INT, title VARCHAR, lexical_score DOUBLE,
                                                                   vec FLOAT[2]);


INSERT INTO corpus_docs
VALUES (1, 'DuckDB Vector Search Guide', 12.5, [0.95, 0.05]::FLOAT[2]),
       (2, 'Postgres HNSW Extension', 14.2, [0.70, 0.30]::FLOAT[2]),
       (3, 'Deep Learning Embeddings', 2.1, [0.92, 0.10]::FLOAT[2]);

5. Query Results & Expected Ground Truth

Tabular Comparison

Actual Candidate Output (1,000,002 rows × 5 cols)

#	doc_id	title	rank_lexical	rank_vector	rrf_score
1	278,539	Postgres HNSW Extension	1	44,343	0.0164
2	260,455	Postgres HNSW Extension	812,906	1	0.0164
3	544,505	Deep Learning Embeddings	2	135,408	0.0161
4	758,893	Postgres HNSW Extension	749,395	2	0.0161
5	9,559	Postgres HNSW Extension	3	200,998	0.0159
6	269,100	DuckDB Vector Search Guide	247,696	3	0.0159
7	259,657	Postgres HNSW Extension	4	125,866	0.0156
8	271,728	DuckDB Vector Search Guide	378,108	4	0.0156
9	194,861	Deep Learning Embeddings	5	353,522	0.0154
10	273,626	Deep Learning Embeddings	380,378	5	0.0154
11	33,236	Deep Learning Embeddings	6	268,815	0.0152
12	275,931	DuckDB Vector Search Guide	510,375	6	0.0152
13	280,069	Postgres HNSW Extension	547,612	7	0.0149
14	698,918	Deep Learning Embeddings	7	535,292	0.0149
15	589,820	Deep Learning Embeddings	8	204,826	0.0147
16	286,538	Deep Learning Embeddings	948,765	8	0.0147
17	290,037	DuckDB Vector Search Guide	13,135	9	0.0146
18	329,148	DuckDB Vector Search Guide	9	677,153	0.0145
19	297,183	DuckDB Vector Search Guide	284,440	10	0.0143
20	861,834	DuckDB Vector Search Guide	10	819,488	0.0143
21	706,609	Postgres HNSW Extension	11	136,559	0.0141
22	263,096	Deep Learning Embeddings	174,700	11	0.0141
23	342,090	DuckDB Vector Search Guide	191,245	12	0.0139
24	875,002	Postgres HNSW Extension	12	396,923	0.0139
25	709,300	Postgres HNSW Extension	13	302,325	0.0137

Showing first 25 of 1,000,002 total rows.

Expected Golden Output (1,000,002 rows × 5 cols)

#	doc_id	title	rank_lexical	rank_vector	rrf_score
1	278,539	Postgres HNSW Extension	1	44,345	0.0164
2	260,455	Postgres HNSW Extension	812,827	1	0.0164
3	544,505	Deep Learning Embeddings	2	135,399	0.0161
4	582,040	Postgres HNSW Extension	785,602	2	0.0161
5	9,559	Postgres HNSW Extension	3	201,000	0.0159
6	269,100	DuckDB Vector Search Guide	247,588	3	0.0159
7	259,657	Postgres HNSW Extension	4	125,866	0.0156
8	271,728	DuckDB Vector Search Guide	378,024	4	0.0156
9	273,626	Deep Learning Embeddings	380,274	5	0.0154
10	194,861	Deep Learning Embeddings	5	353,523	0.0154
11	33,236	Deep Learning Embeddings	6	268,814	0.0152
12	275,931	DuckDB Vector Search Guide	510,272	6	0.0152
13	698,918	Deep Learning Embeddings	7	535,292	0.0149
14	280,069	Postgres HNSW Extension	547,426	7	0.0149
15	589,820	Deep Learning Embeddings	8	204,817	0.0147
16	286,538	Deep Learning Embeddings	936,782	8	0.0147
17	290,037	DuckDB Vector Search Guide	13,125	9	0.0146
18	329,148	DuckDB Vector Search Guide	9	677,152	0.0145
19	297,183	DuckDB Vector Search Guide	284,322	10	0.0143
20	861,834	DuckDB Vector Search Guide	10	819,488	0.0143
21	706,609	Postgres HNSW Extension	11	136,559	0.0141
22	308,817	DuckDB Vector Search Guide	725,275	11	0.0141
23	875,002	Postgres HNSW Extension	12	396,923	0.0139
24	312,724	Postgres HNSW Extension	815,655	12	0.0139
25	263,096	Deep Learning Embeddings	174,595	13	0.0137

Showing first 25 of 1,000,002 total rows.

← Prev: DuckDB Full-Text Search (... ⊞ Back to Benchmark Dashboard Next: Min-Max Normalized Convex... →