Zone maps and min/max indexes

Why does a zone map prune exactly as well as the data is sorted?

A zone map (a min/max index) stores, for each block of rows, the minimum and maximum of a column, and skips any block whose [min, max] cannot hold the value asked for. The cheapest possible index stores no keys at all, just an aggregate per block: a terabyte in 128 MB row groups is 8,192 entries per column.

Seen as a structure, a zone map is a set of intervals, and "which blocks could contain this value?" is a stabbing query. Its pruning power is the interval overlap between blocks, and overlap is set by how the data was ordered when it was written. Parcelo's files were written in arrival order, and tracking_id has nothing to do with arrival time, so every file's [min, max] spans nearly the whole domain and the "starts with 7F" report opens all 40,000.

A zone map is not an index. It is a measurement of how well you sorted, reported at query time as an I/O bill.

How much a zone map prunes, how one outlier zeroes it out, and the stronger summaries above min/max are on sparse indexes and zone maps.