Skip to main content
    • Knowledge Base/
    Projection Example

    How to confirm if a Projection is used by the query?

    Learn how to check if a projection is used in ClickHouse queries by testing with sample data and using EXPLAIN to verify projection usage.

    Question​

    How can I tell if a projection is used?

    Answer​

    1. Create a sample database
    1. Create a sample table that will use column1 as the primary key
    1. Add a projection for_column2 to use column2 as the primary key
    1. Insert test data

    *this inserts 100000 rows with random numbers in column1 and column2

    1. Check sample set of data
    1. Test that it is using the original table with column1:

    *notice that it is reading from db1.table1_projections

    1. Test reading from the projection by using column2 in the where clause

    *notice that now the for_column2 projection is used.

    For more info

    Projections: https://clickhouse.com/docs/en/sql-reference/statements/alter/projection

    numbers table function: https://clickhouse.com/docs/en/sql-reference/table-functions/numbers

    Blog for generating random data: https://clickhouse.com/blog/generating-random-test-distribution-data-for-clickhouse

    · 2 min read