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

Dense Vector Cosine Similarity Nearest Neighbors (Top-K ANN)

Candidate Latency (1M Rows)

9.38 ms

(1.0x slower than golden)

Golden Latency (1M Rows)

9.15 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: 8.5s

✔ 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 user asks a question in an AI assistant, the query is embedded into a dense vector (e.g. 4-dimensional here for illustration, or 384/1536 in prod). We retrieve the top most semantically relevant documentation chunks.

⚡ The Frontier Trap (Why LLMs Fail)

Models frequently try to calculate cosine similarity manually using dot product divided by norms in SQL, which is slow, verbose, and numerically unstable with zero vectors.

Prompt / User Request Given to Model

When a user asks a question in an AI assistant, the query is embedded into a dense vector (e.g. 4-dimensional here for illustration, or 384/1536 in prod). We retrieve the top most semantically relevant documentation chunks.

Execute the required analytical SQL calculation in DuckDB.
Expected output columns: doc_id, title, similarity_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:

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

doc_id (INTEGER) title (VARCHAR) embedding (FLOAT[4])

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

#	doc_id	title	embedding
1	1	DuckDB Architecture Overview	[0.9 0.1 0.2 0. ]
2	2	PostgreSQL Vacuum Internals	[0.1 0.8 0.3 0.1]
3	3	Vector Search & Embeddings	[0.85 0.15 0.3 0.05]
4	4	Kafka Event Streaming Guide	[0. 0.2 0.9 0.4]
5	4	DuckDB Architecture Overview	[ 0.8041 0.0789 -0.0114 0.0505]
6	5	PostgreSQL Vacuum Internals	[0.8474 0.0386 0.052 0.0331]
7	6	Vector Search & Embeddings	[0.8427 0.0133 0.1037 0.066 ]
8	7	Kafka Event Streaming Guide	[ 0.8865 0.0359 -0.0112 -0.0023]
9	8	DuckDB Architecture Overview	[ 0.8563 0.0285 0.0851 -0.0293]
10	9	PostgreSQL Vacuum Internals	[0.752 0.1005 0.0889 0.0438]
11	10	Vector Search & Embeddings	[ 0.8363 0.0608 -0.0014 0.0635]
12	11	Kafka Event Streaming Guide	[ 0.8037 -0.0051 0.0054 0.0318]
13	12	DuckDB Architecture Overview	[ 0.8536 -0.0087 -0.0399 0.056 ]
14	13	PostgreSQL Vacuum Internals	[0.8633 0.0515 0.0517 0.0023]
15	14	Vector Search & Embeddings	[0.8502 0.0439 0.0129 0.0802]

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

⏳ ORDER_BY 0.9%

Latency: 0.20 ms

Rows In/Out: 2

Data Vol: 48 B

📐 PROJECTION 0.1%

Latency: 0.02 ms

Rows In/Out: 2

Data Vol: 56 B

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

⚡ HASH_JOIN 0.4%

Latency: 0.10 ms

Rows In/Out: 2

Data Vol: 72 B

🔍 TABLE_SCAN 1.0%

Latency: 0.23 ms

Rows In/Out: 2

Data Vol: 88 B

📁 memory.main.doc_embeddings

✂️ TOP_N 10.9%

Latency: 2.53 ms

Rows In/Out: 2

Data Vol: 24 B

📐 PROJECTION 🔥 77.1% Cost

Latency: 17.91 ms

Rows In/Out: 1,000,000

Data Vol: 11.44 MB

📁 Projections: ['cosine_score', 'r...

🔍 TABLE_SCAN 9.6%

Latency: 2.23 ms

Rows In/Out: 1,000,000

Data Vol: 22.89 MB

📁 memory.main.doc_embeddings

Operator	Cost %	Latency	Processed Rows	Data Volume	Details / Table	Status
PROJECTION	77.1%	17.91 ms	1,000,000	11.44 MB	Projections: ['cosine_score', 'rowid']	🔥 BOTTLENECK
TOP_N	10.9%	2.53 ms	2	0.00 MB	-	Normal
TABLE_SCAN	9.6%	2.23 ms	1,000,000	22.89 MB	memory.main.doc_embeddings	Normal
TABLE_SCAN	1.0%	0.23 ms	2	0.00 MB	memory.main.doc_embeddings	Normal
ORDER_BY	0.9%	0.20 ms	2	0.00 MB	-	Normal
HASH_JOIN	0.4%	0.10 ms	2	0.00 MB	-	Normal
PROJECTION	0.1%	0.02 ms	2	0.00 MB	Projections: ['doc_id', 'title', 'similarity_sco	Normal

4. Queries Executed (Candidate vs Golden Ground Truth)

Beautified & Syntax Highlighted

WITH scored_docs AS
  (SELECT doc_id,
          title,
          embedding,
          array_cosine_similarity(embedding, [0.9, 0.1, 0.25, 0.0]::FLOAT[4]) AS cosine_score
   FROM doc_embeddings)
SELECT doc_id,
       title,
       round(cosine_score, 4) AS similarity_score
FROM scored_docs
ORDER BY cosine_score DESC
LIMIT 2;

-- Find the top 2 documents closest to the query embedding [0.9, 0.1, 0.25, 0.0]
 WITH scored_docs AS
  (SELECT doc_id,
          title,
          embedding, -- Native SIMD cosine similarity
 array_cosine_similarity(embedding, [0.9, 0.1, 0.25, 0.0]::FLOAT[4]) AS cosine_score
   FROM doc_embeddings)
SELECT doc_id,
       title,
       round(cosine_score, 4) AS similarity_score
FROM scored_docs
ORDER BY cosine_score DESC
LIMIT 2;

CREATE TABLE doc_embeddings (doc_id INT, title VARCHAR, embedding FLOAT[4]);


INSERT INTO doc_embeddings
VALUES (1, 'DuckDB Architecture Overview', [0.9, 0.1, 0.2, 0.0]::FLOAT[4]),
       (2, 'PostgreSQL Vacuum Internals', [0.1, 0.8, 0.3, 0.1]::FLOAT[4]),
       (3, 'Vector Search & Embeddings', [0.85, 0.15, 0.3, 0.05]::FLOAT[4]),
       (4, 'Kafka Event Streaming Guide', [0.0, 0.2, 0.9, 0.4]::FLOAT[4]);

5. Query Results & Expected Ground Truth

Tabular Comparison

Actual Candidate Output (2 rows × 3 cols)

#	doc_id	title	similarity_score
1	3,019	Kafka Event Streaming Guide	0.9989
2	1	DuckDB Architecture Overview	0.9986

Expected Golden Output (2 rows × 3 cols)

#	doc_id	title	similarity_score
1	3,019	Kafka Event Streaming Guide	0.9989
2	1	DuckDB Architecture Overview	0.9986

← Prev: Multi-Touch Marketing Att... ⊞ Back to Benchmark Dashboard Next: L2 Euclidean Distance Nea... →