pingcap / pingcap/tidb

planner: use NOW() as the upper bound in Histogram out-of-range estimation for datetime columns to avoid overestimate

Open
#64,614 2 comments 0 reactions 1 assignee Claimed by @terry1purcell View on GitHub
epic/cardinality-estimation sig/planner type/enhancement
Dominant language
Go
Stars
40.5k
Forks
6.2k
PR merge metrics
PR metrics pending

Description

## Enhancement
Please reach out to me to get the internal plan replayer file.

See the query plan below, we should use the `update_time` index, but the optimizer doesn't choose it due to its over-estimation.

The predicate in this query is like `update_time >= 11-12`, but we only collected stats before `11-04`, then this query triggered our Histogram out-of-range logic.

Image

Image

The problem here is that since the upper bound of this range is `+INF` (`(11-12, +INF)`), and then according to our out-of-range data distribution assumption (see the picture below), we estimate that its result is close to half of the total data (see the red box in that picture), which is like `total-rows / 2`.

Image

Before finally returning it, we use the `modify_cnt` as the upper bound, so the final result is `min(modify_cnt, total-rows/2)`:

Image

I just think maybe we could use "TODAY" as the upper bound for date/time columns in Histogram out-of-range estimation if there is no specified upper bound, which seems appropriate for most cases.
In this case, then the result should be more accurate (see the red box below):

Image

See the example below, it becomes much better immediately after adding today as its upper bound:
Image

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.