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

Dynamic Context Window Token Budget Packing for LLM RAG

Candidate Latency (1M Rows)

98.30 ms

(1.1x faster than golden)

Golden Latency (1M Rows)

111.74 ms

DuckDB in-memory engine

Underlying Data Volume

1,000,000 rows

15.3 MiB across 1 table

Evaluated Model

gemini-3.8-flash-low

Gen Latency: 13.1s

✔ 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 assembling retrieved chunks for an LLM prompt, you have a strict token limit (e.g. 1,000 tokens). You must greedily pick highest-similarity chunks until the cumulative token sum would exceed the budget.

⚡ The Frontier Trap (Why LLMs Fail)

Picking a fixed LIMIT 10 can overflow LLM token limits or waste available prompt capacity.

Prompt / User Request Given to Model

When assembling retrieved chunks for an LLM prompt, you have a strict token limit (e.g. 1,000 tokens). You must greedily pick highest-similarity chunks until the cumulative token sum would exceed the budget.

Execute the required analytical SQL calculation in DuckDB.
Expected output columns: chunk_id, token_count, cumulative_tokens, similarity_score.
Results should be ordered as specified.

2. Underlying Tables, Row Counts & Data Volume

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

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

📊 rag_candidate_chunks 1,000,000 rows 15.3 MiB Data Volume

chunk_id (INTEGER) token_count (INTEGER) vec (FLOAT[2])

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

#	chunk_id	token_count	vec
1	1	400	[0.95 0.05]
2	2	350	[0.9 0.1]
3	3	400	[0.85 0.15]
4	3	401	[0.8576 0.1379]
5	4	412	[0.8928 0.1332]
6	5	352	[0.8087 0.1068]
7	6	353	[0.8309 0.1205]
8	7	379	[0.8807 0.0648]
9	8	357	[0.8191 0.1097]
10	9	425	[0.8583 0.1807]
11	10	369	[0.8021 0.0536]
12	11	363	[0.8342 0.1325]
13	12	405	[0.8565 0.1141]
14	13	337	[0.8809 0.0422]
15	14	374	[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: 340.53 ms) Nodes consuming ≥ 20% of query runtime are highlighted with glowing borders

📐 PROJECTION 0.0%

Latency: 0.00 ms

Rows In/Out: 0

Data Vol: 0 B

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

⏳ ORDER_BY 0.0%

Latency: 0.00 ms

Rows In/Out: 0

Data Vol: 0 B

📐 PROJECTION 0.0%

Latency: 0.00 ms

Rows In/Out: 0

Data Vol: 0 B

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

📐 PROJECTION 0.0%

Latency: 0.00 ms

Rows In/Out: 0

Data Vol: 0 B

📁 Projections: ['chunk_id', 'token...

📐 PROJECTION 0.0%

Latency: 0.00 ms

Rows In/Out: 0

Data Vol: 0 B

📁 Projections: ['chunk_id', 'token...

🎯 FILTER 0.6%

Latency: 2.21 ms

Rows In/Out: 0

Data Vol: 0 B

📐 PROJECTION 0.1%

Latency: 0.23 ms

Rows In/Out: 1,000,000

Data Vol: 30.52 MB

📁 Projections: ['#0', '#1', '#2', ...

🪟 WINDOW 🔥 98.4% Cost

Latency: 335.09 ms

Rows In/Out: 1,000,000

Data Vol: 30.52 MB

📁 Projections: sum_no_overflow(tok...

🔍 TABLE_SCAN 0.9%

Latency: 3.00 ms

Rows In/Out: 1,000,000

Data Vol: 15.26 MB

📁 memory.main.rag_candidate_chunks

Operator	Cost %	Latency	Processed Rows	Data Volume	Details / Table	Status
WINDOW	98.4%	335.09 ms	1,000,000	30.52 MB	Projections: sum_no_overflow(token_count) OVER (	🔥 BOTTLENECK
TABLE_SCAN	0.9%	3.00 ms	1,000,000	15.26 MB	memory.main.rag_candidate_chunks	Normal
FILTER	0.6%	2.21 ms	0	0.00 MB	-	Normal
PROJECTION	0.1%	0.23 ms	1,000,000	30.52 MB	Projections: ['#0', '#1', '#2', '#3']	Normal
ORDER_BY	0.0%	0.00 ms	0	0.00 MB	-	Normal
PROJECTION	0.0%	0.00 ms	0	0.00 MB	Projections: ['#0', 'CAST(#1 AS INTEGER)', '#2',	Normal
PROJECTION	0.0%	0.00 ms	0	0.00 MB	Projections: ['#0', 'CAST(#1 AS SMALLINT)', '#2'	Normal
PROJECTION	0.0%	0.00 ms	0	0.00 MB	Projections: ['chunk_id', 'token_count', 'cumula	Normal
PROJECTION	0.0%	0.00 ms	0	0.00 MB	Projections: ['chunk_id', 'token_count', 'score'	Normal

4. Queries Executed (Candidate vs Golden Ground Truth)

Beautified & Syntax Highlighted

WITH ranked_chunks AS
  (SELECT chunk_id,
          token_count,
          array_cosine_similarity(vec, [1.0, 0.0]::FLOAT[2]) AS score,
          sum(token_count) OVER (
                                 ORDER BY array_cosine_similarity(vec, [1.0, 0.0]::FLOAT[2]) DESC) AS cumulative_tokens
   FROM rag_candidate_chunks)
SELECT chunk_id,
       token_count,
       cumulative_tokens,
       round(score, 4) AS similarity_score
FROM ranked_chunks
WHERE cumulative_tokens <= 1000
ORDER BY similarity_score DESC;

-- Greedily pack chunks ordered by similarity up to strict 1,000 token budget
 WITH ranked_chunks AS
  (SELECT chunk_id,
          token_count,
          array_cosine_similarity(vec, [1.0, 0.0]::FLOAT[2]) AS score,
          sum(token_count) OVER (
                                 ORDER BY array_cosine_similarity(vec, [1.0, 0.0]::FLOAT[2]) DESC) AS cumulative_tokens
   FROM rag_candidate_chunks)
SELECT chunk_id,
       token_count,
       cumulative_tokens,
       round(score, 4) AS similarity_score
FROM ranked_chunks
WHERE cumulative_tokens <= 1000
ORDER BY similarity_score DESC;

CREATE TABLE rag_candidate_chunks (chunk_id INT, token_count INT, vec FLOAT[2]);


INSERT INTO rag_candidate_chunks
VALUES (1, 400, [0.95, 0.05]::FLOAT[2]),
       (2, 350, [0.90, 0.10]::FLOAT[2]),
       (3, 400, [0.85, 0.15]::FLOAT[2]); -- Exceeds 1000 total budget

5. Query Results & Expected Ground Truth

Tabular Comparison

Actual Candidate Output (0 rows × 4 cols)

Candidate returned 0 rows or errored out

Expected Golden Output (0 rows × 4 cols)

Golden returned 0 rows

← Prev: Document-Level Aggregatio... ⊞ Back to Benchmark Dashboard Next: Two-Stage Retrieval: Bi-E... →