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

Maximum Inner Product Search (MIPS) for Normalized Embeddings

Candidate Latency (1M Rows)

15.57 ms

(1.2x faster than golden)

Golden Latency (1M Rows)

18.75 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: 11.4s

✔ 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

For unit-normalized vectors (e.g. OpenAI text-embedding-3 or Cohere embeddings), cosine similarity is identical to the inner product (dot product), which executes even faster.

⚡ The Frontier Trap (Why LLMs Fail)

Re-normalizing vectors in every query wastes compute when embeddings are pre-normalized upon insertion.

Prompt / User Request Given to Model

For unit-normalized vectors (e.g. OpenAI text-embedding-3 or Cohere embeddings), cosine similarity is identical to the inner product (dot product), which executes even faster.

Execute the required analytical SQL calculation in DuckDB.
Expected output columns: product_id, product_name, dot_product_score.
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:

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

product_id (INTEGER) product_name (VARCHAR) norm_vector (FLOAT[3])

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

#	product_id	product_name	norm_vector
1	1	Ultra Laptop	[0.8 0.6 0. ]
2	2	Smart Phone	[0.6 0.8 0. ]
3	3	Coffee Maker	[0. 0. 1.]
4	3	Ultra Laptop	[0.8962 0.0386 0.1012]
5	4	Smart Phone	[0.8322 0.0842 0.0237]
6	5	Coffee Maker	[0.8868 0.1179 0.0536]
7	6	Ultra Laptop	[0.9293 0.0593 0.083 ]
8	7	Smart Phone	[0.8188 0.0535 0.056 ]
9	8	Coffee Maker	[ 0.8699 0.0418 -0.0281]
10	9	Ultra Laptop	[0.8612 0.0166 0.0213]
11	10	Smart Phone	[0.8178 0.026 0.033 ]
12	11	Coffee Maker	[0.8666 0.0928 0.0185]
13	12	Ultra Laptop	[0.8245 0.024 0.079 ]
14	13	Smart Phone	[0.8178 0.0589 0.0965]
15	14	Coffee Maker	[ 0.8548 -0.0117 -0.0109]

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

📐 PROJECTION 0.0%

Latency: 0.00 ms

Rows In/Out: 2

Data Vol: 48 B

📁 Projections: ['#0', '__internal_...

⏳ ORDER_BY 0.3%

Latency: 0.16 ms

Rows In/Out: 2

Data Vol: 48 B

📐 PROJECTION 0.0%

Latency: 0.00 ms

Rows In/Out: 2

Data Vol: 48 B

📁 Projections: ['#0', '__internal_...

📐 PROJECTION 0.1%

Latency: 0.03 ms

Rows In/Out: 2

Data Vol: 48 B

📁 Projections: ['product_id', 'pro...

⚡ HASH_JOIN 0.3%

Latency: 0.14 ms

Rows In/Out: 2

Data Vol: 64 B

🔍 TABLE_SCAN 0.4%

Latency: 0.20 ms

Rows In/Out: 2

Data Vol: 80 B

📁 memory.main.product_catalog_vect...

✂️ TOP_N 5.6%

Latency: 2.78 ms

Rows In/Out: 2

Data Vol: 24 B

📐 PROJECTION 🔥 89.2% Cost

Latency: 44.12 ms

Rows In/Out: 1,000,000

Data Vol: 11.44 MB

📁 Projections: ['dot_product_score...

🔍 TABLE_SCAN 4.2%

Latency: 2.06 ms

Rows In/Out: 1,000,000

Data Vol: 19.07 MB

📁 memory.main.product_catalog_vect...

Operator	Cost %	Latency	Processed Rows	Data Volume	Details / Table	Status
PROJECTION	89.2%	44.12 ms	1,000,000	11.44 MB	Projections: ['dot_product_score', 'rowid']	🔥 BOTTLENECK
TOP_N	5.6%	2.78 ms	2	0.00 MB	-	Normal
TABLE_SCAN	4.2%	2.06 ms	1,000,000	19.07 MB	memory.main.product_catalog_vectors	Normal
TABLE_SCAN	0.4%	0.20 ms	2	0.00 MB	memory.main.product_catalog_vectors	Normal
ORDER_BY	0.3%	0.16 ms	2	0.00 MB	-	Normal
HASH_JOIN	0.3%	0.14 ms	2	0.00 MB	-	Normal
PROJECTION	0.1%	0.03 ms	2	0.00 MB	Projections: ['product_id', 'product_name', 'dot	Normal
PROJECTION	0.0%	0.00 ms	2	0.00 MB	Projections: ['#0', '__internal_compress_string_	Normal
PROJECTION	0.0%	0.00 ms	2	0.00 MB	Projections: ['#0', '__internal_decompress_strin	Normal

4. Queries Executed (Candidate vs Golden Ground Truth)

Beautified & Syntax Highlighted

-- MIPS search for normalized user query vector [0.85, 0.52, 0.0]

SELECT product_id,
       product_name,
       round(array_inner_product(norm_vector, [0.85, 0.52, 0.0]::FLOAT[3]), 4) AS dot_product_score
FROM product_catalog_vectors
ORDER BY dot_product_score DESC
LIMIT 2;

-- MIPS search for normalized user query vector [0.85, 0.52, 0.0]

SELECT product_id,
       product_name,
       round(array_inner_product(norm_vector, [0.85, 0.52, 0.0]::FLOAT[3]), 4) AS dot_product_score
FROM product_catalog_vectors
ORDER BY dot_product_score DESC
LIMIT 2;

CREATE TABLE product_catalog_vectors (product_id INT, product_name VARCHAR, norm_vector FLOAT[3]);


INSERT INTO product_catalog_vectors
VALUES (1, 'Ultra Laptop', [0.8, 0.6, 0.0]::FLOAT[3]),
       (2, 'Smart Phone', [0.6, 0.8, 0.0]::FLOAT[3]),
       (3, 'Coffee Maker', [0.0, 0.0, 1.0]::FLOAT[3]);

5. Query Results & Expected Ground Truth

Tabular Comparison

Actual Candidate Output (2 rows × 3 cols)

#	product_id	product_name	dot_product_score
1	1	Ultra Laptop	0.992
2	556,240	Smart Phone	0.9293

Expected Golden Output (2 rows × 3 cols)

#	product_id	product_name	dot_product_score
1	1	Ultra Laptop	0.992
2	556,240	Smart Phone	0.9293

← Prev: L2 Euclidean Distance Nea... ⊞ Back to Benchmark Dashboard Next: The Pre-Filtering Trap: R... →