SQL databases have statistics which inform the planner
I can’t perform the optimisation myself since SQL does not allow for it
Would such an optimisation be useful for a general purpose language? Seems doubtful, it would basically be an edge case which can be resolved by hand. Not only that, but it could cause undesirable side-effects e.g. a hash joinrequires the allocation of a hash map, is that something I want?
TFA is also missing requirements e.g. performing a hash join requires that data be hashable in the first place, that is more or less intrinsic to most SQL databases / data, but very much not so to for general purpose programming languages.
Would such an optimisation be useful for a general purpose language?
C# LINQ is designed to allow it, though the optimization is done by the LINQ provider not the compiler. And LINQ by and large boils down to a declarative sublanguage somewhere between list comprehensions and SQL.
I imagine that this kind of optimization would be more generally useful in an array language or languages that make heavy use of dataframes.
this sort of query is very common workload
I can’t perform the optimisation myself since SQL does not allow for it
You’re talking about what makes query planning desirable or economical. But none of this has any bearing on what makes the optimizations possible .
SQL databases have statistics which inform the planner
Materialize does not have access to statistics but can still perform the optimization in the example. Whereas the same optimization in python would be intractable even with access to statistics.
TFA is also missing requirements e.g. performing a hash join requires that data be hashable in the first place,
I think that’s covered by:
Some way to reason about the properties of functions (eg that a == b implies hash(a) == hash(b)…
Eg this is very difficult to reason about at compile-time in python, but in rust the compiler could just check for implHashforT.
But none of this has any bearing on what makes the optimizations possible .
But it does, it is the primary reason.
Or the secondary reason if the first is ROI: not only can you implement a hash join yourself in the average langage, such a join would only be useful in specific data shapes following the relational model, in most cases your data would be nested or the link would already be expressed through a hash map.
I think that’s covered by:
It’s not, what you quote is an operational implication, it does not account for hash(a) not being a thing at all.
Eg this is very difficult to reason about at compile-time in python, but in rust the compiler could just check for impl Hash for T.
Not according to TFA, since you can have a nonsensical Hash operation all the same.
But none of this has any bearing on what makes the optimizations possible .
But it does, it is the primary reason.
What if we distinguish “what lets us know the transformation preserves the semantics of the query” (aka makes the transformation possible) from “what lets us know that the transformation is profitable” (aka makes us expect the tranformation is an optimization, not a pessimization). There are plenty of transformations for the first that don’t depend on statistics at all.
I take it this is what Jamie meant by
You’re talking about what makes query planning desirable or economical. But none of this has any bearing on what makes the optimizations possible .
It’s not just that SQL is declarative, it’s that:
Would such an optimisation be useful for a general purpose language? Seems doubtful, it would basically be an edge case which can be resolved by hand. Not only that, but it could cause undesirable side-effects e.g. a hash joinrequires the allocation of a hash map, is that something I want?
TFA is also missing requirements e.g. performing a hash join requires that data be hashable in the first place, that is more or less intrinsic to most SQL databases / data, but very much not so to for general purpose programming languages.
score 3 · canonical
C# LINQ is designed to allow it, though the optimization is done by the LINQ provider not the compiler. And LINQ by and large boils down to a declarative sublanguage somewhere between list comprehensions and SQL.
I imagine that this kind of optimization would be more generally useful in an array language or languages that make heavy use of dataframes.
score 4 · canonical
It’s not even because SQL is declarative :p
You’re talking about what makes query planning desirable or economical. But none of this has any bearing on what makes the optimizations possible .
Materialize does not have access to statistics but can still perform the optimization in the example. Whereas the same optimization in python would be intractable even with access to statistics.
I think that’s covered by:
Eg this is very difficult to reason about at compile-time in python, but in rust the compiler could just check for
impl Hash for T.score 5 · canonical
But it does, it is the primary reason.
Or the secondary reason if the first is ROI: not only can you implement a hash join yourself in the average langage, such a join would only be useful in specific data shapes following the relational model, in most cases your data would be nested or the link would already be expressed through a hash map.
It’s not, what you quote is an operational implication, it does not account for hash(a) not being a thing at all.
Not according to TFA, since you can have a nonsensical Hash operation all the same.
score 1 · canonical
What if we distinguish “what lets us know the transformation preserves the semantics of the query” (aka makes the transformation possible) from “what lets us know that the transformation is profitable” (aka makes us expect the tranformation is an optimization, not a pessimization). There are plenty of transformations for the first that don’t depend on statistics at all.
I take it this is what Jamie meant by
but perhaps the extra elaboration is helpful.
score 2 · canonical