We have everything optimized, and yet somehow DB queries need to be "interpreted" at runtime. There's no reason for DB queries to not be precompiled.
See prepared statements.
Better JIT for Postgres
41–50 of 116 posts
Re: Better JIT for Postgres
#42Earlier quoted context omitted.
Nothing to do with plan caching, thats just talking about plan execution of parallel operations which is that thread or process based in PG? If process based then they can send small parts of plan across processes.
Ah, didn't see the caching part. Plans for prepared statements are cached though.
Only on a per-connection basis
Re: Better JIT for Postgres
#43Earlier quoted context omitted.
The big boys all cache query plans so the amount it time it take to compile is not really a concern.
That's not generally correct. Compile-time is a concern for several databases.
Most systems submit many of the same queries over and over again.
Ad-hoc one off queries usually can accept higher initial up-front compile cost because the main results usually take much longer anyway, vs worrying about an extra 100ms of compile.
Maybe it was too strong to say its not a concern at all, but nothing like PG where every single request needs to replan and potentially jit unless the client manually prepares and keeps the connection open.
Re: Better JIT for Postgres
#44We have everything optimized, and yet somehow DB queries need to be "interpreted" at runtime. There's no reason for DB queries to not be precompiled.
The "byte-code" coming from the query planner typically only has a handful of steps in a linear sequence. Joins, filters, and such. But the individual steps can be very costly.
So there is not much to gain from JITing the query plan execution only.
JITing begins to make more sense, when the individual query plan steps (join, filter, ...) themselves be specialized/recompiled/improved/merged by knowing the context of the query plan.
Re: Better JIT for Postgres
#45Earlier quoted context omitted.
That is quite strange, given that big boys RDMS (Oracle, SQL Server, DB2, Informix,...) all have JIT capabilities for several decades now.
The big boys all cache query plans so the amount it time it take to compile is not really a concern.
Postgres caches query plans too, the problem is you can only cache what you can share, and if your planner works well, you can share very little, there can be a lot of unique plans even for the same query
Re: Better JIT for Postgres
#46What sort of things are people doing in their SQL queries that make them CPU bound? Admittedly I'm a meat-and-potatoes guy, but I like mine I/O bound. Really amazed to see not one but several generic JIT frameworks though, no idea that was a thing.
Most databases in practice are sub-terabyte and even sub-100Gb, their active dataset is almost fully cached. For most databases I worked with, cache hit rate is above 95% and for almost all of them it's above 90%. In that situation, most queries are CPU-bound. It's completely different from typical OLAP in this sense.
Re: Better JIT for Postgres
#47Earlier quoted context omitted.
Sharing executable code between processes it not as easy as sharing data. AFAIK unless somethings changed recently PG shares nothing about plans between process and can't even share a cached plan between session/connections.
Executable code is literally just data that you mark as executable. It did the JIT code, and the idea that it can't then share it between processes is incomprehensible. I was actually confused by this submission as it puts so much of an emphasis on initial compilation time, when every DB (apparently except for pgsql) caches that result and shares it/reuses it until invalidation. Invalidation can occur for a wide vari…
No a lot of jitted code has pointers to addresses specific to that process which makes no sense in another process.
To make code shareable between processes takes effort and will have tradeoff in performance since it is not specialized to the process.
If the query plan where at least serializable which is more like a AST then at least that part could be reused and then maybe have jitted code in each processes cached in memory that the plan can reference by some key.
DB's like MSSQL avoid the problem because they run a single OS process with multiple threads instead. This is also why it can handle more connections easily since each connection is not a whole process.
Re: Better JIT for Postgres
#48Earlier quoted context omitted.
Sharing executable code between processes it not as easy as sharing data. AFAIK unless somethings changed recently PG shares nothing about plans between process and can't even share a cached plan between session/connections.
Executable code is literally just data that you mark as executable. It did the JIT code, and the idea that it can't then share it between processes is incomprehensible. I was actually confused by this submission as it puts so much of an emphasis on initial compilation time, when every DB (apparently except for pgsql) caches that result and shares it/reuses it until invalidation. Invalidation can occur for a wide vari…
The emphasis on compilation time there is because the JIT provider that comes with Postgres (LLVM-based) is broken in that particular area. But you're right, JITed code can be cached, if some conditions are met (it's position independent, for one). Not all JIT providers do that, but many do. Caching is on the table, but if your JIT-compilation takes microseconds, caching could be rather a burden in many cases. Still for some cases useful.
Re: Better JIT for Postgres
#49Earlier quoted context omitted.
Sharing executable code between processes it not as easy as sharing data. AFAIK unless somethings changed recently PG shares nothing about plans between process and can't even share a cached plan between session/connections.
Write the binary to a file, call it `libquery-id1234.so`, and link that to whichever processes that need it?
Might want to take a look at some research like this [1] that goes over the issues:
"This obvious drawback of the current software architecture motivates our work: sharing JIT code caches across applications. During the exploration of this idea, we have encountered several challenges. First of all, most JIT compilers leverage both runtime context and profile information to generate optimized code. The compiled code may be embedded with runtime-specific pointers, simplified through unique class-hierarchy analysis, or inlined recursively. Each of these "improve- ments" can decrease the shareability of JIT compiled code."
Anythings doable here with enough dev time. Would be nice if PG could just serialize the query plan itself maybe just as an SO along with non-process specific executable code that then has to be dynamically linked again in other processes.
Re: Better JIT for Postgres
#50Earlier quoted context omitted.
Nothing to do with plan caching, thats just talking about plan execution of parallel operations which is that thread or process based in PG? If process based then they can send small parts of plan across processes.
Ah, didn't see the caching part. Plans for prepared statements are cached though.
Yes if the client manually prepares the statement it will be cached for just that connection because in PG a connection is a process, but it won't survive from one connection to the next even in same process.
Other databases like MSSQL have prepared statements but they are rarely used now days since plan caching based on query text was introduced decades ago.