SQL-FRONTIER Benchmark Deep-Dive / Search & Retrieval / task_057_Q57_exact_phrase_proximity_boost
← Prev: Multi-Field Weighted Full... ⊞ All 120 Queries Next: Stopword Filtering and Qu... →
✔ PASS Search & Retrieval task_057_Q57_exact_phrase_proximity_boost Scaled: 1M+ Records Ordered: Yes

Exact Phrase Proximity Boosting in Keyword Retrieval

Candidate Latency (1M Rows)
195.90 ms
(1.1x slower than golden)
Golden Latency (1M Rows)
186.10 ms
DuckDB in-memory engine
Underlying Data Volume
1,000,000 rows
22.9 MiB across 1 table
Evaluated Model
gemini-3.8-flash-low
Gen Latency: 13.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

If a user searches for 'machine learning', an article containing the exact phrase 'machine learning' should rank higher than an article where 'machine' is in paragraph 1 and 'learning' is in paragraph 8.

⚡ The Frontier Trap (Why LLMs Fail)

Pure bag-of-words keyword matches ignore token proximity and phrase boundaries.

Prompt / User Request Given to Model
If a user searches for 'machine learning', an article containing the exact phrase 'machine learning' should rank higher than an article where 'machine' is in paragraph 1 and 'learning' is in paragraph 8.

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

2. Underlying Tables, Row Counts & Data Volume

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

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

📊 articles_proximity 1,000,000 rows 22.9 MiB Data Volume
article_id (INTEGER) text_content (VARCHAR)
Data Preview (showing sample rows from 1,000,000 total rows):
# article_id text_content
1 1 Recent breakthroughs in machine learning algorithms have transformed AI.
2 2 The washing machine broke down, so I am learning how to fix the belt.
3 3 Database indexing structures and query execution plans.
4 3 Recent breakthroughs in machine learning algorithms have transformed AI.
5 4 The washing machine broke down, so I am learning how to fix the belt.
6 5 Database indexing structures and query execution plans.
7 6 Recent breakthroughs in machine learning algorithms have transformed AI.
8 7 The washing machine broke down, so I am learning how to fix the belt.
9 8 Database indexing structures and query execution plans.
10 9 Recent breakthroughs in machine learning algorithms have transformed AI.
11 10 The washing machine broke down, so I am learning how to fix the belt.
12 11 Database indexing structures and query execution plans.
13 12 Recent breakthroughs in machine learning algorithms have transformed AI.
14 13 The washing machine broke down, so I am learning how to fix the belt.
15 14 Database indexing structures and query execution plans.

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: 245.83 ms) Nodes consuming ≥ 20% of query runtime are highlighted with glowing borders
⏳ ORDER_BY 🔥 31.3% Cost
Latency: 77.05 ms
Rows In/Out: 666,667
Data Vol: 13.99 MB
⚠️ Heavy Execution Bottleneck (31.3% of query runtime)
📐 PROJECTION 🔥 36.7% Cost
Latency: 90.13 ms
Rows In/Out: 666,667
Data Vol: 13.99 MB
📁 Projections: ['article_id', 'tex...
⚠️ Heavy Execution Bottleneck (36.7% of query runtime)
🔍 TABLE_SCAN 🔥 32.0% Cost
Latency: 78.65 ms
Rows In/Out: 666,667
Data Vol: 12.72 MB
📁 memory.main.articles_proximity
⚠️ Heavy Execution Bottleneck (32.0% of query runtime)
Operator Cost % Latency Processed Rows Data Volume Details / Table Status
PROJECTION 36.7% 90.13 ms 666,667 13.99 MB Projections: ['article_id', 'text_content', 'rel 🔥 BOTTLENECK
TABLE_SCAN 32.0% 78.65 ms 666,667 12.72 MB memory.main.articles_proximity 🔥 BOTTLENECK
ORDER_BY 31.3% 77.05 ms 666,667 13.99 MB - 🔥 BOTTLENECK

4. Queries Executed (Candidate vs Golden Ground Truth)

Beautified & Syntax Highlighted
Candidate SQL Executed by gemini-3.8-flash-low (Runtime on 1M Rows: 195.90 ms) 
-- Query: 'machine' AND 'learning', with a 5.0x boost for exact phrase match

SELECT article_id,
       text_content,
       (CASE
            WHEN text_content ILIKE '%machine%' THEN 1.0
            ELSE 0.0
        END + CASE
                  WHEN text_content ILIKE '%learning%' THEN 1.0
                  ELSE 0.0
              END + CASE
                        WHEN text_content ILIKE '%machine learning%' THEN 5.0
                        ELSE 0.0
                    END) AS relevance_score
FROM articles_proximity
WHERE text_content ILIKE '%machine%'
  AND text_content ILIKE '%learning%'
ORDER BY relevance_score DESC;
Golden Reference Ground Truth SQL (Runtime on 1M Rows: 186.10 ms) 
-- Query: 'machine' AND 'learning', with a 5.0x boost for exact phrase match

SELECT article_id,
       text_content,
       (CASE
            WHEN text_content ILIKE '%machine%' THEN 1.0
            ELSE 0.0
        END + CASE
                  WHEN text_content ILIKE '%learning%' THEN 1.0
                  ELSE 0.0
              END + CASE
                        WHEN text_content ILIKE '%machine learning%' THEN 5.0
                        ELSE 0.0
                    END) AS relevance_score
FROM articles_proximity
WHERE text_content ILIKE '%machine%'
  AND text_content ILIKE '%learning%'
ORDER BY relevance_score DESC;
DuckDB Fixture Setup DDL & Data Population 
CREATE TABLE articles_proximity (article_id INT, text_content VARCHAR);


INSERT INTO articles_proximity
VALUES (1, 'Recent breakthroughs in machine learning algorithms have transformed AI.'),
       (2, 'The washing machine broke down, so I am learning how to fix the belt.'),
       (3, 'Database indexing structures and query execution plans.');

5. Query Results & Expected Ground Truth

Tabular Comparison
Actual Candidate Output (666,667 rows × 3 cols)
# article_id text_content relevance_score
1 1 Recent breakthroughs in machine learning algorithms have transformed AI. 7
2 245,763 Recent breakthroughs in machine learning algorithms have transformed AI. 7
3 245,766 Recent breakthroughs in machine learning algorithms have transformed AI. 7
4 245,769 Recent breakthroughs in machine learning algorithms have transformed AI. 7
5 245,772 Recent breakthroughs in machine learning algorithms have transformed AI. 7
6 245,775 Recent breakthroughs in machine learning algorithms have transformed AI. 7
7 245,778 Recent breakthroughs in machine learning algorithms have transformed AI. 7
8 245,781 Recent breakthroughs in machine learning algorithms have transformed AI. 7
9 245,784 Recent breakthroughs in machine learning algorithms have transformed AI. 7
10 245,787 Recent breakthroughs in machine learning algorithms have transformed AI. 7
11 245,790 Recent breakthroughs in machine learning algorithms have transformed AI. 7
12 245,793 Recent breakthroughs in machine learning algorithms have transformed AI. 7
13 245,796 Recent breakthroughs in machine learning algorithms have transformed AI. 7
14 245,799 Recent breakthroughs in machine learning algorithms have transformed AI. 7
15 245,802 Recent breakthroughs in machine learning algorithms have transformed AI. 7
16 245,805 Recent breakthroughs in machine learning algorithms have transformed AI. 7
17 245,808 Recent breakthroughs in machine learning algorithms have transformed AI. 7
18 245,811 Recent breakthroughs in machine learning algorithms have transformed AI. 7
19 245,814 Recent breakthroughs in machine learning algorithms have transformed AI. 7
20 245,817 Recent breakthroughs in machine learning algorithms have transformed AI. 7
21 245,820 Recent breakthroughs in machine learning algorithms have transformed AI. 7
22 245,823 Recent breakthroughs in machine learning algorithms have transformed AI. 7
23 245,826 Recent breakthroughs in machine learning algorithms have transformed AI. 7
24 245,829 Recent breakthroughs in machine learning algorithms have transformed AI. 7
25 245,832 Recent breakthroughs in machine learning algorithms have transformed AI. 7
Showing first 25 of 666,667 total rows.
Expected Golden Output (666,667 rows × 3 cols)
# article_id text_content relevance_score
1 3 Recent breakthroughs in machine learning algorithms have transformed AI. 7
2 6 Recent breakthroughs in machine learning algorithms have transformed AI. 7
3 9 Recent breakthroughs in machine learning algorithms have transformed AI. 7
4 12 Recent breakthroughs in machine learning algorithms have transformed AI. 7
5 15 Recent breakthroughs in machine learning algorithms have transformed AI. 7
6 18 Recent breakthroughs in machine learning algorithms have transformed AI. 7
7 21 Recent breakthroughs in machine learning algorithms have transformed AI. 7
8 24 Recent breakthroughs in machine learning algorithms have transformed AI. 7
9 27 Recent breakthroughs in machine learning algorithms have transformed AI. 7
10 30 Recent breakthroughs in machine learning algorithms have transformed AI. 7
11 33 Recent breakthroughs in machine learning algorithms have transformed AI. 7
12 36 Recent breakthroughs in machine learning algorithms have transformed AI. 7
13 39 Recent breakthroughs in machine learning algorithms have transformed AI. 7
14 42 Recent breakthroughs in machine learning algorithms have transformed AI. 7
15 45 Recent breakthroughs in machine learning algorithms have transformed AI. 7
16 48 Recent breakthroughs in machine learning algorithms have transformed AI. 7
17 51 Recent breakthroughs in machine learning algorithms have transformed AI. 7
18 54 Recent breakthroughs in machine learning algorithms have transformed AI. 7
19 57 Recent breakthroughs in machine learning algorithms have transformed AI. 7
20 60 Recent breakthroughs in machine learning algorithms have transformed AI. 7
21 63 Recent breakthroughs in machine learning algorithms have transformed AI. 7
22 66 Recent breakthroughs in machine learning algorithms have transformed AI. 7
23 69 Recent breakthroughs in machine learning algorithms have transformed AI. 7
24 72 Recent breakthroughs in machine learning algorithms have transformed AI. 7
25 75 Recent breakthroughs in machine learning algorithms have transformed AI. 7
Showing first 25 of 666,667 total rows.
← Prev: Multi-Field Weighted Full... ⊞ Back to Benchmark Dashboard Next: Stopword Filtering and Qu... →