3 ms·

Asking the SQL engine to explain the query doesn't mean the abstraction is broken or imperfect. It simply means the developer wants to understand how their quer

by kmoser 2y ago

Asking the SQL engine to explain the query doesn't mean the abstraction is broken or imperfect. It simply means the developer wants to understand how their query is being understood and, hopefully, optimized. This is essential when debugging why queries don't behave optimally, e.g. failure to use an index. Even with the perfect abstraction, people will need to dig beneath the surface. This is because every layer of code is subject to side-effects due to its actual implementation. That's why we have, e.g., multiple sorting algorithms, and documentation for their individual behavior, speed, etc.: so developers can understand the limits and idiosyncrasies of each and pick the correct one for the job. We may make incremental steps towards more bug-free code, but because everything is limited by the details of its actual implementation (whether real or an abstraction), there is no magic bullet that will make a huge leap forward in the realm of software development as a whole.

marcosdumay 2y ago
> It simply means the developer wants to understand how their query is being understood and, hopefully, optimized. Or, in other words, the developer wants to break the abstraction. If your definition of "perfect abstraction" doesn't include the developer never needing to look beneath the surface, I'd say it's a pretty bad one.
- RaftPeople 2y ago
  This one is interesting. The abstraction for SQL is related to how the work gets done, not what it produces. The missing piece is for the user to be able to control that optimization of how the work gets done, which is getting right down into the details of what SQL was abstracting in the first place. There could be a middle ground where the user provides input about target performance and the engine uses that input during choice of execution plans. Maybe an OPTIMIZATION clause for difficult or long running queries: ALLOW_EXTENDED_PLAN_SEARCH=True or MINIMIZE_EXECUTION_TIME_FACTOR=10 (1=just do simple, 10=expend more time+space up front to reduce overall time)
  - vbezhenar 2y ago
    User does not want to control optimizations. User wants their queries to run fast enough. Ideally database should monitor the queries and create/remove necessary indices as needed. And some databases do that.
    - RaftPeople 2y ago
      > User does not want to control optimizations. It's true, it would nice if the user didn't need to intervene, but unfortunately query optimization is a combinatorial problem where the engine has incomplete information causing performance problems (or cost problems if you are on Snowflake), so the user is required to intervene some percentage of the time.

by kmoser 2y ago

marcosdumay 2y ago

> It simply means the developer wants to understand how their query is being understood and, hopefully, optimized. Or, in other words, the developer wants to break the abstraction. If your definition of "perfect abstraction" doesn't include the developer never needing to look beneath the surface, I'd say it's a pretty bad one.

RaftPeople 2y ago
This one is interesting. The abstraction for SQL is related to how the work gets done, not what it produces. The missing piece is for the user to be able to control that optimization of how the work gets done, which is getting right down into the details of what SQL was abstracting in the first place. There could be a middle ground where the user provides input about target performance and the engine uses that input during choice of execution plans. Maybe an OPTIMIZATION clause for difficult or long running queries: ALLOW_EXTENDED_PLAN_SEARCH=True or MINIMIZE_EXECUTION_TIME_FACTOR=10 (1=just do simple, 10=expend more time+space up front to reduce overall time)
- vbezhenar 2y ago
  User does not want to control optimizations. User wants their queries to run fast enough. Ideally database should monitor the queries and create/remove necessary indices as needed. And some databases do that.
  - RaftPeople 2y ago
    > User does not want to control optimizations. It's true, it would nice if the user didn't need to intervene, but unfortunately query optimization is a combinatorial problem where the engine has incomplete information causing performance problems (or cost problems if you are on Snowflake), so the user is required to intervene some percentage of the time.