ClickHouse 26.6 introduced hypothetical indexes and EXPLAIN WHATIF: test skip index candidates without materializing them. Learn the workflow, how skip ratio estimation works, and the limitations to watch for.

ClickHouse Hypothetical Indexes

Tuning data skipping indexes in ClickHouse has always been a trial-and-error exercise. You pick an index type, guess a granularity, run ALTER TABLE ADD INDEX, wait for MATERIALIZE INDEX to chew through terabytes of parts, benchmark your query - and if the skip ratio disappoints, you drop it and start over. On a production cluster, each iteration costs real I/O, real disk space, and real time.

ClickHouse 26.6 removes the guesswork. Hypothetical indexes let you ask "what if I had this skip index?" without building anything on disk. You define candidate indexes that exist only in your session, then run EXPLAIN WHATIF on your actual queries to see how many granules each candidate would skip. PostgreSQL users have had this workflow for years via the HypoPG extension; ClickHouse now ships it built in.

Not to Be Confused with the Old hypothesis Index

First, a naming collision worth clearing up. ClickHouse used to have an experimental skip index of TYPE hypothesis - a rarely-used mechanism that stored per-granule boolean assertions about condition expressions. That feature was removed entirely in ClickHouse 26.3 after years of low adoption and known bugs; tables carrying a hypothesis index must drop it before upgrading.

Hypothetical indexes, introduced three releases later in 26.6, are unrelated. They are not a new index type at all - they are a what-if analysis tool for the existing, fully-supported skip index types. The near-identical names are unfortunate, but the features share nothing beyond the word root.

How Hypothetical Indexes Work

A hypothetical index is a virtual, session-scoped definition of a skip index. Creating one writes nothing to disk and changes nothing about the table - it simply registers a candidate that EXPLAIN WHATIF can evaluate. When your session ends, the definitions disappear.

The syntax mirrors regular skip index creation:

CREATE HYPOTHETICAL INDEX idx_b ON t (b) TYPE minmax GRANULARITY 1;

The standard skip index types are supported: minmax, set(N), bloom_filter(p), ngrambf_v1(...), and tokenbf_v1(...). The text and vector_similarity index types are rejected at creation time - they cannot be meaningfully evaluated within a session.

Your session's candidates are listed in a dedicated system table:

SELECT name, type_full, expression, granularity
  FROM system.hypothetical_indexes;

Each connection sees only its own hypothetical indexes (system.hypothetical_indexes is empty until you create some). Cleanup is equally simple: DROP HYPOTHETICAL INDEX idx_b ON t, or DROP ALL HYPOTHETICAL INDEXES to clear the session in one statement. Since nothing was materialized, dropping is instant and requires no special privileges - creating one only requires SELECT on the indexed columns.

EXPLAIN WHATIF in Action

With candidates registered, prefix any SELECT with EXPLAIN WHATIF:

CREATE TABLE t (a UInt64, b UInt64) ENGINE = MergeTree ORDER BY a
  SETTINGS index_granularity = 100;
  
  INSERT INTO t SELECT number, number FROM numbers(10000);
  
  CREATE HYPOTHETICAL INDEX idx_b ON t (b) TYPE minmax GRANULARITY 1;
  
  EXPLAIN WHATIF SELECT * FROM t WHERE b = 42;

The output reports the baseline (100 marks read with no index) against each candidate - here, 1 mark and a skip_ratio of 99.0%. The key columns to read:

  • marks: how many granules the query would read with this index in place.
  • est_bytes: the estimated read volume, which is what actually drives query latency.
  • skip_ratio: the fraction of baseline granules the index would eliminate.
  • source: how the estimate was produced (more on this below).

The real power is comparing multiple candidates side by side. The 26.6 release post demonstrates this on the UK property prices dataset, testing the same set(10) index at two granularities:

CREATE HYPOTHETICAL INDEX town_set_10_granularity_1
  ON uk_price_paid (town) TYPE set(10) GRANULARITY 1;
  
  CREATE HYPOTHETICAL INDEX town_set_10_granularity_128
  ON uk_price_paid (town) TYPE set(10) GRANULARITY 128;
  
  EXPLAIN WHATIF
  SELECT district, count(), round(avg(price)) AS avgPrice
  FROM uk_price_paid
  WHERE town = 'LONDON'
  GROUP BY ALL
  ORDER BY count() DESC
  LIMIT 10;

The result: GRANULARITY 128 skips 77.0% of granules, while GRANULARITY 1 skips 92.2%. That is a materially different outcome from a parameter many people set by copying an example, and you learned it without writing a byte to disk.

How the Estimates Are Computed

EXPLAIN WHATIF uses a three-tier fallback for each candidate:

  1. Empirical (the default): ClickHouse builds the candidate index in memory over the baseline-pruned granules by reading actual table data, then counts the granules the index would skip. This is the most accurate mode - the output shows source: empirical with empirical_status: ok. Note that this scan is real work: it reads the indexed columns and counts against your session's read limits and quotas.
  2. Statistical: with EXPLAIN WHATIF empirical = 0, ClickHouse skips the in-memory scan and derives selectivity from column statistics instead. Cheaper, less precise - useful on very large tables where even a column scan is too expensive to run casually.
  3. Applicability-only: when neither method can produce a number, the output conservatively reports skip_ratio: 0.0%, telling you only whether the index could apply to the query at all.

One caveat to internalize: skip_ratio is an upper bound on the benefit, not a latency prediction. Skipping 90% of granules does not mean the query gets 10x faster - ClickHouse coalesces reads across small gaps between surviving granules, and the estimate does not model that. A high skip ratio on granules scattered evenly through a part delivers less I/O savings than the same ratio on contiguous ranges. Treat EXPLAIN WHATIF as a ranking tool for comparing candidates, then validate the winner with a real benchmark.

A Practical Tuning Workflow

Here is how we approach skip index tuning with this feature on client clusters:

  1. Find the slow queries first. Pull candidates from system.query_log - look for queries reading far more granules than the rows they return (our guide to ClickHouse memory pressure and query optimization covers this triage in depth). A skip index only helps when the filter column has some locality relative to the sort order - if it doesn't, revisiting the table schema and sorting key usually pays off more than any index.
  2. Register several hypothetical candidates at once. Vary both the index type and the granularity: a bloom_filter(0.01) vs bloom_filter(0.001) vs set(100) on the same column, each at GRANULARITY 1 and GRANULARITY 4.
  3. Run EXPLAIN WHATIF with your real production queries, not synthetic ones. The estimate is only as representative as the query you test.
  4. Materialize only the winner:
ALTER TABLE events ADD INDEX idx_user user_id TYPE bloom_filter(0.01) GRANULARITY 1;
  ALTER TABLE events MATERIALIZE INDEX idx_user;
  1. Benchmark the real index before and after, since skip_ratio is an upper bound. If the wall-clock win is marginal, drop it - every skip index adds insert and merge overhead forever.

The last point deserves emphasis. The most common skip index mistake we see in production is not a missing index but too many useless ones, each slowing down every insert for a query pattern that never benefits. Hypothetical indexes finally make it cheap to prove an index is worthless before it ships.

Limitations

A few boundaries to know before relying on the feature:

  • Queries with FINAL are not supported. If your workload leans on ReplacingMergeTree with FINAL, EXPLAIN WHATIF cannot evaluate those queries.
  • Projection-served queries are excluded. If the optimizer would answer the query from a projection, hypothetical index evaluation does not apply - the index would be on the base table, not the projection.
  • Tables must live in Atomic databases (the default engine), since hypothetical indexes track tables by UUID. This also gives you a nice property: definitions survive RENAME TABLE and silently vanish when the table is dropped.
  • Session scope means no sharing. A hypothetical index created in your client session is invisible to your colleague's session and to your BI tool. For team-wide tuning sessions, keep the candidate definitions in a script.
  • Empirical mode costs a scan. On multi-terabyte tables, either budget for the read or fall back to empirical = 0.

Key Takeaways

  • ClickHouse 26.6 added hypothetical indexes: virtual, session-scoped skip index definitions evaluated via EXPLAIN WHATIF, with zero disk writes and instant cleanup.
  • Do not confuse them with the old experimental TYPE hypothesis skip index, which was removed in 26.3. Same word root, entirely different features.
  • EXPLAIN WHATIF reports marks, estimated bytes, and skip ratio per candidate, using empirical in-memory index construction by default and column statistics with empirical = 0.
  • The skip_ratio is an upper bound for ranking candidates, not a latency forecast - always benchmark the materialized winner.
  • The workflow: register several type/granularity variants, test against real production queries, materialize only the best one, and drop indexes that do not earn their insert-time overhead.
  • Watch the limitations: no FINAL, no projection-served queries, Atomic databases only, and empirical estimation reads real data against your session quotas.