ClickHouse DB::Exception: Projection couldn't be applied to this query

The "DB::Exception: Projection couldn't be applied to this query" error in ClickHouse occurs when the setting force_optimize_projection is enabled and the query optimizer determines that no available projection can satisfy the query. The PROJECTION_NOT_USED error code is a hint that the query will run without projection optimization, which the strict setting converts into a hard error.

Impact

The query fails without executing. This error only appears when force_optimize_projection is set to 1, which instructs ClickHouse to reject queries that cannot leverage a projection. Without this setting, the query would simply run using the base table data without projection optimization, potentially slower but still functional.

Common Causes

1. The query references columns not included in any defined projection
2. The query's WHERE clause does not align with the projection's sort order or partition key
3. The aggregation in the query does not match any projection's pre-aggregation definition
4. The projection has not been materialized yet (defined but data not built)
5. The force_optimize_projection setting is enabled at the profile or session level, making projection usage mandatory
6. The query uses features incompatible with projections (e.g., certain JOIN types, FINAL modifier)

Troubleshooting and Resolution Steps

1. Check which projections exist on your table:

   SHOW CREATE TABLE your_database.your_table;
   

2. Verify that projections have been materialized:

   SELECT table, name, part_type, is_broken
   FROM system.projection_parts
   WHERE table = 'your_table' AND active;
   

3. If projections are defined but not materialized, materialize them:

   ALTER TABLE your_database.your_table MATERIALIZE PROJECTION your_projection;
   

4. If you do not require strict projection usage, disable the forcing setting:

   SET force_optimize_projection = 0;
   

5. Adjust your query to match an existing projection. For example, if you have an aggregate projection:

   -- Projection definition:
   -- PROJECTION monthly_sales (SELECT toYYYYMM(date) AS month, sum(amount) GROUP BY month)
   
   -- This query can use it:
   SELECT toYYYYMM(date) AS month, sum(amount) FROM sales GROUP BY month;
   
   -- This query cannot (different aggregation):
   SELECT toYYYYMM(date) AS month, avg(amount) FROM sales GROUP BY month;
   

6. Create a new projection that matches your query pattern:

   ALTER TABLE your_table ADD PROJECTION new_projection (
       SELECT column1, column2, sum(column3)
       GROUP BY column1, column2
   );
   ALTER TABLE your_table MATERIALIZE PROJECTION new_projection;
   

Best Practices

• Only enable force_optimize_projection when you have specifically designed projections for your query patterns and want to ensure they are always used.
• Design projections to cover your most common query patterns, including the correct GROUP BY columns and aggregate functions.
• Remember to materialize projections after creating them; the definition alone does not build the optimized data.
• Monitor projection usage through the query log to verify they are being applied as expected.
• Keep projections lean and targeted. Each projection adds storage overhead and slows down inserts, so only create projections for high-value query patterns.

Frequently Asked Questions

Q: How do I check if a query is using a projection?
A: Use EXPLAIN to see the query plan, which will show whether a projection is selected. You can also check the projections field in system.query_log after the query executes.

Q: Do projections work with the FINAL modifier?
A: Projections may not be used with queries that require the FINAL modifier on ReplacingMergeTree or similar engines, as FINAL processing needs access to all versions of a row, which projections may not store.

Q: Can I have multiple projections on the same table?
A: Yes, you can define multiple projections on a single table. ClickHouse will automatically choose the best matching projection for each query. Each projection adds storage and insert overhead proportional to the data it covers.