ClickHouse DB::Exception: Illegal index

The "DB::Exception: Illegal index" error in ClickHouse indicates that a data skipping index definition is invalid. This can happen when the index expression references non-existent columns, uses an unsupported index type, or applies an index type to incompatible data. The error code is ILLEGAL_INDEX.

Impact

The table creation or ALTER TABLE statement that adds the index is rejected. Existing tables and their data are not affected. If the index was part of a CREATE TABLE statement, the table is not created.

Common Causes

1. Index type not compatible with the column type -- for example, applying a bloom_filter index to a column type it does not support.
2. Invalid index expression -- the expression used in the index definition is not valid or references columns that do not exist.
3. Unsupported index type -- using an index type name that ClickHouse does not recognize.
4. Invalid granularity specification -- providing zero or negative granularity values.
5. Index on key columns with incompatible type -- certain index types have restrictions on which column types they work with.

Troubleshooting and Resolution Steps

1. Verify the index type is valid. Supported data skipping index types include:

   -- Common index types
   INDEX idx_name column_name TYPE minmax GRANULARITY 4
   INDEX idx_name column_name TYPE set(100) GRANULARITY 4
   INDEX idx_name column_name TYPE bloom_filter(0.01) GRANULARITY 4
   INDEX idx_name column_name TYPE ngrambf_v1(3, 256, 2, 0) GRANULARITY 4
   INDEX idx_name column_name TYPE tokenbf_v1(256, 2, 0) GRANULARITY 4
   

2. Check that the column exists and has a compatible type:

   DESCRIBE TABLE your_table;
   

   Ensure the column referenced in the index expression exists and its type is supported by the chosen index type.

3. Use a valid granularity value:

   -- Granularity must be a positive integer
   INDEX idx_name column_name TYPE minmax GRANULARITY 4  -- correct
   INDEX idx_name column_name TYPE minmax GRANULARITY 0  -- may be invalid
   

4. For bloom filter indexes, ensure the parameters are correct:

   -- bloom_filter with false positive rate
   INDEX idx_name column_name TYPE bloom_filter(0.01) GRANULARITY 4
   
   -- ngrambf_v1 parameters: ngram_size, filter_size, hash_count, seed
   INDEX idx_name column_name TYPE ngrambf_v1(3, 256, 2, 0) GRANULARITY 4
   

5. Test the index expression in a SELECT query first:

   -- If the index expression works in a query, it should work in an index definition
   SELECT lower(column_name) FROM your_table LIMIT 1;
   

Best Practices

• Start with minmax indexes for range queries and bloom_filter for equality lookups -- these are the most widely applicable.
• Test index definitions in a development environment before applying to production tables.
• Use set indexes for columns with low cardinality that are frequently filtered.
• Review the ClickHouse documentation for your specific version to confirm which index types and parameters are supported.
• Keep granularity values reasonable (typically between 1 and 8) to balance index overhead and skip effectiveness.

Frequently Asked Questions

Q: What is a data skipping index in ClickHouse?
A: Data skipping indexes store aggregated information (like min/max values or Bloom filters) about groups of granules. During query execution, ClickHouse uses this information to skip entire blocks of data that cannot contain matching rows, improving query performance.

Q: Can I add an index to an existing table?
A: Yes, use ALTER TABLE ... ADD INDEX. The index will apply to newly written parts. To apply it to existing data, run ALTER TABLE ... MATERIALIZE INDEX index_name.

Q: Does the index type affect write performance?
A: Yes, slightly. Each index adds some overhead during writes because the index must be computed and stored with each data part. The overhead is usually small but can add up with many indexes.

Q: What happens if I drop an index?
A: Dropping an index with ALTER TABLE ... DROP INDEX removes the index metadata and its data from future parts. Existing parts retain the index data on disk until they are merged, at which point it is discarded.