Comment by grebc
9 hours ago
You’ve got do a better job selling the title sorry.
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.
The remaining 1% is usually uncomfortable if not down right painful.
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.
Linq, in dot net, is a real life working example of what you’re talking about and it’s agnostic of how the storage is structured.
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.
I feel the opposite way. I very rarely trust the database system to do the right thing. Any query more complex than a basic lookup by primary key requires me to look at query plans and validate that indexes are in place and are being used. Otherwise we risk the production server grinding to a halt.
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.)
You should investigate how you’re storing & retrieving data. A database system is going to abstract away various things.
If you need direct control of primitives then there’s always fopen as SQLite says.
Most databases that aren't postgres have some sort of query hinting.