メインコンテンツまでスキップ
メインコンテンツまでスキップ

PREWHERE最適化はどのように機能しますか？

PREWHERE句は、ClickHouseにおけるクエリ実行の最適化機能です。これは、不要なデータの読み込みを避け、ディスクから非フィルタ列を読み込む前に関連性のないデータをフィルタリングすることで、I/Oを削減し、クエリ速度を向上させます。

このガイドでは、PREWHEREの機能、影響を測定する方法、および最適なパフォーマンスを引き出すための調整方法について説明します。

PREWHERE最適化なしのクエリ処理​

最初に、uk_price_paid_simpleテーブルに対してPREWHEREを使用せずにクエリがどのように処理されるかを示します：

PREWHERE最適化なしのクエリ処理


① クエリには、テーブルの主キーの一部であり、したがって主インデックスの一部でもあるtownカラムに対するフィルタが含まれています。

② クエリを加速するために、ClickHouseはテーブルの主インデックスをメモリに読み込みます。

③ インデックスエントリをスキャンして、townカラムのどのグラニュールが述語に一致する行を含む可能性があるかを特定します。

④ これらの関連がある可能性のあるグラニュールがメモリに読み込まれ、クエリに必要な他のカラムの位置が一致したグラニュールも一緒にロードされます。

⑤ 残りのフィルタがクエリ実行中に適用されます。

ご覧の通り、PREWHEREなしでは、すべての関連性のあるカラムがフィルタリングの前に読み込まれます。実際に一致する行がわずかしかない場合でもすべてがロードされます。

PREWHEREがクエリ効率を改善する方法​

以下のアニメーションは、上記のクエリにPREWHERE句がすべてのクエリ述語に適用されている場合の処理方法を示しています。

最初の3つの処理ステップは以前と同じです：

PREWHERE最適化付きのクエリ処理


① クエリには、テーブルの主キーの一部であり、したがって主インデックスの一部でもあるtownカラムに対するフィルタが含まれています。

② PREWHERE句なしの実行と同様に、クエリを加速するために、ClickHouseは主インデックスをメモリに読み込みます。

③ 次に、インデックスエントリをスキャンして、townカラムのどのグラニュールが述語に一致する行を含む可能性があるかを特定します。

PREWHERE句のおかげで、次のステップが異なります：関連のあるすべてのカラムを前もって読むのではなく、ClickHouseはデータをカラムごとにフィルタリングし、実際に必要なものだけを読み込みます。これにより、特に広いテーブルの場合、I/Oが劇的に削減されます。

各ステップで、前のフィルタを生き残った、つまり一致した少なくとも1行を含むグラニュールのみをロードします。これにより、各フィルタについてのロードと評価されるグラニュールの数が単調に減少します：

ステップ1: townによるフィルタリング
ClickHouseはPREWHERE処理を始め、① townカラムから選択されたグラニュールを読み込み、Londonに一致する行を含むものをチェックします。

例では、すべての選択されたグラニュールが一致するため、② 次のフィルタカラムdateの対応する位置一致グラニュールが処理のために選択されます：

ステップ1: `town`によるフィルタリング


ステップ2: dateによるフィルタリング
次に、ClickHouseは① 選択されたdateカラムのグラニュールを読み込み、フィルタdate > '2024-12-31'を評価します。

この場合、3つのグラニュールのうち2つが一致する行を含むため、② 次のフィルタカラムpriceからの位置一致グラニュールのみがさらなる処理のために選択されます：

ステップ2: `date`によるフィルタリング


ステップ3: priceによるフィルタリング
最後に、ClickHouseは① priceカラムから2つの選択されたグラニュールを読み込み、最後のフィルタprice > 10_000を評価します。

2つのグラニュールのうち1つのみが一致する行を含むため、② その位置一致グラニュールからSELECTカラム—street—をさらなる処理のためにロードする必要があります：

ステップ2: `price`によるフィルタリング


最終ステップでは、一致する行を含む最小のカラムグラニュールセットのみがロードされます。これにより、メモリの使用量が低減し、ディスクI/Oが少なく、クエリ実行が高速化されます。

PREWHEREは読み取るデータを削減しますが、処理される行は削減しません

PREWHEREを使用しても、ClickHouseはPREWHEREありとなしのクエリの両方で同じ数の行を処理します。ただし、PREWHERE最適化が適用されることで、すべての処理行に対してすべてのカラム値を読み込む必要がなくなります。

PREWHERE最適化は自動的に適用されます​

PREWHERE句は、上記の例のように手動で追加できます。しかし、PREWHEREを手動で記述する必要はありません。設定optimize_move_to_prewhereが有効になっている（デフォルトはtrue）場合、ClickHouseは自動的にWHEREからPREWHEREにフィルタ条件を移動させ、最も読み取り量を削減する条件を優先させます。

小さなカラムはスキャンが速いため、より大きなカラムを処理する時点では、ほとんどのグラニュールがすでにフィルタリングされています。すべてのカラムには同じ数の行が含まれているため、カラムのサイズは主にそのデータ型によって決まります。たとえば、UInt8カラムは一般的にStringカラムよりもずっと小さいです。

ClickHouseは、バージョン23.2以降、この戦略をデフォルトで採用し、PREWHEREフィルタカラムの圧縮されていないサイズの昇順でのマルチステップ処理を行います。

バージョン23.11以降、オプションのカラム統計により、カラムサイズだけでなく実際のデータ選択性に基づいてフィルタ処理順序を選択することで、さらに改善が見込まれます。

PREWHEREの影響を測定する方法​

PREWHEREがクエリに役立っていることを確認するために、optimize_move_to_prewhere設定が有効な場合と無効な場合のクエリパフォーマンスを比較できます。

まず、optimize_move_to_prewhere設定が無効な状態でクエリを実行します：

SELECT
    street
FROM
   uk.uk_price_paid_simple
WHERE
   town = 'LONDON' AND date > '2024-12-31' AND price < 10_000
SETTINGS optimize_move_to_prewhere = false;

   ┌─street──────┐
1. │ MOYSER ROAD │
2. │ AVENUE ROAD │
3. │ AVENUE ROAD │
   └─────────────┘

3 rows in set. Elapsed: 0.056 sec. Processed 2.31 million rows, 23.36 MB (41.09 million rows/s., 415.43 MB/s.)
Peak memory usage: 132.10 MiB.

ClickHouseは、クエリの処理中に23.36 MBのカラムデータを読み取り、2.31百万行を処理しました。

次に、optimize_move_to_prewhere設定が有効な状態でクエリを実行します。（この設定はオプションですが、デフォルトで有効になっています）：

SELECT
    street
FROM
   uk.uk_price_paid_simple
WHERE
   town = 'LONDON' AND date > '2024-12-31' AND price < 10_000
SETTINGS optimize_move_to_prewhere = true;

   ┌─street──────┐
1. │ MOYSER ROAD │
2. │ AVENUE ROAD │
3. │ AVENUE ROAD │
   └─────────────┘

3 rows in set. Elapsed: 0.017 sec. Processed 2.31 million rows, 6.74 MB (135.29 million rows/s., 394.44 MB/s.)
Peak memory usage: 132.11 MiB.

処理された行数は同じ（2.31百万）ですが、PREWHEREのおかげで、ClickHouseは3倍以上少ないカラムデータ—わずか6.74 MB（23.36 MBの代わりに）を読み込み、総実行時間を3分の1に削減しました。

ClickHouseがPREWHEREを内部でどのように適用しているかをより深く理解するためには、EXPLAINとトレースログを使用します。

EXPLAIN句を用いて、クエリの論理プランを調べます：

EXPLAIN PLAN actions = 1
SELECT
    street
FROM
   uk.uk_price_paid_simple
WHERE
   town = 'LONDON' and date > '2024-12-31' and price < 10_000;

...
Prewhere info                                                                                                                                                                                                                                          
  Prewhere filter column: 
    and(greater(__table1.date, '2024-12-31'_String), 
    less(__table1.price, 10000_UInt16), 
    equals(__table1.town, 'LONDON'_String)) 
...

プラン出力の大部分は非常に冗長であるため、ここでは省略します。基本的には、3つのカラム述語がすべて自動的にPREWHEREに移動されたことを示しています。

これを自身で再現すると、クエリプランでも、これらの述語の順序がカラムのデータ型サイズに基づいていることがわかります。カラム統計を有効にしていないため、ClickHouseはサイズをPREWHEREの処理順序を決定するためのフォールバックとして利用します。

さらに詳しい内部の動作を観察したい場合は、クエリ実行中にClickHouseにテストレベルのログエントリをすべて返すよう指示することができます：

SELECT
    street
FROM
   uk.uk_price_paid_simple
WHERE
   town = 'LONDON' AND date > '2024-12-31' AND price < 10_000
SETTINGS send_logs_level = 'test';

...
<Trace> ... Condition greater(date, '2024-12-31'_String) moved to PREWHERE
<Trace> ... Condition less(price, 10000_UInt16) moved to PREWHERE
<Trace> ... Condition equals(town, 'LONDON'_String) moved to PREWHERE
...
<Test> ... Executing prewhere actions on block: greater(__table1.date, '2024-12-31'_String)
<Test> ... Executing prewhere actions on block: less(__table1.price, 10000_UInt16)
...

主なポイント​

  • PREWHEREは、後でフィルタリングされるカラムデータの読み込みを回避し、I/Oとメモリを節約します。
  • optimize_move_to_prewhereが有効な場合、新たに手動でPREWHEREを記述することなく自動で機能します（デフォルト）。
  • フィルタリングの順序は重要です：小さく選択性の高いカラムを最初に配置するべきです。
  • EXPLAINとログを使用して、PREWHEREが適用されているかを確認し、その効果を理解します。
  • PREWHEREは、広いテーブルや選択的フィルタを伴う大規模なスキャンにおいて特に影響を持ちます。