5 ms·

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

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 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). Prepared statements != cached execution plans.

SigmundA 7mo ago
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 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: https://en.wikipedia.org/wiki/Prepared_statement https://en.wikipedia.org/wiki/Prepared_statement
- 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 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 databases anyway. Go read it somewhere else.
  - SigmundA 7mo ago
    >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-cache https://www.ibm.com/docs/en/i/7.4.0?topic=overview-plan-cach... https://docs.oracle.com/en/database/oracle/oracle-database/19/tgsql/sql-processing.html#GUID-B3415175-41F2-4EBB-95CF-5F8B5C39E927 https://docs.oracle.com/en/database/oracle/oracle-database/1... https://learn.microsoft.com/en-us/sql/relational-databases/performance-monitor/sql-server-plan-cache-object?view=sql-server-ver17 https://learn.microsoft.com/en-us/sql/relational-databases/p... https://help.sap.com/docs/SAP_HANA_PLATFORM/6b94445c94ae495c83a19646e7c3fd56/f0aaab730a1540758a8f36c9aee2118a.html 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?
    - vladich 7mo ago
      https://www.postgresql.org/docs/current/runtime-config-query.html https://www.postgresql.org/docs/current/runtime-config-query... and then https://www.postgresql.org/docs/current/sql-prepare.html 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.
      - magicalhippo 7mo ago
        > 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-statements-trap/ https://www.michal-drozd.com/en/blog/postgresql-prepared-sta...
        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'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.

by vladich 7mo ago

SigmundA 7mo ago

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 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: https://en.wikipedia.org/wiki/Prepared_statement https://en.wikipedia.org/wiki/Prepared_statement

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 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 databases anyway. Go read it somewhere else.
- SigmundA 7mo ago
  >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-cache https://www.ibm.com/docs/en/i/7.4.0?topic=overview-plan-cach... https://docs.oracle.com/en/database/oracle/oracle-database/19/tgsql/sql-processing.html#GUID-B3415175-41F2-4EBB-95CF-5F8B5C39E927 https://docs.oracle.com/en/database/oracle/oracle-database/1... https://learn.microsoft.com/en-us/sql/relational-databases/performance-monitor/sql-server-plan-cache-object?view=sql-server-ver17 https://learn.microsoft.com/en-us/sql/relational-databases/p... https://help.sap.com/docs/SAP_HANA_PLATFORM/6b94445c94ae495c83a19646e7c3fd56/f0aaab730a1540758a8f36c9aee2118a.html 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?
  - vladich 7mo ago
    https://www.postgresql.org/docs/current/runtime-config-query.html https://www.postgresql.org/docs/current/runtime-config-query... and then https://www.postgresql.org/docs/current/sql-prepare.html 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.
    - magicalhippo 7mo ago
      > 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-statements-trap/ https://www.michal-drozd.com/en/blog/postgresql-prepared-sta...
      - 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'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.