✔ PASS Search & Retrieval task_058_Q58_stopword_cleansing_normalization Scaled: 1M+ Records Ordered: No

Stopword Filtering and Query Token Cleansing in SQL

Candidate Latency (1M Rows)

11.04 ms

(1.3x faster than golden)

Golden Latency (1M Rows)

14.26 ms

DuckDB in-memory engine

Underlying Data Volume

1,000,000 rows

19.1 MiB across 1 table

Evaluated Model

gemini-3.8-flash-low

Gen Latency: 13.7s

✔ 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

Search queries like 'what is the best laptop' contain stop words ('what', 'is', 'the') that dilute search precision if not filtered prior to catalog matching.

⚡ The Frontier Trap (Why LLMs Fail)

Leaving stopwords in keyword queries causes high-frequency words to trigger millions of false positive matches.

Prompt / User Request Given to Model

Search queries like 'what is the best laptop' contain stop words ('what', 'is', 'the') that dilute search precision if not filtered prior to catalog matching.

Execute the required analytical SQL calculation in DuckDB.
Expected output columns: clean_tokens, cleansed_search_query.
Row order does not matter.

2. Underlying Tables, Row Counts & Data Volume

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

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

📊 stopwords 1,000,000 rows 19.1 MiB Data Volume

word (VARCHAR)

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

#	word
1	what
2	is
3	the
4	and
5	or
6	a
7	an
8	what
9	is
10	the
11	and
12	or
13	a
14	an
15	what

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

📊 UNGROUPED_AGGREGATE 0.1%

Latency: 0.03 ms

Rows In/Out: 1

Data Vol: 80 B

📐 PROJECTION 0.0%

Latency: 0.00 ms

Rows In/Out: 3

Data Vol: 144 B

📁 Projections: ['token', 'token', ...

⚡ HASH_JOIN 🔥 59.4% Cost

Latency: 15.98 ms

Rows In/Out: 3

Data Vol: 48 B

🔍 TABLE_SCAN 🔥 40.4% Cost

Latency: 10.86 ms

Rows In/Out: 1,000,000

Data Vol: 15.26 MB

📁 memory.main.stopwords

📐 PROJECTION 0.0%

Latency: 0.00 ms

Rows In/Out: 6

Data Vol: 96 B

📁 Projections: token

⚙️ UNNEST 0.1%

Latency: 0.03 ms

Rows In/Out: 6

Data Vol: 120 B

🔍 DUMMY_SCAN 0.0%

Latency: 0.00 ms

Rows In/Out: 1

Data Vol: 4 B

Operator	Cost %	Latency	Processed Rows	Data Volume	Details / Table	Status
HASH_JOIN	59.4%	15.98 ms	3	0.00 MB	-	🔥 BOTTLENECK
TABLE_SCAN	40.4%	10.86 ms	1,000,000	15.26 MB	memory.main.stopwords	🔥 BOTTLENECK
UNGROUPED_AGGREGATE	0.1%	0.03 ms	1	0.00 MB	-	Normal
UNNEST	0.1%	0.03 ms	6	0.00 MB	-	Normal
PROJECTION	0.0%	0.00 ms	3	0.00 MB	Projections: ['token', 'token', "' '"]	Normal
DUMMY_SCAN	0.0%	0.00 ms	1	0.00 MB	-	Normal
PROJECTION	0.0%	0.00 ms	6	0.00 MB	Projections: token	Normal

4. Queries Executed (Candidate vs Golden Ground Truth)

Beautified & Syntax Highlighted

WITH raw_query AS
  (SELECT unnest(string_split(lower('what is the best gaming laptop'), ' ')) AS token),
     clean_tokens AS
  (SELECT q.token
   FROM raw_query q
   LEFT JOIN stopwords s ON q.token = s.word
   WHERE s.word IS NULL)
SELECT list(token) AS clean_tokens,
       string_agg(token, ' ') AS cleansed_search_query
FROM clean_tokens;

-- Cleanse raw search tokens by filtering out stopwords
 WITH raw_query AS
  (SELECT unnest(string_split(lower('what is the best gaming laptop'), ' ')) AS token),
      clean_tokens AS
  (SELECT q.token
   FROM raw_query q
   LEFT JOIN stopwords s ON q.token = s.word
   WHERE s.word IS NULL)
SELECT list(token) AS clean_tokens,
       string_agg(token, ' ') AS cleansed_search_query
FROM clean_tokens;

CREATE TABLE stopwords (word VARCHAR);


INSERT INTO stopwords
VALUES ('what'),
       ('is'),
       ('the'),
       ('and'),
       ('or'),
       ('a'),
       ('an');

5. Query Results & Expected Ground Truth

Tabular Comparison

Actual Candidate Output (1 rows × 2 cols)

#	clean_tokens	cleansed_search_query
1	['gaming' 'best' 'laptop']	gaming best laptop

Expected Golden Output (1 rows × 2 cols)

#	clean_tokens	cleansed_search_query
1	['gaming' 'best' 'laptop']	gaming best laptop

← Prev: Exact Phrase Proximity Bo... ⊞ Back to Benchmark Dashboard Next: Faceted Search Aggregatio... →