planner: use NOW() as the upper bound in Histogram out-of-range estimation for datetime columns to avoid overestimate
- 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.
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`.
Before finally returning it, we use the `modify_cnt` as the upper bound, so the final result is `min(modify_cnt, total-rows/2)`:
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):
See the example below, it becomes much better immediately after adding today as its upper bound:
Contributor guide
Assessment
This issue has not been assessed yet.