Skip to content
Tutorials Center
My learning

Topics

Lesson 22 of 36 ยท Joins and Optimization

EXPLAIN and the Cost-Based Optimizer

Read Hive query plans with EXPLAIN, collect table and column statistics with ANALYZE TABLE, and understand how the Calcite cost-based optimizer chooses join orders.

Hive does not run your SQL text. It parses it, plans it, rewrites the plan, and only then runs it as a graph of tasks. A slow query is almost always visible in the plan before you run it, and EXPLAIN is how you look.

The planner makes its choices based on statistics: how many rows a table has, how many distinct values a column holds, how big the files are. Without statistics it guesses. This lesson shows how to read a plan and how to give the optimizer the facts it needs.

EXPLAIN

Prefix any query with EXPLAIN and Hive prints the plan instead of running it:

SQL
EXPLAIN
SELECT c.city, SUM(oi.quantity * oi.unit_price) AS revenue
FROM orders o
JOIN customers c   ON o.customer_id = c.id
JOIN order_items oi ON oi.order_id = o.id
GROUP BY c.city;

On Tez the output is a set of vertices (stages of parallel tasks) connected by edges (data movement). A trimmed, typical plan reads like this (read-only, shape only):

TEXT
Vertex dependency in root stage
Map 1 <- Map 3 (BROADCAST_EDGE)
Reducer 2 <- Map 1 (SIMPLE_EDGE)

Stage-0
  Fetch Operator
    Reducer 2
      Group By Operator [aggregations: sum(...)] [keys: city]
        <- Map 1 [SIMPLE_EDGE]
          Map Join Operator [condition map: Inner Join 0 to 1]
            <- Map 3 [BROADCAST_EDGE]
              TableScan [alias: c]
            TableScan [alias: o]

How to read it:

  • TableScan is reading a table. Look at its row and size estimates.
  • Map Join Operator plus a BROADCAST_EDGE is a broadcast join (see map and shuffle joins).
  • SIMPLE_EDGE is a shuffle, the expensive kind of data movement. Count them.
  • Group By Operator appears twice for many aggregations: a partial aggregation in the map side and a final one in the reducer.
  • Filter Operator near the TableScan means predicates were pushed down early. A filter far from the scan is a warning sign.
  • Estimates written as Statistics: Num rows: 1 Data size: ... Basic stats: COMPLETE tell you whether Hive knew the table size. NONE or PARTIAL means it was guessing.

Variants of EXPLAIN

SQL
EXPLAIN EXTENDED ...        -- adds file paths and more operator detail
EXPLAIN CBO ...             -- shows the plan chosen by the cost-based optimizer
EXPLAIN VECTORIZATION ...   -- says which operators run vectorized, and why not
EXPLAIN DEPENDENCY ...      -- JSON list of input tables and partitions
EXPLAIN AUTHORIZATION ...   -- which privileges the query needs
EXPLAIN LOCKS ...           -- which locks it would take
EXPLAIN ANALYZE ...         -- runs the query and shows estimated versus actual rows

EXPLAIN DEPENDENCY is a quick way to check that partition pruning works: the listed partitions should be only the ones you expect. EXPLAIN ANALYZE is the best tool for spotting bad estimates, because it prints both numbers side by side.

A stand-in plan you can read

SQLite cannot show a Hive plan, but its EXPLAIN QUERY PLAN is a small version of the same idea. With an index, the plan changes from a full scan to a seek:

SQL Scan versus seek (SQLite stand-in) ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

SQL After adding an index ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

The first plan says SCAN orders; the second SEARCH orders USING INDEX. Hive has no ordinary indexes any more (they were removed in Hive 3), so its equivalents are partition pruning, ORC min/max statistics and bloom filters, covered in ORC and Parquet.

Statistics

Hive keeps two levels of statistics in the metastore:

Level What it holds Command
Table / partition row count, number of files, total size, raw data size ANALYZE TABLE t COMPUTE STATISTICS
Column distinct values, nulls, min, max, average length ANALYZE TABLE t COMPUTE STATISTICS FOR COLUMNS
SQL
ANALYZE TABLE sales COMPUTE STATISTICS;
ANALYZE TABLE sales COMPUTE STATISTICS FOR COLUMNS customer_id, amount;

-- Partitioned tables: analyze one partition or all of them
ANALYZE TABLE sales PARTITION (sale_date = '2026-01-15') COMPUTE STATISTICS;
ANALYZE TABLE sales PARTITION (sale_date) COMPUTE STATISTICS FOR COLUMNS;

DESCRIBE FORMATTED sales;                 -- shows numRows, totalSize ...
DESCRIBE FORMATTED sales customer_id;     -- column statistics

Settings that keep them fresh:

SQL
SET hive.stats.autogather = true;          -- gather basic stats on INSERT (default true)
SET hive.stats.column.autogather = true;   -- column stats on INSERT (on by default in Hive 3+)
SET hive.compute.query.using.stats = true; -- answer COUNT(*), MIN, MAX from stats alone

With fresh statistics, SELECT COUNT(*) FROM sales can return instantly from the metastore without reading any data. Stale statistics are worse than none: after a big load that bypassed Hive (files copied directly into HDFS), rerun ANALYZE or the optimizer will plan for a table that no longer exists.

The cost-based optimizer (CBO)

Hive uses Apache Calcite as its cost-based optimizer, enabled by hive.cbo.enable (on by default). It takes the logical plan and tries alternatives, estimating the cost of each from the statistics:

  • Join reordering. Put selective joins first so intermediate results stay small.
  • Join type choice. Estimate whether a table is small enough to broadcast.
  • Predicate pushdown and projection pruning. Filter and drop columns early.
  • Rewrites. Remove redundant joins, simplify expressions, push aggregations below joins.

You can see the effect without changing SQL. The same three-table join may be planned in a different order once column statistics exist. This JavaScript stand-in imitates the decision: given row counts and a filter's selectivity, it picks the join order with the smaller intermediate result.

JS Why join order matters (JS stand-in)

Press Run (or Ctrl + Enter) to see the console output here.

No output.

A cost-based optimizer is this arithmetic done thousands of times over many candidate plans, using real statistics. If the statistics are wrong, it confidently picks the wrong plan, so keeping them current is the most effective single tuning habit in Hive.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What does EXPLAIN do in Hive?

    EXPLAIN shows the plan (vertices, operators, edges). Only EXPLAIN ANALYZE also runs the query.
  2. 2. In a Tez plan, which edge type usually signals an expensive shuffle?

    SIMPLE_EDGE is a shuffle between vertices; broadcast edges carry small tables to many tasks.
  3. 3. Which command collects column-level statistics?

    The FOR COLUMNS variant stores distinct counts, nulls, min and max, which the optimizer uses for join and filter estimates.
  4. 4. Why are stale statistics risky?

    Results stay correct, but the plan can be far slower than necessary, or a forced map join can run out of memory.

Summary

  • EXPLAIN shows vertices, edges and operators; look for shuffles (SIMPLE_EDGE), broadcast joins and where filters sit.
  • EXPLAIN DEPENDENCY checks partition pruning; EXPLAIN ANALYZE compares estimated and real rows.
  • ANALYZE TABLE ... COMPUTE STATISTICS [FOR COLUMNS] feeds the Calcite-based cost-based optimizer.
  • Fresh statistics let Hive reorder joins, choose map joins and answer simple aggregates from metadata.

Next: Text, SequenceFile and RCFile