Hacker News Reader

Comment by qorrect | Hacker News Reader

Is this different from 'compiling sql'?

A query planner will consider the idiosyncratic properties of your data to determine the most efficient way to execute your query, whereas a compiler is generally blind to the data your program will be processing.

So if you execute the query "SELECT * FROM (a, b) WHERE a.foo = b.bar", if you have many rows in `a` but few rows in `b`, then it's much for efficient to scan `b` than `a`. The query planner will keep track of properties like this & come up with tricks to speed up execution.

But in the sense that "everything is a compiler", yeah you could totally think of a query planner as a compiler that takes in your query's AST and a statistical description of your data and lowers it to a query plan.

This seems pretty similar to profile guided optimization that say Java or .Net or Javascript can do since since it has access to runtime data during JIT Compilation.

I hadn't made that connection but it's an excellent one!

I think I'd say yea because AFAIK/IIRC query planners tend to use additional environment informations to make better plan. Maybe it would be closer to JIT?

Query planners often choose the algorithm based on data statistics (as described in the article).

Compilers generally just make a lot of constant-factor improvements without changing the algorithm. One exception might be the tail-call optimization, which changes the space complexity of an algorithm. And that's one of the optimizations where the developer needs to know for sure whether it will br applied or not.

I've always thought of it as closer to an optimizing JIT compiler than just a regular translation of instructions to instructions.

Not quite. A query plan is usually represented as a tree of steps to output the desired result. Each node would be a high level operation (e.g. a sort) or source of data (e.g. read rows from table), possibly pulling from other nodes beneath it.

The actual compilation of the plan to machine code is possible and a few database systems do exactly that. But most process then nodes themselves or JIT specific node types represent simpler or more tightly defined operations.

I think they meant it in a more abstract sense; also see relevant sister threads.

Don't most planners also take table and/or index statistics into account? AFAIK for most of the commonly used DBMSes, the same query will result in radically different query plans if the table contents are different enough.

Reply on news.ycombinator.com