The missing tier for query compilers
4 comments
·February 10, 2025jamii
The tradeoff in SingleStore is interesting. By default and unlike eg postgres, parameterized queries are planned ignoring the values of the parameters. This allows caching the compiled query but prevents adapting the query plan - for the example in the post SingleStore would pick one plan for both queries.
But you can opt out of this behaviour by wrapping each parameter in NOPARAM (https://docs.singlestore.com/cloud/reference/sql-reference/c...).
jsmith45
If it ignores the parameter values, then how does it estimate cardinality? Does it optimize for the worst case scenario (which runs the risk of choosing join implementations that requiring far more data from other tables than makes sense if the parameters are such that only a few rows are returned from the base table), or does it assume a row volume for a more average parameter, which risks long running time or timeouts if the parameter happens to be an outlier?
Consider that Microsoft SQL Server for example, does cache query plans ignoring parameters for the purposes of caching (and will auto-parameterize queries by default), but SQL Server uses the parameter value to estimate cardinalities (and thus join types, etc) when first creating the plan, under the assumption that the first parameters it sees for this query are reasonably representative of future parameters.
That approach can work, but if the first query it sees has outlier parameter values, it may cache a plan that is terrible for more common values, and users may have preferred the plan for the more typical values. Of course, it can be the reverse, where users want the plan that handles the outliers well, because that specific plan is still good enough with the more common values.
convolvatron
Clustrix wrote a compiler. It was able to call out to C for string intrinsics and communication and btree operations, so it primarily handled extracting data from serialized formats, simple arithmetic, constructing serializations from downstream.
this was to replace a little byte code VM, and as far I recall took a very good programmer about 2 weeks for the basic version.
I dont see any reason to even try to bring something like a general purpose JIT. there's a huge benefit from just doing the basics, and you can move the line between precompiled functions and dynamically compiled ones on an ongoing basis. its also cheap enough that I wouldn't get hung up on needing to amortize that cost with prepared statements.
The author pans meta-tracing and shows a graph of TruffleRuby having really crazy runtime behavior. However, it looks extremely suspicious - like the kind of thing you'd see if there was a memory leak and the garbage collector was running constantly.
Most people seem really excited about the results coming out of Graal and TruffleRuby seems to have some really impressive results in general, so the graph is surprising. It's also missing a bunch of details, so it's hard to speak to its voracity - for example, what are the different versions of the runtimes, what flags were used, on what hardware?
As a counter example, there's a different (admittedly old) result where TruffleRuby beats cruby on the same benchmark by 4.38x. https://eregon.me/blog/2022/01/06/benchmarking-cruby-mjit-yj...