ClickHouse DB::Exception: Index is not used (Code: 277)

The "DB::Exception: Index ... is not used" error (error code INDEX_NOT_USED, code 277) in ClickHouse occurs when one of the index-forcing settings is enabled and the query cannot use the required index. Three settings can raise it: force_primary_key (the primary key index is not used), force_index_by_date (partition pruning by date is not possible), and force_data_skipping_indices (a named data skipping index is not used). These settings serve as a safeguard to ensure that queries intended to leverage an index are not silently falling back to full scans.

Impact

The query fails without executing. This error only appears when force_primary_key, force_index_by_date, or force_data_skipping_indices is enabled and the optimizer determined the corresponding index cannot be applied. Without these settings, the query would run a full scan without error, potentially consuming more resources and taking longer to complete.

Common Causes

1. The query's WHERE clause does not match the indexed expression or column
2. The skip index type (minmax, set, bloom_filter, etc.) is not suitable for the comparison operator used in the query
3. force_data_skipping_indices references an index name that does not exist on the table, or that the query does not use
4. force_primary_key is enabled but the WHERE clause does not filter on a primary key (ORDER BY) column prefix
5. force_index_by_date is enabled but the query has no filter that allows partition pruning by the partitioning key
6. The index has not been materialized yet (created after data was already inserted)
7. The query uses functions or transformations on the indexed column that prevent index application, or OR conditions spanning indexed and non-indexed columns

Troubleshooting and Resolution Steps

1. Check which indexes exist on the table:

   SHOW CREATE TABLE your_database.your_table;
   

2. Verify the index name matches what is in force_data_skipping_indices:

   SELECT name, type, expr, granularity
   FROM system.data_skipping_indices
   WHERE table = 'your_table' AND database = 'your_database';
   

3. Ensure your WHERE clause matches the index expression. For example, with a bloom_filter index on user_id:

   -- This can use the index:
   SELECT * FROM your_table WHERE user_id = 'abc123';
   
   -- This likely cannot:
   SELECT * FROM your_table WHERE lower(user_id) = 'abc123';
   

4. If the index was added after data existed, materialize it:

   ALTER TABLE your_table MATERIALIZE INDEX your_index_name;
   

5. If you do not need strict index enforcement for this query, remove the forcing:

   SET force_data_skipping_indices = '';
   SET force_primary_key = 0;
   SET force_index_by_date = 0;
   SELECT * FROM your_table WHERE ...;
   

6. Choose the right index type for your query patterns:

   -- For equality checks: bloom_filter or set index
   ALTER TABLE your_table ADD INDEX idx_user bloom_filter(user_id) GRANULARITY 4;
   
   -- For range checks: minmax index
   ALTER TABLE your_table ADD INDEX idx_date minmax(event_date) GRANULARITY 1;
   

7. Use EXPLAIN to see if the index would be applied:

   EXPLAIN indexes = 1
   SELECT * FROM your_table WHERE user_id = 'abc123';
   

Best Practices

• Use EXPLAIN indexes = 1 to verify index utilization before enabling force_primary_key, force_index_by_date, or force_data_skipping_indices.
• Match skip index types to your query patterns: minmax for ranges, bloom_filter for equality, set for low-cardinality columns.
• Materialize indexes after creation to ensure they cover existing data.
• Avoid wrapping indexed columns in functions in WHERE clauses, as this prevents index usage.
• Use the index-forcing settings selectively for critical queries rather than globally, to avoid blocking ad-hoc queries.

Frequently Asked Questions

Q: What is the difference between primary key index and skip index in ClickHouse?
A: The primary key (ORDER BY) determines physical data ordering and is always used for range scans. Skip indexes (also called data skipping indexes or secondary indexes) are additional lightweight indexes that help skip granules that definitely do not contain matching data. They supplement the primary key but work differently.

Q: Can a skip index be used with LIKE queries?
A: It depends on the index type and the LIKE pattern. A ngrambf_v1 (n-gram bloom filter) index can support LIKE and other substring searches. Standard bloom_filter and minmax indexes cannot help with arbitrary LIKE patterns.

Q: Why does my index work for some queries but not others?
A: Different index types support different operations. A minmax index can help with =, <, >, BETWEEN but not with != or NOT IN. A bloom_filter helps with =, IN, has() but not with range comparisons. Check the ClickHouse documentation for supported operations per index type.