4 ms·

Hashing incoming text statements and storing the plan for reuse seems like an obvious optimization that does not require billions in R&D, you just copy the well

Hashing incoming text statements and storing the plan for reuse seems like an obvious optimization that does not require billions in R&D, you just copy the well known behavior of the existing databases. Is there some complexity I am missing that would require huge R&D , like its too hard to share the plan and procedures across sessions or the hashing algo would have too much overhead?

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 ago
  SQL 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 ago
    the 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 ago
      Or 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 ago
        I 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 ago
  Postgres 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 ago
    MSSQL does this as well, it will evict plans based on significant statistic changes and does parameter sniffing to decide on reusing a plan.
jeltz 7y ago
That kind of simple plan cache you imagine has rather limited usage, because it can only handle non-parametrized queries without risking regressions in query plan (e.g. PostgreSQL may be able to find a more optimal plan for "SELECT * FROM t WHERE x = 1" than to "SELECT * FROM t WHERE x = $1", even when the parameters are sent separately in the protocol). BTW, this is also currently an issue with prepared statements in PostgreSQL, so they can be a performance loss compared to re-planning the query (there are some very simple heuristics which tries to solve it but it is far from perfect). Sure, it is possible to apply advanced techniques to try to guess when re-planning is worth it. Alternatively you can make it an option for PostgreSQL to cache parametrized queries too and accept the possible regressions, but that becomes less interesting due to most ORMs sending ["SELECT * FROM t WHERE x = 42"] rather than ["SELECT * FROM t WHERE x = $1", 42] which means you cannot simply hash the string. Again it is possible to do but I think you underestimate the work necessary for it to be a widely useful feature. And on top of this there are probably legacy code reasons like PostgreSQL process based architecture which makes it even more work.
- manigandham 7y ago
  SQL Server automatically replans cached queries when statistics change. It's not a complicated process too hook into stats rebuild and follow up with a concurrent replan. Also databases are designed for SQL, not ORMs. Not using parameterized queries at this point is just archaic and there are lot more problems then query caching to deal with first. The commercial databases can also tokenize input strings and parameterize them after the fact, this is what happens when you submit things as plain text and still use a cached plan.

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 ago
SQL 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 ago
  the 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 ago
    Or 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 ago
      I 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 ago
Postgres 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 ago
  MSSQL does this as well, it will evict plans based on significant statistic changes and does parameter sniffing to decide on reusing a plan.

jeltz 7y ago

That kind of simple plan cache you imagine has rather limited usage, because it can only handle non-parametrized queries without risking regressions in query plan (e.g. PostgreSQL may be able to find a more optimal plan for "SELECT * FROM t WHERE x = 1" than to "SELECT * FROM t WHERE x = $1", even when the parameters are sent separately in the protocol). BTW, this is also currently an issue with prepared statements in PostgreSQL, so they can be a performance loss compared to re-planning the query (there are some very simple heuristics which tries to solve it but it is far from perfect). Sure, it is possible to apply advanced techniques to try to guess when re-planning is worth it. Alternatively you can make it an option for PostgreSQL to cache parametrized queries too and accept the possible regressions, but that becomes less interesting due to most ORMs sending ["SELECT * FROM t WHERE x = 42"] rather than ["SELECT * FROM t WHERE x = $1", 42] which means you cannot simply hash the string. Again it is possible to do but I think you underestimate the work necessary for it to be a widely useful feature. And on top of this there are probably legacy code reasons like PostgreSQL process based architecture which makes it even more work.

manigandham 7y ago
SQL Server automatically replans cached queries when statistics change. It's not a complicated process too hook into stats rebuild and follow up with a concurrent replan. Also databases are designed for SQL, not ORMs. Not using parameterized queries at this point is just archaic and there are lot more problems then query caching to deal with first. The commercial databases can also tokenize input strings and parameterize them after the fact, this is what happens when you submit things as plain text and still use a cached plan.