cockroachdb / cockroachdb/cockroach

opt,import: newly imported tables have poor stats estimates

Open
#122,818 3 comments 0 reactions 0 assignees View on GitHub
A-import A-sql-table-stats C-enhancement O-support O-testcluster P-3 T-sql-foundations
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

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.