Table indexes

Indexes (sometimes called skip indexes) let the database skip the parts of a table that a query's filters prove empty, so filtered queries run faster on large tables. They change nothing about the data itself. You manage them in the Indexes step of a Dimension or Fact table's editor.

The two system indexes

Every Dimension and Fact table shows two locked rows on top of its index list:

• The primary key index, shown as IX_PK_ followed by the table's name.
• The alternate key index, shown as IX_AK_ followed by the table's name, over Source Id and Source System Id.

They are managed automatically. You cannot rename them, change their columns or remove them, and their tooltips say Primary Key index is automatically managed by the system and Alternate Key index is automatically managed by the system. Selecting one shows its columns as locked chips, and the panels note that system index columns are managed automatically.

Add or change your indexes

1. Open the Dimension or Fact table, go to the Table tab and open the Indexes step, the last of the five steps after General, Columns, Dependencies and RLS (the editor in full).
2. Click Add Index. A new row appears with a default name such as New Index 1, which you can change.
3. With the row selected, add its columns under Index Columns: click a column under Available Columns, or drag it across. A number beside each column shows its place in the index, and dragging one column onto another swaps their places. The trash icon removes a column.
4. To rename the index, double-click the row's name, type the new name and click away.
5. Click Apply. A successful save reads Indexes saved successfully. or Indexes updated successfully.; a failure reads Failed to save indexes.

• An index holds up to three columns; the badge beside Index Columns counts them, and reads 3/3 when the index is full.
• Available Columns hides the columns already in the index you are viewing and explains an empty list: All columns have been added., Drag or add columns from the left., Select an index to view available columns. or No index selected.
• To remove an index, hover its row and click the trash icon. Renames, column changes and removals are saved together when you click Apply; Revert drops the changes made since the last save.
• If the table itself has not been saved yet, saving the step reports Table ID is required for saving indexes.; save General first, then come back.

Names, columns and settings

• Names: an index name is unique on its table. While you type a rename, a duplicate of another index's name is flagged with an alert icon and the message This index name is already in use. Please choose a different name. If you leave it, the old name returns, so a duplicate cannot be saved. A name holds 1 to 128 characters and may not contain double quotes, backticks, backslashes or control characters; the platform refuses any name outside those rules.
• Columns: only stored columns can be indexed. A column computed when it is read is refused, with the hint to index the stored column it reads instead. If an index names a column the table no longer has, saving is refused with a message naming the column and listing the table's columns.
• At least one column: an index needs a column. Saving an index without columns reports Index "X" must have at least one column.
• Type and granularity: the editor does not ask for them. The platform picks the type from the columns: minmax for dates and numbers, set for one low-cardinality text column, and bloom_filter for other text columns. The granularity starts at 1. A set index carries a Set Max Rows value (set(100) by default) and a bloom_filter index a false-positive rate (bloom_filter(0.01) by default).
• An index's type and granularity cannot be changed; create a new index instead. Set Max Rows and the false-positive rate are held to the same rule.

Check that an index will help

An index only earns its place when the queries you run can skip work because of it. The Warehouse Builder can check before you build one:

1. Read the plan. Ask what a query costs, and the builder reads the query's plan without running it; the plan view is on Query cost.
2. Estimate. The builder prices candidate indexes against the queries' filters and builds nothing. A candidate is suggested when it would skip at least 25% of the granules the filters keep; when none reaches that bar, no index is proposed.
3. Create. Make the suggested index, from the chat or in the editor.
4. Watch and measure. On a table that already holds rows, the index is written for those rows in the background; when the write finishes, run the queries again and compare.

The cards that report each step are on Warehouse tool cards in chat, and the full behaviour of skip indexes is on Skip indexes and Refreshing views.

Permissions

Without Add/Modify Warehouse Objects, saving indexes is refused. The full list of Warehouse permissions is on Warehouse permissions and row-level security.

Questions, answered

Which tables can I manage indexes for?

Dimensions and Facts. The Indexes step is part of their editor (Create a dimension or fact table); Dictionary, Static and View tables do not have this step.

How many columns can one index hold?

Up to three. When an index has three columns, the lists stop accepting more and the badge reads 3/3.

Can I rename an index?

Yes, for the indexes you added: double-click the name, type the new one, then click away to keep it. If the new name duplicates another index's name, the editor flags it and brings the old name back. The two system indexes cannot be renamed.

Why can't I edit the first two indexes?

They are system indexes. The primary key index and the alternate key index are managed for you, their columns are fixed, and the editor blocks renaming, column changes and removal. Add your own indexes for the columns your queries filter on.

Can two indexes use the same column?

Yes. Available Columns hides only the columns already in the index you are viewing, so the same column can appear in another index as well.

Can I choose an index's type or granularity?

Not in the table editor, where an index is a name and its columns; the type follows the columns (minmax for dates and numbers, set for one low-cardinality text column, bloom_filter for other text columns) and the granularity starts at 1. For chosen settings, ask the Warehouse Builder when it creates an index. Afterwards an index's type and granularity cannot be changed, so create a new index instead (more on skip indexes).

When do my index changes take effect?

When you click Apply. On a table that is already deployed, the change is applied to the table, and on a table that already holds rows the indexes are written for those rows in the background. On a table that is not deployed yet, the indexes are created when you deploy it (Deploy, run, truncate and monitor tables).

What happens if I save an index without any columns?

The save is refused with Index "X" must have at least one column, and nothing is created. Add a column to the index, or remove the row and save again.

Why can't I index a computed column?

Only stored columns can be indexed. A column that is computed when it is read is refused, and the message asks you to index the stored column it reads instead.

Who can add or remove indexes?

Adding, changing and removing indexes needs Add/Modify Warehouse Objects; seeing them needs View Warehouse Objects. Ask an administrator for it; the full list is on Warehouse permissions and row-level security.