cockroachdb / cockroachdb/cockroach
opt,import: newly imported tables have poor stats estimates
- Dominant language
- Go
- Stars
- 32.5k
- Forks
- 4.1k
- PR merge metrics
- PR metrics pending
Description
We recently saw a case where a TPCC workload was started just after the table imports completed, so there were no stats to begin with. One of the queries looks like this:
```
PREPARE p AS SELECT
count(DISTINCT s_i_id)
FROM
order_line JOIN stock ON s_w_id = $1:::INT8 AND s_i_id = ol_i_id
WHERE
ol_w_id = $1:::INT8
AND ol_d_id = $2:::INT8
AND ol_o_id BETWEEN ($3:::INT8 - 20:::INT8) AND ($3:::INT8 - 1:::INT8)
AND s_quantity < $4:::INT8;
```
This query has a "good" plan, which performs a constrained scan on `order_line` and then a lookup-join into `stock`. This plan scans a few hundred rows on average. The "bad" plan performs a hash-join between the constrained `order_line` scan, and a scan+select on the `stock` table. This plan scans ~100k rows on average, and significantly impacted the p99 latency for the cluster.
The good plan was chosen at first, but stats were collected on the `order_line` table first. Currently, we estimate that a table has 1000 rows when we haven't yet collected statistics for it, so `order_line` seemed very large in comparison with `stock`, making the bad plan look attractive. Once stats were collected for `stock` as well, the good plan was chosen once again. Since these tables are several TB, stats collection with throttling takes considerable time, and so the bad plan was chosen for multiple hours.
This problem probably could have been avoided if IMPORT added some initial table statistics, with just the row count for a table. This would likely be enough for the optimizer to make obvious choices, like choosing the lookup-join over the hash-join in this scenario. We could also consider changing our default row-count estimate for a table when other tables in the query (or even in the cluster) have far more than 1000 rows.
Jira issue: CRDB-38082
Epic CRDB-62881
Contributor guide
Assessment
This issue has not been assessed yet.