✔ PASS Vector & Embeddings task_045_Q45_cross_encoder_candidate_generation Scaled: 1M+ Records Ordered: Yes

Two-Stage Retrieval: Bi-Encoder Candidate Generation for Re-ranking

Candidate Latency (1M Rows)

21.75 ms

(1.4x slower than golden)

Golden Latency (1M Rows)

15.38 ms

DuckDB in-memory engine

Underlying Data Volume

1,000,000 rows

30.5 MiB across 1 table

Evaluated Model

gemini-3.8-flash-low

Gen Latency: 9.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

Modern enterprise search uses a 2-stage pipeline: Fast bi-encoder vector search retrieves Top-50 candidates, which are then passed to a computationally heavy cross-encoder for final scoring.

⚡ The Frontier Trap (Why LLMs Fail)

Running heavy cross-encoder models across entire datasets is computationally impossible.

Prompt / User Request Given to Model

Modern enterprise search uses a 2-stage pipeline: Fast bi-encoder vector search retrieves Top-50 candidates, which are then passed to a computationally heavy cross-encoder for final scoring.

Execute the required analytical SQL calculation in DuckDB.
Expected output columns: article_id, category, stage1_biencoder_score.
Results should be ordered as specified.

2. Underlying Tables, Row Counts & Data Volume

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

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

📊 knowledge_articles 1,000,000 rows 30.5 MiB Data Volume

article_id (INTEGER) category (VARCHAR) vec (FLOAT[2])

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

#	article_id	category	vec
1	1	tech	[0.9 0.1]
2	2	tech	[0.8 0.2]
3	3	hr	[0.1 0.9]
4	3	tech	[0.8803 0.0993]
5	4	hr	[0.9646 0.1121]
6	5	tech	[0.8517 0.054 ]
7	6	hr	[0.9068 0.1383]
8	7	tech	[0.85 0.133]
9	8	hr	[0.8176 0.0989]
10	9	tech	[0.84 0.1089]
11	10	hr	[0.819 0.0378]
12	11	tech	[0.8376 0.0675]
13	12	hr	[0.7708 0.0655]
14	13	tech	[0.827 0.0757]
15	14	hr	[0.8413 0.0357]

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

📐 PROJECTION 0.0%

Latency: 0.00 ms

Rows In/Out: 50

Data Vol: 1,200 B

📁 Projections: ['#0', '__internal_...

⏳ ORDER_BY 0.2%

Latency: 0.09 ms

Rows In/Out: 50

Data Vol: 800 B

📐 PROJECTION 0.0%

Latency: 0.00 ms

Rows In/Out: 50

Data Vol: 800 B

📁 Projections: ['#0', '__internal_...

📐 PROJECTION 0.0%

Latency: 0.01 ms

Rows In/Out: 50

Data Vol: 1,200 B

📁 Projections: ['article_id', 'cat...

⚡ HASH_JOIN 0.3%

Latency: 0.14 ms

Rows In/Out: 50

Data Vol: 1,400 B

🔍 TABLE_SCAN 0.4%

Latency: 0.20 ms

Rows In/Out: 50

Data Vol: 1,800 B

📁 memory.main.knowledge_articles

✂️ TOP_N 4.8%

Latency: 2.16 ms

Rows In/Out: 50

Data Vol: 600 B

📐 PROJECTION 🔥 52.0% Cost

Latency: 23.52 ms

Rows In/Out: 500,001

Data Vol: 5.72 MB

📁 Projections: ['stage1_biencoder_...

🔍 TABLE_SCAN 🔥 42.3% Cost

Latency: 19.12 ms

Rows In/Out: 500,001

Data Vol: 7.63 MB

📁 memory.main.knowledge_articles

Operator	Cost %	Latency	Processed Rows	Data Volume	Details / Table	Status
PROJECTION	52.0%	23.52 ms	500,001	5.72 MB	Projections: ['stage1_biencoder_score', 'rowid']	🔥 BOTTLENECK
TABLE_SCAN	42.3%	19.12 ms	500,001	7.63 MB	memory.main.knowledge_articles	🔥 BOTTLENECK
TOP_N	4.8%	2.16 ms	50	0.00 MB	-	Normal
TABLE_SCAN	0.4%	0.20 ms	50	0.00 MB	memory.main.knowledge_articles	Normal
HASH_JOIN	0.3%	0.14 ms	50	0.00 MB	-	Normal
ORDER_BY	0.2%	0.09 ms	50	0.00 MB	-	Normal
PROJECTION	0.0%	0.01 ms	50	0.00 MB	Projections: ['article_id', 'category', 'stage1_	Normal
PROJECTION	0.0%	0.00 ms	50	0.00 MB	Projections: ['#0', '__internal_decompress_strin	Normal
PROJECTION	0.0%	0.00 ms	50	0.00 MB	Projections: ['#0', '__internal_compress_string_	Normal

4. Queries Executed (Candidate vs Golden Ground Truth)

Beautified & Syntax Highlighted

-- First-stage candidate extraction: Top candidates in relevant category

SELECT article_id,
       category,
       round(array_cosine_similarity(vec, [0.95, 0.05]::FLOAT[2]), 4) AS stage1_biencoder_score
FROM knowledge_articles
WHERE category = 'tech'
ORDER BY stage1_biencoder_score DESC
LIMIT 50;

-- First-stage candidate extraction: Top candidates in relevant category

SELECT article_id,
       category,
       round(array_cosine_similarity(vec, [0.95, 0.05]::FLOAT[2]), 4) AS stage1_biencoder_score
FROM knowledge_articles
WHERE category = 'tech'
ORDER BY stage1_biencoder_score DESC
LIMIT 50;

CREATE TABLE knowledge_articles (article_id INT, category VARCHAR, vec FLOAT[2]);


INSERT INTO knowledge_articles
VALUES (1, 'tech', [0.9, 0.1]::FLOAT[2]),
       (2, 'tech', [0.8, 0.2]::FLOAT[2]),
       (3, 'hr', [0.1, 0.9]::FLOAT[2]);

5. Query Results & Expected Ground Truth

Tabular Comparison

Actual Candidate Output (50 rows × 3 cols)

#	article_id	category	stage1_biencoder_score
1	122,923	tech	1
2	123,955	tech	1
3	123,011	tech	1
4	123,013	tech	1
5	123,035	tech	1
6	123,125	tech	1
7	123,139	tech	1
8	123,147	tech	1
9	123,227	tech	1
10	123,259	tech	1
11	123,283	tech	1
12	123,291	tech	1
13	123,341	tech	1
14	122,979	tech	1
15	123,369	tech	1
16	123,381	tech	1
17	123,385	tech	1
18	123,419	tech	1
19	123,467	tech	1
20	123,477	tech	1
21	123,507	tech	1
22	123,509	tech	1
23	123,513	tech	1
24	123,523	tech	1
25	123,545	tech	1

Showing first 25 of 50 total rows.

Expected Golden Output (50 rows × 3 cols)

#	article_id	category	stage1_biencoder_score
1	122,923	tech	1
2	123,955	tech	1
3	123,011	tech	1
4	123,013	tech	1
5	123,035	tech	1
6	123,125	tech	1
7	123,139	tech	1
8	123,147	tech	1
9	123,227	tech	1
10	123,259	tech	1
11	123,283	tech	1
12	123,291	tech	1
13	123,341	tech	1
14	122,979	tech	1
15	123,369	tech	1
16	123,381	tech	1
17	123,385	tech	1
18	123,419	tech	1
19	123,467	tech	1
20	123,477	tech	1
21	123,507	tech	1
22	123,509	tech	1
23	123,513	tech	1
24	123,523	tech	1
25	123,545	tech	1

Showing first 25 of 50 total rows.

← Prev: Dynamic Context Window To... ⊞ Back to Benchmark Dashboard Next: Temporal Semantic Drift T... →