[SigmundA]

Yes manual query preparation by client [1] is what you did in MSSQL server up until v7.0 I believe, which was 1998 when it started doing automatic caching based on statement text. I believe it also cached stored procedures before v7.0 which is one reason they were recommended for all application code access to the database back then.

MSSQL server also does parameter sniffing now days and can have multiple plans based on the parameters values it also has a hint to guide or disable sniffing because many times a generic plan is actually better, again something else PG doesn't have, HINTS [2].

PG being process based per connection instead of thread based makes it much more difficult to share plans between connections and it also has no plan serialization ability. Where MSSQL can save plans to xml and they can be loaded on other servers and "frozen" to use that plan if desired, they can also be loaded into plan inspection tools that way as well [3].

1. https://learn.microsoft.com/en-us/sql/relational-databases/n...

2. https://learn.microsoft.com/en-us/sql/t-sql/queries/hints-tr...

3. https://learn.microsoft.com/en-us/sql/t-sql/queries/hints-tr...

[mxey]

PostgreSQL shares other caches between processes so they probably could have a global plan cache if they wanted. I wonder why they don’t though.

One possible reason is that the planner configuration can be different per connection, so the plans might not transfer

[SigmundA]

In MSSQL Server part of the plan match is the various session/connection options, if they are different there are different plans cached.

I believe the plan data structure PG is intimately tied to process space memory addresses since it was never thought to share between them and can even contain executable code that was generated.

This makes it difficult to share between processes without a heavy redesign but would be a good change IMO.

[anarazel]

> PostgreSQL shares other caches between processes so they probably could have a global plan cache if they wanted. I wonder why they don’t though.

> One possible reason is that the planner configuration can be different per connection, so the plans might not transfer

That's part of it, another big part is that the transactional DDL makes it more complicated, as different sessions might require different plans.