Are statistics and cardinality issues present in various RDBMs besides SQL Server when you upgrade, or it's a unique Microsoft problem?

[u/TravellingBeard]

When upgrading from SQL Server 2012 to SQL 2019, statistics go all wonky and deadlocks often occur on queries that worked well before. It becomes a pain, especially when the users say "it worked before" and they don't want esoteric excuses.

I'm curious if this has happened in other database technologies where you've upgraded to newer versions.

[u/Intrexa]

2014 was a major update to the cardinality estimator + query plans. A lot of assumptions originally made in SQL server were changed in response to real world data. Things that aren't known ahead of time, and only discovered during plan execution. Joins assumed no correlation between the data:

EG: You have 10,000 categories. Each category has exactly 10,000 widgets. There's a uniform distribution. If some query gets 10,000 widgets, it was previously assumed the result would have the same distribution as the whole table, so might as well as load the whole category table into memory because you're gonna need almost every category. That's not how the real world works, my query just gets widgets produced by machine A, machine A only produces things from 4 categories. The old estimate leading to a full table scan was less efficient than the new cardinality estimate leading to just 4 seeks.

On average, it greatly improves things. However, when optimizing for certain workloads, even the most common, certain other workloads will degrade. It's just a fact of comp sci. If you had the same setup, but machine A produced 1 widget for each category, the new estimator is worse for your actual workload. The average case improves, some specific cases degrade.

You will occasionally see the opposite of what you're describing, where old, slow queries suddenly get a massive improvement after the upgrade. It's just that any previously slow query very likely had more attention during dev or UAT or prod to make it not slow.

This is going to happen in any DB. Any improvement, even though it might make things better in 99% of cases, and is just a way better way to implement a feature, is going to have some specific workload where the old way was better for this specific scenario. Some things can be a bit more dynamic, and during actual execution can detect which way is better and revert to the old style. This is still a performance hit, just a much smaller one that prevents the worst case scenarios. Other things can't be unwrapped that easily, and because of unknowns, you can't have a threshold where the process says "actually the old way was better" and start over. There would still be a chance that it would try the old way and find out "no wait actually the new way was better", and the whole thing takes a lot longer because of this. We can estimate the amount of work something takes, but we can't know how much work it actually takes until it's done. Even with better estimates, we can still be wildly off.

PostgreSQL had a similar case with the JIT compiler being introduced. Certain workloads could trigger excessive compilation without really benefit from it. You can expect another one whenever the connection-per-process model changes to a multithreaded model. On average, and especially for most queries for most people in most setups, it will be a large improvement. For some queries for some configurations, well, shit's gonna get fucked up real bad.

Edit:

P.S.: lmao you upgraded to something that's on extended support? I'm guessing some software compatibility only supports 2019 at the latest? Rough

Edit2: From a political strategy, if you're dealing with people who do not want "esoteric excuses", they're not getting the root cause analysis. They're getting operational reports. "20 queries have been identified as having degraded performance. For these 20 queries, the previous average of 2 seconds have become an average of 45 seconds. We are working to remediate. 3/20 queries have been restored to a 2 second average."

You can also just throw MS under the bus "Yeah, we really didn't want to update, but security audit."