sql: "canary release" for SQL table statistics

[mgartner]

The optimizer relies heavily on SQL table stats to planning planning decisions. Consequently, plan regressions are more likely to occur when new stats are collected, e.g., when those stats are less representative of the actual data in the table.

To avoid plan regressions, we could follow the canary release pattern where new changes are released to a subset of users, and only promoted to the entire user base once deemed acceptable. For example, we could introduce the new stats for a subset of queries, and only promote the stats to be used by all queries once we have determined that there are no major regressions.

Thanks to @tbg for the inspiration here.

Jira issue: CRDB-52469

Epic CRDB-55121

[petermattis]

We could also consider providing a knob similar to INDEX VISIBILITY for visibility of a particular stats collection.

For example, we could introduce the new stats for a subset of queries, and only promote the stats to be used by all queries once we have determined that there are no major regressions.

Are you imagining we'd automatically determine there have been no major regressions? Is there a simple way to do that?

[mgartner]

We could also consider providing a knob similar to INDEX VISIBILITY for visibility of a particular stats collection.

We could, but the UX isn't clear to me because stats are automatically collected without a user necessarily knowing about it. An index is manually created which is the opportunity for the user to configure visibility.

Are you imagining we'd automatically determine there have been no major regressions? Is there a simple way to do that?

This is the difficult part. I was thinking something like: if a query's p99 with the new stats is more than 50% of the p99 with the old stats and the plan changed, then delete the new stats or make them "invisible". That should catch the most egregious regressions.

But how would we handle cases where some queries got slower and some got faster? Maybe the faster queries are more critical to the application than the slower ones. Collecting new, more accurate stats often leads to better query plans, so this is probably not all that uncommon.

[petermattis]

We could also consider providing a knob similar to INDEX VISIBILITY for visibility of a particular stats collection.

We could, but the UX isn't clear to me because stats are automatically collected without a user necessarily knowing about it. An index is manually created which is the opportunity for the user to configure visibility.

Good point. I'm not sure of the use case for making stats visible to some fraction of queries. I was mainly just thinking about the similarity with index visibility and that having a similar knob could prove useful in an incident if it isn't difficult to add. I think we're going to want to be able to canary new stats to some fraction of the queries, so this knob probably exists in some form whether we expose it or not.

This is the difficult part. I was thinking something like: if a query's p99 with the new stats is more than 50% of the p99 with the old stats and the plan changed, then delete the new stats or make them "invisible". That should catch the most egregious regressions.

What if p99 for a query gets worse but p50 gets better?

But how would we handle cases where some queries got slower and some got faster? Maybe the faster queries are more critical to the application than the slower ones. Collecting new, more accurate stats often leads to better query plans, so this is probably not all that uncommon.

No good ideas are jumping out at me. If the queries are different fingerprints then you could imagine letting those different fingerprints use different stats, though that would introduce a ton of complexity itself. I believe for plan management we'll want to be tracking query plans by fingerprint. Maybe that's necessary for canarying new table statistics as well. Is "canarying new table statistics" even the right framing for the problem? An alternative framing is "canarying a new query plan".

[rytaft]

@lunevalex had an interesting idea about how we could combine this idea for canary stats with partial stats.

Once we support collecting partial stats with arbitrary predicates, our plan is to trigger automatic partial stats collection whenever a SQL query is executed where the estimated row count of a constrained scan is significantly different from the actual row count (as described in the RFC). Since this plan for using execution feedback to trigger partial stats collections allows us to "repair" bad stats, we can use that to our advantage to make a very simple version of canary stats still effective. The idea is that we'd roll out new full stats slowly (e.g., first to 1% of queries, then 10%, then 100% over the course of several minutes or hours). If the new full stats are bad, a small percentage of queries would experience the bad stats, but those queries would trigger partial stats collections that would repair those stats. By the time the rest of the queries started using the new stats, they would have already been largely repaired by partial stats.

This means we can still benefit from doing canary rollouts without needing to monitor performance or roll back bad stats (at least as an MVP).

[petermattis]

Since this plan for using execution feedback to trigger partial stats collections allows us to "repair" bad stats, we can use that to our advantage to make a very simple version of canary stats still effective.

"Bad stats" in this context are stats which lead to bad cardinality estimation, right? Do we ever encounter scenarios where better cardinality estimation leads to worse query plans? I don't have anything specific in mind. Sort of sounds like a bug if that were to occur. Perhaps something around a query plan that causes more contention even if it results in fewer rows being accessed.

[rytaft]

"Bad stats" in this context are stats which lead to bad cardinality estimation, right?

That's right.

Do we ever encounter scenarios where better cardinality estimation leads to worse query plans? I don't have anything specific in mind. Sort of sounds like a bug if that were to occur. Perhaps something around a query plan that causes more contention even if it results in fewer rows being accessed.

It can happen, typically when our cost model is incorrectly weighting certain plan features too highly or doesn't take certain factors into account (like contention, as you mention). I haven't seen any cases where better estimation leads to catastrophically bad plans, though.