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

Hybrid Search with Hard Categorical Filter & Soft Merchant Quality Boost

Candidate Latency (1M Rows)

135.78 ms

(1.0x faster than golden)

Golden Latency (1M Rows)

139.07 ms

DuckDB in-memory engine

Underlying Data Volume

1,000,000 rows

50.5 MiB across 1 table

Evaluated Model

gemini-3.8-flash-low

Gen Latency: 14.8s

✔ 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

Top marketplaces enforce strict hard filters (in-stock, prime shipping) while soft-boosting items based on merchant review rating: final_score = text_relevance * (1.0 + 0.1 * merchant_rating).

⚡ The Frontier Trap (Why LLMs Fail)

Hard-filtering by rating eliminates high-relevance new merchants with few reviews.

Prompt / User Request Given to Model

Top marketplaces enforce strict hard filters (in-stock, prime shipping) while soft-boosting items based on merchant review rating: final_score = text_relevance * (1.0 + 0.1 * merchant_rating).

Execute the required analytical SQL calculation in DuckDB.
Expected output columns: item_id, title, merchant_rating, base_relevance, boosted_final_score.
Results should be ordered as specified.

2. Underlying Tables, Row Counts & Data Volume

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

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

📊 merchant_inventory 1,000,000 rows 50.5 MiB Data Volume

item_id (INTEGER) category (VARCHAR) title (VARCHAR) merchant_rating (DOUBLE) is_available (BOOLEAN)

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

#	item_id	category	title	merchant_rating	is_available
1	1	Audio	Pro Studio Monitor Headphones	4.9	1
2	2	Audio	Budget Studio Headphones	3.8	1
3	3	Audio	Discontinued Vintage Headphones	4.8	0
4	3	Audio	Pro Studio Monitor Headphones	4.4	1
5	4	Audio	Budget Studio Headphones	4.1	0
6	5	Audio	Discontinued Vintage Headphones	4.51	1
7	6	Audio	Pro Studio Monitor Headphones	5.41	0
8	7	Audio	Budget Studio Headphones	3.51	1
9	8	Audio	Discontinued Vintage Headphones	4.67	0
10	9	Audio	Pro Studio Monitor Headphones	2.93	1
11	10	Audio	Budget Studio Headphones	5.04	0
12	11	Audio	Discontinued Vintage Headphones	3.55	1
13	12	Audio	Pro Studio Monitor Headphones	4.62	0
14	13	Audio	Budget Studio Headphones	4.52	1
15	14	Audio	Discontinued Vintage Headphones	3.97	0

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

⏳ ORDER_BY 🔥 59.0% Cost

Latency: 94.90 ms

Rows In/Out: 500,001

Data Vol: 18.12 MB

📐 PROJECTION 13.4%

Latency: 21.51 ms

Rows In/Out: 500,001

Data Vol: 18.12 MB

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

📐 PROJECTION 7.9%

Latency: 12.75 ms

Rows In/Out: 500,001

Data Vol: 14.31 MB

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

🔍 TABLE_SCAN 19.7%

Latency: 31.77 ms

Rows In/Out: 500,001

Data Vol: 13.35 MB

📁 memory.main.merchant_inventory

Operator	Cost %	Latency	Processed Rows	Data Volume	Details / Table	Status
ORDER_BY	59.0%	94.90 ms	500,001	18.12 MB	-	🔥 BOTTLENECK
TABLE_SCAN	19.7%	31.77 ms	500,001	13.35 MB	memory.main.merchant_inventory	Normal
PROJECTION	13.4%	21.51 ms	500,001	18.12 MB	Projections: ['item_id', 'title', 'merchant_rati	Normal
PROJECTION	7.9%	12.75 ms	500,001	14.31 MB	Projections: ['item_id', 'title', 'merchant_rati	Normal

4. Queries Executed (Candidate vs Golden Ground Truth)

Beautified & Syntax Highlighted

-- Search 'Headphones' in 'Audio': Hard filter available=true, soft-boost by merchant rating
WITH text_matches AS
  (SELECT item_id,
          title,
          merchant_rating, -- Base relevance score from keyword matches
 (CASE
      WHEN title ILIKE '%studio%' THEN 2.0
      ELSE 1.0
  END) AS base_relevance
   FROM merchant_inventory
   WHERE category = 'Audio'
     AND is_available = TRUE
     AND title ILIKE '%Headphones%')
SELECT item_id,
       title,
       merchant_rating,
       base_relevance,
       round(base_relevance * (1.0 + (merchant_rating / 5.0) * 0.50), 2) AS boosted_final_score
FROM text_matches
ORDER BY boosted_final_score DESC;

-- Search 'Headphones' in 'Audio': Hard filter available=true, soft-boost by merchant rating
 WITH text_matches AS
  (SELECT item_id,
          title,
          merchant_rating, -- Base relevance score from keyword matches
 (CASE
      WHEN title ILIKE '%studio%' THEN 2.0
      ELSE 1.0
  END) AS base_relevance
   FROM merchant_inventory
   WHERE category = 'Audio'
     AND is_available = TRUE
     AND title ILIKE '%Headphones%')
SELECT item_id,
       title,
       merchant_rating,
       base_relevance,
       round(base_relevance * (1.0 + (merchant_rating / 5.0) * 0.50), 2) AS boosted_final_score
FROM text_matches
ORDER BY boosted_final_score DESC;

CREATE TABLE merchant_inventory (item_id INT, category VARCHAR, title VARCHAR, merchant_rating DOUBLE,
                                                                                               is_available BOOLEAN);


INSERT INTO merchant_inventory
VALUES (1, 'Audio', 'Pro Studio Monitor Headphones', 4.9, TRUE),
       (2, 'Audio', 'Budget Studio Headphones', 3.8, TRUE),
       (3, 'Audio', 'Discontinued Vintage Headphones', 4.8, FALSE);

5. Query Results & Expected Ground Truth

Tabular Comparison

Actual Candidate Output (500,001 rows × 5 cols)

#	item_id	title	merchant_rating	base_relevance	boosted_final_score
1	784,213	Budget Studio Headphones	7.19	2	3.44
2	303,279	Pro Studio Monitor Headphones	7.16	2	3.43
3	731,113	Budget Studio Headphones	7.15	2	3.43
4	489,013	Budget Studio Headphones	7.05	2	3.41
5	357,721	Budget Studio Headphones	7.01	2	3.4
6	65,473	Budget Studio Headphones	6.96	2	3.39
7	158,607	Pro Studio Monitor Headphones	6.96	2	3.39
8	393,373	Budget Studio Headphones	6.9	2	3.38
9	625,867	Budget Studio Headphones	6.89	2	3.38
10	332,067	Pro Studio Monitor Headphones	6.86	2	3.37
11	358,081	Budget Studio Headphones	6.86	2	3.37
12	991,677	Pro Studio Monitor Headphones	6.83	2	3.37
13	949,417	Budget Studio Headphones	6.84	2	3.37
14	933,771	Pro Studio Monitor Headphones	6.83	2	3.37
15	366,745	Budget Studio Headphones	6.82	2	3.36
16	255,531	Pro Studio Monitor Headphones	6.8	2	3.36
17	580,095	Pro Studio Monitor Headphones	6.81	2	3.36
18	48,799	Budget Studio Headphones	6.82	2	3.36
19	40,287	Pro Studio Monitor Headphones	6.79	2	3.36
20	238,269	Pro Studio Monitor Headphones	6.82	2	3.36
21	142,479	Pro Studio Monitor Headphones	6.81	2	3.36
22	924,705	Pro Studio Monitor Headphones	6.82	2	3.36
23	201,187	Budget Studio Headphones	6.78	2	3.36
24	920,583	Pro Studio Monitor Headphones	6.81	2	3.36
25	834,657	Pro Studio Monitor Headphones	6.74	2	3.35

Showing first 25 of 500,001 total rows.

Expected Golden Output (500,001 rows × 5 cols)

#	item_id	title	merchant_rating	base_relevance	boosted_final_score
1	784,213	Budget Studio Headphones	7.19	2	3.44
2	303,279	Pro Studio Monitor Headphones	7.16	2	3.43
3	731,113	Budget Studio Headphones	7.15	2	3.43
4	489,013	Budget Studio Headphones	7.05	2	3.41
5	357,721	Budget Studio Headphones	7.01	2	3.4
6	158,607	Pro Studio Monitor Headphones	6.96	2	3.39
7	65,473	Budget Studio Headphones	6.96	2	3.39
8	393,373	Budget Studio Headphones	6.9	2	3.38
9	625,867	Budget Studio Headphones	6.89	2	3.38
10	358,081	Budget Studio Headphones	6.86	2	3.37
11	332,067	Pro Studio Monitor Headphones	6.86	2	3.37
12	949,417	Budget Studio Headphones	6.84	2	3.37
13	933,771	Pro Studio Monitor Headphones	6.83	2	3.37
14	991,677	Pro Studio Monitor Headphones	6.83	2	3.37
15	580,095	Pro Studio Monitor Headphones	6.81	2	3.36
16	255,531	Pro Studio Monitor Headphones	6.8	2	3.36
17	366,745	Budget Studio Headphones	6.82	2	3.36
18	924,705	Pro Studio Monitor Headphones	6.82	2	3.36
19	920,583	Pro Studio Monitor Headphones	6.81	2	3.36
20	201,187	Budget Studio Headphones	6.78	2	3.36
21	238,269	Pro Studio Monitor Headphones	6.82	2	3.36
22	142,479	Pro Studio Monitor Headphones	6.81	2	3.36
23	40,287	Pro Studio Monitor Headphones	6.79	2	3.36
24	48,799	Budget Studio Headphones	6.82	2	3.36
25	557,839	Budget Studio Headphones	6.73	2	3.35

Showing first 25 of 500,001 total rows.

← Prev: 'Did You Mean?' Query Spe... ⊞ Back to Benchmark Dashboard Next: Hierarchical Bill of Mate... →