SQL-FRONTIER Benchmark Deep-Dive / Search & Retrieval / task_053_Q53_min_max_convex_hybrid_search
← Prev: Reciprocal Rank Fusion (R... ⊞ All 120 Queries Next: Typo-Tolerant Search with... →
✔ PASS Search & Retrieval task_053_Q53_min_max_convex_hybrid_search Scaled: 1M+ Records Ordered: Yes

Min-Max Normalized Convex Combination Hybrid Search

Candidate Latency (1M Rows)
338.75 ms
(1.0x slower than golden)
Golden Latency (1M Rows)
329.95 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: 7.8s
✔ 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

An alternative to rank-based RRF is score-based fusion: min-max scale lexical scores to [0, 1] and vector scores to [0, 1], then take alpha * lexical + (1 - alpha) * vector.

⚡ The Frontier Trap (Why LLMs Fail)

Division by zero when all scores in a small result set are identical.

Prompt / User Request Given to Model
An alternative to rank-based RRF is score-based fusion: min-max scale lexical scores to [0, 1] and vector scores to [0, 1], then take alpha * lexical + (1 - alpha) * vector.

Execute the required analytical SQL calculation in DuckDB.
Expected output columns: doc_id, title, norm_bm25, norm_vec, convex_hybrid_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:

📊 search_candidates 1,000,000 rows 38.1 MiB Data Volume
doc_id (INTEGER) title (VARCHAR) raw_bm25 (DOUBLE) vector_sim (DOUBLE)
Data Preview (showing sample rows from 1,000,000 total rows):
# doc_id title raw_bm25 vector_sim
1 101 Data Warehousing Fundamentals 25 0.85
2 102 Columnar Storage Internals 10 0.92
3 103 SQL Query Optimization 5 0.7
4 103 Data Warehousing Fundamentals 27.5 0.97
5 104 Columnar Storage Internals 8.96 0.77
6 105 SQL Query Optimization 0.83 0.69
7 106 Data Warehousing Fundamentals 12.94 0.82
8 107 Columnar Storage Internals 27.29 0.97
9 108 SQL Query Optimization 0.01 0.65
10 109 Data Warehousing Fundamentals 22.31 0.91
11 110 Columnar Storage Internals 5.58 0.74
12 111 SQL Query Optimization 0.01 0.67
13 112 Data Warehousing Fundamentals 13.67 0.82
14 113 Columnar Storage Internals 3.59 0.72
15 114 SQL Query Optimization 21.58 0.91

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: 389.00 ms) Nodes consuming ≥ 20% of query runtime are highlighted with glowing borders
⏳ ORDER_BY 🔥 56.9% Cost
Latency: 221.46 ms
Rows In/Out: 1,000,000
Data Vol: 41.96 MB
⚠️ Heavy Execution Bottleneck (56.9% of query runtime)
📐 PROJECTION 🔥 21.8% Cost
Latency: 84.61 ms
Rows In/Out: 1,000,000
Data Vol: 41.96 MB
📁 Projections: ['doc_id', 'title',...
⚠️ Heavy Execution Bottleneck (21.8% of query runtime)
📐 PROJECTION 4.2%
Latency: 16.46 ms
Rows In/Out: 1,000,000
Data Vol: 34.33 MB
📁 Projections: ['doc_id', 'title',...
📐 PROJECTION 0.2%
Latency: 0.80 ms
Rows In/Out: 1,000,000
Data Vol: 64.85 MB
📁 Projections: ['doc_id', 'title',...
📐 PROJECTION 0.1%
Latency: 0.30 ms
Rows In/Out: 1,000,000
Data Vol: 64.85 MB
📁 Projections: ['#0', '#1', '#2', ...
🪟 WINDOW 🔥 14.5% Cost
Latency: 56.56 ms
Rows In/Out: 1,000,000
Data Vol: 64.85 MB
📁 Projections: ['min(raw_bm25) OVE...
⚠️ Heavy Execution Bottleneck (14.5% of query runtime)
🔍 TABLE_SCAN 2.3%
Latency: 8.81 ms
Rows In/Out: 1,000,000
Data Vol: 34.33 MB
📁 memory.main.search_candidates
Operator Cost % Latency Processed Rows Data Volume Details / Table Status
ORDER_BY 56.9% 221.46 ms 1,000,000 41.96 MB - 🔥 BOTTLENECK
PROJECTION 21.8% 84.61 ms 1,000,000 41.96 MB Projections: ['doc_id', 'title', 'norm_bm25', 'n 🔥 BOTTLENECK
WINDOW 14.5% 56.56 ms 1,000,000 64.85 MB Projections: ['min(raw_bm25) OVER ()', 'max(raw_ 🔥 BOTTLENECK
PROJECTION 4.2% 16.46 ms 1,000,000 34.33 MB Projections: ['doc_id', 'title', 'norm_bm25', 'n Normal
TABLE_SCAN 2.3% 8.81 ms 1,000,000 34.33 MB memory.main.search_candidates Normal
PROJECTION 0.2% 0.80 ms 1,000,000 64.85 MB Projections: ['doc_id', 'title', 'raw_bm25', 'mi Normal
PROJECTION 0.1% 0.30 ms 1,000,000 64.85 MB Projections: ['#0', '#1', '#2', '#3', '#4', '#5' Normal

4. Queries Executed (Candidate vs Golden Ground Truth)

Beautified & Syntax Highlighted
Candidate SQL Executed by gemini-3.8-flash-low (Runtime on 1M Rows: 338.75 ms) 
WITH bounds AS
  (SELECT doc_id,
          title,
          raw_bm25,
          vector_sim,
          min(raw_bm25) OVER () AS min_bm25,
          max(raw_bm25) OVER () AS max_bm25,
          min(vector_sim) OVER () AS min_vec,
          max(vector_sim) OVER () AS max_vec
   FROM search_candidates),
     normalized AS
  (SELECT doc_id,
          title,
          coalesce((raw_bm25 - min_bm25) / nullif(max_bm25 - min_bm25, 0), 1.0) AS norm_bm25,
          coalesce((vector_sim - min_vec) / nullif(max_vec - min_vec, 0), 1.0) AS norm_vec
   FROM bounds)
SELECT doc_id,
       title,
       round(norm_bm25, 4) AS norm_bm25,
       round(norm_vec, 4) AS norm_vec,
       round(0.50 * norm_bm25 + 0.50 * norm_vec, 4) AS convex_hybrid_score
FROM normalized
ORDER BY convex_hybrid_score DESC;
Golden Reference Ground Truth SQL (Runtime on 1M Rows: 329.95 ms) 
-- Min-Max normalization and 50/50 convex combination
 WITH bounds AS
  (SELECT doc_id,
          title,
          raw_bm25,
          vector_sim,
          min(raw_bm25) OVER () AS min_bm25,
          max(raw_bm25) OVER () AS max_bm25,
          min(vector_sim) OVER () AS min_vec,
          max(vector_sim) OVER () AS max_vec
   FROM search_candidates),
      normalized AS
  (SELECT doc_id,
          title,
          coalesce((raw_bm25 - min_bm25) / nullif(max_bm25 - min_bm25, 0), 1.0) AS norm_bm25,
          coalesce((vector_sim - min_vec) / nullif(max_vec - min_vec, 0), 1.0) AS norm_vec
   FROM bounds)
SELECT doc_id,
       title,
       round(norm_bm25, 4) AS norm_bm25,
       round(norm_vec, 4) AS norm_vec,
       round(0.50 * norm_bm25 + 0.50 * norm_vec, 4) AS convex_hybrid_score
FROM normalized
ORDER BY convex_hybrid_score DESC;
DuckDB Fixture Setup DDL & Data Population 
CREATE TABLE search_candidates (doc_id INT, title VARCHAR, raw_bm25 DOUBLE,
                                                                    vector_sim DOUBLE);


INSERT INTO search_candidates
VALUES (101, 'Data Warehousing Fundamentals', 25.0, 0.85),
       (102, 'Columnar Storage Internals', 10.0, 0.92),
       (103, 'SQL Query Optimization', 5.0, 0.70);

5. Query Results & Expected Ground Truth

Tabular Comparison
Actual Candidate Output (1,000,000 rows × 5 cols)
# doc_id title norm_bm25 norm_vec convex_hybrid_score
1 890,533 Data Warehousing Fundamentals 1 1 1
2 961,920 SQL Query Optimization 0.9859 0.9902 0.9881
3 305,583 SQL Query Optimization 0.977 0.9902 0.9836
4 254,597 Columnar Storage Internals 0.9637 0.9804 0.9721
5 581,571 SQL Query Optimization 0.9417 0.9608 0.9512
6 970,540 Data Warehousing Fundamentals 0.9404 0.9608 0.9506
7 111,955 Data Warehousing Fundamentals 0.9389 0.9608 0.9499
8 512,247 SQL Query Optimization 0.937 0.9608 0.9489
9 830,446 Data Warehousing Fundamentals 0.9278 0.951 0.9394
10 257,716 Data Warehousing Fundamentals 0.9262 0.951 0.9386
11 425,404 Data Warehousing Fundamentals 0.9201 0.951 0.9355
12 540,292 Data Warehousing Fundamentals 0.9188 0.951 0.9349
13 818,159 Columnar Storage Internals 0.9169 0.951 0.9339
14 237,279 SQL Query Optimization 0.9129 0.9412 0.927
15 115,878 SQL Query Optimization 0.91 0.9412 0.9256
16 2,460 SQL Query Optimization 0.9089 0.9412 0.925
17 506,422 Data Warehousing Fundamentals 0.9049 0.9412 0.923
18 231,119 Columnar Storage Internals 0.8998 0.9314 0.9156
19 74,179 Data Warehousing Fundamentals 0.8931 0.9314 0.9122
20 386,309 Columnar Storage Internals 0.8912 0.9314 0.9113
21 579,818 Columnar Storage Internals 0.8891 0.9314 0.9102
22 103,035 SQL Query Optimization 0.8868 0.9314 0.9091
23 519,737 Columnar Storage Internals 0.8862 0.9314 0.9088
24 967,996 Data Warehousing Fundamentals 0.8862 0.9314 0.9088
25 382,884 SQL Query Optimization 0.8862 0.9314 0.9088
Showing first 25 of 1,000,000 total rows.
Expected Golden Output (1,000,000 rows × 5 cols)
# doc_id title norm_bm25 norm_vec convex_hybrid_score
1 890,533 Data Warehousing Fundamentals 1 1 1
2 961,920 SQL Query Optimization 0.9859 0.9902 0.9881
3 305,583 SQL Query Optimization 0.977 0.9902 0.9836
4 254,597 Columnar Storage Internals 0.9637 0.9804 0.9721
5 581,571 SQL Query Optimization 0.9417 0.9608 0.9512
6 970,540 Data Warehousing Fundamentals 0.9404 0.9608 0.9506
7 111,955 Data Warehousing Fundamentals 0.9389 0.9608 0.9499
8 512,247 SQL Query Optimization 0.937 0.9608 0.9489
9 830,446 Data Warehousing Fundamentals 0.9278 0.951 0.9394
10 257,716 Data Warehousing Fundamentals 0.9262 0.951 0.9386
11 425,404 Data Warehousing Fundamentals 0.9201 0.951 0.9355
12 540,292 Data Warehousing Fundamentals 0.9188 0.951 0.9349
13 818,159 Columnar Storage Internals 0.9169 0.951 0.9339
14 237,279 SQL Query Optimization 0.9129 0.9412 0.927
15 115,878 SQL Query Optimization 0.91 0.9412 0.9256
16 2,460 SQL Query Optimization 0.9089 0.9412 0.925
17 506,422 Data Warehousing Fundamentals 0.9049 0.9412 0.923
18 231,119 Columnar Storage Internals 0.8998 0.9314 0.9156
19 74,179 Data Warehousing Fundamentals 0.8931 0.9314 0.9122
20 386,309 Columnar Storage Internals 0.8912 0.9314 0.9113
21 579,818 Columnar Storage Internals 0.8891 0.9314 0.9102
22 103,035 SQL Query Optimization 0.8868 0.9314 0.9091
23 519,737 Columnar Storage Internals 0.8862 0.9314 0.9088
24 967,996 Data Warehousing Fundamentals 0.8862 0.9314 0.9088
25 382,884 SQL Query Optimization 0.8862 0.9314 0.9088
Showing first 25 of 1,000,000 total rows.
← Prev: Reciprocal Rank Fusion (R... ⊞ Back to Benchmark Dashboard Next: Typo-Tolerant Search with... →