Distributed table shard pruning failing in production despite local test success

Direct Answer

The most likely explanation is that your WHERE clause is not recognized as a deterministic function of the sharding key, so ClickHouse falls back to scanning every shard. The optimize_skip_unused_shards optimization only triggers when the optimizer can invert the predicate into a constraint on the sharding-key columns. A single-node local test masks this because a one-shard cluster produces the same execution plan whether pruning occurs or not.

Confirmed Facts vs. Likely Cause

Confirmed: ClickHouse attempts to rewrite the filter into a deterministic constraint on the sharding-key expression. If the predicate depends on other columns, contains nondeterministic functions, or cannot be mapped to the key, pruning is disabled.

Likely: Your production query wraps the sharding key in an expression that references non-key columns or uses nondeterministic functions, causing a silent full-cluster scan.

Validation Criteria

The optimizer succeeds when the predicate is a deterministic function of the key columns alone, such as shard_key = 123 or shard_key IN (1, 2, 3). It fails when the expression:

• References columns outside the sharding key.
• Applies nondeterministic functions like rand() or now() to the key.
• Uses compositions the analyzer cannot invert back to the key.

The exact set of accepted patterns is version-sensitive; verify against your specific ClickHouse release.

Interaction with allow_nondeterministic_optimize_skip_unused_shards

This setting relaxes the determinism check and allows pruning based on expression shape alone. It is unsafe for production: if the nondeterministic function evaluates differently across shards or rows, ClickHouse may skip shards containing matching rows and return incomplete results without an error.

force_optimize_skip_unused_shards does not resolve this. It raises an error when pruning is impossible, but it validates prunability, not correctness, for nondeterministic predicates. A query can pass the forced check and still yield wrong results under the nondeterministic allowance.

Local Verification Without Multi-Shard

1. Check the plan. Run EXPLAIN on the distributed query and look for a shard-skipping step or reduced shard count.
2. Force the check. Set force_optimize_skip_unused_shards = 1 and run the query. An error means the predicate is not prunable as written.

For a stronger local signal, define a cluster with multiple shard entries pointing to localhost while preserving the production sharding key, then compare system.query_log read metrics against a single-shard baseline.

Steps for This Case

1. Inspect the Distributed table DDL for the exact sharding-key expression.
2. Audit the WHERE clause for nondeterministic functions or non-key dependencies.
3. Run EXPLAIN with force_optimize_skip_unused_shards = 1.
4. If pruning fails, rewrite the predicate to be a deterministic function of the sharding key, or accept the full scan and optimize elsewhere.

Missing Diagnostic

To recommend a specific rewrite, I would need the exact sharding-key expression and the WHERE clause pattern. That detail determines whether the fix is a simple key alignment or a deeper expression-inversion issue.