✔ PASS Search & Retrieval task_056_Q56_multi_field_weighted_scoring Scaled: 1M+ Records Ordered: Yes

Multi-Field Weighted Full-Text Retrieval (Title 3x, Body 1x)

Candidate Latency (1M Rows)

197.27 ms

(1.0x faster than golden)

Golden Latency (1M Rows)

200.39 ms

DuckDB in-memory engine

Underlying Data Volume

1,000,000 rows

42.0 MiB across 1 table

Evaluated Model

gemini-3.8-flash-low

Gen Latency: 9.6s

✔ 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

A match in a document title or product headline is far more indicative of relevance than a passing mention in the body text.

⚡ The Frontier Trap (Why LLMs Fail)

Concatenating title and body into a single column gives equal weight to all words.

Prompt / User Request Given to Model

A match in a document title or product headline is far more indicative of relevance than a passing mention in the body text.

Execute the required analytical SQL calculation in DuckDB.
Expected output columns: post_id, title, multi_field_score.
Results should be ordered as specified.

2. Underlying Tables, Row Counts & Data Volume

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

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

📊 product_posts 1,000,000 rows 42.0 MiB Data Volume

post_id (INTEGER) title (VARCHAR) body (VARCHAR)

Data Preview (showing sample rows from 1,000,000 total rows):

#	post_id	title	body
1	1	Wireless Noise Canceling Headphones	These over-ear headphones feature active noise cancelling technology.
2	2	Travel Accessories and Gadgets	A complete list of travel gear including a wireless charging pad and cables.
3	3	Coffee Brewing Machines	Best espresso makers and grinders for home brewing.
4	3	Wireless Noise Canceling Headphones	These over-ear headphones feature active noise cancelling technology.
5	4	Travel Accessories and Gadgets	A complete list of travel gear including a wireless charging pad and cables.
6	5	Coffee Brewing Machines	Best espresso makers and grinders for home brewing.
7	6	Wireless Noise Canceling Headphones	These over-ear headphones feature active noise cancelling technology.
8	7	Travel Accessories and Gadgets	A complete list of travel gear including a wireless charging pad and cables.
9	8	Coffee Brewing Machines	Best espresso makers and grinders for home brewing.
10	9	Wireless Noise Canceling Headphones	These over-ear headphones feature active noise cancelling technology.
11	10	Travel Accessories and Gadgets	A complete list of travel gear including a wireless charging pad and cables.
12	11	Coffee Brewing Machines	Best espresso makers and grinders for home brewing.
13	12	Wireless Noise Canceling Headphones	These over-ear headphones feature active noise cancelling technology.
14	13	Travel Accessories and Gadgets	A complete list of travel gear including a wireless charging pad and cables.
15	14	Coffee Brewing Machines	Best espresso makers and grinders for home brewing.

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: 307.69 ms) Nodes consuming ≥ 20% of query runtime are highlighted with glowing borders

⏳ ORDER_BY 🔥 19.5% Cost

Latency: 60.04 ms

Rows In/Out: 666,667

Data Vol: 15.26 MB

📐 PROJECTION 🔥 30.7% Cost

Latency: 94.31 ms

Rows In/Out: 666,667

Data Vol: 15.26 MB

📁 Projections: ['post_id', 'title'...

🎯 FILTER 🔥 43.8% Cost

Latency: 134.76 ms

Rows In/Out: 666,667

Data Vol: 22.90 MB

🔍 TABLE_SCAN 6.0%

Latency: 18.59 ms

Rows In/Out: 1,000,000

Data Vol: 34.33 MB

📁 memory.main.product_posts

Operator	Cost %	Latency	Processed Rows	Data Volume	Details / Table	Status
FILTER	43.8%	134.76 ms	666,667	22.90 MB	-	🔥 BOTTLENECK
PROJECTION	30.7%	94.31 ms	666,667	15.26 MB	Projections: ['post_id', 'title', 'multi_field_s	🔥 BOTTLENECK
ORDER_BY	19.5%	60.04 ms	666,667	15.26 MB	-	🔥 BOTTLENECK
TABLE_SCAN	6.0%	18.59 ms	1,000,000	34.33 MB	memory.main.product_posts	Normal

4. Queries Executed (Candidate vs Golden Ground Truth)

Beautified & Syntax Highlighted

-- Search for 'wireless headphones': Title matches weighted 3.0x, Body matches weighted 1.0x
WITH scored AS
  (SELECT post_id,
          title,
          round((CASE
                     WHEN title ILIKE '%wireless%' THEN 3.0
                     ELSE 0.0
                 END) + (CASE
                             WHEN title ILIKE '%headphones%' THEN 3.0
                             ELSE 0.0
                         END) + (CASE
                                     WHEN BODY ILIKE '%wireless%' THEN 1.0
                                     ELSE 0.0
                                 END) + (CASE
                                             WHEN BODY ILIKE '%headphones%' THEN 1.0
                                             ELSE 0.0
                                         END), 2) AS multi_field_score
   FROM product_posts)
SELECT post_id,
       title,
       multi_field_score
FROM scored
WHERE multi_field_score > 0
ORDER BY multi_field_score DESC;

-- Search for 'wireless headphones': Title matches weighted 3.0x, Body matches weighted 1.0x
 WITH scored AS
  (SELECT post_id,
          title,
          round((CASE
                     WHEN title ILIKE '%wireless%' THEN 3.0
                     ELSE 0.0
                 END) + (CASE
                             WHEN title ILIKE '%headphones%' THEN 3.0
                             ELSE 0.0
                         END) + (CASE
                                     WHEN BODY ILIKE '%wireless%' THEN 1.0
                                     ELSE 0.0
                                 END) + (CASE
                                             WHEN BODY ILIKE '%headphones%' THEN 1.0
                                             ELSE 0.0
                                         END), 2) AS multi_field_score
   FROM product_posts)
SELECT post_id,
       title,
       multi_field_score
FROM scored
WHERE multi_field_score > 0
ORDER BY multi_field_score DESC;

CREATE TABLE product_posts (post_id INT, title VARCHAR, BODY VARCHAR);


INSERT INTO product_posts
VALUES (1, 'Wireless Noise Canceling Headphones', 'These over-ear headphones feature active noise cancelling technology.'),
       (2, 'Travel Accessories and Gadgets', 'A complete list of travel gear including a wireless charging pad and cables.'),
       (3, 'Coffee Brewing Machines', 'Best espresso makers and grinders for home brewing.');

5. Query Results & Expected Ground Truth

Tabular Comparison

Actual Candidate Output (666,667 rows × 3 cols)

#	post_id	title	multi_field_score
1	3	Wireless Noise Canceling Headphones	7
2	6	Wireless Noise Canceling Headphones	7
3	9	Wireless Noise Canceling Headphones	7
4	12	Wireless Noise Canceling Headphones	7
5	15	Wireless Noise Canceling Headphones	7
6	18	Wireless Noise Canceling Headphones	7
7	21	Wireless Noise Canceling Headphones	7
8	24	Wireless Noise Canceling Headphones	7
9	27	Wireless Noise Canceling Headphones	7
10	30	Wireless Noise Canceling Headphones	7
11	33	Wireless Noise Canceling Headphones	7
12	36	Wireless Noise Canceling Headphones	7
13	39	Wireless Noise Canceling Headphones	7
14	42	Wireless Noise Canceling Headphones	7
15	45	Wireless Noise Canceling Headphones	7
16	48	Wireless Noise Canceling Headphones	7
17	51	Wireless Noise Canceling Headphones	7
18	54	Wireless Noise Canceling Headphones	7
19	57	Wireless Noise Canceling Headphones	7
20	60	Wireless Noise Canceling Headphones	7
21	63	Wireless Noise Canceling Headphones	7
22	66	Wireless Noise Canceling Headphones	7
23	69	Wireless Noise Canceling Headphones	7
24	72	Wireless Noise Canceling Headphones	7
25	75	Wireless Noise Canceling Headphones	7

Showing first 25 of 666,667 total rows.

Expected Golden Output (666,667 rows × 3 cols)

#	post_id	title	multi_field_score
1	3	Wireless Noise Canceling Headphones	7
2	6	Wireless Noise Canceling Headphones	7
3	9	Wireless Noise Canceling Headphones	7
4	12	Wireless Noise Canceling Headphones	7
5	15	Wireless Noise Canceling Headphones	7
6	18	Wireless Noise Canceling Headphones	7
7	21	Wireless Noise Canceling Headphones	7
8	24	Wireless Noise Canceling Headphones	7
9	27	Wireless Noise Canceling Headphones	7
10	30	Wireless Noise Canceling Headphones	7
11	33	Wireless Noise Canceling Headphones	7
12	36	Wireless Noise Canceling Headphones	7
13	39	Wireless Noise Canceling Headphones	7
14	42	Wireless Noise Canceling Headphones	7
15	45	Wireless Noise Canceling Headphones	7
16	48	Wireless Noise Canceling Headphones	7
17	51	Wireless Noise Canceling Headphones	7
18	54	Wireless Noise Canceling Headphones	7
19	57	Wireless Noise Canceling Headphones	7
20	60	Wireless Noise Canceling Headphones	7
21	63	Wireless Noise Canceling Headphones	7
22	66	Wireless Noise Canceling Headphones	7
23	69	Wireless Noise Canceling Headphones	7
24	72	Wireless Noise Canceling Headphones	7
25	75	Wireless Noise Canceling Headphones	7

Showing first 25 of 666,667 total rows.

← Prev: Brand & Name Fuzzy Search... ⊞ Back to Benchmark Dashboard Next: Exact Phrase Proximity Bo... →