Proposal: use Database Lab to control SQL query performance regression
The problem
Sometimes, for some SQL queries, we deal with a suddenly changed plan and degraded performance. In some cases it's quite difficult to answer the questions: do we really have the plan changed? What was the former plan and how better was it? When did degradation happen?
The proposed solution
With Database Lab, that is already used for Joe bot in the background, it is very easy to request a thin clone of production database with the state, say, "yesterday at 12:00". In parallel, we can request another thin clone with the state "2 days ago, at 12:00". This can be done in parallel.
For a given SQL, we can request to provide EXPLAIN plan, with details (this is what essentially Joe does all the time).
If we have a set of predefined SQL queries, which good performance we consider as critical to have, we can automate regression checks: in CI, on some event or on schedule, we can check all SQLs, one by one, against the set of thin clones, each corresponding to particular moment of time. Then we can compare plans and highlight degradation if any.
This is a preliminary proposal, to explore the interest in having such a tool. Comments and ideas are highly appreciated