SQL-FRONTIER Benchmark Deep-Dive / Vector & Embeddings / task_030_Q30_adaptive_similarity_threshold
← Prev: Clickstream Sessionizatio... ⊞ All 120 Queries Next: Semantic Deduplication of... →
✔ PASS Vector & Embeddings task_030_Q30_adaptive_similarity_threshold Scaled: 1M+ Records Ordered: Yes

Adaptive Similarity Thresholding with Fallback Top-K Guarantee

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

RAG systems want high-confidence matches (similarity > 0.85) to avoid hallucinations, but if no document exceeds 0.85, they need a guaranteed top-1 fallback to provide at least some context.

⚡ The Frontier Trap (Why LLMs Fail)

A hard WHERE similarity > 0.85 yields empty sets, while a raw LIMIT 5 includes irrelevant noise.

Prompt / User Request Given to Model
RAG systems want high-confidence matches (similarity > 0.85) to avoid hallucinations, but if no document exceeds 0.85, they need a guaranteed top-1 fallback to provide at least some context.

Execute the required analytical SQL calculation in DuckDB.
Expected output columns: kb_id, content, sim_score, rank_pos, match_type.
Results should be ordered as specified.

2. Underlying Tables, Row Counts & Data Volume

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

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

📊 knowledge_base 1,000,000 rows 34.3 MiB Data Volume
kb_id (INTEGER) content (VARCHAR) vector (FLOAT[3])
Data Preview (showing sample rows from 1,000,000 total rows):
# kb_id content vector
1 1 DuckDB uses columnar storage [0.4 0.4 0.1]
2 2 DuckDB vector execution engine [0.5 0.3 0.2]
3 3 French cuisine recipes [0. 0.1 0.9]
4 3 DuckDB uses columnar storage [0.8435 0.0527 0.0321]
5 4 DuckDB vector execution engine [0.8887 0.0992 0.0642]
6 5 French cuisine recipes [0.8195 0.0301 0.043 ]
7 6 DuckDB uses columnar storage [0.8694 0.0932 0.0051]
8 7 DuckDB vector execution engine [0.8209 0.0335 0.0929]
9 8 French cuisine recipes [0.7989 0.0078 0.0216]
10 9 DuckDB uses columnar storage [ 0.8599 -0.0056 -0.0088]
11 10 DuckDB vector execution engine [0.8651 0.0429 0.0382]
12 11 French cuisine recipes [0.8852 0.0943 0.0185]
13 12 DuckDB uses columnar storage [ 0.7847 -0.0333 0.023 ]
14 13 DuckDB vector execution engine [0.7936 0.0714 0.0096]
15 14 French cuisine recipes [0.8915 0.0236 0.1403]

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: 609.80 ms) Nodes consuming ≥ 20% of query runtime are highlighted with glowing borders
⏳ ORDER_BY 🔥 17.6% Cost
Latency: 107.34 ms
Rows In/Out: 999,998
Data Vol: 45.78 MB
⚠️ Heavy Execution Bottleneck (17.6% of query runtime)
📐 PROJECTION 4.8%
Latency: 29.47 ms
Rows In/Out: 999,998
Data Vol: 47.69 MB
📁 Projections: ['kb_id', 'content'...
📐 PROJECTION 3.4%
Latency: 20.67 ms
Rows In/Out: 999,998
Data Vol: 30.52 MB
📁 Projections: ['kb_id', 'content'...
🎯 FILTER 5.0%
Latency: 30.62 ms
Rows In/Out: 999,998
Data Vol: 38.15 MB
📐 PROJECTION 0.0%
Latency: 0.20 ms
Rows In/Out: 1,000,000
Data Vol: 38.15 MB
📁 Projections: ['#0', '#1', '#2', ...
🪟 WINDOW 🔥 66.9% Cost
Latency: 408.21 ms
Rows In/Out: 1,000,000
Data Vol: 38.15 MB
📁 Projections: row_number() OVER (...
⚠️ Heavy Execution Bottleneck (66.9% of query runtime)
🔍 TABLE_SCAN 2.2%
Latency: 13.28 ms
Rows In/Out: 1,000,000
Data Vol: 30.52 MB
📁 memory.main.knowledge_base
Operator Cost % Latency Processed Rows Data Volume Details / Table Status
WINDOW 66.9% 408.21 ms 1,000,000 38.15 MB Projections: row_number() OVER (ORDER BY array_c 🔥 BOTTLENECK
ORDER_BY 17.6% 107.34 ms 999,998 45.78 MB - 🔥 BOTTLENECK
FILTER 5.0% 30.62 ms 999,998 38.15 MB - Normal
PROJECTION 4.8% 29.47 ms 999,998 47.69 MB Projections: ['kb_id', 'content', 'sim_score', ' Normal
PROJECTION 3.4% 20.67 ms 999,998 30.52 MB Projections: ['kb_id', 'content', 'sim_score', ' Normal
TABLE_SCAN 2.2% 13.28 ms 1,000,000 30.52 MB memory.main.knowledge_base Normal
PROJECTION 0.0% 0.20 ms 1,000,000 38.15 MB Projections: ['#0', '#1', '#2', '#3'] 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: 504.18 ms) 
-- Score items, guarantee top 1 fallback even if below 0.85 threshold
WITH scored AS
  (SELECT kb_id,
          content,
          array_cosine_similarity(vector, [0.9, 0.2, 0.1]::FLOAT[3]) AS sim_score,
          row_number() OVER (
                             ORDER BY array_cosine_similarity(vector, [0.9, 0.2, 0.1]::FLOAT[3]) DESC) AS rank_pos
   FROM knowledge_base)
SELECT kb_id,
       content,
       round(sim_score, 4) AS sim_score,
       rank_pos,
       CASE
           WHEN sim_score >= 0.85 THEN 'CONFIDENT_MATCH'
           ELSE 'FALLBACK_TOP_MATCH'
       END AS match_type
FROM scored
WHERE sim_score >= 0.85
  OR rank_pos = 1
ORDER BY rank_pos;
Golden Reference Ground Truth SQL (Runtime on 1M Rows: 435.48 ms) 
-- Score items, guarantee top 1 fallback even if below 0.85 threshold
 WITH scored AS
  (SELECT kb_id,
          content,
          array_cosine_similarity(vector, [0.9, 0.2, 0.1]::FLOAT[3]) AS sim_score,
          row_number() OVER (
                             ORDER BY array_cosine_similarity(vector, [0.9, 0.2, 0.1]::FLOAT[3]) DESC) AS rank_pos
   FROM knowledge_base)
SELECT kb_id,
       content,
       round(sim_score, 4) AS sim_score,
       rank_pos,
       CASE
           WHEN sim_score >= 0.85 THEN 'CONFIDENT_MATCH'
           ELSE 'FALLBACK_TOP_MATCH'
       END AS match_type
FROM scored
WHERE sim_score >= 0.85
  OR rank_pos = 1
ORDER BY rank_pos;
DuckDB Fixture Setup DDL & Data Population 
CREATE TABLE knowledge_base (kb_id INT, content VARCHAR, vector FLOAT[3]);


INSERT INTO knowledge_base
VALUES (1, 'DuckDB uses columnar storage', [0.4, 0.4, 0.1]::FLOAT[3]),
       (2, 'DuckDB vector execution engine', [0.5, 0.3, 0.2]::FLOAT[3]),
       (3, 'French cuisine recipes', [0.0, 0.1, 0.9]::FLOAT[3]);

5. Query Results & Expected Ground Truth

Tabular Comparison
Actual Candidate Output (999,998 rows × 5 cols)
# kb_id content sim_score rank_pos match_type
1 139,069 DuckDB vector execution engine 1 1 CONFIDENT_MATCH
2 225,018 DuckDB uses columnar storage 1 2 CONFIDENT_MATCH
3 545,553 DuckDB uses columnar storage 1 3 CONFIDENT_MATCH
4 306,978 DuckDB uses columnar storage 1 4 CONFIDENT_MATCH
5 180,269 French cuisine recipes 1 5 CONFIDENT_MATCH
6 438,322 DuckDB vector execution engine 1 6 CONFIDENT_MATCH
7 81,756 DuckDB uses columnar storage 1 7 CONFIDENT_MATCH
8 711,399 DuckDB uses columnar storage 1 8 CONFIDENT_MATCH
9 586,884 DuckDB uses columnar storage 1 9 CONFIDENT_MATCH
10 526,262 French cuisine recipes 1 10 CONFIDENT_MATCH
11 940,273 DuckDB vector execution engine 1 11 CONFIDENT_MATCH
12 134,824 DuckDB vector execution engine 1 12 CONFIDENT_MATCH
13 621,954 DuckDB uses columnar storage 1 13 CONFIDENT_MATCH
14 514,106 French cuisine recipes 1 14 CONFIDENT_MATCH
15 886,833 DuckDB uses columnar storage 1 15 CONFIDENT_MATCH
16 788,019 DuckDB uses columnar storage 1 16 CONFIDENT_MATCH
17 738,440 French cuisine recipes 1 17 CONFIDENT_MATCH
18 846,651 DuckDB uses columnar storage 1 18 CONFIDENT_MATCH
19 658,996 DuckDB vector execution engine 1 19 CONFIDENT_MATCH
20 56,055 DuckDB uses columnar storage 1 20 CONFIDENT_MATCH
21 478,506 DuckDB uses columnar storage 1 21 CONFIDENT_MATCH
22 623,633 French cuisine recipes 1 22 CONFIDENT_MATCH
23 684,080 French cuisine recipes 1 23 CONFIDENT_MATCH
24 968,490 DuckDB uses columnar storage 1 24 CONFIDENT_MATCH
25 813,524 French cuisine recipes 1 25 CONFIDENT_MATCH
Showing first 25 of 999,998 total rows.
Expected Golden Output (999,998 rows × 5 cols)
# kb_id content sim_score rank_pos match_type
1 139,069 DuckDB vector execution engine 1 1 CONFIDENT_MATCH
2 225,018 DuckDB uses columnar storage 1 2 CONFIDENT_MATCH
3 545,553 DuckDB uses columnar storage 1 3 CONFIDENT_MATCH
4 306,978 DuckDB uses columnar storage 1 4 CONFIDENT_MATCH
5 180,269 French cuisine recipes 1 5 CONFIDENT_MATCH
6 438,322 DuckDB vector execution engine 1 6 CONFIDENT_MATCH
7 81,756 DuckDB uses columnar storage 1 7 CONFIDENT_MATCH
8 711,399 DuckDB uses columnar storage 1 8 CONFIDENT_MATCH
9 586,884 DuckDB uses columnar storage 1 9 CONFIDENT_MATCH
10 526,262 French cuisine recipes 1 10 CONFIDENT_MATCH
11 940,273 DuckDB vector execution engine 1 11 CONFIDENT_MATCH
12 134,824 DuckDB vector execution engine 1 12 CONFIDENT_MATCH
13 621,954 DuckDB uses columnar storage 1 13 CONFIDENT_MATCH
14 514,106 French cuisine recipes 1 14 CONFIDENT_MATCH
15 886,833 DuckDB uses columnar storage 1 15 CONFIDENT_MATCH
16 788,019 DuckDB uses columnar storage 1 16 CONFIDENT_MATCH
17 738,440 French cuisine recipes 1 17 CONFIDENT_MATCH
18 846,651 DuckDB uses columnar storage 1 18 CONFIDENT_MATCH
19 658,996 DuckDB vector execution engine 1 19 CONFIDENT_MATCH
20 56,055 DuckDB uses columnar storage 1 20 CONFIDENT_MATCH
21 478,506 DuckDB uses columnar storage 1 21 CONFIDENT_MATCH
22 623,633 French cuisine recipes 1 22 CONFIDENT_MATCH
23 684,080 French cuisine recipes 1 23 CONFIDENT_MATCH
24 968,490 DuckDB uses columnar storage 1 24 CONFIDENT_MATCH
25 813,524 French cuisine recipes 1 25 CONFIDENT_MATCH
Showing first 25 of 999,998 total rows.
← Prev: Clickstream Sessionizatio... ⊞ Back to Benchmark Dashboard Next: Semantic Deduplication of... →