Day 17 of #100DaysOfClickHouse: Mastering Data Filtering for Faster ClickHouse Queries

Source: Dev.to

When working with ClickHouse®, writing a query is usually straightforward. Writing an efficient query, however, requires understanding how ClickHouse reads and filters data. Many users assume that adding a simple WHERE clause automatically results in fast query performance. While filtering is certainly important, not all filters are equally effective. The difference between a query that scans millions of rows and one that scans only a fraction of them often comes down to how the filtering conditions align with the table design. In this article, we’ll explore how filtering works in ClickHouse®, common mistakes to avoid, and practical best practices for improving query performance. ClickHouse® is designed to process massive analytical datasets efficiently. Even though the engine is incredibly fast, scanning unnecessary data still consumes: CPU resources Memory Disk I/O Network bandwidth Effective filtering helps ClickHouse eliminate irrelevant data as early as possible, reducing the amount of work required to execute a query. For large datasets, proper filtering can significantly reduce query execution times. Unlike traditional databases that often rely heavily on row-level indexes, ClickHouse primarily uses: Partitions Sorting keys Primary indexes Data skipping indexes These mechanisms help the engine determine which portions of data can safely be ignored during query execution. The goal is not to find individual rows immediately but to avoid reading large sections of irrelevant data. One of the most important factors affecting filtering performance is the table’s sorting key. Consider a table created as follows: CREATE TABLE events ( timestamp DateTime, user_id UInt64, event_type String ) ENGINE = MergeTree ORDER BY (user_id, timestamp);

Now consider this query: SELECT * FROM events WHERE user_id = 12345;

Because the data is physically ordered by user_id, ClickHouse can quickly identify the relevant data ranges and skip large portions of the table. This is far more efficient than filtering on a column that does not participate in the sorting key. Design sorting keys around the columns most commonly used in filtering conditions. Filtering conditions should be applied as early as possible. Good: SELECT * FROM events WHERE timestamp >= today() - 7;

Less efficient: SELECT * FROM ( SELECT * FROM events ) WHERE timestamp >= today() - 7;

Applying filters early allows ClickHouse to reduce the amount of data processed throughout the query pipeline. The earlier irrelevant rows are removed, the less work ClickHouse has to perform later. Most analytical workloads involve time-series data. Queries that restrict the time range often perform significantly better than queries that scan the entire dataset. Example: SELECT count() FROM events WHERE timestamp >= now() - INTERVAL 1 DAY;

Instead of reading years of historical data, ClickHouse only examines the relevant portion of the dataset. This becomes even more effective when partitions are based on time. Always narrow the time range whenever possible. A common performance mistake involves applying functions directly to columns used in filtering conditions. Example: SELECT * FROM events WHERE toDate(timestamp) = today();

Although this query works, it may prevent ClickHouse from efficiently using its indexing structures. A better approach is: SELECT * FROM events WHERE timestamp >= today() AND timestamp < today() + 1;

This preserves the original column values and enables more effective data skipping. Functions can force ClickHouse to evaluate every row before determining whether it matches the filter. Queries containing multiple OR conditions can sometimes reduce filtering efficiency. Example: SELECT * FROM events WHERE country = ‘US’ OR country = ‘UK’ OR country = ‘DE’;

In many situations, using IN is clearer and may produce more efficient execution plans. Example: SELECT * FROM events WHERE country IN (‘US’, ‘UK’, ‘DE’);

This approach also improves query readability and maintainability. One of the unique features of ClickHouse is the PREWHERE clause. Example: SELECT * FROM events PREWHERE user_id = 12345;

PREWHERE allows ClickHouse to load filtering columns first and postpone reading other columns until after irrelevant rows have been eliminated. For wide tables containing many columns, this can reduce disk reads and improve performance. In many cases, ClickHouse automatically moves suitable conditions into PREWHERE, but understanding the feature is still valuable. Wide tables with many columns Highly selective filters Queries returning a small subset of rows For columns that are frequently filtered but not part of the sorting key, data skipping indexes may help. Example: INDEX idx_country country TYPE set(100) GRANULARITY 1

Data skipping indexes do not behave like traditional B-tree indexes. Instead, they store lightweight metadata that helps ClickHouse determine whether specific data blocks can be skipped entirely. They can be particularly useful for: Country filters Status fields Categories Event types When used appropriately, they can significantly reduce the amount of scanned data. Data skipping indexes should complement good table design, not replace it. Columns with highly random values often provide limited opportunities for data skipping. Example: WHERE uuid = ‘f47ac10b-58cc-4372-a567-0e02b2c3d479’

If the column is not part of the sorting key and lacks an appropriate index, ClickHouse may need to examine a large amount of data. Whenever possible, design sorting keys around commonly filtered columns. ClickHouse provides several tools for understanding query behavior. Useful system tables include: system.query_log system.parts system.tables Example: SELECT query, read_rows, read_bytes, query_duration_ms FROM system.query_log ORDER BY event_time DESC LIMIT 10;

Monitoring these metrics helps identify queries that are scanning more data than necessary. Read rows Read bytes Query duration Memory usage Number of scanned parts Avoid: SELECT * FROM logs;

Large analytical tables should almost always include meaningful filtering conditions. Designing tables without considering how users will filter data often leads to poor performance. Indexes are helpful, but they should not be viewed as a replacement for proper sorting keys and partitioning strategies. This can limit ClickHouse’s ability to skip irrelevant data efficiently. When filtering data in ClickHouse: Align filters with sorting keys whenever possible. Filter early in the query pipeline. Use time-based filters. Avoid unnecessary functions on filtered columns. Prefer IN over long OR chains. Understand when PREWHERE can help. Consider data skipping indexes for frequently filtered columns. Monitor query performance using system tables. Design tables around actual query patterns. Efficient filtering is one of the most important aspects of query optimization in ClickHouse. While the database is capable of scanning enormous datasets quickly, the best-performing queries are those that help ClickHouse eliminate unnecessary data before it is read. By understanding how partitions, sorting keys, PREWHERE, and data skipping indexes work together, you can dramatically reduce scanned rows, improve response times, and build more efficient analytical workloads. The goal is not simply to write queries that work. The goal is to write queries that allow ClickHouse to do as little work as possible. When ClickHouse reads less data, everything gets faster. Link - https://quantrail-data.com/filtering-data-clickhouse-best-practices/