Impala Table Statistics — COMPUTE STATS and CBO Correctness

Why it matters

Bad plans from missing stats are the #1 cause of Impala performance surprises. A query that should take seconds takes hours because Impala shuffled a huge table it thought was small.

The asymmetry is what makes this the first thing to check. A plan built on good statistics that turns out slightly wrong costs you a percentage. A plan built on no statistics at all can be wrong by orders of magnitude, because the planner's default assumption — that an unmeasured table is small enough to broadcast — is exactly backwards for the fact tables people forget to analyze. Every executor then receives a full copy of a table nobody sized, builds a hash table from it, and either spills to disk or trips its memory limit. The query does not fail with "missing statistics"; it fails with an out-of-memory error or a wall-clock time that looks like a cluster problem.

The second reason it matters operationally is that statistics are the one input the planner cannot recover on its own. Impala refreshes file listings, block locations and partition membership automatically once you tell it to. Row counts and distinct-value counts are not observed as a side effect of anything — they exist only because somebody ran COMPUTE STATS, and they stay at whatever value that run produced until somebody runs it again.

Table stats and column stats are different things with different lifecycles

Impala keeps two distinct bundles in the Hive Metastore, and confusing them is the root of most "but I refreshed it" confusion.

Table statistics

numRows is the only genuinely expensive table-level figure, and it is produced solely by COMPUTE STATS. The file count and total size that appear alongside it are not statistics in the same sense — Impala derives them from the file listing it already holds, so they update whenever the table's metadata is reloaded and are essentially always current. This is why a table can show an accurate size in bytes next to a row count of -1: the cheap numbers are free, the expensive one is not.

Column statistics

Per column, Impala stores the number of distinct values (NDV), the number of nulls, the maximum size and the average size. NDV is the load-bearing one: it drives selectivity for equality predicates and the output cardinality of joins on that column, which in turn drives join order and the broadcast-versus-partitioned decision. It is computed with a HyperLogLog sketch rather than an exact distinct count, so it is an estimate with a few percent of error — fine for planning, not something to quote in a report.

Two details catch people out. First, Impala does not keep per-column minimum and maximum values in metastore column stats. Range predicates are narrowed at scan time from Parquet and ORC footer min/max metadata instead, which is a different mechanism on a different refresh cycle. Second, the null count is collected and displayed but has historically not been consumed by the planner, so do not expect populating it to change an outer-join plan. Max and average size only require measurement for variable-length types; for fixed-width types they follow from the type itself.

Hive's Calcite-based optimizer uses a broader statistics vocabulary and a different estimation algebra; see Hive CBO architecture for that side.

The architecture

COMPUTE STATS gathers table-level (row count, size) and column-level (distinct count, null count, min/max) statistics. Results are stored in the metastore and cached by the Catalog service.

COMPUTE INCREMENTAL STATS refreshes stats per partition rather than whole table. Much faster for large partitioned tables where only recent partitions changed.

The part that surprises people is that COMPUTE STATS is not a metadata operation at all. The coordinator rewrites it into ordinary child queries — a count for the table figure, a grouped aggregation computing NDV and null counts per column — and runs them through the normal execution path. They appear in the coordinator's query list, they consume an admission slot, and they are charged memory like any other query. On a wide table this means a full scan of every column you asked for, which is why COMPUTE STATS on a multi-terabyte table is scheduled work, not something to fire from a dashboard refresh.

That also means the column-subset form matters. Restricting the statement to the columns that actually appear in join keys, filters and group-by clauses avoids scanning and sketching columns no query ever predicates on, which on a table with hundreds of columns is most of them.

Once the child queries finish, the results are handed to the catalog service, written through to the metastore, and broadcast to the other coordinators so they plan against the same numbers. The propagation path itself — catalogd, the statestore topic, versioning, and the REFRESH versus INVALIDATE METADATA repair verbs — is developed in Impala catalog and statestore architecture and is not repeated here. The one consequence worth stating from the statistics side: because the numbers live in the metastore, INVALIDATE METADATA does not destroy them. It forces a reload, and the same stats come back. Dropping and recreating the table does destroy them, and so does rewriting the data through a path that replaces the table object.

Incremental stats and the metadata bill

COMPUTE INCREMENTAL STATS exists because a daily-partitioned table with three years of history should not be fully rescanned to account for yesterday's arrivals. It scans only partitions that have no stats yet (or the partition you name explicitly), and merges the result with what it already has.

The merge is what costs you. To combine yesterday's distinct-value count with the previous three years' without rescanning them, Impala has to keep the intermediate per-partition sketch for every column of every partition, and it keeps it in the table's metadata. The rough figure to plan with is on the order of a few hundred bytes of this intermediate state per column per partition. Multiply it out before you enable this on a wide table: a hundred columns across fifty thousand partitions lands in the low gigabytes of metadata attached to a single table.

That blob is not inert. It is stored in the metastore, held by the catalog service, and shipped to coordinators as part of the table's metadata. A table whose incremental stats outgrow the cluster's tolerance shows up as catalog memory pressure, slow metadata operations and long pauses on the first query after a reload — symptoms that look like a catalog problem and are actually a statistics problem. Impala ships a catalogd size limit for exactly this reason: past the threshold it refuses to store the intermediate state rather than let one table destabilise the metadata layer.

Choosing between the two

Incremental stats pay off when the partition count is high but the churn is low and the column count is modest — the classic append-only event table analyzed on a narrow set of query columns. Full COMPUTE STATS is the better choice when the table is unpartitioned or lightly partitioned, when the column count is large, or when a backfill rewrites history anyway. If a table has drifted into the bad quadrant, DROP INCREMENTAL STATS removes the accumulated intermediate state (per partition or for the table) and lets you go back to periodic full computation.

Reading the stats back

Two statements answer "does the planner know anything about this table", and both should be muscle memory before you open a query profile.

SHOW TABLE STATS  sales;
SHOW COLUMN STATS sales;

SHOW TABLE STATS returns one row per partition on a partitioned table, plus a total row. The columns to read are the row count, the file count, the size, the storage format, and — if you use them — whether incremental stats are present for that partition. A row count of -1 is the entire signal: it means no statistics, not zero rows. On a partitioned table it is common to see a healthy-looking total alongside a handful of -1 partitions from a recent load, and those partitions are precisely the ones a fresh query will misestimate.

+---------+--------+--------+----------+--------------+
| dt      | #Rows  | #Files | Size     | Incremental  |
+---------+--------+--------+----------+--------------+
| 2026-07 | 412M   | 1204   | 38.1GB   | true         |
| 2026-08 | -1     | 96     | 3.0GB    | false        |
| Total   | 412M   | 1300   | 41.1GB   |              |
+---------+--------+--------+----------+--------------+

SHOW COLUMN STATS gives the per-column distinct-value count, null count, maximum size and average size. Again -1 under distinct values means unknown. Read it against your actual query shape: the columns you join and filter on are the ones that must have a plausible NDV. An NDV of 1 on a column you know has millions of values means the stats were computed when the table held a single day's data, or before a backfill, and is worse than no stats at all — the planner will believe it and collapse its cardinality estimate to nothing.

The same numbers are visible in the query profile, so a slow query and its stats can be diagnosed from a single artifact rather than by cross-referencing DDL output.

What a plan without stats looks like

You do not have to infer the problem. EXPLAIN names it directly, appending a warning that lists every table in the query lacking relevant table or column statistics. That warning is the highest-value line in Impala's output and it is routinely scrolled past.

SET EXPLAIN_LEVEL=2;
EXPLAIN SELECT c.region, sum(o.amount)
        FROM   orders o JOIN customers c ON o.cust_id = c.cust_id
        GROUP BY c.region;

At explain level 2 or above each operator carries its estimated cardinality and per-host memory. Two readings tell you almost everything. First, compare the scan's estimated row count against what you know the table holds; a scan estimated at a few thousand rows on a table you know is billions is the misestimate that will propagate up the whole tree. Second, look at the exchange feeding the join build side. BROADCAST means every executor gets a full copy of that input. That is correct and fast for a genuinely small dimension table and ruinous for anything else, and with no row count the planner has no basis to reject it.

The downstream damage is mechanical: an underestimated build side yields an undersized hash table, which yields either a spill or a memory-limit failure, and the per-host memory estimate the query was admitted on was computed from the same wrong number. That interaction is developed in Impala admission control and Impala memory limits; from here the point is only that fixing stats fixes admission sizing for free, whereas raising the memory limit treats the symptom.

As an immediate mitigation you can force the exchange strategy with a join hint, which is the right call when a report is due in ten minutes. It is not a fix: a hint pins one query's plan while every other query over that table keeps guessing. The runtime filter machinery also leans on cardinality estimates, so poor stats quietly cost you there too.

How it works end to end

The CBO uses stats to estimate operator output sizes, choose join orders, decide broadcast vs shuffle joins, and estimate memory needs.

Stats can go stale: table grows, distributions shift, new partitions added. Query plans based on stale stats can be arbitrarily wrong.

The operational rule that holds up is to tie recomputation to data change rather than to the calendar. Recompute after a load that moves the row count materially — a widely used threshold is a change of roughly a fifth — after any backfill or restatement that alters value distributions, and after adding a column that queries will filter on. A nightly cron that analyzes every table regardless of whether it changed burns cluster capacity on tables nobody touched, and on the tables that did change it may still be a day behind.

Sampling and extrapolation

For tables too large to scan in full, Impala offers two escape hatches that are off by default and should be enabled deliberately. Sampled computation runs the statistics queries over a percentage of the table's files rather than all of them, trading exactness for a fraction of the I/O, and can be made repeatable with a fixed seed so successive runs are comparable. Row-count extrapolation lets the planner scale a known row count by the observed change in file sizes, so a partition that grew since the last analysis gets an adjusted estimate instead of a stale one. Both are approximations; both are enormously better than -1.

A workable routine

Make the ingestion pipeline own statistics for the tables it writes, so the analyze step runs in the same job that loads the data and fails visibly when it does not. Audit separately with a periodic sweep of SHOW TABLE STATS, flagging anything reporting -1, which catches hand-created tables and out-of-band partitions. And when a query regresses, check statistics before touching memory limits, hints or pool configuration.