Query parallelization, on the other hand, can actually be useful—and most importantly, rarely hurts.
I had apt updated a couple of packages and then all of a sudden a bigger query we run every 5 minutes was failing. Or rather, Postgres just started silently hanging up the connection mid query execution with even putting anything in the logs.
Took me a while of running manually in explain to see that the variations of the query that ended up using the JIT broke while those that didn’t were ok. Disabled the JIT and everything was ok again.
Oracle has an option to detect literals (CURSOR_SHARING) and essentially replace them with binds internally, in order to increase performance and stop the query pool getting filled up with the same statement(s).
- parameters are opaque to planner, so it prefers (or is even forced to?) to choose generic vs specific plans
- it doesn't play nice with pg_bouncer in transaction mode
(Author of the blog here and Postgres committer). I very much agree that the code to decide if JIT should be used or not needs work. For PG16, it only takes into account the estimated total cost of the plan and does not take into account how many expressions need to be compiled. It's quite fast to compile a few expressions, but if you're querying a partitioned table with hundreds of partitions and the plan contains all those partitions, then the JIT compiler has a lot of work to do. The number of expressions is not considered. A colleague and I do have some code to improve this. Unsure if it'll make PG17 at this stage.
Update: I checked some old IRC logs, and found a query that took 1476 ms without JIT and 8754 ms with JIT on. And that is execution time, not planning time!
Disabling JIT is the way to go for OLTP workloads.
In particular:
> The immediate goal is to be able to generate JITed code/LLVM-IR that doesn't > contain any absolute pointer values. If the generated code doesn't change > regardless of any of the other contents of ExprEvalStep, we can still cache > the JIT optimization / code emission steps - which are the expensive bits.
A colleague is working on getting this patch into shape. So we might see some caching work get done after the relative pointer work is in.
You can share stuff with a multiprocess architecture just fine (either through IPC or just plain shared memory + synchronization)
It's true that threads share memory by default, but processes can opt into sharing memory if they wish. And it appears that Postgres already makes use of shared memory for some things
https://www.instaclustr.com/blog/postgresql-docker-and-share...
https://stackoverflow.com/questions/32930787/understanding-p...
(random links from Google just to illustrate the point)
Seems like these will get cached at some point.
MSSQL caches plans globally and can be used across sessions and connections if the statement text is the same.
One part of this I found rather frustrating is the JIT in newer Postgres versions. The heuristics on when to use appear not robust at all to me. I've seen this for a rather typical ORM-generated query that is pretty straightforward, but pulls in a lot of other tables via joins. It runs in a few milliseconds without the JIT, but the JIT spent 1-1.5 seconds doing its thing on top of that and makes it incredibly slow for tiny amounts of data.
I know now to just disable the JIT, but this feature can give a pretty terrible impression to users that don't know enough yet to figure out why it's slow. I like Postgres a lot, but enabling the JIT just seems far too dangerous as a default setting to me.