5 ms·

It's enabled by default since Postgres 12, and in some cases can be harmful, I mentioned it in my blog post about JSONB indexes [1]. I found it hard to make Po

by murkt 6y ago

It's enabled by default since Postgres 12, and in some cases can be harmful, I mentioned it in my blog post about JSONB indexes [1]. I found it hard to make Postgres reuse JIT-compiled plans, and quite often JIT compilation can kick in when it thinks that there will be many results and spend more time in compilation than executing a non-compiled query. Postgres needs a setting to increase JIT compilation threshold based on a query plan complexity. [1] https://vsevolod.net/postgresql-jsonb-index/#jit-compiling-bad-plans https://vsevolod.net/postgresql-jsonb-index/#jit-compiling-b...

jeltz 6y ago
I am a bit confused by your post. PostgreSQL does not yet support reusing compiled plans at all (it is tricky to implement and there is only one guy working on JIT) but PostgreSQL already has a threshold based on query complexity.
- murkt 6y ago
  > PostgreSQL does not yet support reusing compiled plans at all Well, that explains why I couldn't make it work :) > PostgreSQL already has a threshold based on query complexity. PostgreSQL has settings that threshold based on a query cost - jit_above_cost, jit_inline_above_cost, jit_optimize_above_cost. For example, imagine we query a table with millions of rows, extract some columns, do some transforms. Will the cost be quite big? It will. Will it run for a long time? It will, let's say 2 second. JIT compilation will take, for example, 0.2 seconds and the query itself now will run in 1.4 seconds, so 0.4 second total win. Imagine we have other query that joins seven tables, access many indexes, etc, returns a modest amount of rows and results in the same cost as the first query. Often it could finish even faster, like in 0.5 seconds or something, but let's say it's the same 2 seconds. However, as there are much more code to compile, JIT compilation now takes 10 seconds, and speeds the query up to the same 1.4 seconds. So I want a setting for JIT compilation that will capture a query complexity.
  - mulmen 6y ago
    How does complexity relate to cost? Is it just a matter of weight?
    - murkt 6y ago
      What I called complexity is an amount of different nodes (not sure this is the proper term) in a query plan. So, the more complexity for a given cost - the more work JIT needs to do.
      - mulmen 6y ago
        But the planner sums each step in the plan so isn’t a more complex plan already more expensive? Or is the addition of another step not adequately considered in that cost?
        murkt 6y ago
        A more complex plan isn't already more expensive in a way that captures how much work the JIT will need to do. I think I'll need to write a separate blog post about that, with details and examples.
  - magicalhippo 6y ago
    > query cost That would be query execution cost, right? And you're after some knobs to tune query preparation/compilation cost? I don't know how the PostgreSQL JIT thing works, but I imagine it already has an AST-ish structure of the query by the time it calls LLVM. In that case I guess it should be possible to assign some weights to the nodes/elements and get an estimate for how long it'll take to compile.
  - baq 6y ago
    i had exactly your scenario in tests: very complex query got jitted with full optimizations (expensive!) and then due to the data, etc. 95% of that query wasn't executed at all. performance tanked. you have to carefully analyze your queries if you want the jit to be a net benefit - and by carefully, i mean look at explain analyze outputs to check for major mismatches of estimated vs actual rows (say 10x).
rowanseymour 6y ago
We noticed this recently when we enabled CI testing with PG12 and suddenly unit tests were taking 4x as long to run. Haven't tested it in more production-like settings, but definitely a bit surprised this became the default given its potential for causing performance issues.
darksaints 6y ago
After reading through your blog post, I think the feature you should be looking to is expression indexes. Build up the expression that you use repeatedly and and is so costly, and directly index that.
- murkt 6y ago
  I have thought about expression indexes, and for expressions I need (@> on jsonb) they can't be used.
  - asah 6y ago
    Can you wrap that operator call in a function, and index the output of that function? (sorry if dumb q - but I've used this technique myself, and indeed, my favorite hack in recent years are JavaScript UDFs in plv8... super ugly but very very flexible...)
    - murkt 6y ago
      I'm not sure I can, and I definitely do not want to do that. I'm happy with a separate table and `btree` index on it.

by murkt 6y ago

jeltz 6y ago

I am a bit confused by your post. PostgreSQL does not yet support reusing compiled plans at all (it is tricky to implement and there is only one guy working on JIT) but PostgreSQL already has a threshold based on query complexity.

murkt 6y ago
> PostgreSQL does not yet support reusing compiled plans at all Well, that explains why I couldn't make it work :) > PostgreSQL already has a threshold based on query complexity. PostgreSQL has settings that threshold based on a query cost - jit_above_cost, jit_inline_above_cost, jit_optimize_above_cost. For example, imagine we query a table with millions of rows, extract some columns, do some transforms. Will the cost be quite big? It will. Will it run for a long time? It will, let's say 2 second. JIT compilation will take, for example, 0.2 seconds and the query itself now will run in 1.4 seconds, so 0.4 second total win. Imagine we have other query that joins seven tables, access many indexes, etc, returns a modest amount of rows and results in the same cost as the first query. Often it could finish even faster, like in 0.5 seconds or something, but let's say it's the same 2 seconds. However, as there are much more code to compile, JIT compilation now takes 10 seconds, and speeds the query up to the same 1.4 seconds. So I want a setting for JIT compilation that will capture a query complexity.
- mulmen 6y ago
  How does complexity relate to cost? Is it just a matter of weight?
  - murkt 6y ago
    What I called complexity is an amount of different nodes (not sure this is the proper term) in a query plan. So, the more complexity for a given cost - the more work JIT needs to do.
    - mulmen 6y ago
      But the planner sums each step in the plan so isn’t a more complex plan already more expensive? Or is the addition of another step not adequately considered in that cost?
      - murkt 6y ago
        A more complex plan isn't already more expensive in a way that captures how much work the JIT will need to do. I think I'll need to write a separate blog post about that, with details and examples.
- magicalhippo 6y ago
  > query cost That would be query execution cost, right? And you're after some knobs to tune query preparation/compilation cost? I don't know how the PostgreSQL JIT thing works, but I imagine it already has an AST-ish structure of the query by the time it calls LLVM. In that case I guess it should be possible to assign some weights to the nodes/elements and get an estimate for how long it'll take to compile.
- baq 6y ago
  i had exactly your scenario in tests: very complex query got jitted with full optimizations (expensive!) and then due to the data, etc. 95% of that query wasn't executed at all. performance tanked. you have to carefully analyze your queries if you want the jit to be a net benefit - and by carefully, i mean look at explain analyze outputs to check for major mismatches of estimated vs actual rows (say 10x).

rowanseymour 6y ago

We noticed this recently when we enabled CI testing with PG12 and suddenly unit tests were taking 4x as long to run. Haven't tested it in more production-like settings, but definitely a bit surprised this became the default given its potential for causing performance issues.

darksaints 6y ago

After reading through your blog post, I think the feature you should be looking to is expression indexes. Build up the expression that you use repeatedly and and is so costly, and directly index that.

murkt 6y ago
I have thought about expression indexes, and for expressions I need (@> on jsonb) they can't be used.
- asah 6y ago
  Can you wrap that operator call in a function, and index the output of that function? (sorry if dumb q - but I've used this technique myself, and indeed, my favorite hack in recent years are JavaScript UDFs in plv8... super ugly but very very flexible...)
  - murkt 6y ago
    I'm not sure I can, and I definitely do not want to do that. I'm happy with a separate table and `btree` index on it.