Перейти к основному содержимому

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/sql-reference/statements/alter/projection

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

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

· 2 мин. чтения