Optimizers need a rethink

Posted by ingve 10/23/2024

Optimizers need a rethink(typesanitizer.com)

149 points | 167 commentspage 3

Mikhail_Edoshin 10/28/2024|

One of reasons SQL is declarative is that queries run concurrently yet give the illusion of working in isolation. And unlike the concurrency of independent processes, which is mostly mechanical, this one involves detailed coordination because database queries work on the same data. With these constraints it may be not that easy to come up with an imperative model that is as simple as the current declarative one.

yxhuvud 10/28/2024||

No. Language designers need to think about what their language promises and when some users have differing needs, they may need to fulfill those needs.

einpoklum 10/27/2024||

From the article:

> Have a good mental model of what the optimizer can and cannot do.

Most DB query planner designers and implementers have little imagination, and their mental model of what optimizers can and cannot do is, well, extremely narrow-minded. There is huge unexplored space of what query planning can be (at least for analytic queries, and we think in columnar terms) - if we just stop insisting on thinking of DBMS operations as black boxes.

gpm 10/27/2024||

Arguing against query planning by pointing at a quote about databases is wild. Automatic query planning is ubiquitous and hugely succesfull in databases.

bsder 10/27/2024||

And sometimes highly problematic.

I'm surprised that the "query planner" doesn't have a way to eject an opaque object that is the "assembly language of the query" that you can run that it is not allowed to change.

gpm 10/28/2024|||

Sure. It's definitely a tradeoff which definitely hurts on rare occasion. I agree that the lack of fallback in most databases is a bit strange. Altogether though the productivity benefits have proven larger than the drawbacks of not defaulting to a query planner.

twoodfin 10/28/2024|||

Several commercial databases offer this capability, under a term like “frozen plans”.

typesanitizer 10/28/2024||

I've added a clarification in the post to make my position explicit:

> This is not to imply that we should get rid of SQL or get rid of query planning entirely. Rather, more explicit planning would be an additional tool in database user’s toolbelt.

I'm not sure if there was some specific part of the blog post that made you think I'm against automatic query planning altogether; if there was, please share that so that I can tweak the wording to remove that implication.

gpm 10/28/2024||

> I'm not sure if there was some specific part of the blog post that made you think I'm against automatic query planning altogether; if there was, please share that so that I can tweak the wording to remove that implication.

The quote from another article (which I didn't read) starting with "I dislike query planners".

"Against ... altogether" is mildly stronger than I took away from this, more like "generally of the opinion that the tradeoff nearly everyone is making with sql isn't worth it".

Judging by the lack of upvotes other people didn't react as strongly to this quote as I did, so take it as you will.

sebmellen 10/27/2024||

I like that the author included their intended audience up front. Definitely not me, but it helped me read the article with a different perspective.

QuadmasterXLII 10/27/2024||

Frankly, the problem is that (generally, across languages various compiler hints) @inline sometimes fails to inline. At this point I’ve given up on ever having an @inline that reliably inlines, and I would very happily settle for an @assert_inline that doesn’t change the generated assembly at all but reliably crashes out if the function isn’t inline.

Julia is by far the worst language about this. It would be vastly more usable with the addition of @assert_type_stable, @assert_doesn’t_allocate, and @assert_doesn’t_error macros.

dzaima 10/27/2024||

With gcc & clang you can use __attribute__((always_inline)) to force-inline, even at -O0, giving an error if it's impossible.

andyferris 10/27/2024|||

On the Julia front, I believe the evolving effect system may help a little bit with obtaining/surfacing that information. JET.jl should be able to solve the type stability (inference) one from a whole-of-program perspective. We have `@inferred` for unit tests. The macros would be a cool addition - I wonder if they would get overused though?

I agree that Julia takes the idea of optimization to the extreme - it's semantically a very dynamic language and only fast due to non-semantically guaranteed optimization. On the other hand, getting access to the generated IR, LLVM and assembly and iteratively improving it is far easier than any other language I've seen.

pjmlp 10/28/2024||

What Julia offers is quite common in Common Lisp systems.

affinepplan 10/28/2024||

just in case you're interested, Julia does (now) have tools similar to what you're describing, although of course there are still be lots of possible improvements to be made w.r.t. their UX to make these tools more reliable and easier to use

in particular @assert_type_stable and @assert_doesn't_error is largely provided by JET.jl and the effects system, and @assert_doesn't_allocate is (at least partially) provided in AllocCheck.jl

QuadmasterXLII 10/28/2024||

This is very interesting, thank you

nostromo177 10/23/2024|

[dead]