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

Pairwise Vector Similarity Self-Join with Coarse Cluster Pruning

Candidate Latency (1M Rows)

0.66 ms

(1.2x slower than golden)

Golden Latency (1M Rows)

0.55 ms

DuckDB in-memory engine

Underlying Data Volume

2,500 rows

39.1 KiB across 1 table

Evaluated Model

gemini-3.8-flash-low

Gen Latency: 12.6s

✔ 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

Finding all similar document pairs across 100,000 docs is O(N^2) (10 billion checks). Partitioning into coarse category buckets first reduces search to O(N * (N/K)).

⚡ The Frontier Trap (Why LLMs Fail)

Unpartitioned cross joins cause out-of-memory crashes on analytical engines.

Prompt / User Request Given to Model

Finding all similar document pairs across 100,000 docs is O(N^2) (10 billion checks). Partitioning into coarse category buckets first reduces search to O(N * (N/K)).

Execute the required analytical SQL calculation in DuckDB.
Expected output columns: doc_a, doc_b, bucket_id, similarity.
Results should be ordered as specified.

2. Underlying Tables, Row Counts & Data Volume

1 Tables (2,500 rows • 39.1 KiB)

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

📊 corpus 2,500 rows 39.1 KiB Data Volume

doc_id (INTEGER) bucket_id (INTEGER) vector (FLOAT[2])

Data Preview (showing sample rows from 2,500 total rows):

#	doc_id	bucket_id	vector
1	1	10	[0.9 0.1]
2	2	10	[0.88 0.12]
3	3	20	[0.1 0.9]
4	4	20	[0.12 0.88]
5	4	20	[0.9019 0.0726]
6	5	21	[0.8555 0.144 ]
7	6	22	[0.8506 0.1188]
8	7	23	[0.811 0.0239]
9	8	24	[0.7826 0.083 ]
10	9	25	[0.9228 0.0243]
11	10	26	[0.7986 0.1788]
12	11	27	[0.8492 0.1371]
13	12	28	[0.8524 0.0864]
14	13	29	[0.855 0.0852]
15	14	30	[0.8807 0.0619]

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

📐 PROJECTION 0.3%

Latency: 0.00 ms

Rows In/Out: 2

Data Vol: 32 B

📁 Projections: ['CAST(#0 AS INTEGE...

⏳ ORDER_BY 8.7%

Latency: 0.06 ms

Rows In/Out: 2

Data Vol: 20 B

📐 PROJECTION 0.7%

Latency: 0.00 ms

Rows In/Out: 2

Data Vol: 20 B

📁 Projections: ['CAST(#0 AS SMALLI...

📐 PROJECTION 1.0%

Latency: 0.01 ms

Rows In/Out: 2

Data Vol: 32 B

📁 Projections: ['doc_a', 'doc_b', ...

⚡ HASH_JOIN 🔥 76.8% Cost

Latency: 0.51 ms

Rows In/Out: 2

Data Vol: 56 B

🔍 TABLE_SCAN 6.8%

Latency: 0.05 ms

Rows In/Out: 2,500

Data Vol: 0.04 MB

📁 memory.main.corpus

🔍 TABLE_SCAN 5.7%

Latency: 0.04 ms

Rows In/Out: 2,500

Data Vol: 0.04 MB

📁 memory.main.corpus

Operator	Cost %	Latency	Processed Rows	Data Volume	Details / Table	Status
HASH_JOIN	76.8%	0.51 ms	2	0.00 MB	-	🔥 BOTTLENECK
ORDER_BY	8.7%	0.06 ms	2	0.00 MB	-	Normal
TABLE_SCAN	6.8%	0.05 ms	2,500	0.04 MB	memory.main.corpus	Normal
TABLE_SCAN	5.7%	0.04 ms	2,500	0.04 MB	memory.main.corpus	Normal
PROJECTION	1.0%	0.01 ms	2	0.00 MB	Projections: ['doc_a', 'doc_b', 'bucket_id', 'si	Normal
PROJECTION	0.7%	0.00 ms	2	0.00 MB	Projections: ['CAST(#0 AS SMALLINT)', 'CAST(#1 A	Normal
PROJECTION	0.3%	0.00 ms	2	0.00 MB	Projections: ['CAST(#0 AS INTEGER)', 'CAST(#1 AS	Normal

4. Queries Executed (Candidate vs Golden Ground Truth)

Beautified & Syntax Highlighted

SELECT a.doc_id AS doc_a,
       b.doc_id AS doc_b,
       a.bucket_id,
       round(array_cosine_similarity(a.vector, b.vector), 4) AS similarity
FROM corpus a
JOIN corpus b ON a.bucket_id = b.bucket_id
AND a.doc_id < b.doc_id
WHERE array_cosine_similarity(a.vector, b.vector) >= 0.85
ORDER BY similarity DESC;

-- Pairwise similarity evaluated ONLY within shared bucket partitions

SELECT a.doc_id AS doc_a,
       b.doc_id AS doc_b,
       a.bucket_id,
       round(array_cosine_similarity(a.vector, b.vector), 4) AS similarity
FROM corpus a
JOIN corpus b ON a.bucket_id = b.bucket_id
AND a.doc_id < b.doc_id
WHERE array_cosine_similarity(a.vector, b.vector) >= 0.85
ORDER BY similarity DESC;

CREATE TABLE corpus (doc_id INT, bucket_id INT, vector FLOAT[2]);


INSERT INTO corpus
VALUES (1, 10, [0.9, 0.1]::FLOAT[2]),
       (2, 10, [0.88, 0.12]::FLOAT[2]),
       (3, 20, [0.1, 0.9]::FLOAT[2]),
       (4, 20, [0.12, 0.88]::FLOAT[2]);

5. Query Results & Expected Ground Truth

Tabular Comparison

Actual Candidate Output (2 rows × 4 cols)

#	doc_a	doc_b	bucket_id	similarity
1	1	2	10	0.9997
2	3	4	20	0.9997

Expected Golden Output (2 rows × 4 cols)

#	doc_a	doc_b	bucket_id	similarity
1	1	2	10	0.9997
2	3	4	20	0.9997

← Prev: Historical SCD Type 2 Poi... ⊞ Back to Benchmark Dashboard Next: Latent Factor Matrix Fact... →