5 ms·

MS SQL Server hashes all incoming statements and looks them up in global shared cache, essentially doing what Redis does without having to deal with the hash di

MS SQL Server hashes all incoming statements and looks them up in global shared cache, essentially doing what Redis does without having to deal with the hash directly. The cache will get evicted based on time with no use and memory pressure etc. I am actually kind of surprised to find that Postgresql doesn't do this also, wow, I thought pretty much every modern RDBMS did this.

manigandham 7y ago
MSSQL (and others like Oracle) have 100s of millions in research and development invested by now. PG is great for 80% of uses but it won't match the advanced performance scenarios.
- SigmundA 7y ago
  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...
    - 7y ago
  - 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.
paulddraper 7y ago
> MS SQL Server That applies to queries but not more general TSQL with branching logic, etc. > wow, I thought pretty much every modern RDBMS did this. Both PostgreSQL and MySQL have plan caches using prepared statements. (SQL PREPARE statement) PostgreSQL further allows tuning of whether the query plan is specialized based on parameter values.
- SigmundA 7y ago
  >That applies to queries but not more general TSQL with branching logic, etc. That is not my understanding, all of the TSQL including procedural logic is "compiled" but it is not optimized because it is not a set based operation. However no parsing or binding needs to be done on it if reused. The entire TSQL statement including branching logic is run through the parser and algebrizer and turned into a binary form that the optimizer can work on then cached. >PostgreSQL further allows tuning of whether the query plan is specialized based on parameter values. SQL server has prepared statement but they are basically unnecessary there is no need to manually tell it to prepare and reuse as it will do so on its own, it mostly there for backward compatibility. SQL server will also optimize based on parameter values, lookup parameter sniffing, it can store multiple plans and may not use a stored plan based on parameters. It will also potentially auto parameterize sql statements that don't use parameters so they can be reused. Again I have used sql server going back to 6.5 when it didn't cache ad-hoc queries and you had to use sprocs to get caching. During that time almost every app went through sprocs for every call because of the overhead of parsing and planning, its was like a 2x speedup. Nowdays we have a lot more network memory and cpu, I still don't want my db server wasting it on parsing and planing the same statements over and over for no good reason. What programming language even interpreted doesn't keep some optimized ready to run version around for repeated calls and instead reparses and binds on every hit?
  - paulddraper 7y ago
    > parsing and planing the same statements over and over...What programming language even interpreted...reparses and binds on every hit? Yeah, I agree. But I typically don't see it as a big problem because (1) queries are short so parsing is nothing (2) in some cases you'll want query plans to depend on parameter values, which hurts any cache rate (3) personally I heavily batch operations, e.g. a big insert is simply: INSERT INTO example SELECT * FROM unnest($?::int[], $?::text[]) P.S. AWS Aurora PostgreSQL has a plugin with global query plan caching and management. https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/AuroraPostgreSQL.Optimize.html https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide...

manigandham 7y ago

MSSQL (and others like Oracle) have 100s of millions in research and development invested by now. PG is great for 80% of uses but it won't match the advanced performance scenarios.

SigmundA 7y ago
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...
  - 7y ago
- 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.

paulddraper 7y ago

> MS SQL Server That applies to queries but not more general TSQL with branching logic, etc. > wow, I thought pretty much every modern RDBMS did this. Both PostgreSQL and MySQL have plan caches using prepared statements. (SQL PREPARE statement) PostgreSQL further allows tuning of whether the query plan is specialized based on parameter values.

SigmundA 7y ago
>That applies to queries but not more general TSQL with branching logic, etc. That is not my understanding, all of the TSQL including procedural logic is "compiled" but it is not optimized because it is not a set based operation. However no parsing or binding needs to be done on it if reused. The entire TSQL statement including branching logic is run through the parser and algebrizer and turned into a binary form that the optimizer can work on then cached. >PostgreSQL further allows tuning of whether the query plan is specialized based on parameter values. SQL server has prepared statement but they are basically unnecessary there is no need to manually tell it to prepare and reuse as it will do so on its own, it mostly there for backward compatibility. SQL server will also optimize based on parameter values, lookup parameter sniffing, it can store multiple plans and may not use a stored plan based on parameters. It will also potentially auto parameterize sql statements that don't use parameters so they can be reused. Again I have used sql server going back to 6.5 when it didn't cache ad-hoc queries and you had to use sprocs to get caching. During that time almost every app went through sprocs for every call because of the overhead of parsing and planning, its was like a 2x speedup. Nowdays we have a lot more network memory and cpu, I still don't want my db server wasting it on parsing and planing the same statements over and over for no good reason. What programming language even interpreted doesn't keep some optimized ready to run version around for repeated calls and instead reparses and binds on every hit?
- paulddraper 7y ago
  > parsing and planing the same statements over and over...What programming language even interpreted...reparses and binds on every hit? Yeah, I agree. But I typically don't see it as a big problem because (1) queries are short so parsing is nothing (2) in some cases you'll want query plans to depend on parameter values, which hurts any cache rate (3) personally I heavily batch operations, e.g. a big insert is simply: INSERT INTO example SELECT * FROM unnest($?::int[], $?::text[]) P.S. AWS Aurora PostgreSQL has a plugin with global query plan caching and management. https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/AuroraPostgreSQL.Optimize.html https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide...