61–70 of 116 posts
What sort of things are people doing in their SQL queries that make them CPU bound? Admittedly I'm a meat-and-potatoes guy, but I like mine I/O bound. Really amazed to see not one but several generic JIT frameworks though, no idea that was a thing.
Anything jsonb in my experience is quickly CPU bound...
Earlier quoted context omitted.
No it cannot cache query plans between processes (connections) and the only way it can cache in the same process in the same connection is by the client manually preparing it, this was how the big boys did it 30 years ago, not anymore. Was common guidance back in the day to use stored procedures for all application access code because they where cached in MSSQL (which PG doesn't even do). Then around 2000 it started…
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 in the general case. So you can reliably cache plans only for the same sets of parameters. Or you can cache generic plans, which Postgres already does as well (and sharing that cache won't so…
They also do things like auto parameterization if the statement doesn't have them and parameter sniffing to make multiple different plans based on different values where it makes sense.
https://learn.microsoft.com/en-us/sql/relational-databases/q...
You can also get this, add HINTs to control this behavior if you don't like it or its causing a problem in production, crazy I know.
https://learn.microsoft.com/en-us/sql/t-sql/queries/hints-tr...
PG is extremely primitive compared to these other systems in this area, and it has to be since it doesn't cache anything unless specifically instructed to for a single connection.
Earlier quoted context omitted.
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 in the general case. So you can reliably cache plans only for the same sets of parameters. Or you can cache generic plans, which Postgres already does as well (and sharing that cache won't so…
Other databases cache plans and have for years because it's very useful, many (most?) apps run many of the same statement with differing parameters, its a big win. They do this without the client having to figure out the statement matching logic like your various PG Orms and connection poolers try and do. They also do things like auto parameterization if the statement doesn't have them and parameter sniffing to make…
Earlier quoted context omitted.
Other databases cache plans and have for years because it's very useful, many (most?) apps run many of the same statement with differing parameters, its a big win. They do this without the client having to figure out the statement matching logic like your various PG Orms and connection poolers try and do. They also do things like auto parameterization if the statement doesn't have them and parameter sniffing to make…
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 use based on the query and its parameters alone, unless you already cached that plan for those parameters (and even in that case you need to watch out for possible dramatic changes in statistics).…
I never said it was simple, in fact I said how primitive PG is compared to the "big boys" because they put huge effort into making their systems fast back in the TPS wars of the early 2000's on much slower hardware.
>Prepared statements != cached execution plans
Thats exactly what a prepared statement is:
Earlier quoted context omitted.
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 use based on the query and its parameters alone, unless you already cached that plan for those parameters (and even in that case you need to watch out for possible dramatic changes in statistics).…
Ah yes so Microsoft and Oracle do these things for no good reason, you are the one making unsubstantiated claims such as "you can't do much better". And "You simply can't decide what plan you need to use based on the query and its parameters alone" which is mostly what those systems do (along with statistics). If you bothered to read what I linked you could see exactly how they are doing it. I never said it was simpl…
Earlier quoted context omitted.
At some level the application needs to participate in the performance conversation too. https://www.postgresql.org/docs/current/sql-prepare.html
Postgres’s PREPARE is per-connection so it’s pretty limited, and then connection poolers enter the fray and often can’t track SQL-level prepares. And then the issue is not dissimilar to Postgres’s planner issues.
The problem though was that it had a single shared pool for all queries and it could only run a query if it was in the pool, which is how out DB machine would max out at 50% CPU and bandwidth. We had made some mistakes in our search code that I told the engineer not to make.
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…
Earlier quoted context omitted.
Write the binary to a file, call it `libquery-id1234.so`, and link that to whichever processes that need it?
Won't work well if it executes 20k+ queries per second. Filesystem will be a bottleneck among other things.
Earlier quoted context omitted.
Ah yes so Microsoft and Oracle do these things for no good reason, you are the one making unsubstantiated claims such as "you can't do much better". And "You simply can't decide what plan you need to use based on the query and its parameters alone" which is mostly what those systems do (along with statistics). If you bothered to read what I linked you could see exactly how they are doing it. I never said it was simpl…
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…
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/6b94445c94ae495c...
>Postgres cached plans exist for the same reason.
Postgresql doesn't cache plans unless the client explicitly sends commands to do so. Applications cannot take advantage of this unless they keep connections open and reuse them in a pool and they must mange this themselves. The plan has to be planned for every separate connection/process rather than a single cached planed increasing server memory costs which are plan cache size X number of connections.
It has no "reason" to cache plans the client must do this using its "reasons".
>If you're claiming Oracle and MSSQL do _much_ better in this area - that's what I call unsubstantiated.
You are making all sorts of claims without nary a link to back it up. Are you suggestion PG does better than MSSQL, Oracle and DB2 in planning while be constrained to replan on every single statement? The PG planner is specifically kept simple so that it is fast at its job, not thorough or it would adversely effect execution time more than it already does, this is well documented and always a concern when new features are proposed for it.
>From what you write further it's pretty clear you don't have a lot of understanding what happens under the hood.
Sticks and stones, is that all you have how about something substantial.
> And no, prepared statements are not what you read in Wikipedia. Not in all databases anyway.
Ok Mr. Unsubstantiated are we talking about PG or not? What does one use prepared statements for in PG hmmm, you know the thing you call the PG plan cache? How about something besides your claim that prepared statements are not in fact plan caches? Are you talking about completely different DB systems? How about you substantiate that?