Projections

This setup produces:

- Five separate parts (one per inserted row)
- One primary index entry per row (in the base table and each projection)
- Each part contains exactly one row

With this setup, we run a query filtering on both `region` and `user_id`.
Since the base table’s primary index is built from `event_date` and `id`, it
is unhelpful here, ClickHouse therefore uses:

- `region_proj` to prune parts by region
- `user_id_proj` to further prune by `user_id`

This behavior is visible using `EXPLAIN projections = 1`, which shows how
ClickHouse selects and applies projections.

```
EXPLAIN projections=1
SELECT * FROM page_views WHERE region = 'us_west' AND user_id = 107;
```

```
    ┌─explain────────────────────────────────────────────────────────────────────────────────┐
 1. │ Expression ((Project names + Projection))                                              │
 2. │   Expression                                                                           │                                                                        
 3. │     ReadFromMergeTree (default.page_views)                                             │
 4. │     Projections:                                                                       │
 5. │       Name: region_proj                                                                │
 6. │         Description: Projection has been analyzed and is used for part-level filtering │
 7. │         Condition: (region in ['us_west', 'us_west'])                                  │
 8. │         Search Algorithm: binary search                                                │
 9. │         Parts: 3                                                                       │
10. │         Marks: 3                                                                       │
11. │         Ranges: 3                                                                      │
12. │         Rows: 3                                                                        │
13. │         Filtered Parts: 2                                                              │
14. │       Name: user_id_proj                                                               │
15. │         Description: Projection has been analyzed and is used for part-level filtering │
16. │         Condition: (user_id in [107, 107])                                             │
17. │         Search Algorithm: binary search                                                │
18. │         Parts: 1                                                                       │
19. │         Marks: 1                                                                       │
20. │         Ranges: 1                                                                      │
21. │         Rows: 1                                                                        │
22. │         Filtered Parts: 2                                                              │
    └────────────────────────────────────────────────────────────────────────────────────────┘
```

The `EXPLAIN` output (shown above) reveals the logical query plan, top to bottom:

|  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- |
| Row number Description|  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | | 3 Plans to read from the `page_views` base table| 5-13 Uses `region_proj` to identify 3 parts where region = 'us\_west', pruning 2 of the 5 parts| 14-22 Uses user`_id_proj` to identify 1 part where `user_id = 107`, further pruning 2 of the 3 remaining parts | | | | | | | |

In the end, just **1 out of 5 parts** is read from the base table.
By combining the index analysis of multiple projections, ClickHouse significantly reduces the amount of data scanned,
improving performance while keeping storage overhead low.

## Related content[​](#related-content "Direct link to Related content")

- [A Practical Introduction to Primary Indexes in ClickHouse](/docs/guides/best-practices/sparse-primary-indexes#option-3-projections)
- [Materialized Views](/docs/materialized-views)
- [ALTER PROJECTION](/docs/sql-reference/statements/alter/projection)

- [Introduction](#introduction)- [How do Projections work?](#how-do-projections-work)
    - [Smarter storage with `_part_offset`](#smarter_storage_with_part_offset)- [When to use Projections?](#when-to-use-projections)- [Examples](#examples)
        - [Filtering on columns which aren't in the primary key](#filtering-without-using-primary-keys)- [Using projections to speed up UK price paid queries](#using-projections-to-speed-up-UK-price-paid)- [Further examples](#further-examples)- [Combining projections in one query](#combining-projections)- [Related content](#related-content)

Was this page helpful?