Zone Maps and Pruning

Zone Maps and Min/Max Block Pruning

Every DuckDB 61,228 segment records the minimum and maximum of its values, a zone map (min-max index), and the scan skips zones a filter rules out. The payoff depends on order, as ten copies of the 390,737 sample events show:

zonemap.py: one filter on time-ordered and shuffled copies of the eventsPython

import timeit, duckdb
con = duckdb.connect("zonemap.duckdb")
con.execute("SET threads = 1; ATTACH 'booknest.duckdb' AS b (READ_ONLY)")
ten = "SELECT e.* FROM b.order_events e, range(10)"         # ten copies of every event
con.execute(f"CREATE OR REPLACE TABLE ev_sorted AS {ten} ORDER BY ts")
con.execute(f"CREATE OR REPLACE TABLE ev_shuffled AS {ten} ORDER BY hash(event_id, range)")
con.execute("CHECKPOINT")
for t in ("ev_sorted", "ev_shuffled"):
    groups, hits = con.sql(f"""SELECT count(DISTINCT row_group_id),
        count(DISTINCT row_group_id) FILTER (WHERE stats LIKE '%Max: 2026-06%')
        FROM pragma_storage_info('{t}')
        WHERE column_name = 'ts'""").fetchone()                # per-segment min/max of ts
    q = f"SELECT count(*) FROM {t} WHERE ts >= '2026-06-01'"
    ms = min(timeit.repeat(lambda: con.execute(q).fetchone(), number=1, repeat=7)) * 1000
    print(f"{t:11} {hits:2} of {groups} row groups may match: "
          f"{con.execute(q).fetchone()[0]:,} rows in {ms:5.2f} ms")

Output

ev_sorted    2 of 32 row groups may match: 214,760 rows in  0.91 ms
ev_shuffled 32 of 32 row groups may match: 214,760 rows in 13.44 ms

The events end on 30 June 2026, so only zones whose maximum falls in June can match. In time order 2 of 32 row groups qualify and the query ran 12 to 41 times faster across eight runs; shuffled, every zone spans all 18 months. Zone maps are nearly free but need clustered data: load in time order or sort on the filter column. PostgreSQL 1,289 's opt-in equivalent is the BRIN index (BRIN Indexes); Parquet 129 keeps the same statistics (Statistics and Pushdown).

MENU