Skip indexes and Refreshing views

Skip indexes and Refreshing views make the Warehouse tables behind a model cheaper to read. A skip index lets the database skip the parts of a table that a chart's filters prove empty, without storing or changing anything. A Refreshing view stores its rows and rebuilds them on a schedule, so charts read data as of the last refresh instead of re-running the view's query. Both are early steps of the performance ladder.

Add a skip index

Ask the Warehouse Builder in its chat to add one, for example "add an index on Ship Date". During a build, the Model Builder adds them itself as the first ladder step for slow charts. The change arrives in the chat as an index card; the card's layout is shown in Warehouse tool cards.

• The card lists the table's indexes with their columns, a type chip reading minmax, set(100) or bloom_filter(0.01), and the granularity, for example granularity 1.
• An index on a table that already holds rows is written for the stored rows in the background. While that runs, the card reads Writing for the loaded data...; when it finishes, Written for the loaded data in n s.
• A table with no rows needs no writing: the card reads Not needed: the table is empty, and the index covers everything the next load writes.
• If the write fails, the card reads Could not write for the loaded data: reason. The index still covers the parts written from then on; read the reason and ask again.
• New indexes are published when the table is deployed. If it is not, the card carries Redeploy required: deploy the table.
• After the index is written, measure the charts again. See Query cost.

Defaults and rules

• The default type follows the columns: minmax for dates and numbers, set(100) for one low-cardinality text column, and bloom_filter(0.01) for one other text column. The granularity defaults to 1 and accepts 1 to 4.
• Existing indexes are never changed. An index's type and granularity cannot be updated; create a new index instead.
• Names hold up to 128 characters and may not contain double quotes, backticks, backslashes or control characters.
• Only stored columns can be indexed. A computed column is refused with the hint to index the stored column it reads instead.
• A minmax index on a LowCardinality(Nullable) column is refused, because it can make IS NULL filters return no rows. Use set or bloom_filter on that column instead.

Estimate an index before it is written

On newer database engines you can estimate an index before building it: the estimate reports how many granules each candidate would let the table skip, against representative queries with the slow charts' filters. Ask for it in the Warehouse Builder chat.

• One estimate prices 1 to 6 candidate indexes on a deployed Warehouse Fact or Dimension table, against up to 5 one-table queries. Nothing is built: the candidates exist only for the estimate.
• The card reads Against n queries and lists what each candidate would skip. A candidate is marked Suggested: label when it skips at least 25% of the granules the filters keep; when none reaches that, the card reads No candidate skips at least 25% of the kept granules. A query with nothing to estimate reads Not applicable with the reason.
• Each estimate is recorded for the current Model Builder session, which keeps the newest 20. The card confirms Recorded for this Model Builder session, or warns Not recorded: createTableIndex will ask for it again.
• In a Model Builder session on such a server, an index over a table that already holds rows is written only after the same index (columns, type and granularity) was estimated in the session. An index on an empty table needs no estimate.
• On a server that is too old for estimates, the card reads Needs ClickHouse version and points you to the query plan, which keeps working: choose the index from the plan, write it, and measure the charts after.

Turn a Standard view into a Refreshing view

A Refreshing view stores its rows and refreshes them on a schedule. Create one from the start in the Warehouse by choosing the view type Refreshing (its refresh settings are on Views), or convert an existing Standard view by asking the Warehouse Builder in its chat.

• Outside an approved design, the conversion asks for a person's approval first, on a card titled Turn the view "name" into a Refreshing view. The card names the view, its dependants, the new schedule and the primary key.
• Its line explains the trade-off before you approve. On a newer engine: "It becomes a stored table in one atomic swap: readers keep the view until the stored table is filled, and charts on it show data as of the last refresh." Otherwise: "It is rebuilt as a stored table: it returns no rows until its first refresh finishes, and charts on it show data as of the last refresh."
• On the atomic path, every failure puts the Standard view back. If filling takes longer than 300 seconds, the swap is rolled back and nothing changes: convert the view outside working hours, or make its query cheaper first. If even the rollback fails, ask an administrator to undeploy and deploy the view in the Warehouse UI.
• The view keeps its columns and their settings. It needs a primary key, and a key column cannot be nullable: wrap a nullable key with ifNull or coalesce in the query.
• Only a Standard view converts. Inside a Model Builder session, only the design's own views convert, and a view that existed before the design comes to you first as a shared-table question. See Full-history backfill and the performance ladder.
• The result card reads Created or Converted from a Standard view, shows the schedule as Every 1 hour, the deploy state Deployed, first refresh running or Not deployed: run deployTable, and, once refreshed, the line Last refresh 10:42 · next 11:42.

Refresh schedules

• Every Refreshing view refreshes on its own schedule. Until the first refresh of a converted view finishes, it has no rows to read; on the atomic path, readers keep the Standard view instead.
• The Model Builder converts a view at most once a day, with schedules in days, weeks or months. The done card lists those under Refreshing views the Model Builder scheduled, with Change or stop a schedule in the Warehouse UI. See Model Builder cards.
• In the Warehouse UI, open the view's Settings tab to change the interval, start or stop the schedule, or refresh now; see Views. In the chat you can ask for a refresh now, but starting, stopping and cancelling a schedule stay with a person.

Questions, answered

What does a skip index actually do?

It tells the database which parts of a table a filter can skip, so a chart reads less. It stores no rows and changes nothing in the table.

Can I change an index's type or granularity later?

No. An index's type and granularity cannot be updated, and existing indexes are never changed. Create a new index with what you want; the old one stays as it is.

Why is minmax refused on some columns?

Because the column is a LowCardinality(Nullable) column: a minmax index there can make IS NULL filters return no rows. Use set or bloom_filter on that column instead.

Why does the index card say "Not needed: the table is empty"?

The table holds no rows yet, so there is nothing for the index to cover. It covers every part the next load writes.

What does "Writing for the loaded data..." mean?

The index is being written for the rows the table already holds, in the background. When it finishes, the card reads Written for the loaded data in n s; measure the charts again after that.

Can I estimate an index before it is written?

Yes, on a newer database engine. One estimate prices 1 to 6 candidate indexes against up to 5 one-table queries and builds nothing, and a Model Builder will not write an index over loaded data that was not estimated first. The estimates are kept for the session.

Why does the estimate say "Not recorded: createTableIndex will ask for it again"?

The estimate was not stored for this Model Builder session, so writing that index will ask for an estimate again. Run the estimate again to record it.

What is a Refreshing view?

A view that stores its query's rows as a table and rebuilds them on a schedule. Charts on it read data as of the last refresh instead of re-running the query.

Why does a converted view return no rows?

Its first refresh has not finished yet. On older engines a converted view returns no rows until that refresh completes; on newer engines readers keep the Standard view until the stored table is filled, so they never see the gap.

Who can change or stop a refresh schedule?

A person, in the Warehouse UI: open the view's Settings tab. The Model Builder only schedules what it converts, at most once a day, and never starts, stops or cancels a schedule.

Can the Model Builder convert a view that existed before the design?

No. It converts only the design's own views, and adds indexes only on the design's fact and dimension tables. A change to a table that existed before the design comes to you first as a shared-table question; see Full-history backfill and the performance ladder.