SQL-FRONTIER Benchmark Deep-Dive / Vector & Embeddings / task_039_Q39_maximal_marginal_relevance_mmr
← Prev: Multi-Modal Joint Retriev... ⊞ All 120 Queries Next: Historical SCD Type 2 Poi... →
✔ PASS Vector & Embeddings task_039_Q39_maximal_marginal_relevance_mmr Scaled: 1M+ Records Ordered: Yes

Maximal Marginal Relevance (MMR) Diversity Reranking in SQL

Candidate Latency (1M Rows)
297.35 ms
(1.0x faster than golden)
Golden Latency (1M Rows)
299.08 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.4s
✔ 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

Standard vector retrieval often returns 5 virtually identical articles. MMR penalizes redundancy by selecting documents that are similar to the query but dissimilar to already-selected documents.

⚡ The Frontier Trap (Why LLMs Fail)

Pure relevance search creates echo-chamber search results with zero content diversity.

Prompt / User Request Given to Model
Standard vector retrieval often returns 5 virtually identical articles. MMR penalizes redundancy by selecting documents that are similar to the query but dissimilar to already-selected documents.

Execute the required analytical SQL calculation in DuckDB.
Expected output columns: article_id, headline, query_relevance, redundancy_with_top1, mmr_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:

📊 news_articles 1,000,000 rows 30.5 MiB Data Volume
article_id (INTEGER) headline (VARCHAR) vec (FLOAT[2])
Data Preview (showing sample rows from 1,000,000 total rows):
# article_id headline vec
1 1 Fed cuts interest rates by 25 bps [0.99 0.05]
2 2 Central bank lowers benchmark 25 bps [0.98 0.06]
3 3 Tech stock rally follows monetary shift [0.8 0.45]
4 3 Fed cuts interest rates by 25 bps [0.8446 0.0938]
5 4 Central bank lowers benchmark 25 bps [0.8133 0.1181]
6 5 Tech stock rally follows monetary shift [0.9169 0.1756]
7 6 Fed cuts interest rates by 25 bps [0.8414 0.1053]
8 7 Central bank lowers benchmark 25 bps [0.8747 0.1313]
9 8 Tech stock rally follows monetary shift [0.8288 0.1514]
10 9 Fed cuts interest rates by 25 bps [0.856 0.077]
11 10 Central bank lowers benchmark 25 bps [0.8614 0.1249]
12 11 Tech stock rally follows monetary shift [0.8371 0.1208]
13 12 Fed cuts interest rates by 25 bps [0.8012 0.1274]
14 13 Central bank lowers benchmark 25 bps [0.8922 0.1321]
15 14 Tech stock rally follows monetary shift [0.9104 0.1016]

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: 361.53 ms) Nodes consuming ≥ 20% of query runtime are highlighted with glowing borders
🔄 CTE 11.2%
Latency: 40.50 ms
Rows In/Out: 0
Data Vol: 0 B
📐 PROJECTION 4.6%
Latency: 16.51 ms
Rows In/Out: 1,000,000
Data Vol: 30.52 MB
📁 Projections: ['article_id', 'hea...
🔍 TABLE_SCAN 3.2%
Latency: 11.64 ms
Rows In/Out: 1,000,000
Data Vol: 26.70 MB
📁 memory.main.news_articles
⏳ ORDER_BY 🔥 47.0% Cost
Latency: 170.00 ms
Rows In/Out: 1,000,000
Data Vol: 30.52 MB
⚠️ Heavy Execution Bottleneck (47.0% of query runtime)
📐 PROJECTION 🔥 26.9% Cost
Latency: 97.09 ms
Rows In/Out: 1,000,000
Data Vol: 30.52 MB
📁 Projections: ['article_id', 'hea...
⚠️ Heavy Execution Bottleneck (26.9% of query runtime)
📐 PROJECTION 4.6%
Latency: 16.52 ms
Rows In/Out: 1,000,000
Data Vol: 26.70 MB
📁 Projections: ['article_id', 'hea...
⚙️ CROSS_PRODUCT 0.4%
Latency: 1.31 ms
Rows In/Out: 1,000,000
Data Vol: 38.15 MB
🔍 CTE_SCAN 0.9%
Latency: 3.11 ms
Rows In/Out: 1,000,000
Data Vol: 30.52 MB
📐 PROJECTION 0.0%
Latency: 0.00 ms
Rows In/Out: 1
Data Vol: 8 B
📁 Projections: #0
✂️ TOP_N 0.7%
Latency: 2.69 ms
Rows In/Out: 1
Data Vol: 12 B
📐 PROJECTION 0.0%
Latency: 0.07 ms
Rows In/Out: 1,000,000
Data Vol: 11.44 MB
📁 Projections: ['vec', 'query_sim'...
🔍 CTE_SCAN 0.6%
Latency: 2.09 ms
Rows In/Out: 1,000,000
Data Vol: 30.52 MB
Operator Cost % Latency Processed Rows Data Volume Details / Table Status
ORDER_BY 47.0% 170.00 ms 1,000,000 30.52 MB - 🔥 BOTTLENECK
PROJECTION 26.9% 97.09 ms 1,000,000 30.52 MB Projections: ['article_id', 'headline', 'query_r 🔥 BOTTLENECK
CTE 11.2% 40.50 ms 0 0.00 MB - Normal
PROJECTION 4.6% 16.52 ms 1,000,000 26.70 MB Projections: ['article_id', 'headline', 'query_s Normal
PROJECTION 4.6% 16.51 ms 1,000,000 30.52 MB Projections: ['article_id', 'headline', 'vec', ' Normal
TABLE_SCAN 3.2% 11.64 ms 1,000,000 26.70 MB memory.main.news_articles Normal
CTE_SCAN 0.9% 3.11 ms 1,000,000 30.52 MB - Normal
TOP_N 0.7% 2.69 ms 1 0.00 MB - Normal
CTE_SCAN 0.6% 2.09 ms 1,000,000 30.52 MB - Normal
CROSS_PRODUCT 0.4% 1.31 ms 1,000,000 38.15 MB - Normal
PROJECTION 0.0% 0.07 ms 1,000,000 11.44 MB Projections: ['vec', 'query_sim'] Normal
PROJECTION 0.0% 0.00 ms 1 0.00 MB Projections: #0 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: 297.35 ms) 
WITH ranked_candidates AS
  (SELECT article_id,
          headline,
          vec,
          array_cosine_similarity(vec, [1.0, 0.0]::FLOAT[2]) AS query_sim
   FROM news_articles)
SELECT c.article_id,
       c.headline,
       round(c.query_sim, 4) AS query_relevance,
       round(array_cosine_similarity(c.vec, top1.vec), 4) AS redundancy_with_top1,
       round(0.7 * c.query_sim - 0.3 * array_cosine_similarity(c.vec, top1.vec), 4) AS mmr_score
FROM ranked_candidates c
CROSS JOIN
  (SELECT vec
   FROM ranked_candidates
   ORDER BY query_sim DESC
   LIMIT 1) top1
ORDER BY mmr_score DESC;
Golden Reference Ground Truth SQL (Runtime on 1M Rows: 299.08 ms) 
-- First candidate is highest query similarity; score subsequent candidates by query sim minus similarity to article 1
 WITH ranked_candidates AS
  (SELECT article_id,
          headline,
          vec,
          array_cosine_similarity(vec, [1.0, 0.0]::FLOAT[2]) AS query_sim
   FROM news_articles)
SELECT c.article_id,
       c.headline,
       round(c.query_sim, 4) AS query_relevance,
       round(array_cosine_similarity(c.vec, top1.vec), 4) AS redundancy_with_top1, -- MMR formula: 0.7 * Sim(doc, query) - 0.3 * Sim(doc, top1)
 round(0.7 * c.query_sim - 0.3 * array_cosine_similarity(c.vec, top1.vec), 4) AS mmr_score
FROM ranked_candidates c
CROSS JOIN
  (SELECT vec
   FROM ranked_candidates
   ORDER BY query_sim DESC
   LIMIT 1) top1
ORDER BY mmr_score DESC;
DuckDB Fixture Setup DDL & Data Population 
CREATE TABLE news_articles (article_id INT, headline VARCHAR, vec FLOAT[2]);


INSERT INTO news_articles
VALUES (1, 'Fed cuts interest rates by 25 bps', [0.99, 0.05]::FLOAT[2]),
       (2, 'Central bank lowers benchmark 25 bps', [0.98, 0.06]::FLOAT[2]),
       -- Redundant clone
 (3, 'Tech stock rally follows monetary shift', [0.80, 0.45]::FLOAT[2]); -- Diverse relevant

5. Query Results & Expected Ground Truth

Tabular Comparison
Actual Candidate Output (1,000,000 rows × 5 cols)
# article_id headline query_relevance redundancy_with_top1 mmr_score
1 47,151 Fed cuts interest rates by 25 bps 0.9999 0.9999 0.4
2 47,250 Fed cuts interest rates by 25 bps 1 1 0.4
3 47,287 Central bank lowers benchmark 25 bps 0.9999 0.9999 0.4
4 47,304 Fed cuts interest rates by 25 bps 1 1 0.4
5 47,327 Tech stock rally follows monetary shift 0.9999 0.9999 0.4
6 47,355 Fed cuts interest rates by 25 bps 1 1 0.4
7 47,374 Central bank lowers benchmark 25 bps 1 1 0.4
8 47,391 Fed cuts interest rates by 25 bps 0.9999 0.9999 0.4
9 47,482 Central bank lowers benchmark 25 bps 0.9999 0.9999 0.4
10 47,492 Tech stock rally follows monetary shift 0.9999 0.9999 0.4
11 47,540 Tech stock rally follows monetary shift 0.9999 0.9999 0.4
12 47,790 Fed cuts interest rates by 25 bps 0.9999 0.9999 0.4
13 47,853 Fed cuts interest rates by 25 bps 1 1 0.4
14 47,945 Tech stock rally follows monetary shift 1 1 0.4
15 47,951 Tech stock rally follows monetary shift 1 1 0.4
16 47,994 Fed cuts interest rates by 25 bps 1 1 0.4
17 48,102 Fed cuts interest rates by 25 bps 0.9999 0.9999 0.4
18 48,126 Fed cuts interest rates by 25 bps 1 1 0.4
19 48,144 Fed cuts interest rates by 25 bps 1 1 0.4
20 48,276 Fed cuts interest rates by 25 bps 1 1 0.4
21 48,425 Tech stock rally follows monetary shift 0.9999 0.9999 0.4
22 48,480 Fed cuts interest rates by 25 bps 0.9999 0.9999 0.4
23 48,546 Fed cuts interest rates by 25 bps 1 1 0.4
24 48,767 Tech stock rally follows monetary shift 0.9999 0.9999 0.4
25 48,778 Central bank lowers benchmark 25 bps 0.9999 0.9999 0.4
Showing first 25 of 1,000,000 total rows.
Expected Golden Output (1,000,000 rows × 5 cols)
# article_id headline query_relevance redundancy_with_top1 mmr_score
1 8,257 Central bank lowers benchmark 25 bps 1 1 0.4
2 8,420 Tech stock rally follows monetary shift 1 1 0.4
3 8,439 Fed cuts interest rates by 25 bps 1 1 0.4
4 8,518 Central bank lowers benchmark 25 bps 1 1 0.4
5 8,606 Tech stock rally follows monetary shift 1 1 0.4
6 8,615 Tech stock rally follows monetary shift 0.9999 0.9999 0.4
7 8,686 Central bank lowers benchmark 25 bps 1 1 0.4
8 8,726 Tech stock rally follows monetary shift 1 1 0.4
9 8,751 Fed cuts interest rates by 25 bps 0.9999 0.9999 0.4
10 8,826 Fed cuts interest rates by 25 bps 1 1 0.4
11 8,832 Fed cuts interest rates by 25 bps 1 1 0.4
12 8,891 Tech stock rally follows monetary shift 0.9999 0.9999 0.4
13 8,944 Central bank lowers benchmark 25 bps 1 1 0.4
14 8,959 Central bank lowers benchmark 25 bps 0.9999 0.9999 0.4
15 9,093 Fed cuts interest rates by 25 bps 0.9999 0.9999 0.4
16 9,101 Tech stock rally follows monetary shift 0.9999 0.9999 0.4
17 9,493 Central bank lowers benchmark 25 bps 1 1 0.4
18 9,601 Central bank lowers benchmark 25 bps 1 1 0.4
19 9,635 Tech stock rally follows monetary shift 1 1 0.4
20 9,644 Tech stock rally follows monetary shift 0.9999 0.9999 0.4
21 9,755 Tech stock rally follows monetary shift 0.9999 0.9999 0.4
22 9,821 Tech stock rally follows monetary shift 1 1 0.4
23 9,919 Central bank lowers benchmark 25 bps 0.9999 0.9999 0.4
24 9,959 Tech stock rally follows monetary shift 1 1 0.4
25 10,080 Fed cuts interest rates by 25 bps 0.9999 0.9999 0.4
Showing first 25 of 1,000,000 total rows.
← Prev: Multi-Modal Joint Retriev... ⊞ Back to Benchmark Dashboard Next: Historical SCD Type 2 Poi... →