4 ms·

Query planner improvements are always welcome, it's a very important part of the DB. Though of course most of the time you notice it is when it's not doing what

Query planner improvements are always welcome, it's a very important part of the DB. Though of course most of the time you notice it is when it's not doing what you want ;-). 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.

aidos 3y ago
We hit a curios bug recently on production with the JIT. 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.
czl 3y ago
Did you try using prepared statement so the compilation is done once and compiled results are reused each time that query is run?
- fabian2k 3y ago
  As far as I understand prepared statements don't help here as the JIT output is not saved but generated for each execution. In this case I'm also using an ORM (EF Core) which doesn't expose the ability to prepare statements.
  - trollied 3y ago
    Most optimisers cache the execution plan based on a hash of the query, hence reuse when using prepared statements vs not. 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).
    - davidrowley 3y ago
      Postgres does not currently reuse JIT-compiled code. JIT will run each execution of the query. This may change in the next few years, likely starting with tuple deforming, as that's fairly reusable, per table for any query.
- dur-randir 3y ago
  There's a separate can of worms with prepared statements. Two main are: - 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
  - jeltz 3y ago
    Pgbouncer supports prepared statements these days.
- SigmundA 3y ago
  Only in the same session...
Sesse__ 3y ago
I believe the JIT is pretty much a failure, yes. It was well-meant, but LLVM just isn't the right tool for this. I've turned it off globally. (I don't use any ORMs, so it's not simply about strange query patterns.) Query parallelization, on the other hand, can actually be useful—and most importantly, rarely hurts.
davidrowley 3y ago
> 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. (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.
- dur-randir 3y ago
  If it's not ready for everyone, probably it shouldn't have been made a default, don't you think?
  - davidrowley 3y ago
    Perhaps, but it might be harsh to say it was the wrong decision when it was made as partitioned tables are far more optimised than when JIT was first worked on. It seems to me, most of the people that have issues with slow JIT times are having these issues with partitioned tables and JIT is slow due to having to compile large numbers of expressions. However, maybe this is the place for me to find out that's not always the case. The JIT costing is likely to get an overhaul soon, and if all goes to plan there JIT will be considered per plan node rather than per plan.
    - Sesse__ 3y ago
      FWIW, I've seen planning+query times triple with JIT on, with no partitioned tables involved. It just takes forever to JIT sometimes. (This was with Postgres 13 and 14, IIRC.) 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!
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)
dur-randir 3y ago
I disabled JIT after it became default for our installation (~1TB data). Nice try, useful sometimes, but as a default? No, thanks.

aidos 3y ago

We hit a curios bug recently on production with the JIT. 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.

czl 3y ago

Did you try using prepared statement so the compilation is done once and compiled results are reused each time that query is run?

fabian2k 3y ago
As far as I understand prepared statements don't help here as the JIT output is not saved but generated for each execution. In this case I'm also using an ORM (EF Core) which doesn't expose the ability to prepare statements.
- trollied 3y ago
  Most optimisers cache the execution plan based on a hash of the query, hence reuse when using prepared statements vs not. 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).
  - davidrowley 3y ago
    Postgres does not currently reuse JIT-compiled code. JIT will run each execution of the query. This may change in the next few years, likely starting with tuple deforming, as that's fairly reusable, per table for any query.
dur-randir 3y ago
There's a separate can of worms with prepared statements. Two main are: - 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
- jeltz 3y ago
  Pgbouncer supports prepared statements these days.
SigmundA 3y ago
Only in the same session...

Sesse__ 3y ago

I believe the JIT is pretty much a failure, yes. It was well-meant, but LLVM just isn't the right tool for this. I've turned it off globally. (I don't use any ORMs, so it's not simply about strange query patterns.) Query parallelization, on the other hand, can actually be useful—and most importantly, rarely hurts.

davidrowley 3y ago

> 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. (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.

dur-randir 3y ago
If it's not ready for everyone, probably it shouldn't have been made a default, don't you think?
- davidrowley 3y ago
  Perhaps, but it might be harsh to say it was the wrong decision when it was made as partitioned tables are far more optimised than when JIT was first worked on. It seems to me, most of the people that have issues with slow JIT times are having these issues with partitioned tables and JIT is slow due to having to compile large numbers of expressions. However, maybe this is the place for me to find out that's not always the case. The JIT costing is likely to get an overhaul soon, and if all goes to plan there JIT will be considered per plan node rather than per plan.
  - Sesse__ 3y ago
    FWIW, I've seen planning+query times triple with JIT on, with no partitioned tables involved. It just takes forever to JIT sometimes. (This was with Postgres 13 and 14, IIRC.) 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!

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)

dur-randir 3y ago

I disabled JIT after it became default for our installation (~1TB data). Nice try, useful sometimes, but as a default? No, thanks.