search

cockroachdb / cockroach

CockroachDB — the cloud native, distributed SQL database designed for high availability, effortless scale, and control over data placement.
https://www.cockroachlabs.com
Other
29.96k stars 3.79k forks source link

sql: add time of last updated stats to explain analyze #57517

Closed awoods187 closed 3 years ago

awoods187 commented 3 years ago

One big source of potential slow queries is out of date statistics in the CBO. We aim to refresh these sufficiently often to provide good plans without impacting the cluster too much. However, sometimes, this isn't frequent enough. It can be tricky for developers to think to look at the last time statistics were refreshed. If they do, CREATE STATISTICS is an easy enough solution. We should consider making the last time statistics were updated more discoverable. One place to put this would be in explain analyze as it is a user trigger that this query may be underperforming and it can help clue them on the need to update statistics.

awoods187 commented 3 years ago

@mgartner points out we can also do this in EXPLAIN too.

RaduBerinde commented 3 years ago

Adding this to EXPLAIN is not hard but it makes the EXPLAIN output non-deterministic which messes up our tests. Trying to figure out a way around it.