Query: take indexes into account in optimizations

[ranma42]

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]

An example (from #34048 (comment)) is:

• NOT(x == y)
• x != y
• x == NOT(y)
• NOT(x) == y

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

[ranma42]

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

• Support expression (AKA functional) indexes #28360

[roji]

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]

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

• join order
• parameter processing (specialize to simplify expressions vs share complex expressions for all cases)

[roji]

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.