✔ PASS Vector & Embeddings task_027_Q27_euclidean_l2_nearest_neighbors Scaled: 1M+ Records Ordered: Yes

L2 Euclidean Distance Nearest Neighbors Search via array_distance

Candidate Latency (1M Rows)

14.88 ms

(1.3x faster than golden)

Golden Latency (1M Rows)

19.07 ms

DuckDB in-memory engine

Underlying Data Volume

1,000,000 rows

34.3 MiB across 1 table

Evaluated Model

gemini-3.8-flash-low

Gen Latency: 13.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

In computer vision feature retrieval (facial recognition, product image matching), feature vectors are compared using Euclidean (L2) distance where lower distance equals higher visual similarity.

⚡ The Frontier Trap (Why LLMs Fail)

LLMs often try to write SQRT(SUM(POW(a - b, 2))) which prevents vectorized hardware acceleration.

Prompt / User Request Given to Model

In computer vision feature retrieval (facial recognition, product image matching), feature vectors are compared using Euclidean (L2) distance where lower distance equals higher visual similarity.

Execute the required analytical SQL calculation in DuckDB.
Expected output columns: image_id, label, l2_distance.
Results should be ordered as specified.

2. Underlying Tables, Row Counts & Data Volume

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

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

📊 image_features 1,000,000 rows 34.3 MiB Data Volume

image_id (INTEGER) label (VARCHAR) vector (FLOAT[3])

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

#	image_id	label	vector
1	101	Red Running Shoe	[1. 2. 3.]
2	102	Crimson Sneaker	[1.1 2.1 2.9]
3	103	Blue Hiking Boot	[5. 8. 9.]
4	103	Red Running Shoe	[0.9173 0.0292 0.0724]
5	104	Crimson Sneaker	[0.9288 0.0873 0.0715]
6	105	Blue Hiking Boot	[0.7547 0.0429 0.1477]
7	106	Red Running Shoe	[0.894 0.0532 0.0191]
8	107	Crimson Sneaker	[0.908 0.009 0.1084]
9	108	Blue Hiking Boot	[0.8701 0.0662 0.1037]
10	109	Red Running Shoe	[ 0.7903 0.058 -0.0385]
11	110	Crimson Sneaker	[0.8807 0.0746 0.0432]
12	111	Blue Hiking Boot	[0.786 0.0463 0.0928]
13	112	Red Running Shoe	[0.8275 0.0282 0.0135]
14	113	Crimson Sneaker	[ 0.8436 -0.0199 0.0361]
15	114	Blue Hiking Boot	[0.7908 0.0676 0.0494]

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

⏳ ORDER_BY 0.2%

Latency: 0.10 ms

Rows In/Out: 2

Data Vol: 48 B

📐 PROJECTION 0.0%

Latency: 0.01 ms

Rows In/Out: 2

Data Vol: 48 B

📁 Projections: ['image_id', 'label...

⚡ HASH_JOIN 0.2%

Latency: 0.11 ms

Rows In/Out: 2

Data Vol: 64 B

🔍 TABLE_SCAN 0.0%

Latency: 0.02 ms

Rows In/Out: 2

Data Vol: 80 B

📁 memory.main.image_features

✂️ TOP_N 8.8%

Latency: 4.62 ms

Rows In/Out: 2

Data Vol: 24 B

📐 PROJECTION 🔥 87.2% Cost

Latency: 45.95 ms

Rows In/Out: 1,000,000

Data Vol: 11.44 MB

📁 Projections: ['l2_distance', 'ro...

🔍 TABLE_SCAN 3.6%

Latency: 1.89 ms

Rows In/Out: 1,000,000

Data Vol: 19.07 MB

📁 memory.main.image_features

Operator	Cost %	Latency	Processed Rows	Data Volume	Details / Table	Status
PROJECTION	87.2%	45.95 ms	1,000,000	11.44 MB	Projections: ['l2_distance', 'rowid']	🔥 BOTTLENECK
TOP_N	8.8%	4.62 ms	2	0.00 MB	-	Normal
TABLE_SCAN	3.6%	1.89 ms	1,000,000	19.07 MB	memory.main.image_features	Normal
HASH_JOIN	0.2%	0.11 ms	2	0.00 MB	-	Normal
ORDER_BY	0.2%	0.10 ms	2	0.00 MB	-	Normal
TABLE_SCAN	0.0%	0.02 ms	2	0.00 MB	memory.main.image_features	Normal
PROJECTION	0.0%	0.01 ms	2	0.00 MB	Projections: ['image_id', 'label', 'l2_distance'	Normal

4. Queries Executed (Candidate vs Golden Ground Truth)

Beautified & Syntax Highlighted

SELECT image_id,
       label,
       round(array_distance(vector, [1.05, 2.05, 3.0]::FLOAT[3]), 4) AS l2_distance
FROM image_features
ORDER BY l2_distance ASC
LIMIT 2;

-- Find visual nearest neighbors to query image vector [1.05, 2.05, 3.0]

SELECT image_id,
       label,
       round(array_distance(vector, [1.05, 2.05, 3.0]::FLOAT[3]), 4) AS l2_distance
FROM image_features
ORDER BY l2_distance ASC
LIMIT 2;

CREATE TABLE image_features (image_id INT, label VARCHAR, vector FLOAT[3]);


INSERT INTO image_features
VALUES (101, 'Red Running Shoe', [1.0, 2.0, 3.0]::FLOAT[3]),
       (102, 'Crimson Sneaker', [1.1, 2.1, 2.9]::FLOAT[3]),
       (103, 'Blue Hiking Boot', [5.0, 8.0, 9.0]::FLOAT[3]);

5. Query Results & Expected Ground Truth

Tabular Comparison

Actual Candidate Output (2 rows × 3 cols)

#	image_id	label	l2_distance
1	101	Red Running Shoe	0.0707
2	102	Crimson Sneaker	0.1225

Expected Golden Output (2 rows × 3 cols)

#	image_id	label	l2_distance
1	101	Red Running Shoe	0.0707
2	102	Crimson Sneaker	0.1225

← Prev: Dense Vector Cosine Simil... ⊞ Back to Benchmark Dashboard Next: Maximum Inner Product Sea... →