Re:JIT-Compiling SQL Queries in PostgreSQL Using LLVM
by baq on Monday February 22, 2021 @12:11PM
(#26227018)
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).
Re:JIT-Compiling SQL Queries in PostgreSQL Using LLVM
by magicalhippo on Monday February 22, 2021 @08:42AM
(#26224369)
> 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.
Re:JIT-Compiling SQL Queries in PostgreSQL Using LLVM
by mulmen on Monday February 22, 2021 @05:46AM
(#26222988)
How does complexity relate to cost? Is it just a matter of weight?
Re:JIT-Compiling SQL Queries in PostgreSQL Using LLVM
by murkt on Monday February 22, 2021 @06:06AM
(#26223137)
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.
Re:JIT-Compiling SQL Queries in PostgreSQL Using LLVM
by mulmen on Monday February 22, 2021 @03:25PM
(#26230074)
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?
Re:JIT-Compiling SQL Queries in PostgreSQL Using LLVM
by murkt on Tuesday February 23, 2021 @06:33AM
(#26236108)
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.
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.