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 understand is for different parameters you _need_ different plans, and caching doesn't help with that. It can help with parsing, sure. Parsing is very fast though, in relation to planning. And you keep conflating "prapared" statements with plan caching. Ok.
>If you add a global cache, it will have only a marginal value
Please substantiate this, again all other major commercial RDBMS's do this and have invested a lot of effort and money into these systems, they would not do something that has marginal value.
Again I went through the era of needing to manually prepare queries in client code when it was the only choice as it is now in PG. It was not a marginal improvement when automatic global caching became available, it was objectively measurable via industry standard benchmarks.
You can also find other post complaining about prepared statement cache memory usage especially when libs and pooler auto prepare, the cache is repeated for every connection, 100 connections equals 100X cache size. Another advantage of a shared cache, this is obvious.
I will leave you with a quote from Bruce Momjian, you know one of the founding members of the PG dev team, in the thread I linked that you didn't seem to read just like the other links I gave you:
"I think everyone agrees on the Desirability of the feature, but the Design is the tricky part."
>The main issue you still do not understand is for different parameters you _need_ different plans, and caching doesn't help with that.
You still don't seem to be grasping what other more advanced systems do here and again don't seem to be reading any of the existing literature I am giving you. These systems will make different plans if they detect its necessary, they have MULTIPLE cached plans of the same statement and you can examine their caches and see stats on their usage.
These systems also have hints that let you disable, force a single generic, tell it how you want to evaluate specific parameters for unknown values, specific hard coded values etc. if you want to override their default behavior that uses statistics and heuristic to make a determination of which plan to use.
Please I beg you read what a modern commercial DB can do here and stop saying it doesn't help or can't be done, here is a direct link: https://learn.microsoft.com/en-us/sql/relational-databases/p...
>And you keep conflating "prapared" statements with plan caching.
Again we are talking about PG and the only way PG caches a plan is using prepared statements, in PG prepared statements and plan caching are the same thing, there is no other choice.
From your own link trying to gotcha me on PG plan caching config, first sentence of plan_cache_mode: "Prepared statements (either explicitly prepared or implicitly generated, for example by PL/pgSQL) can be executed using custom or generic plans."
The only other things a prepared statement does is skip parsing, which is another part of caching, and reduce network traffic from client to server. These things can be done with stored procedures in systems that have global caches and are shared across all connections and these systems still support the very rare situation of using a prepared statement, its almost vestigial now days.
Here is Microsoft Guidance on prepared statements in MSSQL now days:
"In SQL Server, the prepare/execute model has no significant performance advantage over direct execution, because of the way SQL Server reuses execution plans. SQL Server has efficient algorithms for matching current Transact-SQL statements with execution plans that are generated for prior executions of the same Transact-SQL statement. If an application executes a Transact-SQL statement with parameter markers multiple times, SQL Server will reuse the execution plan from the first execution for the second and subsequent executions (unless the plan ages from the plan cache)."
https://learn.microsoft.com/en-us/sql/relational-databases/q...
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.
>So, Mr Big Boy, now we can get to what a prepared statement in Postgres is.
Yeah not a gotcha at all mr teacher. I think you should stop posting low effort responses and examine your own opportunities for education that may have been missed here.
Lets get this straight prepared statements should not be conflated with caching, yet the only way to cache a plan and avoid a full parse is to use a prepared statement and it is by far the biggest reason to use it and why many poolers and libraries try to prepare statements.
Do you realize how ridiculous this is, here is PG's own docs on the purpose of preparing:
"Prepared statements potentially have the largest performance advantage when a single session is being used to execute a large number of similar statements. The performance difference will be particularly significant if the statements are complex to plan or rewrite"
"Although the main point of a prepared statement is to avoid repeated parse analysis and planning of the statement, PostgreSQL will force re-analysis and re-planning of the statement before using it whenever database objects used in the statement have undergone definitional (DDL) changes or their planner statistics have been updated since the previous use of the prepared statement."
The MAIN POINT of preparing is what I am conflating with it, yes...
If PG cached plans automatically and globally then settings like constraint_exclusion and enable_partition_pruning would not need to exist or at least be on by default because the added overhead of the optimizations during planning would be meaningless.
Seriously this whole thread is Brandolini's law in action you obviously can't articulate how PG is better because it does not have a global plan cache and act like I don't know how PG works? Get real buddy.
Are you going to post another couple sentences with no content or are you done here?
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 they are created for custom plans. "Custom plan" is a misnomer - having a "custom plan" means the query is replanned on each execution. It's a common misconception - even a sizeable portion of articles you can find on the internet miss this. But if you have a good reading comprehension, you can read, and, possibly, understand, this:
> A prepared statement can be executed with either a generic plan or a custom plan. A generic plan is the same across all executions, while a custom plan is generated for a specific execution using the parameter values given in that call.
here https://www.postgresql.org/docs/current/sql-prepare.html
You're also mixing up parsing and planning for some reason. Query parsing costs like 1/100 of planning, it's not nothing, but pretty close to it.
Even though you're just a rude nobody, it still may be useful for others, who may read this stupid conversation...
>You can't get a plan cache without a prepared statement, but you can get a prepared statement without a plan cache.
What is the purpose of a prepared statement without a plan cache? I thought parsing was a non issue? All thats left is a little extra network traffic savings.
I will for a second time quote the PG documentation that you linked btw of what the MAIN POINT of a prepared statement is according to the maintainers, I am not sure why I have to repeat this again:
"Although the main point of a prepared statement is to avoid repeated parse analysis and planning of the statement, PostgreSQL will force re-analysis and re-planning of the statement before using it whenever database objects used in the statement have undergone definitional (DDL) changes or their planner statistics have been updated since the previous use of the prepared statement.”
I am not sure what point you are trying to make other than worming your way out of your previous statements. Prepared statements are in fact plan caches and it is their MAIN purpose according the PG’s own documentation, you haven't given any other purpose for their existence, I gave the other two, one of which you dismissed, the third is not even listed in the PG docs and is also minor.
> It's not the same thing, and in most cases in Postgres prepared statements _do_not_ give you plan caching, because they are created for custom plans.
The default setting is auto which will cache the plan if the generic plan cost is similar to a custom one based on the 5 run heuristic. This is going to be most of the time on repeated simple statements that make up the bulk of application queries and why other database do this all the time globally without calling prepare. It is a large savings, not sure why you think this would not occur regularly and if you have any data to back this up I am sure everyone would like to see it, it would upset conventional thinking in other major commercial RDBMS’s with hard won gain over many years.
>You're also mixing up parsing and planning for some reason.
No I am not, you are obviously not comprehending what I said and cannot read the documentation I quoted which I had to repeat a second time here. I am not sure why you think I am mixing them up I was only trying to be gracious and include the other benefit of prepared statement, one of two thats left if it doesn't cache the plan, it avoids parsing which yes has a smaller impact, the third even less.
Also not everyone shares PG terminology, Oracle refers to what you call parsing as a soft parse (syntax check, semantic check) and parsing and planning as a hard parse (rewrite and optimizing, row source generation), you obviously have little experience outside of PG and seem to have a myopic view of what is possible in RDBMS systems and how these terms are used.
>Query parsing costs like 1/100 of planning, it's not nothing, but pretty close to it.
Again what is the point of a prepared statement if skipping parsing is meaningless and planning is not THE MAIN POINT?
>Even though you're just a rude nobody, it still may be useful for others, who may read this stupid conversation…
Further ad hominem and you call me rude, who are you to say this? How about you step off your high horse and learn something mr superior somebody. I was trying to debate in good faith and you insult me with zero substance, yeah this is a stupid conversation...