vladich

6 ms·

1.
by vladich 6mo ago
If they count only query execution time, not everything else, it would make sense though. It also could be practical, if your system runs just a few predefined and very optimized queries.
2.
by vladich 7mo ago
Considering it's just s single Phd student who does this work, I don't believe such a task can be realistically accomplished, even as a PoC / research.
3.
by vladich 7mo ago
Then why they write the opposite?
4.
by vladich 7mo ago
That's all great, but sadly impractical. I looked at one of the first statements: > GenDB is an LLM-powered agentic system that decomposes the complex end-to-end query processing and optimization task into a sequence of smaller and
5.
by vladich 7mo ago
The idea with parallel compilation is interesting. Worth considering, in some cases. The only problem with it is the same as too much parallelization - you can exhaust your CPU resources much faster. But with some sort of smart scheduling i
6.
by vladich 7mo ago
Added Windows (x86_64 for now) support
7.
by vladich 7mo ago
It's definitely different, and for Postgres specifically, they may complement each other. SLJit can be used for low latency queries where codegen time is more important than optimizations, also for other platforms like s390x / PPC
8.
by vladich 7mo ago
I have a test of 200Kb query that AsmJit takes 7 seconds to compile (that's not too bad both LLVM and MIR take ~20s), while sljit does it in 50ms. 200Kb is a pathological case, but it's not unheard of in the area I'm working
9.
by vladich 7mo ago
You can't get a plan cache without a prepared statement, but you can get a prepared statement without a plan cache. It's not the same thing, and in most cases in Postgres prepared statements _do_not_ give you plan caching, because
10.
by vladich 7mo ago
SLJIT is a bit smarter than just to use hardcoded registers. It's multi-platform anyway, so it uses registers when they are available on the target platform, if not it will use memory, that's why performance can differ between Win
11.
by vladich 7mo ago
Good point about SIMD opportunities though - it's something other 2 JITs lack.
12.
by vladich 7mo ago
There are other issues with that auto-allocation. I tested all 3 backends on very large queries (hundreds of KBs) per query. Performance of all of them (+LLVM, but -sljit) was abysmal - the compiler overhead was in seconds to tens(!) of sec
13.
by vladich 7mo ago
If you think I'm trying to "gotcha" you, you're mistaken. I'm past time I would care about that. It was simply a (apparently failed) education opportunity. Be well.
14.
by vladich 7mo ago
Sure, but that's not the main issue. If you add a global cache, it will have only a marginal value. There are Postgres extensions / forks with global cache and they are not wildly more efficient. The main issue you still do not u
15.
by vladich 7mo ago
The fact that you plan and execute query by query?
16.
by vladich 7mo ago
Interesting... AsmJit is pretty fast for compilation, but about 3x than sljit. The only way I can see how to make it fast enough, in theory (i.e. without slowing down point-lookup queries and such) would be to fuse planning with code genera
17.
by vladich 7mo ago
Sure, but not more than one query per file
18.
by vladich 7mo ago
That's exactly what I said above - just turn this thing off. The reason is that even if your generic plan is better than 5 custom plans before it, that doesn't guarantee much. With probability high enough to cause troubles, it&#x2
19.
by vladich 7mo ago
It will be added soon
20.
by vladich 7mo ago
It's not useful for sub-millisecond queries like point lookups, or other simple ones that process only a few records. sljit option starts to pay off when you process (not necessarily return) hundreds of records. The more - the better.
21.
by vladich 7mo ago
https://www.postgresql.org/docs/current/runtime-config-query... and then https://www.postgresql.org/docs/current/sql-prepare.html Read carefully about "plan_cache_mode" and how
22.
by vladich 7mo ago
There are reasons for that, it's useful in a very narrow set of situations. Postgres cached plans exist for the same reason. If you're claiming Oracle and MSSQL do _much_ better in this area - that's what I call unsubstantiat
23.
by vladich 7mo ago
You make some unsubstantiated claims here. I assure you that it isn't as simple as you claim. And what Postgres does here is (mostly) the right thing, you can't do much better. You simply can't decide what plan you need to us
24.
by vladich 7mo ago
I didn't say it can cache between processes. The problem is not caching between processes, it's that caching itself is not very useful, because the planner creates different plans for different input parameters of the same query i
25.
by vladich 7mo ago
Won't work well if it executes 20k+ queries per second. Filesystem will be a bottleneck among other things.
26.
by vladich 7mo ago
The emphasis on compilation time there is because the JIT provider that comes with Postgres (LLVM-based) is broken in that particular area. But you're right, JITed code can be cached, if some conditions are met (it's position inde
27.
by vladich 7mo ago
Most databases in practice are sub-terabyte and even sub-100Gb, their active dataset is almost fully cached. For most databases I worked with, cache hit rate is above 95% and for almost all of them it's above 90%. In that situation, mo
28.
by vladich 7mo ago
Postgres caches query plans too, the problem is you can only cache what you can share, and if your planner works well, you can share very little, there can be a lot of unique plans even for the same query
29.
Better JIT for Postgres(github.com)
155 points by vladich 7mo ago|113 comments
30.
by vladich 1y ago
Both Cockroach and CedarDB didn't rewrite anything, they built stuff from scratch. Just used the same client protocol. There are a bunch of other unrelated databases using Postgres protocol btw.

More ›