Improve rough set filtering for temporal columns with sparse extreme values

[JaySon-Huang]

Enhancement

Motivation

Applications sometimes use an extreme date as a sentinel value. For example, 2100-01-01 00:00:00 may represent an unsettled record, while normal values in the same DATETIME, TIMESTAMP, or DATE column are concentrated in a recent time range.

Although these sentinel values may be rare, they can significantly reduce the effectiveness of TiFlash pack-level rough set filtering. A stable pack contains about 8,192 rows by default. If one out of every 10,000 rows is a sentinel value and the values are uniformly distributed, approximately 55.9% of packs contain at least one sentinel. The ordinary min-max value of those packs is then extended to 2100.

As a result, a narrow query over recent data cannot exclude many historical packs using the ordinary min-max index. Those packs remain Some and must be read and filtered row by row, increasing scanned data and query latency.

This problem is not specific to 2100-01-01, so the ordinary min-max index should not hard-code or ignore a particular application value. Doing so could also produce incorrect results for queries that intentionally access that value.

Proposed enhancement

Add an optional pack-level trim_minmax index for DATETIME, TIMESTAMP, and DATE columns.

The initial design uses an effective half-open date range of [1900-01-01 00:00:00, 2100-01-01 00:00:00):

• The ordinary min-max index remains unchanged and continues to represent all non-NULL, non-deleted values.
• The trim min-max index records min/max values only within the effective date range.
• The actual effective bounds are persisted with each DMFile instead of being inferred from the current runtime default.
• Each pack records whether values were trimmed below or above the effective range. These directional marks are used to conservatively correct rough-check results and preserve None, Some, and All semantics.
• A reader uses trim min-max only for predicates whose behavior for out-of-range values can be proven, including eligible equality, IN, bounded-range, and one-sided range predicates.
• Unsupported predicates, old DMFiles, missing or invalid metadata, and queries outside the eligible range fall back to the ordinary min-max index.

The feature should not change SQL semantics or require users to create an index through DDL. It should be transparent to queries and independently controllable for writing and reading during rollout.

Compatibility and rollout

• Store trim data separately from the ordinary .idx payload so old readers continue to use the existing min-max index.
• Initially write trim indexes only for DMFile V3 / MetaV2.
• Do not require rewriting historical DMFiles. Coverage can grow naturally through merge delta, compaction, split, and GC.
• Keep an immediate read-side fallback to the ordinary min-max index.
• Support rolling upgrade and rollback with independent read/write feature switches.

Expected benefit

For data with good temporal locality, trim min-max should restore pack pruning that is otherwise lost because of sparse extreme values. Narrow time-range queries should scan close to the no-sentinel baseline, while workloads without extreme values should see no significant regression.

Validation should compare trim reads enabled and disabled using identical result sets, rough-set pack results, scanned rows and bytes, query latency, index size, and write overhead.