[brasetvik]

> Its was actually very surprising to me to find PG does not cache query plans but instead replans on every execution!

If you used prepared statements, `plan_cache_mode` (since Postgres >=12) can be configured.

It defaults to auto, which will generate a generic plan after 5 (iirc) executions.

If set to `force_generic_plan`, it'll make a generic plan right away. If set to `force_custom_plan`, it'll always do planning. The latter can be useful for queries where the parameters of the prepared statement can have very different selectivities.

[SigmundA]

Prepared statements are only on a single connection (Session), its actually surprising that PG doesn't even cache prepared statements always!

In MS SQL server plans are always cached unless told not to this is across connections, if it sees the same sql statement its will skip planning, this goes way back. If you go back far enough it only did that for stored procedures so there was a push to do everything through sprocs for performance, not so much in the last 20 years. Using prepared statements is for backward compatibility as is has almost no performance advantage due to modern plan caching.

I think PG finally has sprocs but still no plan caching even for them unless somethings changed.

PG has no way to share plans between worker processes AKAIK. I believe the lack of plan caching is noticed more with heavy jit optimizing performance where PG will use LLVM now which has a relatively high cost.

[okr]

One could argue, always caching plans could be costly resource wise and you need to administer timeouts and cache sizes, while when associated with a session, you get the cleanup for free.