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

Multi-Modal Joint Retrieval (Image + Text Query Embedding Fusion)

Candidate Latency (1M Rows)

240.90 ms

(1.0x slower than golden)

Golden Latency (1M Rows)

231.72 ms

DuckDB in-memory engine

Underlying Data Volume

1,000,000 rows

38.1 MiB across 1 table

Evaluated Model

gemini-3.8-flash-low

Gen Latency: 10.3s

✔ 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 e-commerce search, users submit a photo and a text clarification (e.g. photo of dress + 'in floral silk'). We compute a weighted combination of image-vector and text-vector similarity.

⚡ The Frontier Trap (Why LLMs Fail)

Treating text and image vectors as independent queries yields conflicting rank lists.

Prompt / User Request Given to Model

In e-commerce search, users submit a photo and a text clarification (e.g. photo of dress + 'in floral silk'). We compute a weighted combination of image-vector and text-vector similarity.

Execute the required analytical SQL calculation in DuckDB.
Expected output columns: item_id, title, img_sim, txt_sim, joint_multimodal_score.
Results should be ordered as specified.

2. Underlying Tables, Row Counts & Data Volume

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

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

📊 fashion_items 1,000,000 rows 38.1 MiB Data Volume

item_id (INTEGER) title (VARCHAR) img_vec (FLOAT[2]) txt_vec (FLOAT[2])

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

#	item_id	title	img_vec	txt_vec
1	1	Silk Floral Summer Dress	[0.8 0.2]	[0.9 0.1]
2	2	Cotton Floral Blouse	[0.75 0.25]	[0.6 0.4]
3	3	Leather Biker Jacket	[0.1 0.9]	[0. 1.]
4	3	Silk Floral Summer Dress	[0.8906 0.1058]	[0.8906 0.1058]
5	4	Cotton Floral Blouse	[0.8351 0.0917]	[0.8351 0.0917]
6	5	Leather Biker Jacket	[0.8029 0.1437]	[0.8029 0.1437]
7	6	Silk Floral Summer Dress	[0.9339 0.0836]	[0.9339 0.0836]
8	7	Cotton Floral Blouse	[0.8292 0.1046]	[0.8292 0.1046]
9	8	Leather Biker Jacket	[0.8312 0.0219]	[0.8312 0.0219]
10	9	Silk Floral Summer Dress	[0.8443 0.1036]	[0.8443 0.1036]
11	10	Cotton Floral Blouse	[0.854 0.1027]	[0.854 0.1027]
12	11	Leather Biker Jacket	[0.9218 0.094 ]	[0.9218 0.094 ]
13	12	Silk Floral Summer Dress	[0.8348 0.1071]	[0.8348 0.1071]
14	13	Cotton Floral Blouse	[0.8719 0.0936]	[0.8719 0.0936]
15	14	Leather Biker Jacket	[0.824 0.1314]	[0.824 0.1314]

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

⏳ ORDER_BY 🔥 51.9% Cost

Latency: 145.67 ms

Rows In/Out: 1,000,000

Data Vol: 30.52 MB

📐 PROJECTION 🔥 30.7% Cost

Latency: 86.18 ms

Rows In/Out: 1,000,000

Data Vol: 30.52 MB

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

📐 PROJECTION 13.3%

Latency: 37.20 ms

Rows In/Out: 1,000,000

Data Vol: 26.70 MB

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

🔍 TABLE_SCAN 4.1%

Latency: 11.43 ms

Rows In/Out: 1,000,000

Data Vol: 34.33 MB

📁 memory.main.fashion_items

Operator	Cost %	Latency	Processed Rows	Data Volume	Details / Table	Status
ORDER_BY	51.9%	145.67 ms	1,000,000	30.52 MB	-	🔥 BOTTLENECK
PROJECTION	30.7%	86.18 ms	1,000,000	30.52 MB	Projections: ['item_id', 'title', 'img_sim', 'tx	🔥 BOTTLENECK
PROJECTION	13.3%	37.20 ms	1,000,000	26.70 MB	Projections: ['item_id', 'title', 'array_cosine_	Normal
TABLE_SCAN	4.1%	11.43 ms	1,000,000	34.33 MB	memory.main.fashion_items	Normal

4. Queries Executed (Candidate vs Golden Ground Truth)

Beautified & Syntax Highlighted

-- Joint multi-modal score: 60% image vector similarity + 40% text vector similarity

SELECT item_id,
       title,
       round(array_cosine_similarity(img_vec, [0.82, 0.18]::FLOAT[2]), 4) AS img_sim,
       round(array_cosine_similarity(txt_vec, [0.92, 0.08]::FLOAT[2]), 4) AS txt_sim,
       round(0.60 * array_cosine_similarity(img_vec, [0.82, 0.18]::FLOAT[2]) + 0.40 * array_cosine_similarity(txt_vec, [0.92, 0.08]::FLOAT[2]), 4) AS joint_multimodal_score
FROM fashion_items
ORDER BY joint_multimodal_score DESC;

-- Joint multi-modal score: 60% image vector similarity + 40% text vector similarity

SELECT item_id,
       title,
       round(array_cosine_similarity(img_vec, [0.82, 0.18]::FLOAT[2]), 4) AS img_sim,
       round(array_cosine_similarity(txt_vec, [0.92, 0.08]::FLOAT[2]), 4) AS txt_sim,
       round(0.60 * array_cosine_similarity(img_vec, [0.82, 0.18]::FLOAT[2]) + 0.40 * array_cosine_similarity(txt_vec, [0.92, 0.08]::FLOAT[2]), 4) AS joint_multimodal_score
FROM fashion_items
ORDER BY joint_multimodal_score DESC;

CREATE TABLE fashion_items (item_id INT, title VARCHAR, img_vec FLOAT[2], txt_vec FLOAT[2]);


INSERT INTO fashion_items
VALUES (1, 'Silk Floral Summer Dress', [0.8, 0.2]::FLOAT[2], [0.9, 0.1]::FLOAT[2]),
       (2, 'Cotton Floral Blouse', [0.75, 0.25]::FLOAT[2], [0.6, 0.4]::FLOAT[2]),
       (3, 'Leather Biker Jacket', [0.1, 0.9]::FLOAT[2], [0.0, 1.0]::FLOAT[2]);

5. Query Results & Expected Ground Truth

Tabular Comparison

Actual Candidate Output (1,000,000 rows × 5 cols)

#	item_id	title	img_sim	txt_sim	joint_multimodal_score
1	1	Silk Floral Summer Dress	0.9996	0.9997	0.9996
2	245,770	Cotton Floral Blouse	0.9986	0.997	0.998
3	245,777	Leather Biker Jacket	0.9986	0.9971	0.998
4	245,786	Leather Biker Jacket	0.9987	0.9969	0.998
5	245,812	Cotton Floral Blouse	0.9988	0.9969	0.998
6	245,820	Silk Floral Summer Dress	0.999	0.9964	0.998
7	245,822	Leather Biker Jacket	0.9983	0.9975	0.998
8	245,868	Silk Floral Summer Dress	0.9985	0.9972	0.998
9	245,882	Leather Biker Jacket	0.9988	0.9968	0.998
10	245,890	Cotton Floral Blouse	0.9988	0.9968	0.998
11	245,900	Leather Biker Jacket	0.9984	0.9973	0.998
12	245,914	Cotton Floral Blouse	0.9985	0.9972	0.998
13	245,915	Leather Biker Jacket	0.9983	0.9975	0.998
14	245,922	Silk Floral Summer Dress	0.9988	0.9967	0.998
15	245,923	Cotton Floral Blouse	0.9984	0.9974	0.998
16	245,941	Cotton Floral Blouse	0.9983	0.9975	0.998
17	245,958	Silk Floral Summer Dress	0.9989	0.9966	0.998
18	245,976	Silk Floral Summer Dress	0.9988	0.9968	0.998
19	245,984	Leather Biker Jacket	0.9985	0.9972	0.998
20	245,996	Leather Biker Jacket	0.9985	0.9972	0.998
21	246,027	Silk Floral Summer Dress	0.9988	0.9968	0.998
22	246,045	Silk Floral Summer Dress	0.9987	0.9969	0.998
23	246,056	Leather Biker Jacket	0.9986	0.9972	0.998
24	246,073	Cotton Floral Blouse	0.9989	0.9966	0.998
25	246,075	Silk Floral Summer Dress	0.9988	0.9967	0.998

Showing first 25 of 1,000,000 total rows.

Expected Golden Output (1,000,000 rows × 5 cols)

#	item_id	title	img_sim	txt_sim	joint_multimodal_score
1	1	Silk Floral Summer Dress	0.9996	0.9997	0.9996
2	245,770	Cotton Floral Blouse	0.9986	0.997	0.998
3	245,777	Leather Biker Jacket	0.9986	0.9971	0.998
4	245,786	Leather Biker Jacket	0.9987	0.9969	0.998
5	245,812	Cotton Floral Blouse	0.9988	0.9969	0.998
6	245,820	Silk Floral Summer Dress	0.999	0.9964	0.998
7	245,822	Leather Biker Jacket	0.9983	0.9975	0.998
8	245,868	Silk Floral Summer Dress	0.9985	0.9972	0.998
9	245,882	Leather Biker Jacket	0.9988	0.9968	0.998
10	245,890	Cotton Floral Blouse	0.9988	0.9968	0.998
11	245,900	Leather Biker Jacket	0.9984	0.9973	0.998
12	245,914	Cotton Floral Blouse	0.9985	0.9972	0.998
13	245,915	Leather Biker Jacket	0.9983	0.9975	0.998
14	245,922	Silk Floral Summer Dress	0.9988	0.9967	0.998
15	245,923	Cotton Floral Blouse	0.9984	0.9974	0.998
16	245,941	Cotton Floral Blouse	0.9983	0.9975	0.998
17	245,958	Silk Floral Summer Dress	0.9989	0.9966	0.998
18	245,976	Silk Floral Summer Dress	0.9988	0.9968	0.998
19	245,984	Leather Biker Jacket	0.9985	0.9972	0.998
20	245,996	Leather Biker Jacket	0.9985	0.9972	0.998
21	246,027	Silk Floral Summer Dress	0.9988	0.9968	0.998
22	246,045	Silk Floral Summer Dress	0.9987	0.9969	0.998
23	246,056	Leather Biker Jacket	0.9986	0.9972	0.998
24	246,073	Cotton Floral Blouse	0.9989	0.9966	0.998
25	246,075	Silk Floral Summer Dress	0.9988	0.9967	0.998

Showing first 25 of 1,000,000 total rows.

← Prev: Cohort-Constrained Vector... ⊞ Back to Benchmark Dashboard Next: Maximal Marginal Relevanc... →