search

dotnet / efcore

EF Core is a modern object-database mapper for .NET. It supports LINQ queries, change tracking, updates, and schema migrations.
https://docs.microsoft.com/ef/
MIT License
13.82k stars 3.2k forks source link

Query: take indexes into account in optimizations #34159

Open ranma42 opened 5 months ago

ranma42 commented 5 months ago

Most query optimizations do not take into account whether a column (or expression) is indexed. In most cases, the optimization can make reasonable choices by simply taking into account the type of the expressions (example: constant vs parameter vs column, ...). In some cases (especially in the case of symmetries) information coming from the model could help choose the most appropriate expression.

It is not clear how often this is relevant; most cases seem to be handled appropriately by the simpler (type-driven) heuristics. Real-world use cases would be especially interesting to understand the tradeoff.

ranma42 commented 5 months ago

An example (from https://github.com/dotnet/efcore/pull/34048#discussion_r1661103828) is:

are all equivalent assuming both x and y are boolean, but they are treated very differently by (at least) the Sqlite planner.

ranma42 commented 5 months ago

In addition to simple columns, it might make sense to take into account expressions, especially in relationship to

roji commented 4 months ago

Noting that knowing model information isn't trivial - ColumnExpression doesn't reference the IProperty it represents, and reverse-engineering that isn't trivial. At some point we may want to consider enriching our SQL tree model with more information, e.g. adding an option Property to ColumnExpression.

ranma42 commented 4 months ago

@roji enriching the expression tree with additional metadata would also enable hints on other optimization opportunities, for example:

roji commented 4 months ago

Yep. Noting also that for NativeAOT, we currently have trouble generating code representation for our SQL tree, since it references "detached" type mappings, and we can't easily recreate those type mappings in generated code. If we had IProperty instead, we could generate code that looks up those properties in the model. Although fully replacing type mappings by IProperty is a pretty drastic change that needs to be considered more carefully.