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

Cohort-Constrained Vector Match with Geographical Regionality

Candidate Latency (1M Rows)

98.80 ms

(1.1x slower than golden)

Golden Latency (1M Rows)

91.67 ms

DuckDB in-memory engine

Underlying Data Volume

1,000,001 rows

34.3 MiB across 2 tables

Evaluated Model

gemini-3.8-flash-low

Gen Latency: 11.2s

✔ 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

Real-world matchmaking (dating apps, gig-economy driver matching) requires vector personality/skill compatibility joined with strict relational geographic and category boundaries.

⚡ The Frontier Trap (Why LLMs Fail)

Matching vectors globally without partitioning by region produces candidates located 3,000 miles away.

Prompt / User Request Given to Model

Real-world matchmaking (dating apps, gig-economy driver matching) requires vector personality/skill compatibility joined with strict relational geographic and category boundaries.

Execute the required analytical SQL calculation in DuckDB.
Expected output columns: job_id, region, candidate_id, skill_fit_score.
Results should be ordered as specified.

2. Underlying Tables, Row Counts & Data Volume

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

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

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

candidate_id (INTEGER) region (VARCHAR) skills_vec (FLOAT[3])

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

#	candidate_id	region	skills_vec
1	1	US-WEST	[0.8 0.5 0.2]
2	2	EU-CENTRAL	[0.85 0.5 0.2 ]
3	2	US-WEST	[0.8564 0.0135 0.0448]
4	3	EU-CENTRAL	[ 0.8426 -0.0158 0.0398]
5	4	US-WEST	[ 0.8315 0.0152 -0.0075]
6	5	EU-CENTRAL	[ 0.8648 -0.0325 0.0799]
7	6	US-WEST	[0.8107 0.0577 0.0823]
8	7	EU-CENTRAL	[0.8185 0.0194 0.0531]
9	8	US-WEST	[0.898 0.0153 0.0716]
10	9	EU-CENTRAL	[0.8869 0.045 0.0292]
11	10	US-WEST	[ 0.788 -0.0488 -0.001 ]
12	11	EU-CENTRAL	[0.8641 0.0211 0.0239]
13	12	US-WEST	[0.8598 0.0742 0.0206]
14	13	EU-CENTRAL	[8.295e-01 3.670e-02 1.000e-04]
15	14	US-WEST	[0.8183 0.0659 0.1206]

📊 job_openings 1 rows 36 B Data Volume

job_id (INTEGER) region (VARCHAR) required_vec (FLOAT[3])

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

#	job_id	region	required_vec
1	101	US-WEST	[0.82 0.48 0.18]

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

📐 PROJECTION 0.6%

Latency: 0.73 ms

Rows In/Out: 500,000

Data Vol: 13.35 MB

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

⏳ ORDER_BY 🔥 40.2% Cost

Latency: 45.82 ms

Rows In/Out: 500,000

Data Vol: 8.11 MB

📐 PROJECTION 1.8%

Latency: 2.04 ms

Rows In/Out: 500,000

Data Vol: 8.11 MB

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

📐 PROJECTION 🔥 20.6% Cost

Latency: 23.47 ms

Rows In/Out: 500,000

Data Vol: 13.35 MB

📁 Projections: ['job_id', 'region'...

⚡ HASH_JOIN 19.7%

Latency: 22.47 ms

Rows In/Out: 500,000

Data Vol: 22.89 MB

🔍 TABLE_SCAN 17.0%

Latency: 19.32 ms

Rows In/Out: 500,000

Data Vol: 15.26 MB

📁 memory.main.candidates

🔍 TABLE_SCAN 0.0%

Latency: 0.01 ms

Rows In/Out: 1

Data Vol: 32 B

📁 memory.main.job_openings

Operator	Cost %	Latency	Processed Rows	Data Volume	Details / Table	Status
ORDER_BY	40.2%	45.82 ms	500,000	8.11 MB	-	🔥 BOTTLENECK
PROJECTION	20.6%	23.47 ms	500,000	13.35 MB	Projections: ['job_id', 'region', 'candidate_id'	🔥 BOTTLENECK
HASH_JOIN	19.7%	22.47 ms	500,000	22.89 MB	-	Normal
TABLE_SCAN	17.0%	19.32 ms	500,000	15.26 MB	memory.main.candidates	Normal
PROJECTION	1.8%	2.04 ms	500,000	8.11 MB	Projections: ['CAST(#0 AS TINYINT)', '__internal	Normal
PROJECTION	0.6%	0.73 ms	500,000	13.35 MB	Projections: ['CAST(#0 AS INTEGER)', '__internal	Normal
TABLE_SCAN	0.0%	0.01 ms	1	0.00 MB	memory.main.job_openings	Normal

4. Queries Executed (Candidate vs Golden Ground Truth)

Beautified & Syntax Highlighted

SELECT j.job_id,
       j.region,
       c.candidate_id,
       round(array_cosine_similarity(c.skills_vec, j.required_vec), 4) AS skill_fit_score
FROM job_openings j
JOIN candidates c ON j.region = c.region
ORDER BY skill_fit_score DESC;

-- Match jobs to candidates strictly within matching geographical region ordered by skill compatibility

SELECT j.job_id,
       j.region,
       c.candidate_id,
       round(array_cosine_similarity(c.skills_vec, j.required_vec), 4) AS skill_fit_score
FROM job_openings j
JOIN candidates c ON j.region = c.region
ORDER BY skill_fit_score DESC;

CREATE TABLE candidates (candidate_id INT, region VARCHAR, skills_vec FLOAT[3]);


CREATE TABLE job_openings (job_id INT, region VARCHAR, required_vec FLOAT[3]);


INSERT INTO candidates
VALUES (1, 'US-WEST', [0.8, 0.5, 0.2]::FLOAT[3]),
       (2, 'EU-CENTRAL', [0.85, 0.5, 0.2]::FLOAT[3]);


INSERT INTO job_openings
VALUES (101, 'US-WEST', [0.82, 0.48, 0.18]::FLOAT[3]);

5. Query Results & Expected Ground Truth

Tabular Comparison

Actual Candidate Output (500,000 rows × 4 cols)

#	job_id	region	candidate_id	skill_fit_score
1	101	US-WEST	1	0.9994
2	101	US-WEST	368,840	0.9637
3	101	US-WEST	751,078	0.9613
4	101	US-WEST	205,910	0.961
5	101	US-WEST	498,798	0.9591
6	101	US-WEST	938,400	0.9586
7	101	US-WEST	212,330	0.9585
8	101	US-WEST	594,744	0.9582
9	101	US-WEST	464,044	0.9578
10	101	US-WEST	453,520	0.9576
11	101	US-WEST	78,506	0.9575
12	101	US-WEST	29,152	0.9573
13	101	US-WEST	844,956	0.9562
14	101	US-WEST	909,572	0.9558
15	101	US-WEST	855,096	0.9553
16	101	US-WEST	492,032	0.9551
17	101	US-WEST	457,548	0.955
18	101	US-WEST	878,170	0.9548
19	101	US-WEST	586,582	0.9546
20	101	US-WEST	486,514	0.9545
21	101	US-WEST	836,916	0.9543
22	101	US-WEST	535,410	0.9538
23	101	US-WEST	647,984	0.9538
24	101	US-WEST	203,650	0.9538
25	101	US-WEST	544,574	0.9535

Showing first 25 of 500,000 total rows.

Expected Golden Output (500,000 rows × 4 cols)

#	job_id	region	candidate_id	skill_fit_score
1	101	US-WEST	1	0.9994
2	101	US-WEST	368,840	0.9637
3	101	US-WEST	751,078	0.9613
4	101	US-WEST	205,910	0.961
5	101	US-WEST	498,798	0.9591
6	101	US-WEST	938,400	0.9586
7	101	US-WEST	212,330	0.9585
8	101	US-WEST	594,744	0.9582
9	101	US-WEST	464,044	0.9578
10	101	US-WEST	453,520	0.9576
11	101	US-WEST	78,506	0.9575
12	101	US-WEST	29,152	0.9573
13	101	US-WEST	844,956	0.9562
14	101	US-WEST	909,572	0.9558
15	101	US-WEST	855,096	0.9553
16	101	US-WEST	492,032	0.9551
17	101	US-WEST	457,548	0.955
18	101	US-WEST	878,170	0.9548
19	101	US-WEST	586,582	0.9546
20	101	US-WEST	486,514	0.9545
21	101	US-WEST	836,916	0.9543
22	101	US-WEST	647,984	0.9538
23	101	US-WEST	535,410	0.9538
24	101	US-WEST	203,650	0.9538
25	101	US-WEST	544,574	0.9535

Showing first 25 of 500,000 total rows.

← Prev: Vector Anomaly Detection ... ⊞ Back to Benchmark Dashboard Next: Multi-Modal Joint Retriev... →