How do you optimize ClickHouse queries using PREWHERE, array functions, and approximate aggregations?

Think of shopping for a specific book in a massive warehouse. The naive approach (WHERE) loads every book onto a cart and then checks the title. The smart approach (PREWHERE) first reads only the title labels on the shelves, discards non-matching sections, and only loads the full books that match. You move far less weight (I/O) by checking the lightweight filter column first.

PREWHERE is a ClickHouse-specific optimization that splits query execution into two phases. In phase one, only the columns used in the PREWHERE clause are read from disk, and non-matching rows are identified. In phase two, remaining columns are read only for rows that passed the PREWHERE filter. ClickHouse automatically converts WHERE to PREWHERE for conditions on columns not in the primary key (controlled by optimize_move_to_prewhere setting), but you can manually specify PREWHERE for maximum control. The benefit is dramatic when your filter is selective (eliminates 90%+ of rows) and the remaining columns are wide (large strings, many columns).

Internally, array functions provide a powerful alternative to JOINs for nested or multi-valued data. Instead of normalizing events with multiple tags into a separate tags table requiring a JOIN, you store tags as Array(String) and use arrayJoin to unnest, arrayMap to transform, arrayFilter to select, and hasAny/hasAll for membership tests. This keeps all data in a single table scan without the network overhead and memory cost of distributed JOINs. For time-series data with variable attributes, Nested types combine multiple arrays with matching indices.

Approximate aggregation functions are the key to interactive analytics on billion-row tables. uniqHLL12 uses HyperLogLog with 2^12 buckets for approximate distinct counts with ~1.5% error and 100x less memory than exact COUNT(DISTINCT). quantileTDigest approximates percentiles using a t-digest sketch, enabling p50/p95/p99 calculations on billions of values without sorting. These functions also compose with materialized views: store the sketch state with uniqState/quantileState and merge across time windows with uniqMerge/quantileMerge.

At production scale, combining these techniques yields dramatic performance improvements. A query on a table with 50 columns and 10 billion rows that filters by status (5% selectivity) and computes unique users: PREWHERE on status reads only the status column first (1 column instead of 50 = 98% I/O reduction), then reads user_id only for matching rows, and uniqHLL12 computes the distinct count using KB of memory instead of GB. The query completes in seconds instead of minutes.

The non-obvious gotcha is that PREWHERE should not be used on columns that are part of the ORDER BY key because those columns are already efficiently filtered by the sparse primary index in the WHERE phase. Using PREWHERE on primary key columns can actually slow queries by adding an extra processing step. Also, approximate functions have error bounds that matter: uniqHLL12's 1.5% error on 1 million users means your count could be off by 15,000. For billing or compliance reporting where exact counts matter, use uniqExact and accept the performance cost.