初めてのPROJECTIONを作成する

uk_price_paid テーブルは (postcode, addr1, addr2) でソートされています。つまり、postcode、addr1、addr2 でフィルタする場合、ClickHouse は大きなデータブロックをスキップできますが、town でフィルタするクエリでは 3,000 万行すべてをスキャンしなければなりません。

projection は、同じテーブル内に (一部またはすべての) カラムの追加のソート済みコピーを保持します。テーブルにクエリを実行すると、クエリオプティマイザは projection から読み取ったほうがベースデータより少ない granule で済むかどうかを自動的に確認し、該当する場合は透過的にそれを使用します。

materialized view との主な違い:

• 別のテーブルは不要 - projection は uk_price_paid 自体の中にあります
• 透過的なクエリ最適化 - 通常どおり uk_price_paid をクエリするだけで、ClickHouse が自動的に projection を選択します
• mutation と同期された状態を維持 - テーブルに適用された削除や更新は projection に反映されます

詳しくは、projection のリファレンスドキュメントを参照してください。

uk_price_paid に、town、date、price、type を (town, date) でソートして保存するプロジェクションを定義します。

ALTER TABLE uk_price_paid
    ADD PROJECTION uk_price_paid_by_town
    (
        SELECT town, date, price, type
        ORDER BY (town, date)
    );

これにより、projection はテーブルのメタデータに登録されますが、既存データに対してはマテリアライズされません。反映されるのは今後の insert のみです。

projection がテーブル定義に表示されていることを確認します:

SHOW CREATE TABLE uk_price_paid;

出力に PROJECTION uk_price_paid_by_town ブロックが表示されるはずです。

materialized view と同様に、新しく追加したプロジェクションが適用されるのは今後の insert のみです。テーブルにすでにある 3,000 万行にも適用するには、明示的にマテリアライズします:

ALTER TABLE uk_price_paid
    MATERIALIZE PROJECTION uk_price_paid_by_town;

これはバックグラウンドミューテーションとして実行されます。進捗は次のように確認できます:

SELECT
    mutation_id,
    command,
    is_done
FROM system.mutations
WHERE table = 'uk_price_paid'
ORDER BY create_time DESC
LIMIT 5;

is_done = 1 になれば、プロジェクションのマテリアライズは完了です。system.projection_parts を確認して確かめることもできます:

SELECT
    name,
    count() AS parts,
    sum(rows) AS total_rows,
    formatReadableSize(sum(bytes_on_disk)) AS size
FROM system.projection_parts
WHERE table = 'uk_price_paid'
  AND active = true
GROUP BY name;

では、これまでクエリしてきた同じテーブルに対して、town で絞り込むクエリを実行します:

SELECT
    toYear(date) AS year,
    round(avg(price)) AS avg_price,
    count() AS sales
FROM uk_price_paid
WHERE town = 'LONDON'
GROUP BY year
ORDER BY year DESC;

クエリ統計を確認すると、プロジェクション作成前に比べて読み取られる行数が大幅に減っていることがわかります。これは、ClickHouse がベースデータをスキャンする代わりに、uk_price_paid_by_town プロジェクションから読み取るよう自動的に選択したためです。

EXPLAIN を使えば、プロジェクションが使用されたことを確認できます。

EXPLAIN
SELECT
    toYear(date) AS year,
    round(avg(price)) AS avg_price,
    count() AS sales
FROM uk_price_paid
WHERE town = 'LONDON'
GROUP BY year
ORDER BY year DESC;

出力内で、プロジェクション名を参照している ReadFromMergeTree を探します。パフォーマンスを明確に比較したい場合は、単一のクエリに対してプロジェクションの最適化を無効にできます。

SELECT
    toYear(date) AS year,
    round(avg(price)) AS avg_price,
    count() AS sales
FROM uk_price_paid
WHERE town = 'LONDON'
GROUP BY year
ORDER BY year DESC
SETTINGS optimize_use_projections = 0;

これによりテーブル全体がスキャンされるため、読み取られる行数の違いを確認できます。

プロジェクションとmaterialized viewは、どちらも同じ課題、つまり異なるアクセスパターンでの読み取りを高速化するという課題を解決しますが、そのためのトレードオフは異なります。要するに、プロジェクションは同じデータに対して別のソート順が必要なだけの場合に最適です。一方、materialized viewは、データの変換や集約、あるいは別のスキーマへの振り分けが必要な場合に、より柔軟に対応できます。詳しい比較については、materialized view とプロジェクションの比較を参照してください。

プロジェクションは、選択したカラムのコピーを同じテーブル内にもう1つ保存するため、ディスク使用量が増加します。system.parts をクエリして、uk_price_paid の合計サイズ (プロジェクションのデータも含まれるようになっています) を確認します。

SELECT
    table,
    count() AS parts,
    sum(rows) AS total_rows,
    formatReadableSize(sum(bytes_on_disk)) AS compressed_size
FROM system.parts
WHERE table = 'uk_price_paid'
  AND active = true
GROUP BY table;

プロジェクション専用のストレージも確認できます。

SELECT
    name,
    count() AS parts,
    sum(rows) AS total_rows,
    formatReadableSize(sum(bytes_on_disk)) AS projection_size
FROM system.projection_parts
WHERE table = 'uk_price_paid'
  AND active = true
GROUP BY name;

これはmaterialized viewと同じ基本的なトレードオフです。読み取りを高速化する代わりに、より多くのディスク領域が必要になります。プロジェクションは、選択した4つのカラムのみを含み、圧縮率もソート順によって変わるため、完全なコピーより小さくなる場合があります。