ClickHouse DB::Exception: Cannot skip unused shards in distributed query

The "DB::Exception: Cannot skip unused shards in distributed query" error in ClickHouse occurs when the force_optimize_skip_unused_shards setting is enabled but the query optimizer cannot determine which shards to skip. The UNABLE_TO_SKIP_UNUSED_SHARDS error code means the query does not contain conditions on the sharding key that would allow ClickHouse to route it to specific shards, and the strict setting converts this into a hard error.

Impact

The query fails without executing on any shard. This error only occurs when force_optimize_skip_unused_shards is set to 1 or 2, and it takes effect only when optimize_skip_unused_shards is also enabled (the forcing setting governs what happens when shard skipping is requested but not possible). Without the forcing setting, the query would simply execute on all shards, which is functional but potentially wasteful. The error is a safeguard to ensure distributed queries always benefit from shard pruning.

Common Causes

1. The query's WHERE clause does not include a filter on the sharding key column
2. The sharding key expression is complex (e.g., cityHash64(user_id) % 3) and the query filter does not match it exactly
3. The force_optimize_skip_unused_shards setting is enabled at the profile level, catching queries that were not designed for shard pruning
4. Using a distributed table where the sharding key is not defined or is empty
5. JOINs or subqueries that prevent the optimizer from extracting the sharding key condition
6. Using non-deterministic functions or expressions in the WHERE clause that prevent static shard resolution

Troubleshooting and Resolution Steps

1. Check the distributed table's sharding key:

   SELECT database, name, engine_full
   FROM system.tables
   WHERE engine = 'Distributed' AND name = 'your_distributed_table';
   

2. Add a filter on the sharding key to your query:

   -- If sharding key is user_id:
   SELECT * FROM distributed_table WHERE user_id = 12345;
   

3. If you do not need strict shard pruning for this query, disable the forcing:

   SET force_optimize_skip_unused_shards = 0;
   SELECT * FROM distributed_table WHERE some_other_column = 'value';
   

4. Check the current setting level and determine where it is being set:

   SELECT name, value, changed
   FROM system.settings
   WHERE name LIKE '%skip_unused_shards%';
   

5. For complex sharding expressions, ensure the WHERE clause uses compatible conditions:

   -- If sharding expression is cityHash64(user_id):
   -- This works (ClickHouse can compute the shard):
   SELECT * FROM distributed_table WHERE user_id = 12345;
   -- This does NOT work (range condition on sharding key):
   SELECT * FROM distributed_table WHERE user_id > 100 AND user_id < 200;
   

6. Use force_optimize_skip_unused_shards = 1 instead of 2 if you only want the error thrown for tables that actually have a sharding key (rather than for every distributed table, including those with no sharding key defined):

   SET force_optimize_skip_unused_shards = 1;
   

   Note that both 1 and 2 throw a hard error when shards cannot be skipped; the difference is only whether tables without a sharding key are also affected. Neither value produces a non-fatal warning.

Best Practices

• Design your sharding key to align with your most common query filters so shard pruning is effective for the majority of queries.
• Prefer force_optimize_skip_unused_shards = 1 over 2: value 1 only enforces pruning for tables that have a sharding key, while value 2 also fails on tables with no sharding key defined. Both values throw a hard error, not a warning, when pruning is not possible.
• Document the sharding key for each distributed table so query authors know which filters enable shard pruning.
• Consider using optimize_skip_unused_shards = 1 (without force_) to enable shard pruning opportunistically without requiring it.
• For queries that genuinely need to scan all shards, set the forcing to 0 at the query level rather than globally.

Frequently Asked Questions

Q: What is the difference between force_optimize_skip_unused_shards = 1 and = 2?
A: With = 1, ClickHouse will throw an error only if the sharding key is present in the table but the query cannot use it. With = 2, the error is thrown even if the table has no sharding key defined, making it the strictest mode.

Q: Can ClickHouse skip shards for range queries on the sharding key?
A: It depends on the sharding expression. For simple column-based sharding keys, range queries on that column may enable partial shard pruning. For hash-based sharding expressions (e.g., cityHash64(col)), only equality conditions allow shard resolution.

Q: Does shard pruning work with IN clauses?
A: Yes, WHERE sharding_key IN (value1, value2, value3) allows ClickHouse to determine which shards hold the specified values and route the query only to those shards.