✔ PASS
Search & Retrieval
task_070_Q70_search_snippet_keyword_extraction
Scaled: 1M+ Records
Ordered: No
Search Snippet Extraction: Context Window Around Matching Terms
Candidate Latency (1M Rows)
152.31 ms
(1.0x faster than golden)
Golden Latency (1M Rows)
158.18 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: 18.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 TrapBusiness Scenario & Problem
Search results show a preview snippet containing the searched keyword with 30 characters of surrounding context rather than the beginning of a 2,000-word article.
⚡ The Frontier Trap (Why LLMs Fail)
Substring extraction crashes when matching keywords appear near the very beginning or end of the text.
Prompt / User Request Given to Model
Search results show a preview snippet containing the searched keyword with 30 characters of surrounding context rather than the beginning of a 2,000-word article. Execute the required analytical SQL calculation in DuckDB. Expected output columns: post_id, snippet_preview. Row order does not matter.
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:
📊 blog_posts
1,000,000 rows
22.9 MiB Data Volume
post_id (INTEGER) content (VARCHAR)Data Preview (showing sample rows from 1,000,000 total rows):
| # | post_id | content |
|---|---|---|
| 1 | 1 | In this tutorial we demonstrate high performance data analysis. The key feature of DuckDB is vectorized execution which processes data in columnar chunks for maximum CPU cache locality. |
| 2 | 2 | PostgreSQL is an advanced open source relational database with robust ACID transaction support. |
| 3 | 2 | In this tutorial we demonstrate high performance data analysis. The key feature of DuckDB is vectorized execution which processes data in columnar chunks for maximum CPU cache locality. |
| 4 | 3 | PostgreSQL is an advanced open source relational database with robust ACID transaction support. |
| 5 | 4 | In this tutorial we demonstrate high performance data analysis. The key feature of DuckDB is vectorized execution which processes data in columnar chunks for maximum CPU cache locality. |
| 6 | 5 | PostgreSQL is an advanced open source relational database with robust ACID transaction support. |
| 7 | 6 | In this tutorial we demonstrate high performance data analysis. The key feature of DuckDB is vectorized execution which processes data in columnar chunks for maximum CPU cache locality. |
| 8 | 7 | PostgreSQL is an advanced open source relational database with robust ACID transaction support. |
| 9 | 8 | In this tutorial we demonstrate high performance data analysis. The key feature of DuckDB is vectorized execution which processes data in columnar chunks for maximum CPU cache locality. |
| 10 | 9 | PostgreSQL is an advanced open source relational database with robust ACID transaction support. |
| 11 | 10 | In this tutorial we demonstrate high performance data analysis. The key feature of DuckDB is vectorized execution which processes data in columnar chunks for maximum CPU cache locality. |
| 12 | 11 | PostgreSQL is an advanced open source relational database with robust ACID transaction support. |
| 13 | 12 | In this tutorial we demonstrate high performance data analysis. The key feature of DuckDB is vectorized execution which processes data in columnar chunks for maximum CPU cache locality. |
| 14 | 13 | PostgreSQL is an advanced open source relational database with robust ACID transaction support. |
| 15 | 14 | In this tutorial we demonstrate high performance data analysis. The key feature of DuckDB is vectorized execution which processes data in columnar chunks for maximum CPU cache locality. |
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:
🔥 63.1% Cost
Latency: 160.39 ms
Rows In/Out: 500,000
Data Vol: 38.16 MB
📁 Projections: ['post_id', 'snippe...
🔥 36.9% Cost
Latency: 93.88 ms
Rows In/Out: 500,000
Data Vol: 9.54 MB
📁 memory.main.blog_posts
| Operator | Cost % | Latency | Processed Rows | Data Volume | Details / Table | Status |
|---|---|---|---|---|---|---|
| PROJECTION | 63.1% | 160.39 ms | 500,000 | 38.16 MB | Projections: ['post_id', 'snippet_preview'] | 🔥 BOTTLENECK |
| TABLE_SCAN | 36.9% | 93.88 ms | 500,000 | 9.54 MB | memory.main.blog_posts | 🔥 BOTTLENECK |
4. Queries Executed (Candidate vs Golden Ground Truth)
Beautified & Syntax HighlightedSELECT post_id,
'...' || substring(content, greatest(1, instr(lower(content), 'vectorized') - 15), length('vectorized') + 30) || '...' AS snippet_preview
FROM blog_posts
WHERE content ILIKE '%vectorized%';
-- Extract 25-character window surrounding the word 'vectorized'
SELECT post_id,
'...' || substring(content, greatest(1, instr(lower(content), 'vectorized') - 15), length('vectorized') + 30) || '...' AS snippet_preview
FROM blog_posts
WHERE content ILIKE '%vectorized%';
CREATE TABLE blog_posts (post_id INT, content VARCHAR);
INSERT INTO blog_posts
VALUES (1, 'In this tutorial we demonstrate high performance data analysis. The key feature of DuckDB is vectorized execution which processes data in columnar chunks for maximum CPU cache locality.'),
(2, 'PostgreSQL is an advanced open source relational database with robust ACID transaction support.');
5. Query Results & Expected Ground Truth
Tabular Comparison
Actual Candidate Output
(500,000 rows × 2 cols)
| # | post_id | snippet_preview |
|---|---|---|
| 1 | 1 | ...e of DuckDB is vectorized execution whic... |
| 2 | 2 | ...e of DuckDB is vectorized execution whic... |
| 3 | 4 | ...e of DuckDB is vectorized execution whic... |
| 4 | 6 | ...e of DuckDB is vectorized execution whic... |
| 5 | 8 | ...e of DuckDB is vectorized execution whic... |
| 6 | 10 | ...e of DuckDB is vectorized execution whic... |
| 7 | 12 | ...e of DuckDB is vectorized execution whic... |
| 8 | 14 | ...e of DuckDB is vectorized execution whic... |
| 9 | 16 | ...e of DuckDB is vectorized execution whic... |
| 10 | 18 | ...e of DuckDB is vectorized execution whic... |
| 11 | 20 | ...e of DuckDB is vectorized execution whic... |
| 12 | 22 | ...e of DuckDB is vectorized execution whic... |
| 13 | 24 | ...e of DuckDB is vectorized execution whic... |
| 14 | 26 | ...e of DuckDB is vectorized execution whic... |
| 15 | 28 | ...e of DuckDB is vectorized execution whic... |
| 16 | 30 | ...e of DuckDB is vectorized execution whic... |
| 17 | 32 | ...e of DuckDB is vectorized execution whic... |
| 18 | 34 | ...e of DuckDB is vectorized execution whic... |
| 19 | 36 | ...e of DuckDB is vectorized execution whic... |
| 20 | 38 | ...e of DuckDB is vectorized execution whic... |
| 21 | 40 | ...e of DuckDB is vectorized execution whic... |
| 22 | 42 | ...e of DuckDB is vectorized execution whic... |
| 23 | 44 | ...e of DuckDB is vectorized execution whic... |
| 24 | 46 | ...e of DuckDB is vectorized execution whic... |
| 25 | 48 | ...e of DuckDB is vectorized execution whic... |
Showing first 25 of 500,000 total rows.
Expected Golden Output
(500,000 rows × 2 cols)
| # | post_id | snippet_preview |
|---|---|---|
| 1 | 1 | ...e of DuckDB is vectorized execution whic... |
| 2 | 2 | ...e of DuckDB is vectorized execution whic... |
| 3 | 4 | ...e of DuckDB is vectorized execution whic... |
| 4 | 6 | ...e of DuckDB is vectorized execution whic... |
| 5 | 8 | ...e of DuckDB is vectorized execution whic... |
| 6 | 10 | ...e of DuckDB is vectorized execution whic... |
| 7 | 12 | ...e of DuckDB is vectorized execution whic... |
| 8 | 14 | ...e of DuckDB is vectorized execution whic... |
| 9 | 16 | ...e of DuckDB is vectorized execution whic... |
| 10 | 18 | ...e of DuckDB is vectorized execution whic... |
| 11 | 20 | ...e of DuckDB is vectorized execution whic... |
| 12 | 22 | ...e of DuckDB is vectorized execution whic... |
| 13 | 24 | ...e of DuckDB is vectorized execution whic... |
| 14 | 26 | ...e of DuckDB is vectorized execution whic... |
| 15 | 28 | ...e of DuckDB is vectorized execution whic... |
| 16 | 30 | ...e of DuckDB is vectorized execution whic... |
| 17 | 32 | ...e of DuckDB is vectorized execution whic... |
| 18 | 34 | ...e of DuckDB is vectorized execution whic... |
| 19 | 36 | ...e of DuckDB is vectorized execution whic... |
| 20 | 38 | ...e of DuckDB is vectorized execution whic... |
| 21 | 40 | ...e of DuckDB is vectorized execution whic... |
| 22 | 42 | ...e of DuckDB is vectorized execution whic... |
| 23 | 44 | ...e of DuckDB is vectorized execution whic... |
| 24 | 46 | ...e of DuckDB is vectorized execution whic... |
| 25 | 48 | ...e of DuckDB is vectorized execution whic... |
Showing first 25 of 500,000 total rows.