Re: Catching up with performance & PostgreSQL 15
On Tue, 2022-11-29 at 19:09 +0100, Alvaro Herrera wrote: > On 2022-Nov-29, Mladen Gogala wrote: > > > Hmmm, I think I will run pgbench with and without JIT on and see > > the > > difference. > > I doubt you'll notice anything, because the pgbench queries will be > far > below the JIT cost, so nothing will get JIT compiled at all. Or are > you > planning on using a custom set of queries? > Nope. I am planning to set jit_above_cost parameter to 5. That should take care of the pgbench problem. Other than that, you're right: JIT should not be used for OLTP. However, pure OLTP or DW databases are a rarity these days. Reporting is a crucial function and almost every OLTP database that I've seen also has reporting function, which means that there are complex queries to be executed. -- Mladen Gogala Database Consultant https://dbwhisperer.wordpress.com
Re: Catching up with performance & PostgreSQL 15
On Wed, 30 Nov 2022 at 03:31, Tom Lane wrote: > > Alvaro Herrera writes: > > IMO it was a mistake to turn JIT on in the default config, so that's one > > thing you'll likely want to change. > > I wouldn't necessarily go quite that far, but I do think that the > default cost thresholds for invoking it are enormously too low, > or else there are serious bugs in the cost-estimation algorithms > for deciding when to use it. A nearby example[1] of a sub-1-sec > partitioned query that took 30sec after JIT was enabled makes me > wonder if we're accounting correctly for per-partition JIT costs. I'm very grateful for JIT. However, I do agree that the costs need to work. The problem is that the threshold to turn JIT on does not consider how many expressions need to be compiled. It's quite different to JIT compile a simple one-node plan with a total cost of 10 than to JIT compile a plan that costs the same but queries 1000 partitions. I think we should be compiling expressions based on the cost of the individial node rather than the total cost of the plan. We need to make some changes so we can more easily determine the number of times a given node will be executed before we can determine how worthwhile JITting an expression in a node will be. David > [1] > https://www.postgresql.org/message-id/B6025887-D73F-4B5B-9925-4DA4B675F7E5%40elevated-dev.com
Re: Catching up with performance & PostgreSQL 15
On 2022-Nov-29, Mladen Gogala wrote: > Hmmm, I think I will run pgbench with and without JIT on and see the > difference. I doubt you'll notice anything, because the pgbench queries will be far below the JIT cost, so nothing will get JIT compiled at all. Or are you planning on using a custom set of queries? -- Álvaro Herrera PostgreSQL Developer — https://www.EnterpriseDB.com/
Re: Catching up with performance & PostgreSQL 15
Alvaro Herrera writes: > IMO it was a mistake to turn JIT on in the default config, so that's one > thing you'll likely want to change. I wouldn't necessarily go quite that far, but I do think that the default cost thresholds for invoking it are enormously too low, or else there are serious bugs in the cost-estimation algorithms for deciding when to use it. A nearby example[1] of a sub-1-sec partitioned query that took 30sec after JIT was enabled makes me wonder if we're accounting correctly for per-partition JIT costs. regards, tom lane [1] https://www.postgresql.org/message-id/B6025887-D73F-4B5B-9925-4DA4B675F7E5%40elevated-dev.com
Re: Catching up with performance & PostgreSQL 15
On 11/29/22 03:36, Alvaro Herrera wrote: On 2022-Nov-28, Mladen Gogala wrote: As for JIT, I've recently asked that question myself. I was told that PostgreSQL with LLVM enabled performs approximately 25% better than without it. Hmm, actually, normally you're better off turning JIT off, because it's very common to diagnose cases of queries that become much, much slower because of it. Some queries do become faster, but it's not a wide margin, and it's not a lot. There are rare cases where JIT is beneficial, but those tend to be queries that take upwards of several seconds already. IMO it was a mistake to turn JIT on in the default config, so that's one thing you'll likely want to change. Hmmm, I think I will run pgbench with and without JIT on and see the difference. -- Mladen Gogala Database Consultant Tel: (347) 321-1217 https://dbwhisperer.wordpress.com
Re: Catching up with performance & PostgreSQL 15
On 11/29/22 03:31, Alvaro Herrera wrote: On 2022-Nov-28, Mladen Gogala wrote: You'll probably be glad to learn that we have hints now. What hints are you talking about? As I understand, we still don't have Oracle-style query hints. https://github.com/ossc-db/pg_hint_plan -- Mladen Gogala Database Consultant Tel: (347) 321-1217 https://dbwhisperer.wordpress.com
Re: Catching up with performance & PostgreSQL 15
On 2022-Nov-28, Mladen Gogala wrote: > As for JIT, I've recently asked that question myself. I was told that > PostgreSQL with LLVM enabled performs approximately 25% better than without > it. Hmm, actually, normally you're better off turning JIT off, because it's very common to diagnose cases of queries that become much, much slower because of it. Some queries do become faster, but it's not a wide margin, and it's not a lot. There are rare cases where JIT is beneficial, but those tend to be queries that take upwards of several seconds already. IMO it was a mistake to turn JIT on in the default config, so that's one thing you'll likely want to change. -- Álvaro Herrera 48°01'N 7°57'E — https://www.EnterpriseDB.com/ "Thou shalt not follow the NULL pointer, for chaos and madness await thee at its end." (2nd Commandment for C programmers)
Re: Catching up with performance & PostgreSQL 15
On 2022-Nov-28, Mladen Gogala wrote: > You'll probably be glad to learn that we have hints now. What hints are you talking about? As I understand, we still don't have Oracle-style query hints. -- Álvaro HerreraBreisgau, Deutschland — https://www.EnterpriseDB.com/
