Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Better JIT for Postgres

github.com

Post reply on HN
Print view View original

71–80 of 116 posts

Re: Better JIT for Postgres

#71

Post by eru »

olau wrote: post #7
eru wrote: post #2

> However, standard LLVM-based JIT is notoriously slow at compilation. When it takes tens to hundreds of milliseconds, it may be suitable only for very heavy, OLAP-style queries, in some cases. I don't know anything here, but this seems like a good case for ahead of time compilation? Or at least caching your JIT results? I can image much of the time, you are getting more or less the same query again and again?

Yes. Some years ago we ported some code from querying out the data and tallying in Python (how many are in each bucket) to using SQL to do that. It didn't speed up the execution. I was surprised by that, but I guess the Postgres interpreter is roughly the same speed as Python, which when you think about it perhaps isn't that surprising. But Python is truly general purpose while the core query stuff in SQL is really s…

That's curious. I regularly get speed ups when moving processing from Python to postgres. At least when using indices properly and when the shift reduces the amount of data carried back and forth.

Re: Better JIT for Postgres

#72

Post by vladich »

SigmundA wrote: post #70
vladich wrote: post #65

Earlier quoted context omitted.

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 unsubstantiated. From what you write further it's pretty clear you don't have a lot of understanding what happens under the hood. And no, prepared statements are not what you read in Wikipedia. Not in all dat…

>There are reasons for that, it's useful in a very narrow set of situations. So narrow its enabled by default for all statements from the "big boy" commercial RDBMS's... https://www.ibm.com/docs/en/i/7.4.0?topic=overview-plan-cach... https://docs.oracle.com/en/database/oracle/oracle-database/1... https://learn.microsoft.com/en-us/sql/relational-databases/p... https://help.sap.com/docs/SAP_HANA_PLATFORM/6b94445c94ae49…

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 it works (and its default settings). Sorry, that's my last message in this thread, and I'm still here just for educational purposes, because what you're talking about is in fact a common misconception. If you read it carefully, you'll see that generic plans do not require any "explicit commands", Postgres executes a query 5 times in custom mode, then tries a generic one, if it worked (not much worse than an average of 5 custom plans), the plan is cached. You can turn it off though. And I'd recommend to turn it off for most cases, because it's a pretty bad heuristics. Nevertheless, for some (pretty narrow set of) cases it's useful.

So, Mr Big Boy, now we can get to what a prepared statement in Postgres is. Prepared statements are cached in a session, but if that statement was cached in custom mode, it won't contain a plan. When Postgres receives a prepared statement in custom mode, it will just skip parsing, that's it. The query will still be planned, because custom plans rely on input parameters. If we run it in generic mode, then the plan is cached.

Re: Better JIT for Postgres

#73

Post by patagurbon »

SigmundA wrote: post #47
llm_nerd wrote: post #30

Earlier quoted context omitted.

Executable code is literally just data that you mark as executable. It did the JIT code, and the idea that it can't then share it between processes is incomprehensible. I was actually confused by this submission as it puts so much of an emphasis on initial compilation time, when every DB (apparently except for pgsql) caches that result and shares it/reuses it until invalidation. Invalidation can occur for a wide vari…

No a lot of jitted code has pointers to addresses specific to that process which makes no sense in another process. To make code shareable between processes takes effort and will have tradeoff in performance since it is not specialized to the process. If the query plan where at least serializable which is more like a AST then at least that part could be reused and then maybe have jitted code in each processes cached…

What does specialized to the process mean? Lots of JIT tooling these days readily supports caching and precompilation. Invalidation is hard but things like reloading global references are hardly intractable problems especially for an org as large as pgsql.

Re: Better JIT for Postgres

#74

Post by magicalhippo »

vladich wrote: post #72
SigmundA wrote: post #70

Earlier quoted context omitted.

>There are reasons for that, it's useful in a very narrow set of situations. So narrow its enabled by default for all statements from the "big boy" commercial RDBMS's... https://www.ibm.com/docs/en/i/7.4.0?topic=overview-plan-cach... https://docs.oracle.com/en/database/oracle/oracle-database/1... https://learn.microsoft.com/en-us/sql/relational-databases/p... https://help.sap.com/docs/SAP_HANA_PLATFORM/6b94445c94ae49…

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 it works (and its default settings). Sorry, that's my last message in this thread, and I'm still here just for educational purposes, because what you're talking about is in fact a common misconception. If you read it carefully, you'll see that…

> then tries a generic one, if it worked (not much worse than an average of 5 custom plans), the plan is cached

Seems like it's not great at detecting this in all cases[1]. That said, I do note that was reproduced on PG16, perhaps they've made improvements since, given the documentation explicitly mentions what you said.

[1]: https://www.michal-drozd.com/en/blog/postgresql-prepared-sta...

Re: Better JIT for Postgres

#75

Post by vladich »

swaminarayan wrote: post #14

Have you tested this under high concurrency with lots of short OLTP queries? I’m curious whether the much faster compile time actually moves the point where JIT starts paying off, or if it’s still mostly useful for heavier queries.

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. I'm still thinking about a caching option, that will allow to lift this requirement somewhat - for cached plans. For non-cached ones it will stay.

Re: Better JIT for Postgres

#77

Post by vladich »

magicalhippo wrote: post #74
vladich wrote: post #72

Earlier quoted context omitted.

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 it works (and its default settings). Sorry, that's my last message in this thread, and I'm still here just for educational purposes, because what you're talking about is in fact a common misconception. If you read it carefully, you'll see that…

> then tries a generic one, if it worked (not much worse than an average of 5 custom plans), the plan is cached Seems like it's not great at detecting this in all cases[1]. That said, I do note that was reproduced on PG16, perhaps they've made improvements since, given the documentation explicitly mentions what you said. [1]: https://www.michal-drozd.com/en/blog/postgresql-prepared-sta...

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's just a coincidence, and generic plans in general tend to be very bad (because they use some hardcoded constants instead of statistics for planning).

This behavior is often a source of random latency spikes, when your queries suddenly start misbehaving, and then suddenly stop doing it. If you don't have auto_explain on, it will look like mysterious glitches in production.

The few cases when they are useful are very simple ones, like single table selects by index. They are already fast, and with generic plans you can cut planning time completely. Which is kinda...not much. There are more complicated cases where they are useful, involving Postgres forks like AWS Aurora, which has query plan management subsystem, allowing to store plans directly. Then you can cut planning time for them. But that's a completely different story.

Re: Better JIT for Postgres

#78

Post by Asm2D »

aengelke wrote: post #38
Asm2D wrote: post #31

Earlier quoted context omitted.

Many SQL engines have JIT compilers. The problems related to PostgreSQL are pretty much all described here. It's very difficult to do low-latency queries if you cannot cache the compiled code and do it over and over again. And once your JIT is slow you need a logic to decide whether to interpret or compile. I think it would be the best to start interpreting the query and start compilation in another thread, and once…

> It's very difficult to do low-latency queries if you cannot cache the compiled code This is not too difficult, it just requires a different execution style. Salesforce's Hyper for example very heavily relies on JIT compilation, as does Umbra [1], which some people regard as one of the fastest databases right now. Umbra doesn't cache any IR or compiled code and still has an extremely low start-up latency; an interpr…

If I recall research papers regarding Umbra it's also using AsmJit as a JIT backend, which means that theoretically the compilation times would be comparable if you only consider code emitting overhead.

The problem will always be queries where the compilation is orders of magnitude more expensive than the query itself. I can imagine indexed lookup of 1 or few entries, etc... Accessing indexed entries like these are very well optimized by SQL query engines and possibly make no sense JIT optimizing.

Re: Better JIT for Postgres

#79

Post by Asm2D »

chrisaycock wrote: post #40
Asm2D wrote: post #31

Earlier quoted context omitted.

Many SQL engines have JIT compilers. The problems related to PostgreSQL are pretty much all described here. It's very difficult to do low-latency queries if you cannot cache the compiled code and do it over and over again. And once your JIT is slow you need a logic to decide whether to interpret or compile. I think it would be the best to start interpreting the query and start compilation in another thread, and once…

> I think it would be the best to start interpreting the query and start compilation in another thread This technique is known as a "tiered JIT". It's how production virtual machines operate for high-level languages like JavaScript. There can be many tiers, like an interpreter, baseline compiler, optimizing compiler, etc. The runtime switches into the faster tier once it becomes ready. More info for the interested: h…

I write JITs so I know, but I always try to write in a way that even non-JIT people can understand :)
Post reply on HN