redb 3.7.1: props search up to 100x faster. An alternative to EF Core, or a companion to it

Query cost did not depend on what you searched for: the filter sat above GROUP BY. Now the cut happens before the aggregate, on three engines, with no application code changed. Up to 100x on a date range and 5.3x on a full result set in SQL Server.

There is a class of performance problem you cannot see on small data and cannot miss on large. Ours looked like this: a query hunting for one rare order number among a hundred thousand objects took exactly as long as a search that found nothing at all. Selectivity did not move the clock. At all.

The cause was the shape of the generated SQL. Props values live one per row, so the query first folds them into a wide row through GROUP BY and only then applies the filter. The condition sat above the aggregate, filtering the result of a fold rather than a column. No index can help there: by the time the condition runs, the engine has already read and folded every value of every object in the scheme. The trigram index on strings sat unused.

3.7.1 adds a step that narrows the object set before the aggregate runs.

How it works

The planner walks the filter tree and tries to express it as a condition on a single value row. A row belongs to exactly one structure, so a disjunction falls out naturally: "this is a Position row and it contains the needle, or this is a Department row and it contains the needle". That condition is spliced straight into the value scan, and the fold now receives an already narrowed set.

The key property: the prefilter is built as a superset. It may let extra objects through, it may never lose one. The authoritative filter stays exactly where it was, above GROUP BY. Anything the planner cannot analyse yields no prefilter and today's behaviour unchanged, so the worst outcome is the absence of a speedup rather than a change of results.

What already worked before

Without this caveat the numbers below read wrong, so it goes here rather than in a footnote.

Cutting before the aggregate has always existed in redb. Just not for props, but for the object's own fields: id, parent, creation and modification dates, name. Such a condition was spliced straight into the value scan:

AND v._id_object IN (SELECT o_src._id FROM _objects o_src
                     WHERE o_src._id_scheme = $1 AND <your condition>)

In code that is WhereRedb, and it is what production queries have been leaning on all along. In our own production and in redb.Identity such filters are almost everywhere: pick within a subtree, within a date range, within a set of ids, by owner. As long as objects were cut by _objects, the fold received an already narrowed set and ran fast.

The hole was exactly where there is nothing to cut by on _objects. Then the only selective condition left was props, and props sat above the aggregate and cut nothing: one rare order number cost the same as searching for nothing. 3.7.1 closes that case, and the two cuts now compose: first by objects, then by value rows.

What was measured

Two shapes, both plain LINQ, no special calls. And both without WhereRedb, which is the worst case: nothing to narrow by on _objects, the whole weight falls on props.

The first is one search box across several fields. This is what a UI search looks like when the user has typed a word and you have to look in both the job title and the department.

var found = await redb.Query<Employee>()
    .Where(e => e.Position.Contains("Design") || e.Department.Contains("Design"))
    .OrderByRedb(o => o.Id)
    .Take(100)
    .ToListAsync();

One needle, Design, two fields, an OR between them. In the table below this is the needle row. Before 3.7.1 that query read and folded the values of every employee in the scheme and only then checked for "Design". Now the rows that obviously cannot match are cut on the way in.

The second is a date range matching 3 288 objects out of 100 000, or 3.3%.

var hired = await redb.Query<Employee>()
    .Where(e => e.HireDate >= new DateTime(2030, 1, 1)
             && e.HireDate <  new DateTime(2031, 1, 1))
    .Take(100)
    .ToListAsync();

The numbers

All three engines seeded identically: 100 000 objects, roughly 8.4M value rows, statistics refreshed. Server-side time, best of three runs.

Read this table as the upper bound of the gain rather than as an expectation for any query. If you have a WhereRedb, you were already fast and the absolute difference will be smaller. The gain grows the less there was to narrow by on _objects.

One caveat about what is being measured. This is query time, without materialising objects in memory. A real call adds materialisation on top, and materialisation is identical in both modes. So the search itself got faster by exactly the ratios above, while the share of that gain in the end-to-end call depends on how many objects you pull.

Which is where a second, independent lever comes in: projections. Select fetches only the fields you actually need instead of the whole object. One mechanism cuts the engine's work, the other cuts the materialiser's, and they compose.

Three engines, three different mechanisms

The same algebra, and every engine wins for its own reason. Worth knowing before predicting numbers on your own data.

PostgreSQL engages the trigram GIN and reads far fewer rows: the string index returns 40 958 rows where the structure index returns 200 000. SQLite engages a covering partial index and stops going back to the table. SQL Server reads the same pages but saves CPU instead, because the string column is NVARCHAR(MAX) and the comparison carries an explicit collation.

The spread shows most clearly on the date range: a hundredfold gain on PostgreSQL, sixteenfold on SQLite, and nothing whatsoever on SQL Server.

That last one is not a misfire, it is a sign that there was nothing left to cut. The condition matches 3.3% of the scheme, the query asks for a hundred rows, and the work is already bounded by the limit rather than by the size of the scheme: at that density you find a hundred matches after visiting roughly three thousand objects. Two milliseconds before, two after. Remove the limit, force a walk over the whole scheme, and the gain shows up there too: 88 ms against 65. That is a useful rule of thumb for your own data. The prefilter pays off where the engine would otherwise fold the entire scheme, and gives nothing where a limit already keeps the work small.

What this means in practice

We got very close to a flat table with an index. On simple conditions, an equality or a range on one field, the query now travels an index the way it would travel an ordinary column of an ordinary table.

And it keeps what a flat schema cannot have by construction. No row multiplication from joins: values fold per object in a single pass, and related collections do not turn a result set into a cartesian product you then have to collapse. No Include either: the graph loads by depth rather than by naming every branch by hand. On deep graphs that lands somewhere a classic ORM built on joins and Include does not reach.

Hence the two-sided positioning in the title. Nobody is asking you to replace EF Core wholesale: the two live side by side in one application, on one database. The relational part, where the schema is stable and the table is flat, stays with EF, which is the right home for it. redb takes what a flat model finds hard: shifting sets of fields, heterogeneous entities inside one scheme, trees and graphs that would otherwise become a chain of Include and a pile of duplicated rows. The choice is about the shape of your data, not about ideology.

There is no head-to-head benchmark against EF in this article, and I am not going to invent one. What is described here are properties of the execution model, not a measurement we did not take.

That measurement is coming separately. A deep dive is in preparation with code for both sides, execution plans and numbers: a simple condition against a flat indexed table, a graph several levels deep against Include, collections where a join multiplies rows, and what projections do about all of it. This post is short and about one change; that one will be long and about where this model wins and where it loses.

The boundaries, honestly

The prefilter is opt-in through EnablePvtPrefilter and off by default.

Today it covers a disjunction over selective fields and a range or equality on a single field. A top-level AND across different fields does not qualify: a value row belongs to one structure, and a conjunction of different fields is not expressible at row level. Filters over arrays, dictionaries, null checks, cross-field comparisons and computed expressions are recognised as unanalysable and yield no prefilter.

There is also a case where the prefilter had to step aside. On SQLite a query with a limit and no ordering at all streams without the prefilter and stops at the hundredth group; with it the planner switches to a multi-index OR, loses the ordering, needs a temporary B-tree and materialises everything. Measured: 8-12 ms against 388-521 ms, with no overlap between the ranges. So on SQLite, in that single shape, no prefilter is emitted. PostgreSQL knows no such trouble, and SQL Server cannot even produce the offending shape, because its paging is required to carry an ORDER BY.

A word on how this was verified. The invariant that results must match row for row is held by a suite of differential tests: every query runs twice, with the flag and without, over the same data, and the id sets are compared. That suite caught a defect that would otherwise have shipped: the row form cuts rows rather than objects, so an object qualifying through one branch of a disjunction lost the column values belonging to the other branches. The object set stayed intact, which is why fifteen hundred existing tests noticed nothing, while DistinctBy and OrderBy over such a field quietly lied. Fixed before publication.

What you get

Search finally costs what you are searching for. A rare condition is now cheap instead of costing the same as a query that finds nothing.

Three engines, one flag, zero changes in application code. The same LINQ, the same model, nothing to rewrite: turn it on and go.

• up to 100x faster on a date range in PostgreSQL, 154 ms became one and a half;
• 3.7x on string search in SQLite and twice as fast in PostgreSQL;
• 5.3x on a full result set in SQL Server, where seven seconds became 1.3.

And not a single row of any result changed. The prefilter is a superset by construction, and the invariant is pinned by differential tests that run every query both ways and compare id sets exactly. That is not a figure of speech but a working tool, and it is what caught the defect that would otherwise have shipped.

Compose that with projections and you get two independent levers: one cuts the engine's work, the other the materialiser's. On simple conditions the query travels an index the way a flat table would, and on deep graphs you keep what a flat schema does not have: no rows multiplied by joins, no Include written by hand.

Where it lives

The prefilter lives inside redb and works on all three Pro providers: PostgreSQL, SQL Server, SQLite. One flag, EnablePvtPrefilter in RedbServiceConfiguration, turns it on. It is a step of query compilation rather than a separate mode: the same LINQ, the same result set, the same result, the same observability. The difference is that the aggregate now receives not the whole scheme, but only what could possibly match.

If this was useful — a ⭐ on GitHub helps others find it.

More of my writing: redbase.app/articles, and on dev.to.