4 ms·

One other thing about JIT that I feel is pretty crazy is that the generated code is not cached. I mean it's the most expensive part of the query execution a lot

by fuy 3y ago

One other thing about JIT that I feel is pretty crazy is that the generated code is not cached. I mean it's the most expensive part of the query execution a lot of the time, how come it's not cached? I couldn't find good reasons for this looking through Postgres mailing lists discussion around JIT. Disabling JIT is the way to go for OLTP workloads.

davidrowley 3y ago
There's some information about why that does not happen in https://www.postgresql.org/message-id/20211104234742.ao2qzqf5kpr2wgzn%40alap3.anarazel.de https://www.postgresql.org/message-id/20211104234742.ao2qzqf... 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.
SigmundA 3y ago
Unlike say MSSQL or Oracle PG does not cache plans at all. I think this is mostly due to its multiprocess architecture vs just sharing in memory plans between threads. In MSSQL a plan can take a while to optimize including jitting if needed but it doesn't matter that much because all plans are cached so when that statement comes in again the plan is ready to go.
- nextaccountic 3y ago
  > I think this is mostly due to its multiprocess architecture vs just sharing in memory plans between threads 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-shared-memory/ https://www.instaclustr.com/blog/postgresql-docker-and-share... https://stackoverflow.com/questions/32930787/understanding-postgresql-shared-memory https://stackoverflow.com/questions/32930787/understanding-p... (random links from Google just to illustrate the point)
  - SigmundA 3y ago
    PG shares data between process not code as far as I know and definetly not any plans. Sharing jitted code is not straight forward as pointers will be different per process.
- fakwandi_priv 3y ago
  https://www.postgresql.org/docs/current/plpgsql-implementation.html#PLPGSQL-PLAN-CACHING https://www.postgresql.org/docs/current/plpgsql-implementati... Seems like these will get cached at some point.
  - SigmundA 3y ago
    Only within a session unless something has changed there, that means no sharing between clients or even from one connection to the next form the same client. MSSQL caches plans globally and can be used across sessions and connections if the statement text is the same.

by fuy 3y ago

davidrowley 3y ago

There's some information about why that does not happen in https://www.postgresql.org/message-id/20211104234742.ao2qzqf5kpr2wgzn%40alap3.anarazel.de https://www.postgresql.org/message-id/20211104234742.ao2qzqf... 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.

SigmundA 3y ago

Unlike say MSSQL or Oracle PG does not cache plans at all. I think this is mostly due to its multiprocess architecture vs just sharing in memory plans between threads. In MSSQL a plan can take a while to optimize including jitting if needed but it doesn't matter that much because all plans are cached so when that statement comes in again the plan is ready to go.

nextaccountic 3y ago
> I think this is mostly due to its multiprocess architecture vs just sharing in memory plans between threads 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-shared-memory/ https://www.instaclustr.com/blog/postgresql-docker-and-share... https://stackoverflow.com/questions/32930787/understanding-postgresql-shared-memory https://stackoverflow.com/questions/32930787/understanding-p... (random links from Google just to illustrate the point)
- SigmundA 3y ago
  PG shares data between process not code as far as I know and definetly not any plans. Sharing jitted code is not straight forward as pointers will be different per process.
fakwandi_priv 3y ago
https://www.postgresql.org/docs/current/plpgsql-implementation.html#PLPGSQL-PLAN-CACHING https://www.postgresql.org/docs/current/plpgsql-implementati... Seems like these will get cached at some point.
- SigmundA 3y ago
  Only within a session unless something has changed there, that means no sharing between clients or even from one connection to the next form the same client. MSSQL caches plans globally and can be used across sessions and connections if the statement text is the same.