alt Hacker NewsYou 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...