✔ PASS
Search & Retrieval
task_051_Q51_fts_bm25_lexical_search
Scaled: 1M+ Records
Ordered: Yes
DuckDB Full-Text Search (FTS) BM25 Term Relevance Scoring
Candidate Latency (1M Rows)
54.74 ms
(1.0x faster than golden)
Golden Latency (1M Rows)
55.81 ms
DuckDB in-memory engine
Underlying Data Volume
1,000,000 rows
42.0 MiB across 1 table
Evaluated Model
gemini-3.8-flash-low
Gen Latency: 16.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 TrapBusiness Scenario & Problem
Traditional databases use simple `LIKE '%search%'` which treats 1 mention the same as 10 mentions and ignores document frequency. BM25 scores term saturation and document rarity.
⚡ The Frontier Trap (Why LLMs Fail)
ILIKE matches substrings blindly (e.g. 'cat' matches 'scattered' or 'certificate') with zero relevancy ranking.
Prompt / User Request Given to Model
Traditional databases use simple `LIKE '%search%'` which treats 1 mention the same as 10 mentions and ignores document frequency. BM25 scores term saturation and document rarity. Execute the required analytical SQL calculation in DuckDB. Expected output columns: doc_id, title, bm25_score. Results should be ordered as specified.
2. Underlying Tables, Row Counts & Data Volume
1 Tables (1,000,000 rows • 42.0 MiB)Production-scale fixtures loaded into the isolated DuckDB sandbox with at least 1,000,000 records per table:
📊 articles
1,000,000 rows
42.0 MiB Data Volume
doc_id (INTEGER) title (VARCHAR) body (VARCHAR)Data Preview (showing sample rows from 1,000,000 total rows):
| # | doc_id | title | body |
|---|---|---|---|
| 1 | 1 | Fast Columnar Databases | DuckDB is a vectorized analytical database designed for fast analytics. |
| 2 | 2 | Postgres Tuning Guide | Relational database indexing and buffer pool optimization. |
| 3 | 3 | Vector Search Frontiers | Combining dense vector embeddings with sparse BM25 indexing. |
| 4 | 3 | Fast Columnar Databases | DuckDB is a vectorized analytical database designed for fast analytics. |
| 5 | 4 | Postgres Tuning Guide | Relational database indexing and buffer pool optimization. |
| 6 | 5 | Vector Search Frontiers | Combining dense vector embeddings with sparse BM25 indexing. |
| 7 | 6 | Fast Columnar Databases | DuckDB is a vectorized analytical database designed for fast analytics. |
| 8 | 7 | Postgres Tuning Guide | Relational database indexing and buffer pool optimization. |
| 9 | 8 | Vector Search Frontiers | Combining dense vector embeddings with sparse BM25 indexing. |
| 10 | 9 | Fast Columnar Databases | DuckDB is a vectorized analytical database designed for fast analytics. |
| 11 | 10 | Postgres Tuning Guide | Relational database indexing and buffer pool optimization. |
| 12 | 11 | Vector Search Frontiers | Combining dense vector embeddings with sparse BM25 indexing. |
| 13 | 12 | Fast Columnar Databases | DuckDB is a vectorized analytical database designed for fast analytics. |
| 14 | 13 | Postgres Tuning Guide | Relational database indexing and buffer pool optimization. |
| 15 | 14 | Vector Search Frontiers | Combining dense vector embeddings with sparse BM25 indexing. |
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:
0.0%
Latency: 0.09 ms
Rows In/Out: 2
Data Vol: 56 B
0.0%
Latency: 0.01 ms
Rows In/Out: 2
Data Vol: 4,152 B
📁 Projections: ['doc_id', 'title',...
🔥 41.9% Cost
Latency: 87.62 ms
Rows In/Out: 0
Data Vol: 0 B
3.8%
Latency: 8.01 ms
Rows In/Out: 1,000,000
Data Vol: 26.70 MB
📁 memory.main.articles
1.7%
Latency: 3.49 ms
Rows In/Out: 0
Data Vol: 0 B
🔥 45.1% Cost
Latency: 94.17 ms
Rows In/Out: 1,000,000
Data Vol: 11.44 MB
0.1%
Latency: 0.17 ms
Rows In/Out: 1,000,000
Data Vol: 11.44 MB
📁 Projections: ['#[331.0]', '#[1.0...
0.0%
Latency: 0.02 ms
Rows In/Out: 1,000,000
Data Vol: 26.70 MB
0.4%
Latency: 0.80 ms
Rows In/Out: 2
Data Vol: 4,152 B
4.4%
Latency: 9.11 ms
Rows In/Out: 1,000,000
Data Vol: 26.83 MB
0.5%
Latency: 0.98 ms
Rows In/Out: 1,000,000
Data Vol: 26.70 MB
0.0%
Latency: 0.01 ms
Rows In/Out: 0
Data Vol: 0 B
0.0%
Latency: 0.00 ms
Rows In/Out: 2
Data Vol: 64 B
📁 Projections: ['fieldid', '1.0', ...
0.0%
Latency: 0.00 ms
Rows In/Out: 2
Data Vol: 82 B
0.0%
Latency: 0.01 ms
Rows In/Out: 2
Data Vol: 112 B
0.0%
Latency: 0.02 ms
Rows In/Out: 1
Data Vol: 40 B
0.0%
Latency: 0.00 ms
Rows In/Out: 1
Data Vol: 8 B
📁 Projections: default_b
0.0%
Latency: 0.00 ms
Rows In/Out: 1
Data Vol: 4 B
0.0%
Latency: 0.01 ms
Rows In/Out: 1
Data Vol: 32 B
📁 memory.fts_main_articles.stats
0.0%
Latency: 0.00 ms
Rows In/Out: 2
Data Vol: 48 B
📁 memory.fts_main_articles.fields
0.0%
Latency: 0.00 ms
Rows In/Out: 0
Data Vol: 0 B
📁 Projections: field
0.0%
Latency: 0.00 ms
Rows In/Out: 0
Data Vol: 0 B
📁 Projections: #1
0.0%
Latency: 0.00 ms
Rows In/Out: 0
Data Vol: 0 B
0.0%
Latency: 0.00 ms
Rows In/Out: 0
Data Vol: 0 B
0.0%
Latency: 0.00 ms
Rows In/Out: 1
Data Vol: 4 B
0.0%
Latency: 0.01 ms
Rows In/Out: 0
Data Vol: 0 B
0.0%
Latency: 0.05 ms
Rows In/Out: 2
Data Vol: 32 B
0.0%
Latency: 0.00 ms
Rows In/Out: 2
Data Vol: 32 B
📁 Projections: t
0.0%
Latency: 0.02 ms
Rows In/Out: 2
Data Vol: 32 B
📁 Projections: t
0.1%
Latency: 0.19 ms
Rows In/Out: 2
Data Vol: 32 B
0.0%
Latency: 0.00 ms
Rows In/Out: 2
Data Vol: 32 B
📁 Projections: raw_token
0.0%
Latency: 0.00 ms
Rows In/Out: 2
Data Vol: 32 B
📁 Projections: #0
0.0%
Latency: 0.01 ms
Rows In/Out: 2
Data Vol: 290 B
0.1%
Latency: 0.15 ms
Rows In/Out: 2
Data Vol: 290 B
0.0%
Latency: 0.00 ms
Rows In/Out: 2
Data Vol: 32 B
📁 Projections: #1
0.0%
Latency: 0.06 ms
Rows In/Out: 2
Data Vol: 40 B
0.0%
Latency: 0.01 ms
Rows In/Out: 2
Data Vol: 40 B
0.0%
Latency: 0.00 ms
Rows In/Out: 1
Data Vol: 4 B
0.0%
Latency: 0.02 ms
Rows In/Out: 571
Data Vol: 9,136 B
📁 memory.fts_main_articles.stopwor...
0.0%
Latency: 0.06 ms
Rows In/Out: 0
Data Vol: 0 B
0.0%
Latency: 0.00 ms
Rows In/Out: 5
Data Vol: 100 B
📁 Projections: ['termid', 'docid',...
0.0%
Latency: 0.08 ms
Rows In/Out: 5
Data Vol: 100 B
0.0%
Latency: 0.06 ms
Rows In/Out: 5
Data Vol: 100 B
0.0%
Latency: 0.03 ms
Rows In/Out: 5
Data Vol: 100 B
📁 memory.fts_main_articles.terms
0.0%
Latency: 0.00 ms
Rows In/Out: 2
Data Vol: 16 B
📁 Projections: termid
0.0%
Latency: 0.05 ms
Rows In/Out: 2
Data Vol: 48 B
0.0%
Latency: 0.04 ms
Rows In/Out: 2
Data Vol: 48 B
📁 memory.fts_main_articles.dict
0.0%
Latency: 0.00 ms
Rows In/Out: 2
Data Vol: 32 B
0.0%
Latency: 0.00 ms
Rows In/Out: 2
Data Vol: 16 B
📁 Projections: fieldid
0.0%
Latency: 0.00 ms
Rows In/Out: 2
Data Vol: 64 B
0.1%
Latency: 0.12 ms
Rows In/Out: 0
Data Vol: 0 B
0.0%
Latency: 0.02 ms
Rows In/Out: 4
Data Vol: 480 B
📁 Projections: ['docid', 'termid',...
0.0%
Latency: 0.00 ms
Rows In/Out: 4
Data Vol: 672 B
📁 Projections: ['termid', 'rawterm...
0.0%
Latency: 0.06 ms
Rows In/Out: 4
Data Vol: 352 B
0.0%
Latency: 0.02 ms
Rows In/Out: 3
Data Vol: 120 B
📁 memory.fts_main_articles.docs
0.1%
Latency: 0.14 ms
Rows In/Out: 4
Data Vol: 240 B
0.0%
Latency: 0.00 ms
Rows In/Out: 2
Data Vol: 64 B
0.0%
Latency: 0.00 ms
Rows In/Out: 4
Data Vol: 144 B
📁 Projections: ['#0', '#1', 'CAST(...
0.1%
Latency: 0.16 ms
Rows In/Out: 4
Data Vol: 88 B
0.0%
Latency: 0.00 ms
Rows In/Out: 5
Data Vol: 70 B
📁 Projections: ['docid', 'fieldid'...
0.0%
Latency: 0.00 ms
Rows In/Out: 5
Data Vol: 70 B
📁 Projections: ['CAST(#1 AS TINYIN...
0.0%
Latency: 0.05 ms
Rows In/Out: 5
Data Vol: 140 B
0.0%
Latency: 0.01 ms
Rows In/Out: 2
Data Vol: 32 B
📁 memory.fts_main_articles.dict
0.0%
Latency: 0.05 ms
Rows In/Out: 5
Data Vol: 140 B
0.0%
Latency: 0.00 ms
Rows In/Out: 5
Data Vol: 100 B
0.0%
Latency: 0.00 ms
Rows In/Out: 2
Data Vol: 16 B
📁 Projections: docid
0.0%
Latency: 0.03 ms
Rows In/Out: 2
Data Vol: 24 B
0.0%
Latency: 0.00 ms
Rows In/Out: 1
Data Vol: 4 B
📁 Projections: 42
0.0%
Latency: 0.00 ms
Rows In/Out: 1
Data Vol: 8 B
0.0%
Latency: 0.00 ms
Rows In/Out: 2
Data Vol: 32 B
0.1%
Latency: 0.29 ms
Rows In/Out: 2
Data Vol: 16 B
0.0%
Latency: 0.00 ms
Rows In/Out: 3
Data Vol: 24 B
📁 Projections: #0
0.0%
Latency: 0.00 ms
Rows In/Out: 3
Data Vol: 24 B
📁 Projections: #0
0.0%
Latency: 0.07 ms
Rows In/Out: 3
Data Vol: 27 B
0.0%
Latency: 0.00 ms
Rows In/Out: 5
Data Vol: 45 B
📁 Projections: ['docid', 'termid']
0.0%
Latency: 0.00 ms
Rows In/Out: 5
Data Vol: 45 B
📁 Projections: ['CAST(#0 AS TINYIN...
0.0%
Latency: 0.00 ms
Rows In/Out: 5
Data Vol: 100 B
0.0%
Latency: 0.00 ms
Rows In/Out: 0
Data Vol: 0 B
0.0%
Latency: 0.00 ms
Rows In/Out: 2
Data Vol: 48 B
📁 Projections: ['score', 'delim_in...
0.0%
Latency: 0.06 ms
Rows In/Out: 2
Data Vol: 48 B
0.7%
Latency: 1.38 ms
Rows In/Out: 4
Data Vol: 48 B
0.2%
Latency: 0.51 ms
Rows In/Out: 1,000,000
Data Vol: 11.44 MB
0.0%
Latency: 0.10 ms
Rows In/Out: 2
Data Vol: 24 B
0.0%
Latency: 0.01 ms
Rows In/Out: 2
Data Vol: 24 B
📁 memory.fts_main_articles.docs
0.0%
Latency: 0.00 ms
Rows In/Out: 0
Data Vol: 0 B
0.0%
Latency: 0.00 ms
Rows In/Out: 2
Data Vol: 32 B
📁 Projections: ['docid', 'score']
0.0%
Latency: 0.02 ms
Rows In/Out: 2
Data Vol: 40 B
0.0%
Latency: 0.06 ms
Rows In/Out: 2
Data Vol: 32 B
0.0%
Latency: 0.01 ms
Rows In/Out: 3
Data Vol: 48 B
📁 Projections: ['docid', '((expans...
0.0%
Latency: 0.02 ms
Rows In/Out: 3
Data Vol: 120 B
0.0%
Latency: 0.00 ms
Rows In/Out: 3
Data Vol: 96 B
📁 Projections: ['docid', 'df', 'ex...
0.1%
Latency: 0.13 ms
Rows In/Out: 3
Data Vol: 400 B
0.0%
Latency: 0.00 ms
Rows In/Out: 4
Data Vol: 448 B
📁 Projections: ['docid', 'termid',...
0.0%
Latency: 0.00 ms
Rows In/Out: 4
Data Vol: 480 B
0.0%
Latency: 0.00 ms
Rows In/Out: 1
Data Vol: 8 B
📁 memory.fts_main_articles.stats
0.0%
Latency: 0.00 ms
Rows In/Out: 1
Data Vol: 4 B
📁 Projections: 42
0.0%
Latency: 0.00 ms
Rows In/Out: 1
Data Vol: 4 B
📁 Projections: 42
0.0%
Latency: 0.00 ms
Rows In/Out: 1
Data Vol: 4 B
0.0%
Latency: 0.00 ms
Rows In/Out: 0
Data Vol: 0 B
0.0%
Latency: 0.00 ms
Rows In/Out: 0
Data Vol: 0 B
📁 Projections: ['CAST("error"(mess...
0.0%
Latency: 0.00 ms
Rows In/Out: 0
Data Vol: 0 B
0.0%
Latency: 0.01 ms
Rows In/Out: 8,192
Data Vol: 0.09 MB
0.0%
Latency: 0.00 ms
Rows In/Out: 0
Data Vol: 0 B
📁 Projections: #0
0.0%
Latency: 0.00 ms
Rows In/Out: 0
Data Vol: 0 B
0.0%
Latency: 0.00 ms
Rows In/Out: 0
Data Vol: 0 B
📁 Projections: ['message', 'priori...
0.0%
Latency: 0.00 ms
Rows In/Out: 0
Data Vol: 0 B
0.0%
Latency: 0.00 ms
Rows In/Out: 0
Data Vol: 0 B
📁 Projections: #0
0.0%
Latency: 0.00 ms
Rows In/Out: 0
Data Vol: 0 B
0.0%
Latency: 0.05 ms
Rows In/Out: 0
Data Vol: 0 B
0.0%
Latency: 0.00 ms
Rows In/Out: 0
Data Vol: 0 B
📁 Projections: key
0.1%
Latency: 0.13 ms
Rows In/Out: 0
Data Vol: 0 B
0.0%
Latency: 0.00 ms
Rows In/Out: 1
Data Vol: 1,064 B
📁 Projections: field_weights
0.0%
Latency: 0.00 ms
Rows In/Out: 1
Data Vol: 4 B
0.0%
Latency: 0.00 ms
Rows In/Out: 2
Data Vol: 32 B
📁 memory.fts_main_articles.fields
0.0%
Latency: 0.00 ms
Rows In/Out: 0
Data Vol: 0 B
0.0%
Latency: 0.00 ms
Rows In/Out: 0
Data Vol: 0 B
📁 Projections: ['priority', 'messa...
0.0%
Latency: 0.00 ms
Rows In/Out: 0
Data Vol: 0 B
📁 Projections: key
0.0%
Latency: 0.00 ms
Rows In/Out: 0
Data Vol: 0 B
📁 Projections: #1
0.0%
Latency: 0.00 ms
Rows In/Out: 0
Data Vol: 0 B
0.0%
Latency: 0.01 ms
Rows In/Out: 0
Data Vol: 0 B
0.0%
Latency: 0.00 ms
Rows In/Out: 1
Data Vol: 1,064 B
📁 Projections: field_weights
0.0%
Latency: 0.00 ms
Rows In/Out: 1
Data Vol: 4 B
0.0%
Latency: 0.00 ms
Rows In/Out: 0
Data Vol: 0 B
📁 Projections: ['priority', 'messa...
0.0%
Latency: 0.00 ms
Rows In/Out: 0
Data Vol: 0 B
0.0%
Latency: 0.00 ms
Rows In/Out: 0
Data Vol: 0 B
📁 Projections: ['priority', 'messa...
0.0%
Latency: 0.00 ms
Rows In/Out: 0
Data Vol: 0 B
📁 Projections: key
0.0%
Latency: 0.00 ms
Rows In/Out: 0
Data Vol: 0 B
📁 Projections: #1
0.0%
Latency: 0.00 ms
Rows In/Out: 0
Data Vol: 0 B
0.0%
Latency: 0.01 ms
Rows In/Out: 0
Data Vol: 0 B
0.0%
Latency: 0.01 ms
Rows In/Out: 1
Data Vol: 1,064 B
📁 Projections: field_b
0.0%
Latency: 0.00 ms
Rows In/Out: 1
Data Vol: 4 B
0.0%
Latency: 0.00 ms
Rows In/Out: 0
Data Vol: 0 B
📁 Projections: ['priority', 'messa...
0.0%
Latency: 0.00 ms
Rows In/Out: 0
Data Vol: 0 B
| Operator | Cost % | Latency | Processed Rows | Data Volume | Details / Table | Status |
|---|---|---|---|---|---|---|
| HASH_GROUP_BY | 45.1% | 94.17 ms | 1,000,000 | 11.44 MB | - | 🔥 BOTTLENECK |
| CTE | 41.9% | 87.62 ms | 0 | 0.00 MB | - | 🔥 BOTTLENECK |
| HASH_JOIN | 4.4% | 9.11 ms | 1,000,000 | 26.83 MB | - | Normal |
| TABLE_SCAN | 3.8% | 8.01 ms | 1,000,000 | 26.70 MB | memory.main.articles | Normal |
| CTE | 1.7% | 3.49 ms | 0 | 0.00 MB | - | Normal |
| FILTER | 0.7% | 1.38 ms | 4 | 0.00 MB | - | Normal |
| CTE_SCAN | 0.5% | 0.98 ms | 1,000,000 | 26.70 MB | - | Normal |
| FILTER | 0.4% | 0.80 ms | 2 | 0.00 MB | - | Normal |
| CTE_SCAN | 0.2% | 0.51 ms | 1,000,000 | 11.44 MB | - | Normal |
| HASH_GROUP_BY | 0.1% | 0.29 ms | 2 | 0.00 MB | - | Normal |
| HASH_GROUP_BY | 0.1% | 0.19 ms | 2 | 0.00 MB | - | Normal |
| PROJECTION | 0.1% | 0.17 ms | 1,000,000 | 11.44 MB | Projections: ['#[331.0]', '#[1.0]'] | Normal |
| HASH_GROUP_BY | 0.1% | 0.16 ms | 4 | 0.00 MB | - | Normal |
| HASH_JOIN | 0.1% | 0.15 ms | 2 | 0.00 MB | - | Normal |
| HASH_JOIN | 0.1% | 0.14 ms | 4 | 0.00 MB | - | Normal |
| UNNEST | 0.1% | 0.13 ms | 0 | 0.00 MB | - | Normal |
| HASH_GROUP_BY | 0.1% | 0.13 ms | 3 | 0.00 MB | - | Normal |
| CTE | 0.1% | 0.12 ms | 0 | 0.00 MB | - | Normal |
| HASH_JOIN | 0.0% | 0.10 ms | 2 | 0.00 MB | - | Normal |
| ORDER_BY | 0.0% | 0.09 ms | 2 | 0.00 MB | - | Normal |
| HASH_JOIN | 0.0% | 0.08 ms | 5 | 0.00 MB | - | Normal |
| HASH_GROUP_BY | 0.0% | 0.07 ms | 3 | 0.00 MB | - | Normal |
| HASH_GROUP_BY | 0.0% | 0.06 ms | 2 | 0.00 MB | - | Normal |
| FILTER | 0.0% | 0.06 ms | 2 | 0.00 MB | - | Normal |
| HASH_JOIN | 0.0% | 0.06 ms | 4 | 0.00 MB | - | Normal |
| HASH_JOIN | 0.0% | 0.06 ms | 2 | 0.00 MB | - | Normal |
| HASH_JOIN | 0.0% | 0.06 ms | 5 | 0.00 MB | - | Normal |
| CTE | 0.0% | 0.06 ms | 0 | 0.00 MB | - | Normal |
| HASH_JOIN | 0.0% | 0.05 ms | 2 | 0.00 MB | - | Normal |
| HASH_JOIN | 0.0% | 0.05 ms | 5 | 0.00 MB | - | Normal |
| HASH_JOIN | 0.0% | 0.05 ms | 0 | 0.00 MB | - | Normal |
| HASH_JOIN | 0.0% | 0.05 ms | 5 | 0.00 MB | - | Normal |
| HASH_GROUP_BY | 0.0% | 0.05 ms | 2 | 0.00 MB | - | Normal |
| TABLE_SCAN | 0.0% | 0.04 ms | 2 | 0.00 MB | memory.fts_main_articles.dict | Normal |
| CROSS_PRODUCT | 0.0% | 0.03 ms | 2 | 0.00 MB | - | Normal |
| TABLE_SCAN | 0.0% | 0.03 ms | 5 | 0.00 MB | memory.fts_main_articles.terms | Normal |
| PROJECTION | 0.0% | 0.02 ms | 4 | 0.00 MB | Projections: ['docid', 'termid', 'rawtermid', 'd | Normal |
| CROSS_PRODUCT | 0.0% | 0.02 ms | 3 | 0.00 MB | - | Normal |
| CTE_SCAN | 0.0% | 0.02 ms | 1,000,000 | 26.70 MB | - | Normal |
| CROSS_PRODUCT | 0.0% | 0.02 ms | 2 | 0.00 MB | - | Normal |
| PROJECTION | 0.0% | 0.02 ms | 2 | 0.00 MB | Projections: t | Normal |
| TABLE_SCAN | 0.0% | 0.02 ms | 571 | 0.01 MB | memory.fts_main_articles.stopwords | Normal |
| CROSS_PRODUCT | 0.0% | 0.02 ms | 1 | 0.00 MB | - | Normal |
| TABLE_SCAN | 0.0% | 0.02 ms | 3 | 0.00 MB | memory.fts_main_articles.docs | Normal |
| CROSS_PRODUCT | 0.0% | 0.01 ms | 2 | 0.00 MB | - | Normal |
| PROJECTION | 0.0% | 0.01 ms | 3 | 0.00 MB | Projections: ['docid', '((expansion_weight * log | Normal |
| CTE | 0.0% | 0.01 ms | 0 | 0.00 MB | - | Normal |
| TABLE_SCAN | 0.0% | 0.01 ms | 2 | 0.00 MB | memory.fts_main_articles.dict | Normal |
| TABLE_SCAN | 0.0% | 0.01 ms | 2 | 0.00 MB | memory.fts_main_articles.docs | Normal |
| UNNEST | 0.0% | 0.01 ms | 0 | 0.00 MB | - | Normal |
| CTE_SCAN | 0.0% | 0.01 ms | 8,192 | 0.09 MB | - | Normal |
| UNNEST | 0.0% | 0.01 ms | 2 | 0.00 MB | - | Normal |
| CTE | 0.0% | 0.01 ms | 0 | 0.00 MB | - | Normal |
| UNNEST | 0.0% | 0.01 ms | 0 | 0.00 MB | - | Normal |
| TABLE_SCAN | 0.0% | 0.01 ms | 1 | 0.00 MB | memory.fts_main_articles.stats | Normal |
| FILTER | 0.0% | 0.01 ms | 2 | 0.00 MB | - | Normal |
| PROJECTION | 0.0% | 0.01 ms | 1 | 0.00 MB | Projections: field_b | Normal |
| PROJECTION | 0.0% | 0.01 ms | 2 | 0.00 MB | Projections: ['doc_id', 'title', 'bm25_score'] | Normal |
| PROJECTION | 0.0% | 0.00 ms | 4 | 0.00 MB | Projections: ['#0', '#1', 'CAST(#2 AS BIGINT)', | Normal |
| PROJECTION | 0.0% | 0.00 ms | 2 | 0.00 MB | Projections: ['fieldid', '1.0', 'default_b', 'av | Normal |
| TABLE_SCAN | 0.0% | 0.00 ms | 2 | 0.00 MB | memory.fts_main_articles.fields | Normal |
| PROJECTION | 0.0% | 0.00 ms | 1 | 0.00 MB | Projections: field_weights | Normal |
| UNNEST | 0.0% | 0.00 ms | 0 | 0.00 MB | - | Normal |
| CTE_SCAN | 0.0% | 0.00 ms | 2 | 0.00 MB | - | Normal |
| TABLE_SCAN | 0.0% | 0.00 ms | 2 | 0.00 MB | memory.fts_main_articles.fields | Normal |
| TABLE_SCAN | 0.0% | 0.00 ms | 1 | 0.00 MB | memory.fts_main_articles.stats | Normal |
| CTE_SCAN | 0.0% | 0.00 ms | 2 | 0.00 MB | - | Normal |
| PROJECTION | 0.0% | 0.00 ms | 1 | 0.00 MB | Projections: field_weights | Normal |
| CROSS_PRODUCT | 0.0% | 0.00 ms | 0 | 0.00 MB | - | Normal |
| UNGROUPED_AGGREGATE | 0.0% | 0.00 ms | 1 | 0.00 MB | - | Normal |
| HASH_JOIN | 0.0% | 0.00 ms | 2 | 0.00 MB | - | Normal |
| CTE_SCAN | 0.0% | 0.00 ms | 5 | 0.00 MB | - | Normal |
| CTE_SCAN | 0.0% | 0.00 ms | 2 | 0.00 MB | - | Normal |
| PROJECTION | 0.0% | 0.00 ms | 5 | 0.00 MB | Projections: ['CAST(#1 AS TINYINT)', 'CAST(#0 AS | Normal |
| PROJECTION | 0.0% | 0.00 ms | 4 | 0.00 MB | Projections: ['termid', 'rawtermid', 'docid', 'f | Normal |
| PROJECTION | 0.0% | 0.00 ms | 5 | 0.00 MB | Projections: ['CAST(#0 AS TINYINT)', '#1'] | Normal |
| PROJECTION | 0.0% | 0.00 ms | 2 | 0.00 MB | Projections: ['docid', 'score'] | Normal |
| PROJECTION | 0.0% | 0.00 ms | 1 | 0.00 MB | Projections: default_b | Normal |
| PROJECTION | 0.0% | 0.00 ms | 3 | 0.00 MB | Projections: #0 | Normal |
| PROJECTION | 0.0% | 0.00 ms | 4 | 0.00 MB | Projections: ['docid', 'termid', 'rawtermid', 'd | Normal |
| PROJECTION | 0.0% | 0.00 ms | 3 | 0.00 MB | Projections: ['docid', 'df', 'expansion_weight', | Normal |
| PROJECTION | 0.0% | 0.00 ms | 2 | 0.00 MB | Projections: ['score', 'delim_index'] | Normal |
| DUMMY_SCAN | 0.0% | 0.00 ms | 1 | 0.00 MB | - | Normal |
| PROJECTION | 0.0% | 0.00 ms | 1 | 0.00 MB | Projections: 42 | Normal |
| PROJECTION | 0.0% | 0.00 ms | 1 | 0.00 MB | Projections: 42 | Normal |
| PROJECTION | 0.0% | 0.00 ms | 2 | 0.00 MB | Projections: fieldid | Normal |
| PROJECTION | 0.0% | 0.00 ms | 2 | 0.00 MB | Projections: #1 | Normal |
| PROJECTION | 0.0% | 0.00 ms | 5 | 0.00 MB | Projections: ['termid', 'docid', 'fieldid'] | Normal |
| PROJECTION | 0.0% | 0.00 ms | 2 | 0.00 MB | Projections: termid | Normal |
| PROJECTION | 0.0% | 0.00 ms | 3 | 0.00 MB | Projections: #0 | Normal |
| PROJECTION | 0.0% | 0.00 ms | 2 | 0.00 MB | Projections: t | Normal |
| DUMMY_SCAN | 0.0% | 0.00 ms | 1 | 0.00 MB | - | Normal |
| PROJECTION | 0.0% | 0.00 ms | 1 | 0.00 MB | Projections: 42 | Normal |
| PROJECTION | 0.0% | 0.00 ms | 2 | 0.00 MB | Projections: docid | Normal |
| EMPTY_RESULT | 0.0% | 0.00 ms | 0 | 0.00 MB | - | Normal |
| TOP_N | 0.0% | 0.00 ms | 0 | 0.00 MB | - | Normal |
| PROJECTION | 0.0% | 0.00 ms | 5 | 0.00 MB | Projections: ['docid', 'fieldid', 'termid', 'df' | Normal |
| CTE_SCAN | 0.0% | 0.00 ms | 0 | 0.00 MB | - | Normal |
| DUMMY_SCAN | 0.0% | 0.00 ms | 1 | 0.00 MB | - | Normal |
| DUMMY_SCAN | 0.0% | 0.00 ms | 1 | 0.00 MB | - | Normal |
| FILTER | 0.0% | 0.00 ms | 0 | 0.00 MB | - | Normal |
| PROJECTION | 0.0% | 0.00 ms | 2 | 0.00 MB | Projections: #0 | Normal |
| DUMMY_SCAN | 0.0% | 0.00 ms | 1 | 0.00 MB | - | Normal |
| DUMMY_SCAN | 0.0% | 0.00 ms | 1 | 0.00 MB | - | Normal |
| DUMMY_SCAN | 0.0% | 0.00 ms | 1 | 0.00 MB | - | Normal |
| CTE_SCAN | 0.0% | 0.00 ms | 0 | 0.00 MB | - | Normal |
| PROJECTION | 0.0% | 0.00 ms | 5 | 0.00 MB | Projections: ['docid', 'termid'] | Normal |
| FILTER | 0.0% | 0.00 ms | 0 | 0.00 MB | - | Normal |
| PROJECTION | 0.0% | 0.00 ms | 2 | 0.00 MB | Projections: raw_token | Normal |
| CTE_SCAN | 0.0% | 0.00 ms | 5 | 0.00 MB | - | Normal |
| CTE_SCAN | 0.0% | 0.00 ms | 4 | 0.00 MB | - | Normal |
| CTE_SCAN | 0.0% | 0.00 ms | 2 | 0.00 MB | - | Normal |
| PROJECTION | 0.0% | 0.00 ms | 0 | 0.00 MB | Projections: field | Normal |
| PROJECTION | 0.0% | 0.00 ms | 0 | 0.00 MB | Projections: #1 | Normal |
| UNION | 0.0% | 0.00 ms | 0 | 0.00 MB | - | Normal |
| UNION | 0.0% | 0.00 ms | 0 | 0.00 MB | - | Normal |
| PROJECTION | 0.0% | 0.00 ms | 0 | 0.00 MB | Projections: ['CAST("error"(message) AS DOUBLE)' | Normal |
| PROJECTION | 0.0% | 0.00 ms | 0 | 0.00 MB | Projections: #0 | Normal |
| PROJECTION | 0.0% | 0.00 ms | 0 | 0.00 MB | Projections: ['message', 'priority'] | Normal |
| CTE | 0.0% | 0.00 ms | 0 | 0.00 MB | - | Normal |
| PROJECTION | 0.0% | 0.00 ms | 0 | 0.00 MB | Projections: #0 | Normal |
| PROJECTION | 0.0% | 0.00 ms | 0 | 0.00 MB | Projections: key | Normal |
| UNION | 0.0% | 0.00 ms | 0 | 0.00 MB | - | Normal |
| PROJECTION | 0.0% | 0.00 ms | 0 | 0.00 MB | Projections: ['priority', 'message'] | Normal |
| PROJECTION | 0.0% | 0.00 ms | 0 | 0.00 MB | Projections: key | Normal |
| PROJECTION | 0.0% | 0.00 ms | 0 | 0.00 MB | Projections: #1 | Normal |
| FILTER | 0.0% | 0.00 ms | 0 | 0.00 MB | - | Normal |
| PROJECTION | 0.0% | 0.00 ms | 0 | 0.00 MB | Projections: ['priority', 'message'] | Normal |
| PROJECTION | 0.0% | 0.00 ms | 0 | 0.00 MB | Projections: ['priority', 'message'] | Normal |
| PROJECTION | 0.0% | 0.00 ms | 0 | 0.00 MB | Projections: key | Normal |
| PROJECTION | 0.0% | 0.00 ms | 0 | 0.00 MB | Projections: #1 | Normal |
| FILTER | 0.0% | 0.00 ms | 0 | 0.00 MB | - | Normal |
| PROJECTION | 0.0% | 0.00 ms | 0 | 0.00 MB | Projections: ['priority', 'message'] | Normal |
4. Queries Executed (Candidate vs Golden Ground Truth)
Beautified & Syntax HighlightedWITH scored_articles AS
(SELECT doc_id,
title,
BODY,
fts_main_articles.match_bm25(doc_id, 'analytical database') AS bm25_score
FROM articles)
SELECT doc_id,
title,
round(bm25_score, 4) AS bm25_score
FROM scored_articles
WHERE bm25_score IS NOT NULL
ORDER BY bm25_score DESC;
-- BM25 full-text search for 'analytical database'
WITH scored_articles AS
(SELECT doc_id,
title,
BODY,
fts_main_articles.match_bm25(doc_id, 'analytical database') AS bm25_score
FROM articles)
SELECT doc_id,
title,
round(bm25_score, 4) AS bm25_score
FROM scored_articles
WHERE bm25_score IS NOT NULL
ORDER BY bm25_score DESC;
install fts;
LOAD fts;
CREATE TABLE articles (doc_id INT, title VARCHAR, BODY VARCHAR);
INSERT INTO articles
VALUES (1, 'Fast Columnar Databases', 'DuckDB is a vectorized analytical database designed for fast analytics.'),
(2, 'Postgres Tuning Guide', 'Relational database indexing and buffer pool optimization.'),
(3, 'Vector Search Frontiers', 'Combining dense vector embeddings with sparse BM25 indexing.');
pragma create_fts_index('articles', 'doc_id', 'title', 'body');
5. Query Results & Expected Ground Truth
Tabular Comparison
Actual Candidate Output
(2 rows × 3 cols)
| # | doc_id | title | bm25_score |
|---|---|---|---|
| 1 | 1 | Fast Columnar Databases | 0.8563 |
| 2 | 2 | Postgres Tuning Guide | 0.2128 |
Expected Golden Output
(2 rows × 3 cols)
| # | doc_id | title | bm25_score |
|---|---|---|---|
| 1 | 1 | Fast Columnar Databases | 0.8563 |
| 2 | 2 | Postgres Tuning Guide | 0.2128 |