3 ms·
AIUI, Postgres has had transactional DDL from early on, while other DBMSes didn't. That means that different Postgres sessions can see different schemas. So I c
by mjw1007 7y ago
AIUI, Postgres has had transactional DDL from early on, while other DBMSes didn't. That means that different Postgres sessions can see different schemas. So I can easily believe that sharing plans across sessions would have been more difficult than for other systems, yes.
There are presumably also concerns that the same text can have different meanings in different sessions because of different per-session configuration, like the search path.
Nowadays, I think it's more that the gain isn't terribly large.
Parsing SQL is pretty cheap.
Normally with PostgreSQL you re-plan for each execution anyway. If you're going to ask it to reuse plans, the overhead of planning once per session is probably small enough that reducing it further doesn't gain much.
And with modern core counts and scalability concerns, there's a lot to be said for per-process rather than global caches anyway.
- SigmundA 7y agoSQL server has had transactional DDL for as long as I remember. MSSQL also uses per session settings as part of the cache key. This can be confusing if you think its only based on statement text and wondering why two different client are getting different plans, its because they have different settings. The planning overhead would completely destroy some applications I have worked on. Some more complex queries execute extremely fast but take some time to plan. Its hard for me to believe planner is so much faster as to be a non issue. Global caches are key to db performance thats why PG has shared buffers. MSSQL is single process multithreaded though while PG is process per connection so I would imagine that complicates shared data structures somewhat.
- gfody 7y agothe penalty for using a cached plan that is no longer optimal can be devastating, I've often had to use "with recompile" to get around this on SQL Server - I don't think global plan cache is a no-brainer, the scenario you describe is probably better served by a short-lived results cache
- cogman10 7y agoOr worse, I've ran into cases where just enough "bad" parameters flow into the planner to topple the statistics on a previously well behaving stored proc. SQL server does not recover quickly after a cached plan goes wrong. More than once, either adding "WITH RECOMPILE" or manually recompiling a SP has fixed our performance issues. "Everyone has a plan until they get punched in the face" - Mike Tyson
- SigmundA 7y agoI have rarely run into this issue, but they do occur which is why WITH RECOMPILE exists. The majority of the time caching the plans is a benefit and therefore has been the default behavior with a opt out option. With that kind a of plan instability I start looking at query hints though which PG doesn't have either...
- munk-a 7y agoPostgres can operate over transaction DDL but in most cases I think this would be a fine thing to isolate over - the more worrying case is when different transaction spaces have different understandings of row statistics - PG does use row statistics to inform planning so it's not inconceivable that sharing a plan between two queries running in vastly different data states would result in a highly inefficient query.
- SigmundA 7y agoMSSQL does this as well, it will evict plans based on significant statistic changes and does parameter sniffing to decide on reusing a plan.