Projections

[Edit this page](https://github.com/ClickHouse/clickhouse-docs/blob/main/docs/data-modeling/projections/1_projections.md)

## Introduction[​](#introduction "Direct link to Introduction")

ClickHouse offers various mechanisms of speeding up analytical queries on large
amounts of data for real-time scenarios. One such mechanism to speed up your
queries is through the use of *Projections*. Projections help optimize
queries by creating a reordering of data by attributes of interest. This can be:

1. A complete reordering
2. A subset of the original table with a different order
3. A precomputed aggregation (similar to a materialized view) but with an ordering
   aligned to the aggregation.

  


## How do Projections work?[​](#how-do-projections-work "Direct link to How do Projections work?")

Practically, a Projection can be thought of as an additional, hidden table to the
original table. The projection can have a different row order, and therefore a
different primary index, to that of the original table and it can automatically
and incrementally pre-compute aggregate values. As a result, using Projections
provide two "tuning knobs" for speeding up query execution:

- **Properly using primary indexes**
- **Pre-computing aggregates**

Projections are in some ways similar to [Materialized Views](/docs/materialized-views)
, which also allow you to have multiple row orders and pre-compute aggregations
at insert time.
Projections are automatically updated and
kept in-sync with the original table, unlike Materialized Views, which are
explicitly updated. When a query targets the original table,
ClickHouse automatically samples the primary keys and chooses a table that can
generate the same correct result, but requires the least amount of data to be
read as shown in the figure below:

### Smarter storage with `_part_offset`[​](#smarter_storage_with_part_offset "Direct link to smarter_storage_with_part_offset")

Since version 25.5, ClickHouse supports the virtual column `_part_offset` in
projections which offers a new way to define a projection.

There are now two ways to define a projection:

- **Store full columns (the original behavior)**: The projection contains full
  data and can be read directly, offering faster performance when filters match
  the projection’s sort order.
- **Store only the sorting key + `_part_offset`**: The projection works like an index.
  ClickHouse uses the projection’s primary index to locate matching rows, but reads the
  actual data from the base table. This reduces storage overhead at the cost of
  slightly more I/O at query time.

The approaches above can also be mixed, storing some columns in the projection and
others indirectly via `_part_offset`.

## When to use Projections?[​](#when-to-use-projections "Direct link to When to use Projections?")

Projections are an appealing feature for new users as they're automatically
maintained as data is inserted. Furthermore, queries can just be sent to a
single table where the projections are exploited where possible to speed up
the response time.

This is in contrast to Materialized Views, where the user has to select the
appropriate optimized target table or rewrite their query, depending on the
filters. This places greater emphasis on user applications and increases
client-side complexity.

Despite these advantages, projections come with some inherent limitations which
you should be aware of and thus should be deployed sparingly.

- Projections don't allow using different TTL for the source table and the
  (hidden) target table, materialized views allow different TTLs.
- Lightweight updates and deletes aren't supported for tables with projections.
- Materialized Views can be chained: the target table of one materialized view
  can be the source table of another materialized view, and so on. This isn't
  possible with projections.
- Projection definitions don't support joins, but Materialized Views do. However, queries on tables with projections can use joins freely.
- Projection definitions don't support filters (`WHERE` clause), but Materialized Views do. However, queries on tables with projections can filter freely.

We recommend using projections when:

- A complete re-ordering of the data is required. While the expression in the
  projection can, in theory, use a `GROUP BY,` materialized views are more
  effective for maintaining aggregates. The query optimizer is also more likely
  to exploit projections that use a simple reordering, i.e., `SELECT * ORDER BY x`.
  You can select a subset of columns in this expression to reduce storage
  footprint.
- Users are comfortable with the potential associated increase in storage footprint and
  overhead of writing data twice. Test the impact on insertion speed and
  [evaluate the storage overhead](/docs/data-compression/compression-in-clickhouse).

## Examples[​](#examples "Direct link to Examples")

### Filtering on columns which aren't in the primary key[​](#filtering-without-using-primary-keys "Direct link to Filtering on columns which aren't in the primary key")

In this example, we'll show you how to add a projection to a table.
We'll also look at how the projection can be used to speed up queries which filter
on columns which aren't in the primary key of a table.

For this example, we'll be using the New York Taxi Data
dataset available at [sql.clickhouse.com](https://sql.clickhouse.com/) which is ordered
by `pickup_datetime`.

Let's write a simple query to find all the trip IDs for which passengers
tipped their driver greater than $200:

```
SELECT
  tip_amount,
  trip_id,
  dateDiff('minutes', pickup_datetime, dropoff_datetime) AS trip_duration_min
FROM nyc_taxi.trips WHERE tip_amount > 200 AND trip_duration_min > 0
ORDER BY tip_amount, trip_id ASC
```

Notice that because we're filtering on `tip_amount` which isn't in the `ORDER BY`, ClickHouse
had to do a full table scan. Let's speed this query up.

So as to preserve the original table and results, we'll create a new table and copy the data using an `INSERT INTO SELECT`:

```
CREATE TABLE nyc_taxi.trips_with_projection AS nyc_taxi.trips;
INSERT INTO nyc_taxi.trips_with_projection SELECT * FROM nyc_taxi.trips;
```

To add a projection we use the `ALTER TABLE` statement together with the `ADD PROJECTION`
statement:

```
ALTER TABLE nyc_taxi.trips_with_projection
ADD PROJECTION prj_tip_amount
(
    SELECT *
    ORDER BY tip_amount, dateDiff('minutes', pickup_datetime, dropoff_datetime)
)
```

It is necessary after adding a projection to use the `MATERIALIZE PROJECTION`
statement so that the data in it is physically ordered and rewritten according
to the specified query above:

```
ALTER TABLE nyc.trips_with_projection MATERIALIZE PROJECTION prj_tip_amount
```

Let's run the query again now that we've added the projection:

```
SELECT
  tip_amount,
  trip_id,
  dateDiff('minutes', pickup_datetime, dropoff_datetime) AS trip_duration_min
FROM nyc_taxi.trips_with_projection WHERE tip_amount > 200 AND trip_duration_min > 0
ORDER BY tip_amount, trip_id ASC
```

Notice how we were able to decrease the query time substantially, and needed to scan
less rows.

We can confirm that our query above did indeed use the projection we made by
querying the `system.query_log` table:

```
SELECT query, projections 
FROM system.query_log 
WHERE query_id='<query_id>'
```