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.

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

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

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

How does complexity relate to cost? Is it just a matter of weight?

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.

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?

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.

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.

I have thought about expression indexes, and for expressions I need (@> on jsonb) they can't be used.

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

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.

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.