I feel like the separation between a query & the query execution plan is one of the benefits of SQL. I trust the database system to do the right thing 99% of the time, and I don’t want to think about that either really.
But yes, I agree a query optimizer is valuable. Luckily there’s nothing stopping us from implementing one, as Prela is algebraic and all optimization techniques for SQL carry over.
That said I still rarely use the but the basics of Linq. I just don’t see the upgrade of what you’re solving here.
Personally I'd love a more explicit form of SQL that allowed specifying things like "select via scan" or "select via index lookup". (I don't think this HN submission is that - I'm just saying generally.)
If you need direct control of primitives then there’s always fopen as SQLite says.