All posts
Why ClickHouse Scans Every Granule Instead of Using Indexes

Why ClickHouse Scans Every Granule Instead of Using Indexes

November 17, 202611 min readSanjeev Kumar G
Share:

A query can have a selective WHERE condition and still read almost every granule in a table. This is a common problem in production ClickHouse clusters.

The query may return only a few rows, but ClickHouse must read millions of rows to find them. This increases disk I/O, CPU usage, query duration, and memory pressure.

The first thing to understand is that filtering rows and skipping granules are different operations.

PREWHERE can reduce the amount of column data that ClickHouse reads. However, it does not automatically reduce the number of granules that ClickHouse must inspect.

To reduce granule reads, ClickHouse needs an index that can identify which granules may contain matching rows.

This article explains how to identify this problem, understand the relevant settings, and prevent unexpected full-table scans.

1. The problem

Consider a production table that stores application events.

The table has 500 million rows and uses the following sorting key:

The table is sorted by (tenant_id, event_time).

CREATE TABLE events
(
    tenant_id UInt32,
    event_time DateTime,
    user_id UInt64,
    event_type LowCardinality(String),
    message String
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(event_time)
ORDER BY (tenant_id, event_time);

An application runs this query:

SELECT
    user_id,
    event_time,
    event_type
FROM events
WHERE user_id = 12345;

The query returns only a small number of rows. However, user_id is not part of the sorting key.

ClickHouse cannot use the primary key to identify a small range of granules for this condition. It must inspect the table's data to find matching rows.

The query can therefore read a large part of the table.

This problem is not always caused by a missing index. Sometimes, the query does not match the table's sorting key.

2. Why it happens

ClickHouse uses sparse primary indexes in MergeTree tables. These indexes store key values at granule boundaries.

The primary key helps ClickHouse identify which granules may contain matching rows. ClickHouse can skip granules that cannot contain matching data.

However, the primary key works best when the query filters on the sorting key.

For example, the table uses:

ORDER BY (tenant_id, event_time)

This query matches the sorting key:

SELECT
    count()
FROM events
WHERE tenant_id = 100
  AND event_time >= '2026-10-01 00:00:00'
  AND event_time < '2026-10-02 00:00:00';

ClickHouse can use the primary key to restrict the data ranges.

Now consider this query:

SELECT
    count()
FROM events
WHERE event_time >= '2026-10-01 00:00:00'
  AND event_time < '2026-10-02 00:00:00';

The query does not filter on the first sorting key column, tenant_id.

ClickHouse may not be able to identify a small range through the primary key. The result depends on the key structure and available partition information.

There are three important concepts to understand.

Primary key index: This index helps ClickHouse skip granules based on the sorting key.

Data skipping indexes: These indexes help ClickHouse skip granules based on other columns. Examples include minmax, set, and bloom_filter.

PREWHERE: This optimization reduces the amount of column data read before ClickHouse applies the remaining filters. It does not guarantee that ClickHouse skips granules.

Another optimization is optimize_read_in_order.

This setting helps ClickHouse read data in sorting key order when the query's ORDER BY matches the table's sorting key. It can avoid an additional sorting step.

However, it does not solve a filter that cannot use the primary key.

3. How to confirm

Start by checking the table's sorting key and primary key.

SELECT
    database,
    name,
    engine,
    partition_key,
    sorting_key,
    primary_key
FROM system.tables
WHERE database = 'default'
  AND name = 'events';

Check whether the query's filter columns match the sorting key.

Next, use EXPLAIN indexes = 1 to inspect index selection.

Run the following query:

EXPLAIN indexes = 1
SELECT
    count()
FROM events
WHERE user_id = 12345;

The output contains information about the indexes used during query planning.

For example:

ReadFromMergeTree (default.events)
Indexes:
  PrimaryKey
    Condition: true
    Parts: 120/120
    Granules: 61000/61000

The numbers above are illustrative.

Read the Granules value as selected granules divided by total granules.

In this example, ClickHouse selects all 61,000 granules. The primary key cannot restrict the data ranges.

Now inspect the skip indexes.

If the table has skip indexes, the output also shows their selection results.

For example:

Skip
  Name: user_id_idx
  Description: bloom_filter GRANULARITY 4
  Parts: 0/120
  Granules: 0/61000

This example shows that the skip index can eliminate the granules for this query.

If the output shows that a skip index selects most granules, the index may not provide useful filtering.

Do not assume that an index works only because it exists. Check its actual selection results.

Also inspect production queries through system.query_log.

SELECT
    query_id,
    query_duration_ms,
    read_rows,
    read_bytes,
    substring(query, 1, 150) AS query_start
FROM system.query_log
WHERE type = 'QueryFinish'
  AND event_time >= now() - INTERVAL 1 HOUR
  AND read_rows > 1000000
ORDER BY read_rows DESC
LIMIT 20;

This query identifies completed queries that read many rows.

Compare read_rows with the number of rows returned. A large difference can indicate inefficient filtering.

4. The settings

The following defaults come from ClickHouse master, commit 9f79cc1, dated 30 October 2026.

Check the actual values on your server before changing them.

SettingDefaultWhat it controlsRestart needed
optimize_move_to_prewheretrueAutomatically moves suitable conditions from WHERE to PREWHERE.No
optimize_read_in_ordertrueEnables reading in sorting key order for suitable ORDER BY queries.No
use_skip_indexestrueEnables data skipping indexes during query execution.No
force_primary_keyfalseRejects queries that cannot use the primary key.No
force_index_by_datefalseRejects queries that cannot use a suitable date index condition.No

These are query-level settings. You can change them in a settings profile, a session, or one query.

Defaults can change between releases. Check your own server.

SELECT
    name,
    value,
    default,
    changed
FROM system.settings
WHERE name IN
(
    'optimize_move_to_prewhere',
    'optimize_read_in_order',
    'use_skip_indexes',
    'force_primary_key',
    'force_index_by_date'
)
ORDER BY name;

system.server_settings contains server-level settings. Use system.settings to inspect the query settings covered here.

What each setting does

  • optimize_move_to_prewhere: Moves suitable filters to PREWHERE automatically. This reduces unnecessary column reads.
  • optimize_read_in_order: Helps queries read data in sorting key order. This is useful for queries that use compatible ORDER BY clauses.
  • use_skip_indexes: Enables data skipping indexes. Set it to true if your workload needs these indexes.
  • force_primary_key: Rejects a query when ClickHouse cannot use the primary key to restrict data ranges.
  • force_index_by_date: Rejects a query when ClickHouse cannot use a suitable date condition for data restriction.

One important detail: force_primary_key does not guarantee that a query reads fewer granules. It only checks whether a usable primary key condition exists.

Similarly, force_index_by_date does not guarantee a selective date range.

5. How to fix it

Start with the query and table design. Do not change settings before checking the sorting key.

Change 1: Match the filter to the sorting key

The original query filters only on user_id.

SELECT
    count()
FROM events
WHERE user_id = 12345;

If the application knows the tenant ID and time range, include them in the filter.

SELECT
    count()
FROM events
WHERE tenant_id = 100
  AND event_time >= '2026-10-01 00:00:00'
  AND event_time < '2026-10-02 00:00:00'
  AND user_id = 12345;

This query can use the primary key to restrict the data ranges.

Keep user_id as an additional filter. ClickHouse applies it to the remaining candidate rows.

Do not add artificial conditions just to satisfy the primary key. Every condition must preserve the application's intended results.

Change 2: Keep PREWHERE optimization enabled

Use the default value unless testing shows a specific problem.

For one query:

SELECT
    count()
FROM events
WHERE tenant_id = 100
  AND event_time >= '2026-10-01 00:00:00'
SETTINGS optimize_move_to_prewhere = 1;

This allows ClickHouse to move suitable conditions into PREWHERE.

For a session:

SET optimize_move_to_prewhere = 1;

You can also use the setting in a settings profile.

Change 3: Keep skip indexes enabled

If your table has useful data skipping indexes, keep this setting enabled.

SELECT
    count()
FROM events
WHERE user_id = 12345
SETTINGS use_skip_indexes = 1;

This allows ClickHouse to use available skip indexes.

Do not create a skip index without testing its selectivity. An index that cannot eliminate enough granules can add maintenance overhead without improving query performance.

Change 4: Reject queries without primary key conditions

This is useful when your production workload must use the primary key for data restriction.

You can enable force_primary_key in a settings profile.

For example, add the following profile to users.xml:

<clickhouse>
    <profiles>
        <production>
            <force_primary_key>1</force_primary_key>
        </production>
    </profiles>
</clickhouse>

Assign the production profile to the intended user through the user configuration.

For a session, use:

SET force_primary_key = 1;

For one query, use:

SELECT
    count()
FROM events
WHERE tenant_id = 100
  AND event_time >= '2026-10-01 00:00:00'
SETTINGS force_primary_key = 1;

The value 1 rejects queries without a suitable primary key condition.

Use 0 to disable this restriction.

Change 5: Enforce date index conditions when required

If your table and workload depend on date-based filtering, consider force_index_by_date.

For a session:

SET force_index_by_date = 1;

For one query:

SELECT
    count()
FROM events
WHERE event_time >= '2026-10-01 00:00:00'
  AND event_time < '2026-10-02 00:00:00'
SETTINGS force_index_by_date = 1;

Use this setting only when your workload requires a suitable date key condition.

It does not guarantee that the selected date range is small. Check the actual granule selection with EXPLAIN.

6. How to verify the fix

After changing the query or settings, run EXPLAIN indexes = 1 again.

EXPLAIN indexes = 1
SELECT
    count()
FROM events
WHERE tenant_id = 100
  AND event_time >= '2026-10-01 00:00:00'
  AND event_time < '2026-10-02 00:00:00'
  AND user_id = 12345;

Compare the selected granules with the original query.

For example:

MetricBeforeAfter
Total granules61,00061,000
Selected granules61,000120
Granules skipped060,880

These numbers are illustrative.

Next, execute both queries and compare their actual resource usage.

SELECT
    query_id,
    query_duration_ms,
    read_rows,
    read_bytes,
    memory_usage
FROM system.query_log
WHERE type = 'QueryFinish'
  AND event_time >= now() - INTERVAL 1 HOUR
ORDER BY event_time DESC
LIMIT 10;

Compare the following metrics:

  • read_rows: Rows read by the query.
  • read_bytes: Bytes read by the query.
  • query_duration_ms: Query execution time.
  • memory_usage: Memory used by the query.

Use the same parameters and compare results. The optimized query must return the same data.

Also test representative production queries before enabling restrictions for all users.

7. Risks and trade-offs

The settings in this article are useful, but they do not replace good table design.

Consider these risks before applying them in production.

Some queries intentionally scan the full table. Examples include reports, aggregations, and administrative queries.

Enabling this setting globally can cause those queries to fail.

Use a separate settings profile for workloads that require primary key filtering.

Some queries do not need date filtering. They may still be valid for the application.

Also, a date condition does not always produce a small data range.

PREWHERE does not replace index pruning.

A query can use PREWHERE and still read every granule. PREWHERE reduces column reads, not necessarily granule reads.

Skip indexes have maintenance costs.

They consume storage and add work during data insertion and merges. Their usefulness depends on the data distribution and query patterns.

Reading in order has specific requirements.

optimize_read_in_order does not make an unrelated WHERE condition selective. It helps when the ORDER BY clause matches the sorting key.

Always test these changes against real production queries before applying them to every user.

8. Summary

  • Check the table's sorting key before changing query settings.
  • Use EXPLAIN indexes = 1 to inspect primary key and skip index granule selection.
  • Use PREWHERE to reduce unnecessary column reads, not as a replacement for index pruning.
  • Enable force_primary_key in a suitable settings profile to reject queries without usable primary key conditions.
  • Test all restrictions against existing production queries before enabling them globally.

Technical references

Share: