cockroachdb / cockroachdb/cockroach

stats: add explicit AOST syntax to ANALYZE

Open
#96,430 4 comments 0 reactions 0 assignees View on GitHub
C-enhancement docs-done docs-known-limitation T-sql-queries
Dominant language
Go
Stars
32.5k
Forks
4.1k
PR merge metrics
PR metrics pending

Description

**Is your feature request related to a problem? Please describe.**

When you run an explain command, the engine may report back saying to run ANALYZE and not CREATE STATS. If you run ANALYZE and look at the schema jobs, you'll see CREATE STATS WITH OPTIONS AS OF SYSTEM TIME. Running ANALYZE with AOST explicitly will break however, even though they are analogous.

**Describe the solution you'd like**

I understand that ANALYZE runs with AOST in the background and adding syntactic sugar is redundant but perhaps a bit confusing to the end user as executing CREATE STATS WITH AOST explicitly works. There's also an opportunity to run ANALYZE with a different AOST interval than default. Yes, then you can just run CREATE STATS with that interval but we also should strive for being consistent.

From Yahor:
> there could be value in overriding AOST that we add automatically. The main goal of adding AOST for stats collection is to use “inconsistent scan”, and for that purpose AOST -0.001ms is sufficient. However, one might be ok with running ANALYZE further into the past in order to utilize follower reads, and then ANALYZE AOST -5s or something would be good.

The syntax I imagine should be `ANALYZE t1 AS OF SYSTEM TIME`. I'm surprised CREATE STATS syntax is `CREATE STATISTICS "" FROM t1 WITH OPTIONS AS OF SYSTEM TIME` but there must be a good reason to use `WITH OPTIONS`, which is not common to the other uses of AOST for backups, SELECT, etc.

**Describe alternatives you've considered**

Run CREATE STATS WITH AOST instead.

**Additional context**

`CREATE STATISTICS "" FROM t1 WITH OPTIONS AS OF SYSTEM TIME '-0.001ms';`
and
`ANALYZE t1 WITH OPTIONS AS OF SYSTEM TIME '-0.001ms';` is not possible? I did try `ANALYZE t1 AS OF SYSTEM TIME '-0.001ms';` and it doesn’t work either.

The following however works

```sql
BEGIN;
SET TRANSACTION AS OF SYSTEM TIME follower_read_timestamp();
ANALYZE t1;
COMMIT;
```

Jira issue: CRDB-24121

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.