Severe performance degradation and massive allocations on indexed string queries (Potential Full Table Scan fallback) compared to LiteDB

[LeoYang06]

Hi, I am currently evaluating migrating a desktop application's data layer from LiteDB to BLite. The primary motivation is to fully support Native AOT and we have high expectations for BLite's performance, especially given the official benchmarks and its zero-reflection architecture.

However, during our local integration tests and subsequent BenchmarkDotNet profiling, we observed severe performance bottlenecks when querying data by indexed string fields.

The Issue:

Queries that filter by an indexed string field (using either .FindAsync() or .AsQueryable().Where()) are executing magnitudes slower than LiteDB and allocating an enormous amount of memory.

The discrepancy between our findings and the official BLite benchmarks makes me wonder if there is a specific API usage pattern I am missing, or if there is a defect in the Query Provider causing it to fail to use the B-Tree index and fallback to a full collection scan.

Benchmark Results:

We generated 5,000 dummy records representing photos, distributed across 20 folders. We then randomly sampled records and queried them in a loop.
Here are the BenchmarkDotNet results:

Technical Observations & Hypothesis:

Looking at the BLite: Query by FilePath row: querying a uniquely identifiable string field takes ~6.29 seconds and allocates 158.81 MB of memory for a database of only 5,000 records.

This strongly implies that:

The B-Tree Index is being completely ignored for string property queries.

The query provider might be failing to translate the closure variable (e.g., x.FilePath == localVariable) in the Expression Tree into an Index Seek operation.
As a result, it seems to fall back to scanning the entire collection and deserializing every single BSON document into memory repeatedly.

Code Snippet Context:

Here is how the Context and Indexes are configured:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<BenchmarkPhotoPo>()
        .ToCollection("Photos")
        .HasIndex(x => x.Id, unique: true)
        .HasIndex(x => x.SourceId)
        .HasIndex(x => x.FilePath, unique: true);
}

And here is the exact query executing in the Benchmark loop:

// Tried both AsQueryable and FindAsync, similar slow results
var result = await _bliteDb.Photos.FindAsync(x => x.FilePath == filePath).FirstOrDefaultAsync();

Reproduction Repo:

I have created a minimal, reproducible BenchmarkDotNet project here: PerformanceBenchmark

Could you please help shed some light on this? Is there a recommended/different way to query indexed string fields to ensure a proper Index Seek, or is this a known limitation/bug in the current query optimizer for string types?

Thank you for your fantastic work on this AOT-friendly database, looking forward to your insights!

[mrdevrobot]

Hi @LeoYang06, I confirm that the async path provided is materializing too early and it is not passing the necessary information to the QueryProvider.
We will write a core async within the BTreeQueryable and provide a clear API for DocumentCollection directly (FindAllAsync will remain as an immediate Where(...).ToListAsync(), applying a FirstOrDefaultAsync() there will still materialize alla data accordingly to the expression passed.

[mrdevrobot]

@LeoYang06 the benchmark has been very useful, thank you very much. The single query on secondary indexes has identified some allocation issues in the kernel. The 4.1.1 patch has been released, I hope you can confirm the performance improvements for those kind of queries.

Hereafter the benchmark inspired by yours against which we made the optimizations.

Method	TotalFolders	TotalPhotos	Mean	Error	StdDev	Ratio	RatioSD	Gen0	Gen1	Allocated	Alloc Ratio
'LiteDB: Query by FilePath (1-to-1)'	20	5000	1.9627 ms	0.2582 ms	0.2651 ms	baseline		359.3750	7.8125	4441.26 KB
'LiteDB: Query by SourceId (1-to-1)'	20	5000	0.0219 ms	0.0009 ms	0.0011 ms	89.797x faster	12.49x	3.6621	-	45.41 KB	97.810x less
'BLite: Query by FilePath via LINQ (1-to-1)'	20	5000	0.3140 ms	0.0094 ms	0.0105 ms	6.258x faster	0.85x	13.6719	-	179.69 KB	24.716x less
'BLite: Query by SourceId via LINQ (1-to-1)'	20	5000	0.0047 ms	0.0002 ms	0.0002 ms	416.991x faster	58.35x	0.2747	-	3.41 KB	1,303.858x less
'LiteDB: Query by SourceId (1-to-N)'	20	5000	16.7191 ms	2.7706 ms	3.1906 ms	baseline		1906.2500	1156.2500	23625.37 KB
'BLite: Query by SourceId via LINQ (1-to-N)'	20	5000	4.3019 ms	0.1848 ms	0.2129 ms	3.90x faster	0.75x	300.7813	207.0313	3707.5 KB	6.37x less

[LeoYang06]

Thank you so much for your excellent work and incredibly prompt response! The benchmark results are outstanding and perfectly demonstrate BLite's expected performance characteristics.

I’ve reviewed your commit e7583cb and it’s truly an exceptional piece of performance optimization. The depth of analysis and engineering rigor you put into this tuning really shines through in the numbers.

Great job, and thanks again for all your hard work on this!

[mrdevrobot]

It is good to have found those weak paths, if you found more performance area to optimize please share ;)

It was a patch with a small feature: you can now use FindOneAsync directly on the collection which is optmized for indexed queries

[LeoYang06]

Benchmark tests show that FindOneAsync has the best performance. While comparing benchmark results, I noticed an interesting performance difference between API usage patterns. I did a quick source code analysis and would appreciate your confirmation.

Finding

Through benchmarking, I observed a clear performance hierarchy across three single-record query patterns:

Pattern	Perf	Internal Execution Path
FindOneAsync(predicate)	Fastest ✅	Dedicated index Seek fast path
FindAsync(predicate).FirstOrDefaultAsync()	Faster	Direct delegation to FetchAsync, lazy eval
FirstOrDefaultAsync(predicate)	Slower	Expression tree pipeline + materialization

Source Code Analysis

FindOneAsync(predicate) — Dedicated index seek fast path (DocumentCollection.cs:1950)

• After IndexOptimizer identifies an equality query on an indexed field, it directly calls CollectionSecondaryIndex.Seek() → BTreeIndex.TryFindFirst(), achieving O(log n) complexity. No node traversal is needed — it pinpoints the target document’s storage location in a single operation.

• Only a single FindByLocationAsync() call is made, with no page cache (Dictionary<uint, byte[]>) allocation. Unlike QueryIndexAsync’s range scan, this is a precise single-point lookup.

• Completely bypasses the FetchAsync state machine, QueryIndexAsync range scan, and the entire BTreeQueryProvider pipeline — a true zero-allocation fast path.

FindAsync(predicate).FirstOrDefaultAsync() — Direct delegation to FetchAsync (DocumentCollection.cs:1941)

• FindAsync delegates directly to FetchAsync(predicate, int.MaxValue), returning IAsyncEnumerable. This is a straightforward method forward with no expression tree processing involved.

• .FirstOrDefaultAsync() is a standard LINQ Async extension that only consumes the first element via lazy evaluation. Although fetchLimit = int.MaxValue, the lazy nature of IAsyncEnumerable ensures only the first matching record is actually processed.

• Avoids expression tree construction, BTreeExpressionVisitor parsing, and QueryModel building overhead. However, it still allocates a page cache dictionary and goes through QueryIndexAsync range scan infrastructure.

FirstOrDefaultAsync(predicate) — Via BTreeQueryable expression tree pipeline (BTreeQueryable.cs:40)

• Internally constructs Queryable.Where + Queryable.Take(1) expression tree, which goes through BTreeExpressionVisitor parsing and QueryModel construction — this step alone incurs additional heap allocations.

• Executes through the BTreeQueryProvider.ExecuteAsync pipeline, including multiple fast-path checks (Count/Sum/Select push-down), before eventually calling FetchAsync(predicate, fetchLimit=1).

• Results are materialized into a List<T> before being returned as IEnumerable<T>, introducing extra collection allocation and return value unboxing overhead.

Summary

For single equality lookup scenarios, FindOneAsync is the recommended API — it has a dedicated O(log n) index seek fast path with zero extra allocations. While FindAsync().FirstOrDefaultAsync() has less overhead than the FirstOrDefaultAsync() expression tree pipeline, it still goes through FetchAsync + QueryIndexAsync + page cache allocation.

Is it expected that FindAsync().FirstOrDefaultAsync() would outperform FirstOrDefaultAsync()? This analysis is based on my reading of the source code. @mrdevrobot, could you please confirm if this is accurate?

---

After completing the analysis above, I noticed a new section in the README titled “Choosing the right query API — performance matters” which is exactly what I was looking for. Great job!