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

Dynamic Query Synonym Expansion via Relational Thesaurus

Candidate Latency (1M Rows)

211.77 ms

(1.1x faster than golden)

Golden Latency (1M Rows)

228.99 ms

DuckDB in-memory engine

Underlying Data Volume

1,000,003 rows

22.9 MiB across 2 tables

Evaluated Model

gemini-3.8-flash-low

Gen Latency: 12.0s

✔ 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

When a customer searches for 'sofa', they also expect to see listings titled 'couch' or 'settee'. Expanding queries using a synonym thesaurus bridges terminology gaps.

⚡ The Frontier Trap (Why LLMs Fail)

Hardcoding synonyms into client-side application code makes vocabulary updates inflexible.

Prompt / User Request Given to Model

When a customer searches for 'sofa', they also expect to see listings titled 'couch' or 'settee'. Expanding queries using a synonym thesaurus bridges terminology gaps.

Execute the required analytical SQL calculation in DuckDB.
Expected output columns: item_id, description, matched_alias.
Results should be ordered as specified.

2. Underlying Tables, Row Counts & Data Volume

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

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

📊 furniture_items 1,000,000 rows 22.9 MiB Data Volume

item_id (INTEGER) description (VARCHAR)

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

#	item_id	description
1	1	Modern Italian leather sofa with oak legs
2	2	Vintage velvet couch in emerald green
3	3	Solid wood dining table and 6 chairs
4	3	Modern Italian leather sofa with oak legs
5	4	Vintage velvet couch in emerald green
6	5	Solid wood dining table and 6 chairs
7	6	Modern Italian leather sofa with oak legs
8	7	Vintage velvet couch in emerald green
9	8	Solid wood dining table and 6 chairs
10	9	Modern Italian leather sofa with oak legs
11	10	Vintage velvet couch in emerald green
12	11	Solid wood dining table and 6 chairs
13	12	Modern Italian leather sofa with oak legs
14	13	Vintage velvet couch in emerald green
15	14	Solid wood dining table and 6 chairs

📊 synonym_thesaurus 3 rows 120 B Data Volume

base_term (VARCHAR) synonym (VARCHAR)

Data Preview (showing sample rows from 3 total rows):

#	base_term	synonym
1	sofa	couch
2	sofa	settee
3	sofa	loveseat

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

📐 PROJECTION 2.4%

Latency: 4.49 ms

Rows In/Out: 666,667

Data Vol: 22.89 MB

📁 Projections: ['#0', '#1', '__int...

⏳ ORDER_BY 🔥 33.1% Cost

Latency: 61.62 ms

Rows In/Out: 666,667

Data Vol: 22.89 MB

📐 PROJECTION 0.3%

Latency: 0.63 ms

Rows In/Out: 666,667

Data Vol: 22.89 MB

📁 Projections: ['#0', '#1', '__int...

📐 PROJECTION 0.1%

Latency: 0.21 ms

Rows In/Out: 666,667

Data Vol: 22.89 MB

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

⚡ BLOCKWISE_NL_JOIN 🔥 58.7% Cost

Latency: 109.39 ms

Rows In/Out: 666,667

Data Vol: 22.89 MB

🔍 TABLE_SCAN 5.0%

Latency: 9.30 ms

Rows In/Out: 1,000,000

Data Vol: 19.07 MB

📁 memory.main.furniture_items

📐 PROJECTION 0.0%

Latency: 0.00 ms

Rows In/Out: 4

Data Vol: 64 B

📁 Projections: __internal_decompre...

📊 HASH_GROUP_BY 0.3%

Latency: 0.55 ms

Rows In/Out: 4

Data Vol: 64 B

📐 PROJECTION 0.0%

Latency: 0.00 ms

Rows In/Out: 4

Data Vol: 64 B

📁 Projections: search_term

📐 PROJECTION 0.0%

Latency: 0.01 ms

Rows In/Out: 4

Data Vol: 64 B

📁 Projections: __internal_compress...

⚙️ UNION 0.0%

Latency: 0.00 ms

Rows In/Out: 0

Data Vol: 0 B

📐 PROJECTION 0.0%

Latency: 0.00 ms

Rows In/Out: 1

Data Vol: 16 B

📁 Projections: search_term

🔍 DUMMY_SCAN 0.0%

Latency: 0.00 ms

Rows In/Out: 1

Data Vol: 4 B

🔍 TABLE_SCAN 0.0%

Latency: 0.01 ms

Rows In/Out: 3

Data Vol: 48 B

📁 memory.main.synonym_thesaurus

Operator	Cost %	Latency	Processed Rows	Data Volume	Details / Table	Status
BLOCKWISE_NL_JOIN	58.7%	109.39 ms	666,667	22.89 MB	-	🔥 BOTTLENECK
ORDER_BY	33.1%	61.62 ms	666,667	22.89 MB	-	🔥 BOTTLENECK
TABLE_SCAN	5.0%	9.30 ms	1,000,000	19.07 MB	memory.main.furniture_items	Normal
PROJECTION	2.4%	4.49 ms	666,667	22.89 MB	Projections: ['#0', '#1', '__internal_decompress	Normal
PROJECTION	0.3%	0.63 ms	666,667	22.89 MB	Projections: ['#0', '#1', '__internal_compress_s	Normal
HASH_GROUP_BY	0.3%	0.55 ms	4	0.00 MB	-	Normal
PROJECTION	0.1%	0.21 ms	666,667	22.89 MB	Projections: ['item_id', 'description', 'matched	Normal
PROJECTION	0.0%	0.01 ms	4	0.00 MB	Projections: __internal_compress_string_uhugeint	Normal
TABLE_SCAN	0.0%	0.01 ms	3	0.00 MB	memory.main.synonym_thesaurus	Normal
PROJECTION	0.0%	0.00 ms	4	0.00 MB	Projections: __internal_decompress_string(#0)	Normal
PROJECTION	0.0%	0.00 ms	1	0.00 MB	Projections: search_term	Normal
DUMMY_SCAN	0.0%	0.00 ms	1	0.00 MB	-	Normal
PROJECTION	0.0%	0.00 ms	4	0.00 MB	Projections: search_term	Normal
UNION	0.0%	0.00 ms	0	0.00 MB	-	Normal

4. Queries Executed (Candidate vs Golden Ground Truth)

Beautified & Syntax Highlighted

WITH expanded_terms AS
  (SELECT 'sofa' AS search_term
   UNION SELECT SYNONYM
   FROM synonym_thesaurus
   WHERE base_term = 'sofa')
SELECT f.item_id,
       f.description,
       t.search_term AS matched_alias
FROM furniture_items f
JOIN expanded_terms t ON f.description ILIKE ('%' || t.search_term || '%')
ORDER BY f.item_id;

-- Expand search term 'sofa' with all relational synonyms and match inventory
 WITH expanded_terms AS
  (SELECT 'sofa' AS search_term
   UNION SELECT SYNONYM
   FROM synonym_thesaurus
   WHERE base_term = 'sofa')
SELECT f.item_id,
       f.description,
       t.search_term AS matched_alias
FROM furniture_items f
JOIN expanded_terms t ON f.description ILIKE ('%' || t.search_term || '%')
ORDER BY f.item_id;

CREATE TABLE synonym_thesaurus (base_term VARCHAR, SYNONYM VARCHAR);


CREATE TABLE furniture_items (item_id INT, description VARCHAR);


INSERT INTO synonym_thesaurus
VALUES ('sofa', 'couch'),
       ('sofa', 'settee'),
       ('sofa', 'loveseat');


INSERT INTO furniture_items
VALUES (1, 'Modern Italian leather sofa with oak legs'),
       (2, 'Vintage velvet couch in emerald green'),
       (3, 'Solid wood dining table and 6 chairs');

5. Query Results & Expected Ground Truth

Tabular Comparison

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

#	item_id	description	matched_alias
1	1	Modern Italian leather sofa with oak legs	sofa
2	2	Vintage velvet couch in emerald green	couch
3	3	Modern Italian leather sofa with oak legs	sofa
4	4	Vintage velvet couch in emerald green	couch
5	6	Modern Italian leather sofa with oak legs	sofa
6	7	Vintage velvet couch in emerald green	couch
7	9	Modern Italian leather sofa with oak legs	sofa
8	10	Vintage velvet couch in emerald green	couch
9	12	Modern Italian leather sofa with oak legs	sofa
10	13	Vintage velvet couch in emerald green	couch
11	15	Modern Italian leather sofa with oak legs	sofa
12	16	Vintage velvet couch in emerald green	couch
13	18	Modern Italian leather sofa with oak legs	sofa
14	19	Vintage velvet couch in emerald green	couch
15	21	Modern Italian leather sofa with oak legs	sofa
16	22	Vintage velvet couch in emerald green	couch
17	24	Modern Italian leather sofa with oak legs	sofa
18	25	Vintage velvet couch in emerald green	couch
19	27	Modern Italian leather sofa with oak legs	sofa
20	28	Vintage velvet couch in emerald green	couch
21	30	Modern Italian leather sofa with oak legs	sofa
22	31	Vintage velvet couch in emerald green	couch
23	33	Modern Italian leather sofa with oak legs	sofa
24	34	Vintage velvet couch in emerald green	couch
25	36	Modern Italian leather sofa with oak legs	sofa

Showing first 25 of 666,667 total rows.

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

#	item_id	description	matched_alias
1	1	Modern Italian leather sofa with oak legs	sofa
2	2	Vintage velvet couch in emerald green	couch
3	3	Modern Italian leather sofa with oak legs	sofa
4	4	Vintage velvet couch in emerald green	couch
5	6	Modern Italian leather sofa with oak legs	sofa
6	7	Vintage velvet couch in emerald green	couch
7	9	Modern Italian leather sofa with oak legs	sofa
8	10	Vintage velvet couch in emerald green	couch
9	12	Modern Italian leather sofa with oak legs	sofa
10	13	Vintage velvet couch in emerald green	couch
11	15	Modern Italian leather sofa with oak legs	sofa
12	16	Vintage velvet couch in emerald green	couch
13	18	Modern Italian leather sofa with oak legs	sofa
14	19	Vintage velvet couch in emerald green	couch
15	21	Modern Italian leather sofa with oak legs	sofa
16	22	Vintage velvet couch in emerald green	couch
17	24	Modern Italian leather sofa with oak legs	sofa
18	25	Vintage velvet couch in emerald green	couch
19	27	Modern Italian leather sofa with oak legs	sofa
20	28	Vintage velvet couch in emerald green	couch
21	30	Modern Italian leather sofa with oak legs	sofa
22	31	Vintage velvet couch in emerald green	couch
23	33	Modern Italian leather sofa with oak legs	sofa
24	34	Vintage velvet couch in emerald green	couch
25	36	Modern Italian leather sofa with oak legs	sofa

Showing first 25 of 666,667 total rows.

← Prev: Trigram / N-Gram Token Su... ⊞ Back to Benchmark Dashboard Next: Search Autocomplete Sugge... →