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

Reciprocal Rank Fusion (RRF) Hybrid Retrieval without Scale Collapse

Candidate Latency (1M Rows)

546.14 ms

(1.1x slower than golden)

Golden Latency (1M Rows)

518.75 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: 16.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

Fuses BM25 keyword rankings and vector cosine similarity rankings using rank-based Reciprocal Rank Fusion (k=60), preventing unbounded BM25 scores from dominating vector scores.

⚡ The Frontier Trap (Why LLMs Fail)

Frontier models frequently confuse RRF rank fusion with raw score addition (bm25_score + vector_sim). Because BM25 scores can reach 400+ while cosine similarity is bounded between 0 and 1, adding raw scores allows BM25 to completely drown out semantic vector relevance.

Prompt / User Request Given to Model

Combine sparse BM25 lexical scores and dense vector similarity scores using Reciprocal Rank Fusion (RRF):
RRF_Score = 1.0 / (60.0 + bm25_rank) + 1.0 / (60.0 + vector_rank)

Required output columns:
- doc_id (INT)
- title (VARCHAR)
- bm25_rank (BIGINT)
- vector_rank (BIGINT)
- rrf_score (DOUBLE)

Order by rrf_score DESC, doc_id ASC.

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:

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

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

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

#	doc_id	title	bm25_raw	vec
1	1	PostgreSQL Manual	450	[0.1 0.9]
2	2	DuckDB Architecture	80	[0.95 0.05]
3	3	Vector Search Guide	60	[0.99 0.01]
4	3	PostgreSQL Manual	400.43	[0.8871 0.1714]
5	4	DuckDB Architecture	48.94	[0.8231 0.1475]
6	5	Vector Search Guide	0.01	[0.8132 0.0912]
7	6	PostgreSQL Manual	496.58	[0.9046 0.1308]
8	7	DuckDB Architecture	85.5	[ 0.8298 -0.0246]
9	8	Vector Search Guide	415.77	[0.8899 0.085 ]
10	9	PostgreSQL Manual	231.73	[0.8564 0.1466]
11	10	DuckDB Architecture	0.01	[0.8123 0.0438]
12	11	Vector Search Guide	362.45	[0.8802 0.0989]
13	12	PostgreSQL Manual	0.01	[0.8053 0.147 ]
14	13	DuckDB Architecture	297.54	[0.8684 0.0789]
15	14	Vector Search Guide	281.16	[0.8654 0.1111]

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: 1020.93 ms) Nodes consuming ≥ 20% of query runtime are highlighted with glowing borders

⏳ ORDER_BY 🔥 20.9% Cost

Latency: 212.92 ms

Rows In/Out: 1,000,002

Data Vol: 41.96 MB

📐 PROJECTION 🔥 7.9% Cost

Latency: 81.01 ms

Rows In/Out: 1,000,002

Data Vol: 41.97 MB

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

⚡ HASH_JOIN 🔥 17.1% Cost

Latency: 174.48 ms

Rows In/Out: 1,000,002

Data Vol: 34.34 MB

📐 PROJECTION 0.0%

Latency: 0.30 ms

Rows In/Out: 1,000,000

Data Vol: 19.07 MB

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

🪟 WINDOW 🔥 27.7% Cost

Latency: 282.82 ms

Rows In/Out: 1,000,000

Data Vol: 19.07 MB

📁 Projections: row_number() OVER (...

🔍 TABLE_SCAN 0.5%

Latency: 5.56 ms

Rows In/Out: 1,000,000

Data Vol: 11.44 MB

📁 memory.main.search_index

📐 PROJECTION 0.0%

Latency: 0.15 ms

Rows In/Out: 1,000,000

Data Vol: 34.33 MB

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

🪟 WINDOW 🔥 24.8% Cost

Latency: 253.11 ms

Rows In/Out: 1,000,000

Data Vol: 34.33 MB

📁 Projections: row_number() OVER (...

🔍 TABLE_SCAN 1.0%

Latency: 10.58 ms

Rows In/Out: 1,000,000

Data Vol: 26.70 MB

📁 memory.main.search_index

Operator	Cost %	Latency	Processed Rows	Data Volume	Details / Table	Status
WINDOW	27.7%	282.82 ms	1,000,000	19.07 MB	Projections: row_number() OVER (ORDER BY array_c	🔥 BOTTLENECK
WINDOW	24.8%	253.11 ms	1,000,000	34.33 MB	Projections: row_number() OVER (ORDER BY bm25_ra	🔥 BOTTLENECK
ORDER_BY	20.9%	212.92 ms	1,000,002	41.96 MB	-	🔥 BOTTLENECK
HASH_JOIN	17.1%	174.48 ms	1,000,002	34.34 MB	-	🔥 BOTTLENECK
PROJECTION	7.9%	81.01 ms	1,000,002	41.97 MB	Projections: ['doc_id', 'title', 'bm25_rank', 'v	🔥 BOTTLENECK
TABLE_SCAN	1.0%	10.58 ms	1,000,000	26.70 MB	memory.main.search_index	Normal
TABLE_SCAN	0.5%	5.56 ms	1,000,000	11.44 MB	memory.main.search_index	Normal
PROJECTION	0.0%	0.30 ms	1,000,000	19.07 MB	Projections: ['#0', '#1', '#2']	Normal
PROJECTION	0.0%	0.15 ms	1,000,000	34.33 MB	Projections: ['#0', '#1', '#2', '#3']	Normal

4. Queries Executed (Candidate vs Golden Ground Truth)

Beautified & Syntax Highlighted

WITH ranked_bm25 AS
  (SELECT doc_id,
          title,
          row_number() OVER (
                             ORDER BY bm25_raw DESC, doc_id ASC) AS bm25_rank
   FROM search_index),
     ranked_vector AS
  (SELECT doc_id,
          title,
          row_number() OVER (
                             ORDER BY array_cosine_similarity(vec, [1.0, 0.0]::FLOAT[2]) DESC, doc_id ASC) AS vector_rank
   FROM search_index)
SELECT b.doc_id,
       b.title,
       b.bm25_rank,
       v.vector_rank,
       round((1.0 / (60.0 + b.bm25_rank)) + (1.0 / (60.0 + v.vector_rank)), 6) AS rrf_score
FROM ranked_bm25 b
JOIN ranked_vector v ON b.doc_id = v.doc_id
ORDER BY rrf_score DESC,
         b.doc_id ASC;

WITH ranked_bm25 AS
  (SELECT doc_id,
          title,
          row_number() OVER (
                             ORDER BY bm25_raw DESC, doc_id ASC) AS bm25_rank
   FROM search_index),
     ranked_vector AS
  (SELECT doc_id,
          title,
          row_number() OVER (
                             ORDER BY array_cosine_similarity(vec, [1.0, 0.0]::FLOAT[2]) DESC, doc_id ASC) AS vector_rank
   FROM search_index)
SELECT b.doc_id,
       b.title,
       b.bm25_rank,
       v.vector_rank,
       round((1.0 / (60.0 + b.bm25_rank)) + (1.0 / (60.0 + v.vector_rank)), 6) AS rrf_score
FROM ranked_bm25 b
JOIN ranked_vector v ON b.doc_id = v.doc_id
ORDER BY rrf_score DESC,
         b.doc_id ASC;

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

-- Doc 1: Massive BM25 score, poor vector match
-- Doc 2: Balanced high BM25 and high vector match
-- Doc 3: Perfect vector match, moderate BM25

INSERT INTO search_index
VALUES (1, 'PostgreSQL Manual', 450.0, [0.1, 0.9]::FLOAT[2]),
       (2, 'DuckDB Architecture', 80.0, [0.95, 0.05]::FLOAT[2]),
       (3, 'Vector Search Guide', 60.0, [0.99, 0.01]::FLOAT[2]);

5. Query Results & Expected Ground Truth

Tabular Comparison

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

#	doc_id	title	bm25_rank	vector_rank	rrf_score
1	505,395	PostgreSQL Manual	1	96,973	0.0164
2	5,496	PostgreSQL Manual	621,759	1	0.0164
3	274,090	DuckDB Architecture	2	426,409	0.0161
4	6,006	PostgreSQL Manual	724,765	2	0.0161
5	16,965	PostgreSQL Manual	285,396	3	0.0159
6	615,482	Vector Search Guide	3	705,983	0.0159
7	18,190	DuckDB Architecture	571,601	4	0.0156
8	471,205	DuckDB Architecture	4	401,114	0.0156
9	148,087	DuckDB Architecture	5	179,834	0.0154
10	19,669	DuckDB Architecture	437,461	5	0.0154
11	114,867	PostgreSQL Manual	6	109,827	0.0152
12	20,266	DuckDB Architecture	174,466	6	0.0152
13	30,177	PostgreSQL Manual	345,775	7	0.0149
14	506,539	DuckDB Architecture	7	822,032	0.0149
15	439,703	Vector Search Guide	8	246,962	0.0147
16	40,526	Vector Search Guide	334,552	8	0.0147
17	46,510	DuckDB Architecture	823,174	9	0.0145
18	364,552	DuckDB Architecture	9	736,591	0.0145
19	50,424	PostgreSQL Manual	107,307	10	0.0143
20	752,018	Vector Search Guide	10	125,971	0.0143
21	247,458	PostgreSQL Manual	11	374,545	0.0141
22	57,809	Vector Search Guide	825,272	11	0.0141
23	685,461	PostgreSQL Manual	12	416,839	0.0139
24	68,630	Vector Search Guide	793,428	12	0.0139
25	465,756	PostgreSQL Manual	13	107,883	0.0137

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

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

#	doc_id	title	bm25_rank	vector_rank	rrf_score
1	505,395	PostgreSQL Manual	1	96,973	0.0164
2	5,496	PostgreSQL Manual	621,759	1	0.0164
3	274,090	DuckDB Architecture	2	426,409	0.0161
4	6,006	PostgreSQL Manual	724,765	2	0.0161
5	16,965	PostgreSQL Manual	285,396	3	0.0159
6	615,482	Vector Search Guide	3	705,983	0.0159
7	18,190	DuckDB Architecture	571,601	4	0.0156
8	471,205	DuckDB Architecture	4	401,114	0.0156
9	148,087	DuckDB Architecture	5	179,834	0.0154
10	19,669	DuckDB Architecture	437,461	5	0.0154
11	114,867	PostgreSQL Manual	6	109,827	0.0152
12	20,266	DuckDB Architecture	174,466	6	0.0152
13	30,177	PostgreSQL Manual	345,775	7	0.0149
14	506,539	DuckDB Architecture	7	822,032	0.0149
15	439,703	Vector Search Guide	8	246,962	0.0147
16	40,526	Vector Search Guide	334,552	8	0.0147
17	46,510	DuckDB Architecture	823,174	9	0.0145
18	364,552	DuckDB Architecture	9	736,591	0.0145
19	50,424	PostgreSQL Manual	107,307	10	0.0143
20	752,018	Vector Search Guide	10	125,971	0.0143
21	247,458	PostgreSQL Manual	11	374,545	0.0141
22	57,809	Vector Search Guide	825,272	11	0.0141
23	685,461	PostgreSQL Manual	12	416,839	0.0139
24	68,630	Vector Search Guide	793,428	12	0.0139
25	465,756	PostgreSQL Manual	13	107,883	0.0137

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

← Prev: ASOF Point-in-Time Quote ... ⊞ Back to Benchmark Dashboard Next: Directed Dependency Graph... →