TiFlash: optimize full-table MAX(CHAR_LENGTH) scans on string columns

Problem

Production workloads include full-table scans that compute MAX(CHAR_LENGTH(col)) on many string columns of a large table. In a few seconds TiFlash can read tens to hundreds of gigabytes, saturate cloud-disk read bandwidth, and stall other reads and writes on the same nodes.

Typical SQL:

SELECT
  MAX(CHAR_LENGTH(c1))  AS c1,
  MAX(CHAR_LENGTH(c2))  AS c2,
  MAX(CHAR_LENGTH(c3))  AS c3,
  -- ... a dozen or more string columns
  MAX(CHAR_LENGTH(c16)) AS c16
FROM t;

The same pattern appears on other large tables, with anywhere from a few to more than ten columns. The intent is always “the maximum character length of each string column”.

Query characteristics

Aspect	Observation
Semantics	Max character length per column; often used to size VARCHAR for downstream schemas
Access path	TiFlash MPP full table scan, no WHERE / LIMIT
Column types	All strings; a dozen-plus columns scanned together
Plan	TableFullScan → Projection(char_length × N) → HashAgg(max × N)
Cadence	Typically once per table, not a steady OLAP load
Resources	Per-request Read RU often exceeds (10^6), peak over (7 \times 10^6)

Observed scale of one run:

• About 1.8 billion rows, 16 columns, ~373 GB
• TableScan ~7 s; 7 TiFlash nodes (example: 32C / 256 GB, cloud-disk baseline read bandwidth 1250 MB/s)
• ~7.5 GB/s decompressed scan per node, above the instance’s disk read cap

Approximate per-node MPP stats:

Operator	Rows	Outbound
TableScan	~260 million	~66 GB strings
Projection	~260 million	~38 GB Int64
HashAgg	1	144 B

Why this SQL is expensive

The bottleneck is reading string columns from disk, not the aggregation.

1. Every value must be read; packs cannot be skipped
   There is no filter, so late materialization and RS / MinMax do not help. MinMax stores lexicographic min/max of string values, which is unrelated to length, so packs that cannot refresh the global max still cannot be skipped.

2. CHAR_LENGTH must scan UTF-8 payload
   TiFlash stores strings in DMFile as two streams: StringSizes (per-row length) and chars (payload).

   • LENGTH() uses offsets only; the function already ignores data.
   • CHAR_LENGTH() (utf8mb4) goes through lengthUTF8 and counts code points; TableScan still reads both streams.

3. Columnar layout does not help this query
   Columnar storage avoids reading unused columns, but the selected columns are the fat string columns. Scanning a dozen of them in parallel saturates disk bandwidth.

4. Large intermediates
   Projection materializes ~1.8 billion rows × 16 Int64s (~38 GB per node) before MAX. It pipelines with Scan, so wall time is still IO-bound, but extra CPU and memory are spent.

5. Instance bandwidth is saturated
   373 GB / 7 nodes / ~7 s ≈ 7.5 GB/s decompressed per node. Disk baseline is 1.25 GB/s; even compressed reads overshoot, and other IO on the node (including wait-index) jitters.

Workarounds (application side, brief)

Approach	Notes
Decide whether a true global max is required	Sampling or scanning hot partitions is often enough to size downstream VARCHAR
Rewrite SQL: fewer columns / batches	Similar total IO, lower peak bandwidth
Resource Control	Put these scans in one resource group and throttle. The query does not get faster, but the disk is less likely to saturate
Do not rely on adding a STORED generated column later	TiDB currently rejects ALTER TABLE ADD COLUMN ... STORED (error 3106). VIRTUAL can be added, but TiFlash still expands it and reads the base strings
Materialize length only if the query is repeated	Define INT AS (CHAR_LENGTH(c1)) STORED at CREATE TABLE, or maintain a regular INT column on the write path, and query MAX(length_col). The optimizer will not rewrite MAX(CHAR_LENGTH(c1)) to the generated column

For a one-shot probe: prefer sampling / splitting columns / throttling. Do not change the table just to run the query once.

Proposed engine optimizations

Ordered by payoff for this SQL.

1. Pack-level max_len skip (highest priority)

Today
MinMaxIndex stores lexicographic string ranges. MAX(CHAR_LENGTH(col)) cannot skip packs from that.

Proposal
On pack write, also record max_byte_len / max_char_len (cost similar to existing MinMax). While scanning, keep a running max:

• If pack_max_len <= running_max → skip the whole pack (including compressed chars)
• Otherwise read the pack and update the running max

Effect
After a few packs that contain longer strings, most remaining packs can be skipped. The more concentrated the length distribution, the more is skipped. This is the main way to turn a hundreds-of-GB full read into “pack metadata + a few packs”.

Notes

• Keep CHAR_LENGTH and LENGTH stats separate (UTF-8 character count ≠ byte length), or store both.
• Delta / memtable still need a full scan or equivalent stats.
• Needs a new pack-stat format and a compatible reader.

2. Read StringSizes only, skip chars

Today
LengthImpl already uses offsets only; TableScan still reads both sizes and chars.

Proposal
Push “length only, no payload” down to the DMFile reader:

• LENGTH(col): read StringSizes (and the nullmap), skip chars.
• CHAR_LENGTH(col): utf8mb4 still needs chars by default; if a pack has an all_ascii flag (no high-bit bytes), ASCII packs can also read sizes only.

Effect
In the observation above, payload dominates the ~66 GB outbound per node; sizes are ~4 B/row. Reading sizes only can drop IO by an order of magnitude for those columns. This is general, not limited to MAX().

Notes
Scan / Projection must declare “this column needs length, not payload”. CHAR_LENGTH on non-ASCII packs must fall back to reading chars.

3. Fold CHAR_LENGTH into HashAgg

Today
The plan is Scan → Projection(char_length × N) → Agg(max × N). Projection emits Int64 columns for every row.

Proposal
Recognize MAX(CHAR_LENGTH(col)) / MAX(LENGTH(col)) and keep N running maxes inside Agg, without materializing all intermediate rows. Better: TableScan emits length columns directly and Agg only computes max.

Effect
Saves CPU / memory (~38 GB Projection outbound per node in the observation). Wall time barely changes when already IO-bound. Treat this as a companion to (1) / (2), not the fix for disk saturation.

4. Persist length in column metadata or a hidden column (optional)

Write length alongside the string (hidden column, or actually store a STORED generated column in TiFlash). MAX(length_col) becomes an integer-column scan, often an order of magnitude smaller after compression.

Large surface: replication decode, schema, file format, and alignment with TiDB generated-column semantics. TiDB cannot add STORED generated columns via ALTER TABLE; TiFlash inserts only a placeholder for VIRTUAL columns and still reads the base strings. After pack-level max_len skip, per-row length persistence is not required for this SQL; keep it as a longer-term, broader capability.

Not recommended as the primary engine fix

• Resource Control: protects the cluster; does not reduce IO of a single query.
• Rewriting to LENGTH: close to CHAR_LENGTH on ASCII data, but TableScan still reads chars today, so only CPU is saved until (2) lands.
• Splitting into several queries with fewer columns: lowers peak, increases total time; a scheduling tactic.

Suggested order

1. Pack-level max_len skip
2. Sizes-only scan
3. Fold Projection into Agg as a companion

The issue names the DMFile reader, MinMaxIndex, LengthImpl, TableScan, Projection, and HashAgg, but no files or tests. First trace those components and agree on one scoped optimization; done means the selected optimization is implemented with correctness and scan-efficiency coverage for MAX(CHAR_LENGTH) or related length queries.