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

ColBERT Multi-Vector Late Interaction (MaxSim) in Pure SQL

Candidate Latency (1M Rows)

211.54 ms

(1.1x slower than golden)

Golden Latency (1M Rows)

186.73 ms

DuckDB in-memory engine

Underlying Data Volume

1,000,002 rows

34.3 MiB across 2 tables

Evaluated Model

gemini-3.8-flash-low

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

Advanced retrieval models (ColBERT / Sanity) represent queries and documents as collections of token vectors. Scoring computes MaxSim: for each query token, take the maximum cosine similarity across all document tokens, then sum.

⚡ The Frontier Trap (Why LLMs Fail)

Implementing late interaction typically requires specialized Python tensor pipelines.

Prompt / User Request Given to Model

Advanced retrieval models (ColBERT / Sanity) represent queries and documents as collections of token vectors. Scoring computes MaxSim: for each query token, take the maximum cosine similarity across all document tokens, then sum.

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

2. Underlying Tables, Row Counts & Data Volume

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

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

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

doc_id (INTEGER) d_token_id (INTEGER) token_text (VARCHAR) vec (FLOAT[2])

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

#	doc_id	d_token_id	token_text	vec
1	10	1	quick	[0.9 0.1]
2	10	2	relational	[0.1 0.85]
3	10	3	store	[0.2 0.7]
4	10	3	quick	[0.8434 0.0992]
5	11	4	relational	[0.8589 0.1156]
6	12	5	store	[0.8646 0.0125]
7	13	6	quick	[0.892 0.1175]
8	14	7	relational	[0.8808 0.1366]
9	15	8	store	[0.9029 0.1273]
10	16	9	quick	[0.7903 0.0871]
11	17	10	relational	[0.8711 0.1096]
12	18	11	store	[0.8061 0.0871]
13	19	12	quick	[0.8365 0.0909]
14	20	13	relational	[0.778 0.0539]
15	21	14	store	[0.8612 0.0375]

📊 query_tokens 2 rows 64 B Data Volume

q_token_id (INTEGER) token_text (VARCHAR) vec (FLOAT[2])

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

#	q_token_id	token_text	vec
1	1	fast	[1. 0.]
2	2	database	[0. 1.]

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

⏳ ORDER_BY 🔥 17.9% Cost

Latency: 108.55 ms

Rows In/Out: 999,997

Data Vol: 11.44 MB

📐 PROJECTION 4.4%

Latency: 26.63 ms

Rows In/Out: 999,997

Data Vol: 11.44 MB

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

📊 HASH_GROUP_BY 🔥 35.4% Cost

Latency: 215.26 ms

Rows In/Out: 999,997

Data Vol: 11.44 MB

📐 PROJECTION 0.2%

Latency: 1.16 ms

Rows In/Out: 1,999,994

Data Vol: 22.89 MB

📁 Projections: ['doc_id', 'CAST(ma...

📐 PROJECTION 0.0%

Latency: 0.17 ms

Rows In/Out: 1,999,994

Data Vol: 15.26 MB

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

📐 PROJECTION 0.2%

Latency: 1.09 ms

Rows In/Out: 1,999,994

Data Vol: 22.89 MB

📁 Projections: ['#0', 'CAST(#1 AS ...

📊 HASH_GROUP_BY 🔥 35.6% Cost

Latency: 216.38 ms

Rows In/Out: 1,999,994

Data Vol: 17.17 MB

📐 PROJECTION 0.0%

Latency: 0.19 ms

Rows In/Out: 2,000,000

Data Vol: 17.17 MB

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

📐 PROJECTION 0.1%

Latency: 0.57 ms

Rows In/Out: 2,000,000

Data Vol: 17.17 MB

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

📐 PROJECTION 5.5%

Latency: 33.32 ms

Rows In/Out: 2,000,000

Data Vol: 22.89 MB

📁 Projections: ['q_token_id', 'doc...

⚙️ CROSS_PRODUCT 0.3%

Latency: 2.06 ms

Rows In/Out: 2,000,000

Data Vol: 45.78 MB

🔍 TABLE_SCAN 0.4%

Latency: 2.72 ms

Rows In/Out: 1,000,000

Data Vol: 11.44 MB

📁 memory.main.doc_token_embeddings

🔍 TABLE_SCAN 0.0%

Latency: 0.01 ms

Rows In/Out: 2

Data Vol: 24 B

📁 memory.main.query_tokens

Operator	Cost %	Latency	Processed Rows	Data Volume	Details / Table	Status
HASH_GROUP_BY	35.6%	216.38 ms	1,999,994	17.17 MB	-	🔥 BOTTLENECK
HASH_GROUP_BY	35.4%	215.26 ms	999,997	11.44 MB	-	🔥 BOTTLENECK
ORDER_BY	17.9%	108.55 ms	999,997	11.44 MB	-	🔥 BOTTLENECK
PROJECTION	5.5%	33.32 ms	2,000,000	22.89 MB	Projections: ['q_token_id', 'doc_id', 'sim']	Normal
PROJECTION	4.4%	26.63 ms	999,997	11.44 MB	Projections: ['doc_id', 'colbert_maxsim_score']	Normal
TABLE_SCAN	0.4%	2.72 ms	1,000,000	11.44 MB	memory.main.doc_token_embeddings	Normal
CROSS_PRODUCT	0.3%	2.06 ms	2,000,000	45.78 MB	-	Normal
PROJECTION	0.2%	1.16 ms	1,999,994	22.89 MB	Projections: ['doc_id', 'CAST(max_token_sim AS D	Normal
PROJECTION	0.2%	1.09 ms	1,999,994	22.89 MB	Projections: ['#0', 'CAST(#1 AS INTEGER)', '#2']	Normal
PROJECTION	0.1%	0.57 ms	2,000,000	17.17 MB	Projections: ['CAST(#0 AS TINYINT)', '#1', '#2']	Normal
PROJECTION	0.0%	0.19 ms	2,000,000	17.17 MB	Projections: ['doc_id', 'q_token_id', 'sim']	Normal
PROJECTION	0.0%	0.17 ms	1,999,994	15.26 MB	Projections: ['doc_id', 'max_token_sim']	Normal
TABLE_SCAN	0.0%	0.01 ms	2	0.00 MB	memory.main.query_tokens	Normal

4. Queries Executed (Candidate vs Golden Ground Truth)

Beautified & Syntax Highlighted

WITH token_sims AS
  (SELECT q.q_token_id,
          q.token_text AS q_token,
          d.doc_id,
          d.token_text AS d_token,
          array_cosine_similarity(q.vec, d.vec) AS sim
   FROM query_tokens q
   CROSS JOIN doc_token_embeddings d),
     max_sim_per_query_token AS
  (SELECT doc_id,
          q_token_id,
          max(sim) AS max_token_sim
   FROM token_sims
   GROUP BY doc_id,
            q_token_id)
SELECT doc_id,
       round(sum(max_token_sim), 4) AS colbert_maxsim_score
FROM max_sim_per_query_token
GROUP BY doc_id
ORDER BY colbert_maxsim_score DESC;

-- ColBERT MaxSim calculation: SUM_q( MAX_d( sim(q, d) ) )
 WITH token_sims AS
  (SELECT q.q_token_id,
          q.token_text AS q_token,
          d.doc_id,
          d.token_text AS d_token,
          array_cosine_similarity(q.vec, d.vec) AS sim
   FROM query_tokens q
   CROSS JOIN doc_token_embeddings d),
      max_sim_per_query_token AS
  (SELECT doc_id,
          q_token_id,
          max(sim) AS max_token_sim
   FROM token_sims
   GROUP BY doc_id,
            q_token_id)
SELECT doc_id,
       round(sum(max_token_sim), 4) AS colbert_maxsim_score
FROM max_sim_per_query_token
GROUP BY doc_id
ORDER BY colbert_maxsim_score DESC;

CREATE TABLE query_tokens (q_token_id INT, token_text VARCHAR, vec FLOAT[2]);


CREATE TABLE doc_token_embeddings (doc_id INT, d_token_id INT, token_text VARCHAR, vec FLOAT[2]);

-- Query: "fast database"

INSERT INTO query_tokens
VALUES (1, 'fast', [1.0, 0.0]::FLOAT[2]),
       (2, 'database', [0.0, 1.0]::FLOAT[2]);

-- Document 10: "quick relational store"

INSERT INTO doc_token_embeddings
VALUES (10, 1, 'quick', [0.9, 0.1]::FLOAT[2]),
       (10, 2, 'relational', [0.1, 0.85]::FLOAT[2]),
       (10, 3, 'store', [0.2, 0.7]::FLOAT[2]);

5. Query Results & Expected Ground Truth

Tabular Comparison

Actual Candidate Output (999,997 rows × 2 cols)

#	doc_id	colbert_maxsim_score
1	10	1.987
2	516,742	1.2735
3	339,598	1.2704
4	942,379	1.267
5	325,972	1.2658
6	97,360	1.2655
7	844,599	1.2643
8	947,934	1.2642
9	518,269	1.2638
10	934,672	1.2613
11	875,869	1.261
12	457,607	1.2606
13	5,446	1.2589
14	771,091	1.2583
15	771,473	1.2582
16	606,911	1.2579
17	133,834	1.2573
18	682,425	1.256
19	751,771	1.2559
20	510,824	1.2556
21	42,564	1.2556
22	751,148	1.2552
23	394,974	1.255
24	813,372	1.2547
25	53,958	1.2546

Showing first 25 of 999,997 total rows.

Expected Golden Output (999,997 rows × 2 cols)

#	doc_id	colbert_maxsim_score
1	10	1.987
2	516,742	1.2735
3	339,598	1.2704
4	942,379	1.267
5	325,972	1.2658
6	97,360	1.2655
7	844,599	1.2643
8	947,934	1.2642
9	518,269	1.2638
10	934,672	1.2613
11	875,869	1.261
12	457,607	1.2606
13	5,446	1.2589
14	771,091	1.2583
15	771,473	1.2582
16	606,911	1.2579
17	133,834	1.2573
18	682,425	1.256
19	751,771	1.2559
20	42,564	1.2556
21	510,824	1.2556
22	751,148	1.2552
23	394,974	1.255
24	813,372	1.2547
25	529,620	1.2546

Showing first 25 of 999,997 total rows.

← Prev: Multi-Merchant Near-Dupli... ⊞ Back to Benchmark Dashboard Next: User Profile Preference V... →