Statistics stores

Why are scan-based statistics stale where it matters most, and what makes write-time statistics exact?

Statistics are summary facts about data: row count, smallest and largest value per column, number of nulls, number of distinct values. A planner uses them to choose a cheap way to run a query. Two production models exist, and arithmetic decides between them.

Model 1: scan-based collection

A background job (such as ANALYZE) reads the data and recomputes the numbers. This costs O(data) per refresh. Something that costs O(data) cannot run often.

So scan-based statistics are stale by construction, and the part that matters: staleness is worst exactly where data changes fastest. The optimizer is most confidently wrong about your most active tables. That is not a tuning mistake. It is the structure of the mechanism. (This explains Hana's problem 1: running ANALYZE more often only moves the cost, and the table changes faster than any scan can follow.)

Model 2: write-time collection

The writer computes statistics for each file as it writes it, and stores them in the manifest. This costs O(new data), is exact for the files it describes, and composes by merging.

The requirement: the statistic must be mergeable, meaning the number for two files can be computed from the numbers for each file, without re-reading the data.

# Per-file statistics from two files:
A = {"rows": 1000, "min": 3, "max": 90, "nulls": 4}
B = {"rows":  500, "min": 1, "max": 70, "nulls": 0}

merged = {
    "rows":  A["rows"] + B["rows"],        # 1500   (counts add)
    "min":   min(A["min"], B["min"]),      # 1
    "max":   max(A["max"], B["max"]),      # 90
    "nulls": A["nulls"] + B["nulls"],      # 4
}

Min, max, counts, sums and null counts all merge trivially. Exact distinct counts and histograms do not. If file A has 100 distinct customer IDs and file B has 100, the merged count could be anything from 100 to 200, depending on the overlap. You cannot know it without the data, and sampling does not rescue it: there are proven lower bounds showing that no estimator gets distinct counts right across all distributions without reading almost all of the data.

That is exactly why HyperLogLog and theta sketches (small structures that estimate distinct counts and can be merged) appear inside manifests. Sketches have been used as a way to give approximate query answers. Here they have a second role: they make incremental metadata possible. Their real property is not approximation. It is mergeability. Approximation is the price paid for it.

What the planner does with them

How the optimizer uses these numbers is query planning's question. Two of its findings set limits on what better statistics can buy: the planner needs correctly ordered alternatives, not accurate numbers, and cardinality errors compound through a join tree (four joins each off by 3× give 3⁴ = 81×), so past three or four joins only adaptive re-planning helps.

This mechanism's central conversion, in one example

Statistics give the sharpest illustration of the main idea of this mechanism.

• As an advisory annotation, a wrong statistic causes a slow query that nobody diagnoses for a year.
• Written into the manifest and used for file skipping, a wrong statistic causes a missing row, which is noticed at once.

Scan-based stats are stale where it matters most. Write-time stats are cheap and mergeable, which is why sketches ended up inside manifests.