3 ms·

A key difference between many compilers and DB query planners, is that a compiler can spend more time over its optimisations because it is run dev-side and the

A key difference between many compilers and DB query planners, is that a compiler can spend more time over its optimisations because it is run dev-side and the benefits (and none of the time taken) are felt by the users. A query planner needs to be more conservative with its resource use. This is not true of JIT compilers, of course, which have similar constraints to DB query planners. In these cases the goal is to do a good job pretty quickly, rather than an excellent job in a reasonable time.

jiggawatts 2y ago
> A query planner needs to be more conservative with its resource use. The number of possible distinct query plans grows very rapidly as the complexity increases (exponentially or factorially... I can't remember). So even if you have 10x as much time available for optimisation, it makes a surprisingly small difference. One approach I've seen with systems like Microsoft Exchange and its undrelying Jet database is that queries are expressed in a lower-level syntax tree DOM structure. The specific query plan is "baked in" by developers right from the beginning, which provides stable and consistent performance in production. It's also lower latency because the time spent by the optimiser at runtime is zero.
pjmlp 2y ago
However most big boys databases also package a JIT compiler into the mix, making it more fun.
- dspillett 2y ago
  As the optimal plan will change as data sizes and patterns do, it is almost all JIT with fairly aggressive caching. They keep some stats on each object and plan to help guess when a cached plan should be used or the full planner be engaged again, though this process often needs a little help.
joncrocks 2y ago
Query planners can (and will) use statistics gathered on the contents of the tables to more effectively execute queries, which can be gathered in the background/batch processes. For large database vendors there is a good amount of complexity put into up-front information gathering so that query execution is fast.
mamcx 2y ago
An even bigger issue is that regular optimizer see a whole static world (the code). You can know very well the size of most things. For DBs, it would be 'trivial' if we could know the exact (or very very close) size of the tables and indexes. But the db is a mutating environment, that start with 1 row and 1 second later is 1 million, and 1 second later is 1 row again. And then you mutate it from thousands of different connections. Also, the users will mutate the shape, structure, runtime parameters, indexes, kind of indexes, data types, etc.

jiggawatts 2y ago

> A query planner needs to be more conservative with its resource use. The number of possible distinct query plans grows very rapidly as the complexity increases (exponentially or factorially... I can't remember). So even if you have 10x as much time available for optimisation, it makes a surprisingly small difference. One approach I've seen with systems like Microsoft Exchange and its undrelying Jet database is that queries are expressed in a lower-level syntax tree DOM structure. The specific query plan is "baked in" by developers right from the beginning, which provides stable and consistent performance in production. It's also lower latency because the time spent by the optimiser at runtime is zero.

pjmlp 2y ago

However most big boys databases also package a JIT compiler into the mix, making it more fun.

dspillett 2y ago
As the optimal plan will change as data sizes and patterns do, it is almost all JIT with fairly aggressive caching. They keep some stats on each object and plan to help guess when a cached plan should be used or the full planner be engaged again, though this process often needs a little help.

joncrocks 2y ago

Query planners can (and will) use statistics gathered on the contents of the tables to more effectively execute queries, which can be gathered in the background/batch processes. For large database vendors there is a good amount of complexity put into up-front information gathering so that query execution is fast.

mamcx 2y ago

An even bigger issue is that regular optimizer see a whole static world (the code). You can know very well the size of most things. For DBs, it would be 'trivial' if we could know the exact (or very very close) size of the tables and indexes. But the db is a mutating environment, that start with 1 row and 1 second later is 1 million, and 1 second later is 1 row again. And then you mutate it from thousands of different connections. Also, the users will mutate the shape, structure, runtime parameters, indexes, kind of indexes, data types, etc.