What I talk about when I talk about query optimizer (part 1): IR design

&gt; the Query Graph Model (QGM) representation is quite abstract and hardcodes many properties, making it exceptionally difficult to understand. Its claimed extensibility is also questionable.<p>I don&#x27;t know much about the context, but it was interesting to note that Materialize scrapped their QGM code last year: <a href="https:&#x2F;&#x2F;github.com&#x2F;MaterializeInc&#x2F;materialize&#x2F;pull&#x2F;17139">https:&#x2F;&#x2F;github.com&#x2F;MaterializeInc&#x2F;materialize&#x2F;pull&#x2F;17139</a><p>Also, a couple of interesting projects in the IR space:<p>- <a href="https:&#x2F;&#x2F;substrait.io&#x2F;" rel="nofollow">https:&#x2F;&#x2F;substrait.io&#x2F;</a> is a cross-language serialization for Relational Algebra<p>- <a href="https:&#x2F;&#x2F;www.lingo-db.com&#x2F;" rel="nofollow">https:&#x2F;&#x2F;www.lingo-db.com&#x2F;</a> is an MLIR-based (LLVM) query engine described extensively in this paper <a href="https:&#x2F;&#x2F;db.in.tum.de&#x2F;~jungmair&#x2F;papers&#x2F;p2485-jungmair.pdf?lang=en" rel="nofollow">https:&#x2F;&#x2F;db.in.tum.de&#x2F;~jungmair&#x2F;papers&#x2F;p2485-jungmair.pdf?lan...</a>

BTW you can see a version of what an industrial strength query optimizer &#x2F; execution engine looks like in Rust <a href="https:&#x2F;&#x2F;arrow.apache.org&#x2F;datafusion&#x2F;" rel="nofollow">https:&#x2F;&#x2F;arrow.apache.org&#x2F;datafusion&#x2F;</a><p>(can also use it in your own projects)<p>It is quite similar to what is described in this post

We implemented a query optimizer with a flexible intermediate representation in pure Go:<p><a href="https:&#x2F;&#x2F;github.com&#x2F;dolthub&#x2F;go-mysql-server">https:&#x2F;&#x2F;github.com&#x2F;dolthub&#x2F;go-mysql-server</a><p>Getting the IR correct so that it&#x27;s both easy to use and flexible enough to be useful is a really interesting design challenge. Our primary abstraction in the query plan is called a Node, and is way more general than the IR type described in the article from OP. This has probably hurt us: we only recently separated the responsibility to fetch rows into its own part of the runtime, out of the IR -- originally row fetching was coupled to the Node type directly.<p>This is also the query engine that Dolt uses:<p><a href="https:&#x2F;&#x2F;github.com&#x2F;dolthub&#x2F;dolt">https:&#x2F;&#x2F;github.com&#x2F;dolthub&#x2F;dolt</a><p>But it has a plug-in architecture, so you can use the engine on any data source that implements a handful of Go interface.

First installment is mostly introductory but gets more interesting near the end, and the rest of the series sounds promising. I&#x27;m looking forward to it.<p>I also recommend the sqlite.org docs &quot;overview of the optimizer&quot; and &quot;vbdb bytecode&quot; which you could see as an IR.

It&#x27;s a pity calcite.apache.org has never been known for extensive documentation.

Thanks for the writeup. As a compiler dev, I&#x27;m interested in databases as of late. Is there a good introductory text on the field?

As a Haruki Murakami fan, I appreciate this title

Hmm, no credit to Haruki Murakami, who wrote the memoir of the title you yanked “What I Talk About When I Talk About Running”?<p>Murakami credited Raymond Carver&#x27;s collection of short stories called “What We Talk About When We Talk About Love” for inspiring his title. Might be nice for you to include a small mention.