How ClickHouse Picks a Projection to Read

TL;DR: Every projection of a table is a candidate. For each one, the planner asks the same question the base table’s read asks — how many marks will this read touch? — and adds the marks of the parts the projection cannot serve, because those parts still have to be read the old way. The cheapest candidate wins, with ties broken by whether the projection’s sort order serves the query’s ORDER BY.

A skip index is a filter: it tells the reader “these granules cannot contain matching rows”, and the reader skips them. A projection is something else entirely. It is a second copy of the data, stored inside the part, with its own sorting key, its own columns and its own aggregate states. When a query matches a projection, the engine can read the projection instead of the table — and if the projection is sorted by something the query wants ordered, the engine can even skip the ORDER BY entirely.

That makes projections a substitution mechanism, and it makes their selection an optimisation problem rather than a filter problem. The planner has to answer: for this query, is reading the projection cheaper than reading the table? And “cheaper” is not a single number, because a projection may only exist for some of the parts.

What a projection is, physically

The syntax is a SELECT inside the table definition:

sql
-- src/Storages/MergeTree/registerStorageMergeTree.cpp:1331
    PROJECTION projection_name_1 (SELECT <COLUMN LIST EXPR> [GROUP BY] [ORDER BY]),
    PROJECTION projection_name_2 (SELECT <COLUMN LIST EXPR> [GROUP BY] [ORDER BY])

and the storage model is spelled out in the engine’s own documentation (src/Storages/MergeTree/registerStorageMergeTree.cpp:1974): the projection is stored inside the part directory, in a subdirectory that holds an anonymous MergeTree table’s part, induced by the definition query of the projection. If the projection has a GROUP BY clause, the underlying storage engine becomes AggregatingMergeTree and all aggregate functions are converted to AggregateFunction; if it has an ORDER BY clause, the MergeTree table uses it as its primary key expression. During the merge process the projection part is merged via its storage’s merge routine.

So a projection is a full MergeTree table, living in a subdirectory of the parent’s part directory, with its own sorting key and its own merge behaviour. A projection with a GROUP BY is an AggregatingMergeTree; a projection with an ORDER BY is sorted by that key, and a query that wants that order can read it in order.

Projections are per-part, and that is the whole difficulty

A projection part is created per parent part. On insert, the writer computes the projection for every block and stores the result next to the parent part:

cpp
// src/Storages/MergeTree/MergeTreeDataWriter.cpp:1180
    if ((*data.getSettings())[MergeTreeSetting::materialize_projections_on_insert])
    {
        for (const auto & projection : metadata_snapshot->getProjections())
        {
            /// Commit-order projections use `_block_number` which is only finalized at commit time.
            /// Skip during insert; they will be built correctly during the first merge.
            if (projection.with_block_number)
                continue;

            Block projection_block;
            {
                ProfileEventTimeIncrement<Microseconds> watch(ProfileEvents::MergeTreeDataWriterProjectionsCalculationMicroseconds);
                projection_block = projection.calculate(block, 0, context, perm_ptr);
                LOG_DEBUG(
                    log, "Spent {} ms calculating projection {} for the part {}", watch.elapsed() / 1000, projection.name, new_data_part->name);
            }

            if (projection_block.rows())
            {
                auto proj_temp_part
                    = writeProjectionPart(data, projection_block, projection, new_data_part.get(), compression_codec, /*merge_is_needed=*/false, context);
                new_data_part->addProjectionPart(projection.name, std::move(proj_temp_part->part));

The setting is on by default (materialize_projections_on_insert, new in 26.6), so a fresh part has its projections. But a part that was inserted before the projection existed does not, and a part whose projection is broken does not either. The planner cannot assume “the projection exists” — it has to check, per part.

That is exactly what the candidate analysis does. For every part the base-table read would touch, it asks whether the part has a usable projection part:

cpp
// src/Processors/QueryPlan/Optimizations/projectionsCommon.cpp:446
    RangesInDataParts projection_parts;
    size_t parent_parts_sum_marks = 0;
    for (const auto & part_with_ranges : parent_reading_select_result.parts_with_ranges)
    {
        const auto & created_projections = part_with_ranges.data_part->getProjectionParts();
        auto it = created_projections.find(candidate.projection->name);
        MergeTreeDataPartPtr projection_part = it != created_projections.end() && !it->second->is_broken ? it->second : nullptr;
        if (!projection_part && hypothetical_projection && candidate.projection == &hypothetical_projection->projection)
            projection_part = hypothetical_projection->findPart(part_with_ranges.data_part->name);
        /// A projection part returns every matching row of its part, so it cannot replace a read cut by exact counting.
        if (projection_part
            && !(parent_reading_select_result.exact_ranges_excluded && !part_with_ranges.exact_ranges.empty())
            && projectionPartHasRequiredColumns(
                *projection_part, *part_with_ranges.data_part, *candidate.projection, parent_metadata, required_column_names)
            && !partHasPendingMetadataMutationsOnColumns(
                parent_mutations_snapshot, part_with_ranges.data_part, *candidate.projection, required_column_names))
        {
            projection_parts.push_back(RangesInDataPart(
                projection_part,
                part_with_ranges.data_part,
                part_with_ranges.part_index_in_query,
                part_with_ranges.part_starting_offset_in_query));
        }
        else
        {
            candidate.parent_parts.emplace(part_with_ranges.data_part.get());
            parent_parts_sum_marks += part_with_ranges.getMarksCount();
        }
    }

Read the else branch: when a part has no usable projection, that part is pinned to the base table, and its marks are added to the projection’s cost. That single line is the heuristic. A projection that covers 90 % of the parts still costs the marks of the remaining 10 %, because those parts must be read the old way.

The cost is then the sum of two terms:

cpp
// src/Processors/QueryPlan/Optimizations/projectionsCommon.cpp:516
    candidate.merge_tree_projection_select_result_ptr = std::move(projection_result_ptr);
    candidate.sum_marks += candidate.merge_tree_projection_select_result_ptr->selected_marks + parent_parts_sum_marks;

    return true;

selected_marks is the number of granules the projection read would touch; parent_parts_sum_marks is the number of granules the uncovered parts would touch. The unit is deliberately the same one the base-table read uses, so the comparison is apples to apples.

Note also the early exit at the top of the function:

cpp
// src/Processors/QueryPlan/Optimizations/projectionsCommon.cpp:476
    if (projection_parts.empty())
        return false;

A projection that no part materialises is not a candidate at all. The planner’s own message for this case is explicit:

cpp
// src/Processors/QueryPlan/Optimizations/optimizeUseNormalProjection.cpp:664
        if (!analyzed)
        {
            result.projection_reject_reasons.try_emplace(projection->name, "no selected part has the projection materialized, or its analysis exceeded the read limits");
            continue;
        }

The comparison

Now the selection itself. The candidate is compared against the base table’s read, and the tie-break is the sort order:

cpp
// src/Processors/QueryPlan/Optimizations/optimizeUseNormalProjection.cpp:697
        /// Consider projections with equal read cost only if:
        /// - `force_optimize_projection` or `prefer_optimize_projection` is enabled, or
        /// - the parent reading's `selected_marks` becomes zero, or
        /// - the projection's sort order matches the query's ORDER BY,
        if (!relax_projection_checks && candidate.sum_marks > parent_reading_marks)
        {
            stat.description = fmt::format(
                "Projection {} is usable but requires reading {} marks, which is not better than the original table with {} marks",
                candidate.projection->name,
                candidate.sum_marks,
                parent_reading_marks);

            result.projection_reject_reasons.try_emplace(projection->name, stat.description);
            LOG_DEBUG(logger, "{}", stat.description);
            continue;
        }
        else if (candidate.sum_marks == parent_reading_marks && parent_reading_marks > 0 && !relax_projection_checks && !sort_order_helps)
        {
            stat.description = fmt::format(
                "Projection {} is usable but requires reading {} marks and does not help with sorting, which is not better than the original table",
                candidate.projection->name,
                candidate.sum_marks);

            result.projection_reject_reasons.try_emplace(projection->name, stat.description);
            LOG_DEBUG(logger, "{}", stat.description);
            continue;
        }

        if (best_candidate == nullptr
            || candidate.sum_marks < best_candidate->sum_marks
            || (candidate.sum_marks == best_candidate->sum_marks && sort_order_helps && !projection_sort_order_useful(best_candidate->projection)))
            best_candidate = &candidate;

        /// All parts has been filtered out; no need to analyze further projections.
        if (parent_reading_marks == 0)
            break;

The rules, distilled:

A projection that reads more marks than the base table is rejected. No amount of “it would be nicer” beats reading more data.
A projection that reads exactly as many marks is accepted only if its sort order serves the query’s ORDER BY — because then it saves the sort, which is worth something even at equal I/O.
Among the survivors, the one with the fewest marks wins; ties are broken in favour of the projection whose sort order helps.

Two escape hatches relax rule 1 and 2: the force_optimize_projection and prefer_optimize_projection settings. The latter is what the streaming read path uses when it has already decided which projection it wants:

cpp
// src/Storages/MergeTree/Streaming/ReadingPlan/ReadRoundContext.cpp:20
ContextPtr makeStreamingContext(ContextPtr context_, const ProjectionDescription * projection)
{
    auto copy = Context::createCopy(context_);
    copy->makeQueryContext();
    copy->setQueryMetadataCache(nullptr);

    if (projection)
    {
        copy->setSetting("preferred_optimize_projection_name", projection->name);
        copy->setSetting("prefer_optimize_projection", true);
    }

    return copy;
}

There is also a hypothetical mode: the planner can be asked “what would happen if this projection existed?”, and it answers with the marks it would read, the rows it would read, and whether the optimiser would actually choose it. That is what the EXPLAIN output for projections reports.

Two rejections that are not about cost

Before the cost comparison, two structural rejections happen, and they are worth separating because they are about correctness, not efficiency.

The first is the WHERE implication check. A projection may carry its own WHERE, which means it only stores a subset of the rows. If the query’s filter does not imply the projection’s filter, the projection is missing rows that the query might need, and it cannot be used:

cpp
// src/Processors/QueryPlan/Optimizations/optimizeUseNormalProjection.cpp:610
        /// Skip projections whose WHERE condition is not implied by the query's filter (Issue #74234).
        /// A projection with WHERE stores only a subset of rows, so we can only use it
        /// if the query's filter guarantees it won't need rows outside that subset.
        if (projection->where_clause_ast)
        {
            if (!doesQueryFilterImplyProjectionWhere(query.filter_node, projection->where_clause_ast, projection->query_ast, context))
            {
                LOG_DEBUG(logger, "Projection {} skipped: query WHERE does not imply projection WHERE", projection->name);
                result.projection_reject_reasons.try_emplace(projection->name, "the query WHERE does not imply the projection WHERE");
                continue;
            }
        }

The second is the column check: the projection must contain every column the query needs. If it does not, the projection cannot serve the query — but it may still be useful for filtering, so the planner does not simply give up:

cpp
// src/Processors/QueryPlan/Optimizations/optimizeUseNormalProjection.cpp:623
        if (!has_all_required_columns(projection))
        {
            result.projection_reject_reasons.try_emplace(projection->name, "the projection does not contain all columns required by the query");

            /// Check if projection can be used to filter parts or building projection index filters
            if (query.filter_node && optimize_use_projection_filtering)
            {
                MergeTreeDataSelectExecutor reader(reading->getMergeTreeData(), projection);
                filterPartsAndCollectProjectionCandidates(
                    *reading,
                    *projection,
                    reader,
                    empty_mutations_snapshot,
                    *parent_reading_select_result,
                    projection_query_info,
                    query.filter_node,
                    context);
            }

            continue;
        }

A projection that is missing a column can still prune parts and granules, which is the “projection as a filter” mode.

The projection’s own key must be used

There is one more rule, and it is easy to miss because it is documented in the settings rather than in the planner. A projection is sorted by its own key, so a query that does not restrict by that key cannot use the projection’s ordering to narrow anything — and the engine will not pretend otherwise:

cpp
// src/Core/Settings.cpp:2190
    DECLARE(Bool, force_primary_key, false, R"(
Disables query execution if indexing by the primary key is not possible.

Works with tables in the MergeTree family.

If `force_primary_key=1`, ClickHouse checks to see if the query has a primary key condition that can be used for restricting data ranges. If there is no suitable condition, it throws an exception. However, it does not check whether the condition reduces the amount of data to read. For more information about data ranges in MergeTree tables, see [MergeTree](/reference/engines/table-engines/mergetree-family/mergetree).

The check applies to the primary key of the table itself. A projection serves the query only when the query also restricts data ranges by that projection's own key; otherwise the projection is skipped and the table is read instead. A projection forced with [force_optimize_projection](#force_optimize_projection) whose key is not used makes the query throw an exception.
)", 0) \

The same sentence is the reason a projection is usually declared with an ORDER BY that matches the query’s filter: without it, the projection can only help if the query happens to filter by the same column.

The two “relaxation” settings are documented next to each other, and the difference between them is only what happens when no projection can be used:

cpp
// src/Core/Settings.cpp:6815
    DECLARE(Bool, force_optimize_projection, false, R"(
Enables or disables the obligatory use of [projections](/reference/engines/table-engines/mergetree-family/mergetree#projections) in `SELECT` queries, when projection optimization is enabled (see [optimize_use_projections](#optimize_use_projections) setting).

When enabled, a projection that can serve the query is used even if it requires reading more marks than the table itself, and the query fails with the `PROJECTION_NOT_USED` error when no projection can be used.

Possible values:

- 0 — Projection optimization is not obligatory.
- 1 — Projection optimization is obligatory.
)", 0) \
    DECLARE(Bool, prefer_optimize_projection, false, R"(
Makes the projection optimization prefer [projections](/reference/engines/table-engines/mergetree-family/mergetree#projections) over the table in `SELECT` queries, when projection optimization is enabled (see [optimize_use_projections](#optimize_use_projections) setting).

When enabled, a projection that can serve the query is used even if it requires reading more marks than the table itself, the same as with [force_optimize_projection](#force_optimize_projection), but the query does not fail when no projection can be used.

Possible values:

- 0 — Projections are chosen by their estimated cost.
- 1 — A usable projection is chosen regardless of its estimated cost.
)", 0, \
        {"26.10", false, false, "New setting: choose a usable projection regardless of its estimated cost, like `force_optimize_projection`, but without failing the query when no projection is used."}) \

Keeping the projection parts alive

Writing a projection is only half the job: the projection parts have to be merged, or the table fills up with thousands of tiny projection parts. The projection merge is a cascade of levels, and each level merges a bounded number of parts at a time:

cpp
// src/Storages/MergeTree/MergeProjectionPartsTask.cpp:33
bool MergeProjectionPartsTask::executeStep()
{
    auto & current_level_parts = level_parts[current_level];
    auto & next_level_parts = level_parts[next_level];

    MergeTreeData::MutableDataPartsVector selected_parts;
    while (selected_parts.size() < max_parts_to_merge_in_one_level && !current_level_parts.empty())
    {
        selected_parts.push_back(std::move(current_level_parts.back()));
        current_level_parts.pop_back();
    }

    if (selected_parts.empty())
    {
        if (next_level_parts.empty())
        {
            LOG_WARNING(log, "There is no projection parts merged");

            /// Task is finished
            return false;
        }
        current_level = next_level;
        ++next_level;
        updatePartsRemaining();
    }
    else if (selected_parts.size() == 1)
    {
        if (next_level_parts.empty())
        {
            LOG_DEBUG(log, "Merged a projection part in level {}", current_level);
            selected_parts[0]->renameTo(projection.name + ".proj", true);
            selected_parts[0]->setName(projection.name);
            selected_parts[0]->is_temp = false;
            selected_parts[0]->temp_projection_block_number.reset();
            new_data_part->addProjectionPart(name, std::move(selected_parts[0]));

            /// Task is finished
            return false;
        }

        LOG_DEBUG(log, "Forwarded part {} in level {} to next level", selected_parts[0]->name, current_level);
        next_level_parts.push_back(std::move(selected_parts[0]));
        updatePartsRemaining();
    }

It is a merge tree of merge trees: a level that ends up with a single part forwards it to the next level, and a level that ends up with several parts merges them into one. The projection’s own merge mode follows its definition — Ordinary for a plain projection, Aggregating for one with a GROUP BY:

cpp
// src/Storages/MergeTree/MergeProjectionPartsTask.cpp:87
        MergeTreeData::MergingParams projection_merging_params;
        projection_merging_params.mode = MergeTreeData::MergingParams::Ordinary;
        if (projection.type == ProjectionDescription::Type::Aggregate)
            projection_merging_params.mode = MergeTreeData::MergingParams::Aggregating;

Because projections are per-part, the ordinary merge of the parent table also has to rebuild the projections of the parts it merges. During a merge, the projection is recomputed from the merged rows and written as a temporary projection part, and the codec of the parent is inherited so that a projection of a ZSTD parent is not silently downgraded to LZ4.

Finally, the merge predicate refuses to merge two parts whose projection sets differ:

cpp
// src/Storages/MergeTree/Compaction/MergePredicates/MergeTreeMergePredicate.cpp:118
    if (left.projection_names != right.projection_names)
    {
        return std::unexpected(
            PreformattedMessage::create(
                "Parts have different projection sets: {} in '{}' and {} in '{}'",
                left.projection_names,
                left.name,
                right.projection_names,
                right.name));
    }

A part “has” a projection only if the projection part exists and is not broken:

cpp
// src/Storages/MergeTree/Compaction/PartProperties.cpp:67
std::set<std::string> getCalculatedProjectionNames(const MergeTreeDataPartPtr & part)
{
    std::set<std::string> projection_names;

    for (auto && [name, projection_part] : part->getProjectionParts())
        if (!projection_part->is_broken)
            projection_names.insert(name);

    return projection_names;
}

which is why a projection added to a table does not immediately appear in old parts: the old parts have a different projection set, they will not merge with new ones, and the projection is backfilled by an explicit ALTER TABLE ... MATERIALIZE PROJECTION (or by materialize_projections_on_merge = 1).

Trade-offs

What the mechanism buys:

A second read path for free. The projection is stored once, at write time, and it is chosen per query by cost. No extra query, no extra table, no materialised-view pipeline to keep running.
A cost model in the same unit as the base read. Comparing “marks to read” against the base table’s “marks to read” means the decision automatically accounts for the fact that a projection may not cover all parts.
Order elimination as a tie-break. A projection that is sorted the way the query wants can win even at equal I/O, because it removes the Sort step entirely.
Graceful degradation. A projection that is missing, broken, or too incomplete is not an error — the planner falls back to the base table, and can still use the projection to prune.

What it costs:

Storage is doubled for the projected columns, and the write path pays for computing the projection on every insert.
The projection may not be usable at all. If a table has many small parts and the projection was only materialised on the new ones, every candidate costs “projection marks + the marks of the uncovered parts”, and the projection usually loses.
The cost model is a proxy. Marks are a good proxy for I/O, but not for decompression cost, cache behaviour, or the cost of the GROUP BY that an aggregate projection needs at read time.
The WHERE implication check is conservative. It compares the query’s filter with the projection’s filter structurally, and a mismatch rejects the projection even when the projection would in fact be correct.
Parts with different projection sets do not merge, which fragments the table until a MATERIALIZE PROJECTION backfill catches up.
Everything is per part. Adding a projection to a live table does not make it usable until every part has one.

In short, a projection is a bet that some queries will want a different shape of the data, and the planner’s job is to decide per query whether the bet paid off. The cost model is deliberately simple — marks — because a projection that cannot beat the base read on marks is rarely worth using at all, whatever else it might offer.