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

Document-Level Aggregation with Best-Chunk Pooling in RAG

Candidate Latency (1M Rows)

459.21 ms

(1.0x faster than golden)

Golden Latency (1M Rows)

464.20 ms

DuckDB in-memory engine

Underlying Data Volume

1,000,000 rows

53.4 MiB across 1 table

Evaluated Model

gemini-3.8-flash-low

Gen Latency: 10.7s

✔ 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

Long PDF manuals are split into 50 chunks each. To avoid returning 5 chunks from the exact same PDF manual, we retrieve the best chunk per document and aggregate at the document level.

⚡ The Frontier Trap (Why LLMs Fail)

Returning raw chunk Top-K dominates the context window with duplicates from a single document.

Prompt / User Request Given to Model

Long PDF manuals are split into 50 chunks each. To avoid returning 5 chunks from the exact same PDF manual, we retrieve the best chunk per document and aggregate at the document level.

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

2. Underlying Tables, Row Counts & Data Volume

1 Tables (1,000,000 rows • 53.4 MiB)

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

📊 document_chunks 1,000,000 rows 53.4 MiB Data Volume

chunk_id (INTEGER) doc_id (INTEGER) doc_title (VARCHAR) chunk_text (VARCHAR) vec (FLOAT[2])

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

#	chunk_id	doc_id	doc_title	chunk_text	vec
1	101	1	Manual A	Setup guide section 1	[0.95 0.1 ]
2	102	1	Manual A	Setup guide section 2	[0.92 0.15]
3	103	2	Manual B	Troubleshooting section 1	[0.88 0.2 ]
4	103	2	Manual A	Setup guide section 1	[0.8323 0.1369]
5	104	3	Manual B	Setup guide section 2	[0.8597 0.115 ]
6	105	4	Manual A	Troubleshooting section 1	[0.8104 0.0788]
7	106	5	Manual B	Setup guide section 1	[0.8427 0.0882]
8	107	6	Manual A	Setup guide section 2	[0.8655 0.1166]
9	108	7	Manual B	Troubleshooting section 1	[0.8935 0.079 ]
10	109	8	Manual A	Setup guide section 1	[0.8974 0.1511]
11	110	9	Manual B	Setup guide section 2	[0.888 0.0805]
12	111	10	Manual A	Troubleshooting section 1	[0.8278 0.0686]
13	112	11	Manual B	Setup guide section 1	[0.8165 0.1021]
14	113	12	Manual A	Setup guide section 2	[0.8981 0.0871]
15	114	13	Manual B	Troubleshooting section 1	[0.8776 0.0762]

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

📐 PROJECTION 1.0%

Latency: 6.95 ms

Rows In/Out: 999,998

Data Vol: 41.96 MB

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

⏳ ORDER_BY 🔥 21.3% Cost

Latency: 141.37 ms

Rows In/Out: 999,998

Data Vol: 41.96 MB

📐 PROJECTION 1.4%

Latency: 9.21 ms

Rows In/Out: 999,998

Data Vol: 41.96 MB

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

📐 PROJECTION 6.6%

Latency: 43.93 ms

Rows In/Out: 999,998

Data Vol: 41.96 MB

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

⚡ HASH_JOIN 🔥 13.3% Cost

Latency: 88.64 ms

Rows In/Out: 999,998

Data Vol: 45.78 MB

🔍 TABLE_SCAN 5.0%

Latency: 33.33 ms

Rows In/Out: 999,998

Data Vol: 53.41 MB

📁 memory.main.document_chunks

📊 HASH_GROUP_BY 🔥 44.2% Cost

Latency: 293.61 ms

Rows In/Out: 999,998

Data Vol: 11.44 MB

📐 PROJECTION 3.3%

Latency: 21.90 ms

Rows In/Out: 1,000,000

Data Vol: 15.26 MB

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

🔍 TABLE_SCAN 3.8%

Latency: 25.18 ms

Rows In/Out: 1,000,000

Data Vol: 53.41 MB

📁 memory.main.document_chunks

Operator	Cost %	Latency	Processed Rows	Data Volume	Details / Table	Status
HASH_GROUP_BY	44.2%	293.61 ms	999,998	11.44 MB	-	🔥 BOTTLENECK
ORDER_BY	21.3%	141.37 ms	999,998	41.96 MB	-	🔥 BOTTLENECK
HASH_JOIN	13.3%	88.64 ms	999,998	45.78 MB	-	🔥 BOTTLENECK
PROJECTION	6.6%	43.93 ms	999,998	41.96 MB	Projections: ['doc_id', 'doc_title', 'chunk_id',	Normal
TABLE_SCAN	5.0%	33.33 ms	999,998	53.41 MB	memory.main.document_chunks	Normal
TABLE_SCAN	3.8%	25.18 ms	1,000,000	53.41 MB	memory.main.document_chunks	Normal
PROJECTION	3.3%	21.90 ms	1,000,000	15.26 MB	Projections: ['doc_id', 'rowid', 'array_cosine_s	Normal
PROJECTION	1.4%	9.21 ms	999,998	41.96 MB	Projections: ['#0', '__internal_compress_string_	Normal
PROJECTION	1.0%	6.95 ms	999,998	41.96 MB	Projections: ['#0', '__internal_decompress_strin	Normal

4. Queries Executed (Candidate vs Golden Ground Truth)

Beautified & Syntax Highlighted

SELECT doc_id,
       doc_title,
       chunk_id,
       chunk_text,
       round(array_cosine_similarity(vec, [1.0, 0.0]::FLOAT[2]), 4) AS chunk_score
FROM document_chunks QUALIFY row_number() OVER (PARTITION BY doc_id
                                                ORDER BY array_cosine_similarity(vec, [1.0, 0.0]::FLOAT[2]) DESC) = 1
ORDER BY chunk_score DESC;

-- Retrieve the single highest-scoring chunk per document using QUALIFY

SELECT doc_id,
       doc_title,
       chunk_id,
       chunk_text,
       round(array_cosine_similarity(vec, [1.0, 0.0]::FLOAT[2]), 4) AS chunk_score
FROM document_chunks QUALIFY row_number() OVER (PARTITION BY doc_id
                                                ORDER BY array_cosine_similarity(vec, [1.0, 0.0]::FLOAT[2]) DESC) = 1
ORDER BY chunk_score DESC;

CREATE TABLE document_chunks (chunk_id INT, doc_id INT, doc_title VARCHAR, chunk_text VARCHAR, vec FLOAT[2]);


INSERT INTO document_chunks
VALUES (101, 1, 'Manual A', 'Setup guide section 1', [0.95, 0.1]::FLOAT[2]),
       (102, 1, 'Manual A', 'Setup guide section 2', [0.92, 0.15]::FLOAT[2]),
       (103, 2, 'Manual B', 'Troubleshooting section 1', [0.88, 0.2]::FLOAT[2]);

5. Query Results & Expected Ground Truth

Tabular Comparison

Actual Candidate Output (999,998 rows × 5 cols)

#	doc_id	doc_title	chunk_id	chunk_text	chunk_score
1	303	Manual B	404	Setup guide section 2	1
2	318	Manual A	419	Setup guide section 2	1
3	394	Manual A	495	Troubleshooting section 1	1
4	464	Manual A	565	Setup guide section 1	1
5	844	Manual A	945	Troubleshooting section 1	1
6	850	Manual A	951	Troubleshooting section 1	1
7	887	Manual B	988	Setup guide section 1	1
8	1,147	Manual B	1,248	Troubleshooting section 1	1
9	1,254	Manual A	1,355	Setup guide section 2	1
10	1,363	Manual B	1,464	Troubleshooting section 1	1
11	1,448	Manual A	1,549	Setup guide section 1	1
12	1,506	Manual A	1,607	Setup guide section 2	1
13	2,397	Manual B	2,498	Setup guide section 2	1
14	2,551	Manual B	2,652	Troubleshooting section 1	1
15	2,570	Manual A	2,671	Setup guide section 1	1
16	2,573	Manual B	2,674	Setup guide section 1	1
17	2,768	Manual A	2,869	Setup guide section 1	1
18	2,824	Manual A	2,925	Troubleshooting section 1	1
19	2,996	Manual A	3,097	Setup guide section 1	1
20	3,077	Manual B	3,178	Setup guide section 1	1
21	3,118	Manual A	3,219	Troubleshooting section 1	1
22	3,196	Manual A	3,297	Troubleshooting section 1	1
23	3,345	Manual B	3,446	Setup guide section 2	1
24	3,602	Manual A	3,703	Setup guide section 1	1
25	3,655	Manual B	3,756	Troubleshooting section 1	1

Showing first 25 of 999,998 total rows.

Expected Golden Output (999,998 rows × 5 cols)

#	doc_id	doc_title	chunk_id	chunk_text	chunk_score
1	245,789	Manual B	245,890	Setup guide section 1	1
2	245,799	Manual B	245,900	Setup guide section 2	1
3	245,857	Manual B	245,958	Troubleshooting section 1	1
4	245,958	Manual A	246,059	Setup guide section 2	1
5	246,052	Manual A	246,153	Troubleshooting section 1	1
6	246,053	Manual B	246,154	Setup guide section 1	1
7	246,089	Manual B	246,190	Setup guide section 1	1
8	246,242	Manual A	246,343	Setup guide section 1	1
9	246,268	Manual A	246,369	Troubleshooting section 1	1
10	246,388	Manual A	246,489	Troubleshooting section 1	1
11	246,571	Manual B	246,672	Troubleshooting section 1	1
12	246,748	Manual A	246,849	Troubleshooting section 1	1
13	246,767	Manual B	246,868	Setup guide section 1	1
14	246,835	Manual B	246,936	Troubleshooting section 1	1
15	246,885	Manual B	246,986	Setup guide section 2	1
16	247,025	Manual B	247,126	Setup guide section 1	1
17	247,067	Manual B	247,168	Setup guide section 1	1
18	247,133	Manual B	247,234	Setup guide section 1	1
19	247,472	Manual A	247,573	Setup guide section 1	1
20	248,661	Manual B	248,762	Setup guide section 2	1
21	248,677	Manual B	248,778	Troubleshooting section 1	1
22	248,718	Manual A	248,819	Setup guide section 2	1
23	249,053	Manual B	249,154	Setup guide section 1	1
24	249,062	Manual A	249,163	Setup guide section 1	1
25	249,070	Manual A	249,171	Troubleshooting section 1	1

Showing first 25 of 999,998 total rows.

← Prev: Vector Repulsion Scoring ... ⊞ Back to Benchmark Dashboard Next: Dynamic Context Window To... →