ClickHouseにおけるインデックスベースのプルーニング

最も高速な分析クエリとは、読み込むデータ量が最も少ないクエリです。私たちはこのことを何度も繰り返していますが、それはまさに事実だからにほかなりません。

ClickHouse には、これを実現するための手法がいくつか用意されています。本ブログ記事では、英国の不動産販売データセットを用いて、インデックスベースの 3 つのプルーニング(絞り込み)テクニックを順を追って解説します。これにより、いつ何を使えばよいかが正確に把握できるようになります。

プルーニングテクニック #1: プライマリインデックス

1 つ目のプルーニングテクニックである主キーは、テーブルを作成する際に最初に学ぶ項目の 1 つです。テーブルの主キーによって、データパート内におけるデータのソート順が決まります。

テーブルパートはグラニュールで構成されており、デフォルトでは各グラニュールに 8,192 行が含まれます。ClickHouse のプライマリインデックスには、グラニュールごとの先頭行の主キーカラム値が格納されます。

下の図では、データが主キーである C1 でソートされ、行がグラニュール(g1 〜 g4)に整理されています。分かりやすくするため、この図では 1 グラニュールあたり 3 行としています。プライマリインデックスには、各グラニュールの先頭の値(g1 では 10、g2 では 20 など)が格納されます。

プライマリインデックスを利用すると、主キーに対するフィルター条件に基づき、データを読み込む前にグラニュール全体をスキップできます。たとえば、WHERE C1 > 60 を含むクエリの場合、インデックスを用いてグラニュール g1 と g2 がプルーニングされるため、残りのデータのみが読み込まれます。

プルーニングテクニック #2: 軽量プロジェクション

次のプルーニングテクニックは軽量プロジェクション (lightweight projections) です。ClickHouse 25.6 で初めて導入され、ClickHouse 26.1 ではより使いやすい構文が追加されました。

ClickHouse におけるプロジェクションとは、異なるソート順(つまり異なるプライマリインデックス)で格納され、自動的に維持される隠れたテーブルコピーです。これらの別のレイアウトを活用することで、その並び順の恩恵を受けるクエリを高速化できます。デメリットは、プロジェクションによってベーステーブルのデータがディスク上で重複して保持される点でした。

軽量プロジェクションは、行全体を複製することなくセカンダリインデックスのように機能します。完全なデータコピーを保持する代わりに、ソートキーとベーステーブルを指す _part_offset ポインタのみを格納します。これによりストレージのオーバーヘッドが大幅に削減されますが、結果として返される他のカラムはベーステーブルから読み込む必要があります。

図を更新して C2 に軽量プロジェクションを追加してみると、その仕組みがよく分かります。

WHERE C2 > 900 のように主キーに含まれないカラムに対するフィルターの場合、ClickHouse は軽量プロジェクションを使用できます。このプロジェクションには、ソートされたプロジェクションキー(C2)の値と _part_offset の値が格納されており、プロジェクションキーに対するフィルター用にグラニュールをプルーニングできる独自のプライマリインデックス(②)が提供されます。

プルーニングテクニック #3: スキッピングインデックス

最後のテクニックはスキッピングインデックスです。スキッピングインデックスの一種である minmax インデックスは、各グラニュールにおけるカラムの最小値と最大値を記録します。

minmax インデックスは ClickHouse で 5 年以上にわたってサポートされていますが、最近ではテーブル内の特定タイプの全カラムに対してこれらのインデックスを自動作成するサポートも追加されました。

軽量プロジェクションと比較した minmax インデックスの利点は、ディスク上でカラム値を複製しないことです。ただし留意すべき点として、minmax インデックスを適用するカラムは主キーとある程度の相関関係がある必要があり、そうでなければインデックスによって効率的にデータをプルーニングできません。

下の図では、minmax インデックス(③)が各グラニュールにおける C3 の最小値と最大値を記録しています。

WHERE C3 > 600 のようなフィルターの場合、グラニュール g1 〜 g3 は最大値が 600 未満であるためスキップでき、読み込む必要があるのは g4 のみとなります。

プルーニングの実践: 英国の不動産データセット

各プルーニングテクニックの概要を理解したところで、実際のデータセットを使って実践してみましょう。ここでは、英国における不動産販売の詳細が含まれる英国の不動産価格データセットを使用します。

すべてのクエリは、64 GB の RAM を搭載した Apple Mac M2 Max 上で実行します。

英国の不動産データセットの取り込み

まずはテーブルの作成から始めましょう。

1CREATE OR REPLACE TABLE uk_price_paid
2(
3    price UInt32,
4    date Date,
5    postcode1 LowCardinality(String),
6    postcode2 LowCardinality(String),
7    type Enum8('terraced' = 1, 'semi-detached' = 2, 'detached' = 3, 'flat' = 4, 'other' = 0),
8    is_new UInt8,
9    duration Enum8('freehold' = 1, 'leasehold' = 2, 'unknown' = 0),
10    addr1 String,
11    addr2 String,
12    street LowCardinality(String),
13    locality LowCardinality(String),
14    town LowCardinality(String),
15    district LowCardinality(String),
16    county LowCardinality(String)
17)
18ENGINE = MergeTree
19ORDER BY (postcode1, postcode2, addr1, addr2);

主キー(特に指定しない限り ORDER BY 句と同じになります)は (postcode1, postcode2, addr1, addr2) です。

テーブルが作成されたら、データを取り込みます。

1INSERT INTO uk_price_paid
2SELECT *
3FROM file('uk_all.parquet');

uk_all.parquet は、まず pp-complete.csv からデータをインポートし(ドキュメントの手順通り)、それを Parquet にエクスポートして作成しました。

INSERT クエリを実行した出力は以下のとおりです。

130452463 rows in set. Elapsed: 5.366 sec. Processed 30.45 million rows, 170.44 MB (5.68 million rows/s., 31.76 MB/s.)
2Peak memory usage: 774.00 MiB.

このデータセットには 3,000 万行が含まれていますが、ClickHouse の基準からすると比較的小さなサイズです。Parquet を複数回インポートしてデータ量を増やすこともできますが、ATTACH PARTITION を使用する、より高速な方法があります。

次のコマンドは、テーブル内のすべてのパートを複製し、データ量を 2 倍にします。

1ALTER TABLE uk_price_paid 
2ATTACH PARTITION ID 'all' 
3FROM uk_price_paid;

十分なデータ量で作業できるように、このコマンドを何回か実行しました。参考までに、このクエリを 3 回実行した際の出力は以下のようになります。

10 rows in set. Elapsed: 0.167 sec.
2
30 rows in set. Elapsed: 0.458 sec.
4
50 rows in set. Elapsed: 0.412 sec.

テーブル内のレコード数を返すには、次のクエリを実行します。

1SELECT count()
2FROM uk_price_paid;

1┌───count()─┐
2│ 243619704 │ -- 243.62 million
3└───────────┘
4
51 row in set. Elapsed: 0.001 sec.

プライマリインデックスによるフィルタリング

まずは、主キーでフィルタリングするクエリの作成から始めましょう。次のクエリは、クロイドン(ロンドン郊外の地区)で販売された不動産の件数と平均販売価格を返します。

1SELECT postcode1, count(), avg(price)
2FROM uk_price_paid
3WHERE postcode1 LIKE 'CR%'
4GROUP BY ALL
5ORDER BY count() DESC
6SETTINGS 
7  output_format_pretty_single_large_number_tip_threshold=0,
8  use_query_condition_cache=0;

クエリを実行した出力は以下のとおりです。

1┌─postcode1─┬─count()─┬─────────avg(price)─┐
2│ CR0       │  573952 │  264860.4016363738 │
3│ CR2       │  219464 │ 287568.45715014765 │
4│ CR4       │  192912 │ 218234.12212822426 │
5│ CR3       │  155304 │  306863.8307319837 │
6│ CR8       │  147880 │  373809.7425480119 │
7│ CR7       │  141152 │  211355.8734413965 │
8│ CR5       │  123112 │ 355812.51777243486 │
9│ CR6       │   47920 │  384279.0923205342 │
10│ CR9       │     352 │ 12324871.113636363 │
11│ CR24      │      16 │              25000 │
12└───────────┴─────────┴────────────────────┘

110 rows in set. Elapsed: 0.030 sec.
2
310 rows in set. Elapsed: 0.015 sec.
4
510 rows in set. Elapsed: 0.021 sec.

最も速いクエリ実行時間は 15 ミリ秒でした。2 億件以上のレコードを含むテーブルに対するクエリとしては悪くない結果です。

このクエリの先頭に EXPLAIN indexes=1, pretty=1, compact=1 を付けると、クエリプランを確認できます。

1┌─explain─────────────────────────────────────────────┐
2 1. │ Output: postcode1, count(), avg(price)              │
3 2. │                                                     │
4 3. │ Sorting (Sorting for ORDER BY)                      │
5 4. │ └──Aggregating                                      │
6 5. │    └──ReadFromMergeTree (default.uk_price_paid)     │
7 6. │          Indexes:                                   │
8 7. │            PrimaryKey                               │
9 8. │              Keys:                                  │
10 9. │                postcode1                            │
1110. │              Condition: (postcode1 in ['CR', 'CS')) │
1211. │              Parts: 36/36                           │
1312. │              Granules: 235/29751                    │
1413. │              Search Algorithm: binary search        │
1514. │            Ranges: 36                               │
16    └─────────────────────────────────────────────────────┘

12 行目を見ると、このクエリを実行するためにクエリエンジンが処理する必要があったのは、29,751 グラニュールのうちわずか 235 グラニュール(1% 未満)であることが分かります。

処理された行数は、system.query_log テーブルをクエリすることで確認できます。

1SELECT event_time, query, read_rows
2FROM system.query_log
3WHERE type = 'QueryFinish' AND query NOT LIKE '%query_log%'
4ORDER BY event_time DESC 
5LIMIT 1
6FORMAT Vertical;

1Row 1:
2──────
3event_time: 2026-04-09 10:37:22
4query:      SELECT postcode1, count(), avg(price)...
5read_rows:  1687552 -- 1.69 million

このクエリでは、対象となり得る 2 億 4,300 万行のうち 160 万行しか読み込んでおらず、プライマリインデックスが読み込み対象データの削減に大きく貢献していると言えます。

プライマリインデックスは、主キーを構成する複数のカラムでフィルタリングする場合でも、それらがキー全体のプレフィックス(前方一致)を形成していれば効果を発揮します。

主キーは (postcode1, postcode2, addr1, addr2) であるため、たとえば postcode1 と postcode2 でフィルタリングすれば効率的に処理されます。

1SELECT postcode1, postcode2, count(), avg(price)
2FROM uk_price_paid
3WHERE postcode1 LIKE 'CR%' AND postcode2 LIKE '4%'
4GROUP BY ALL
5ORDER BY count() DESC 
6LIMIT 10
7SETTINGS
8  output_format_pretty_single_large_number_tip_threshold=0,
9  use_query_condition_cache=0;

1┌─postcode1─┬─postcode2─┬─count()─┬─────────avg(price)─┐
2│ CR4       │ 4FD       │    2496 │ 136439.84935897434 │
3│ CR4       │ 4FF       │    2056 │ 111415.15953307394 │
4│ CR4       │ 4FE       │    1376 │  98730.37790697675 │
5│ CR4       │ 4LT       │    1320 │ 104595.98787878788 │
6│ CR0       │ 4UX       │    1240 │ 118912.51612903226 │
7│ CR8       │ 4DZ       │    1200 │             103860 │
8│ CR0       │ 4TX       │    1184 │ 110415.50675675676 │
9│ CR0       │ 4HB       │    1152 │ 162919.75694444444 │
10│ CR0       │ 4FG       │    1144 │  230394.2097902098 │
11│ CR0       │ 4GA       │    1032 │ 211535.29457364342 │
12└───────────┴───────────┴─────────┴────────────────────┘
13
1410 rows in set. Elapsed: 0.015 sec. Processed 638.98 thousand rows, 3.30 MB (42.27 million rows/s., 218.59 MB/s.)
15Peak memory usage: 3.92 MiB.

このクエリでは、2 億 4,300 万行のうち 63 万行余りしか処理されません。

一方、主キーの一部ではあるものの先頭のカラムではない postcode2 のみでフィルタリングした場合は、それほど効率的ではありません。

1SELECT postcode1, postcode2, count(), avg(price)
2FROM uk_price_paid
3WHERE postcode2 LIKE '4%'
4GROUP BY ALL
5ORDER BY count() DESC 
6LIMIT 10
7SETTINGS
8  output_format_pretty_single_large_number_tip_threshold=0,
9  use_query_condition_cache=0;

1┌─postcode1─┬─postcode2─┬─count()─┬─────────avg(price)─┐
2│ TR8       │ 4LX       │    3328 │  67047.70913461539 │
3│ CR4       │ 4FD       │    2496 │ 136439.84935897434 │
4│ SS16      │ 4TY       │    2328 │  85003.52233676976 │
5│ NR29      │ 4NW       │    2328 │ 36411.996563573884 │
6│ SS16      │ 4TQ       │    2184 │  88534.72161172162 │
7│ SS16      │ 4TD       │    2160 │  67603.75925925926 │
8│ BS4       │ 4EY       │    2104 │ 100474.69201520912 │
9│ RG22      │ 4UR       │    2096 │  143119.3893129771 │
10│ BB11      │ 4JZ       │    2096 │  29956.74427480916 │
11│ LS1       │ 4ES       │    2088 │  256009.9655172414 │
12└───────────┴───────────┴─────────┴────────────────────┘
13
1410 rows in set. Elapsed: 0.787 sec. Processed 138.82 million rows, 572.89 MB (176.47 million rows/s., 728.26 MB/s.)
15Peak memory usage: 146.53 MiB.

今度は 1 億 3,800 万行がスキャンされ、結果が返るまでに 50 倍の時間がかかっています。このクエリの EXPLAIN を確認すると、次のような出力が表示されます。

1┌─explain───────────────────────────────────────────────────────────┐
2 1. │ Expression (Project names)                                        │
3 2. │   Limit (preliminary LIMIT)                                       │
4 3. │     Sorting (Sorting for ORDER BY)                                │
5 4. │       Expression ((Before ORDER BY + Projection))                 │
6 5. │         Aggregating                                               │
7 6. │           Expression (Before GROUP BY)                            │
8 7. │             Expression ((WHERE + Change column names to column id⋯│
9 8. │               ReadFromMergeTree (default.uk_price_paid)           │
10 9. │               Indexes:                                            │
1110. │                 PrimaryKey                                        │
1211. │                   Keys:                                           │
1312. │                     postcode2                                     │
1413. │                   Condition: (postcode2 in ['4', '5'))            │
1514. │                   Parts: 11/11                                    │
1615. │                   Granules: 16950/29744                           │
1716. │                   Search Algorithm: generic exclusion search      │
1817. │                 Ranges: 7066                                      │
19    └───────────────────────────────────────────────────────────────────┘

クエリエンジンは主キーを使用してグラニュールのほぼ半分を除外できていますが(15 行目)、16 行目を見ると汎用除外検索アルゴリズム (generic exclusion search algorithm) を使用していることが分かります。

このアルゴリズムの効率は、postcode2 カラムとその前にあるキーカラムである postcode1 のカーディナリティの差に左右されます。ドキュメントに段階的な例が記載されていますが、要点は、先行するカラムのカーディナリティが低い場合はアルゴリズムが効率的に機能し、カーディナリティが高い場合はそれほど効率的ではないということです。

次のセクションでは、主キーにまったく含まれていないカラムでより効率的にフィルタリングする方法を見ていきます。

軽量プロジェクションによるフィルタリング

プライマリインデックスによるフィルタリングは最も優れた手法であり、テーブルを設計する際には、フィルターに使用する可能性が最も高いカラムでデータをソートしておくことが大切です。

しかし実際には、他のカラムでもクエリを実行したいケースがよくあります。たとえば、district = 'BURNLEY' の条件で、町(town)ごとの不動産販売件数を調べたいとしましょう。

1SELECT town, count(), round(avg(price)) AS avgPrice, argAndMax(date, price)
2FROM uk_price_paid
3WHERE district = 'BURNLEY'
4GROUP BY ALL
5ORDER BY count() DESC LIMIT 10
6SETTINGS
7    use_query_condition_cache = 0;

1┌─town─────────┬─count()─┬─avgPrice─┬─argAndMax(date, price)──┐
2│ BURNLEY      │  485808 │    84552 │ ('2020-03-01',68945000) │
3│ NELSON       │     200 │    67794 │ ('2004-12-10',170000)   │
4│ ACCRINGTON   │     120 │    61303 │ ('2022-11-30',223995)   │
5│ COLNE        │      88 │    68455 │ ('2000-12-21',185000)   │
6│ ROSSENDALE   │      40 │   124490 │ ('2007-09-10',237000)   │
7│ BARNOLDSWICK │      32 │    26488 │ ('1999-06-04',38000)    │
8│ BLACKBURN    │      32 │    56500 │ ('2004-09-24',78000)    │
9│ BLACKPOOL    │      24 │    40833 │ ('1999-07-23',44000)    │
10│ PRESTON      │       8 │   221995 │ ('2022-12-09',221995)   │
11│ CLITHEROE    │       8 │   250000 │ ('2020-07-16',250000)   │
12└──────────────┴─────────┴──────────┴─────────────────────────┘

このクエリを数回実行した結果は以下のとおりです。

110 rows in set. Elapsed: 0.428 sec. Processed 240.96 million rows, 480.72 MB (562.98 million rows/s., 1.12 GB/s.)
2Peak memory usage: 841.37 KiB.
3
410 rows in set. Elapsed: 0.466 sec. Processed 219.79 million rows, 438.38 MB (471.16 million rows/s., 939.77 MB/s.)
5Peak memory usage: 852.86 KiB.
6
710 rows in set. Elapsed: 0.481 sec. Processed 207.87 million rows, 414.50 MB (432.32 million rows/s., 862.05 MB/s.)
8Peak memory usage: 844.83 KiB.

クエリエンジンはこのクエリに答えるため、データセット内のほぼすべての行を処理しなければなりません。

district カラムに軽量プロジェクションを追加して、パフォーマンスを改善できるか試してみましょう。

1ALTER TABLE uk_price_paid
2ADD PROJECTION by_district INDEX district TYPE basic;

すぐに使用できるよう、このプロジェクションをマテリアライズします。

1ALTER TABLE uk_price_paid
2MATERIALIZE PROJECTION by_district
3SETTINGS mutations_sync=1;

10 rows in set. Elapsed: 14.480 sec.

それでは、プロジェクションを使ってクエリを実行してみましょう。

1SELECT town, count(), round(avg(price)) AS avgPrice, argAndMax(date, price)
2FROM uk_price_paid
3WHERE district = 'BURNLEY'
4GROUP BY ALL
5ORDER BY count() DESC LIMIT 10
6SETTINGS
7    use_query_condition_cache = 0,
8    optimize_use_projections = 1;

optimize_use_projections はデフォルトで有効になっていますが、ここでは念のために明示しています。これを無効にするとプロジェクションは使用されません。プロジェクションが実際に機能しているかどうかをテストするのに便利です。

上記のクエリの実行時間は以下のとおりです。

110 rows in set. Elapsed: 0.023 sec.
2
310 rows in set. Elapsed: 0.056 sec.
4
510 rows in set. Elapsed: 0.046 sec.

BURNLEY のクエリでは、district カラムに軽量プロジェクションを使用することで、クエリ時間が 428 ミリ秒から 23 ミリ秒へと短縮され、94% の改善が見られました。

では、内部で何が起きているのかを確認してみましょう。最初は、先ほどと同じ EXPLAIN 句をクエリの先頭に付けました。

1EXPLAIN indexes=1, pretty=1, compact= 1 
2SELECT town, count(), round(avg(price)) AS avgPrice, argAndMax(date, price)
3FROM uk_price_paid
4WHERE district = 'BURNLEY'
5GROUP BY ALL
6ORDER BY count() DESC LIMIT 10
7SETTINGS
8    use_query_condition_cache = 0,
9    optimize_use_projections = 1;

1┌─explain─────────────────────────────────────────────────┐
2│ Output: town, count(), avgPrice, argAndMax(date, price) │
3│                                                         │
4│ Limit (preliminary LIMIT)                               │
5│ └──Sorting (Sorting for ORDER BY)                       │
6│    └──Aggregating                                       │
7│       └──ReadFromMergeTree (default.uk_price_paid)      │
8│             Indexes:                                    │
9│               PrimaryKey                                │
10│                 Condition: true                         │
11│                 Parts: 6/6                              │
12│                 Granules: 29741/29741                   │
13│               Ranges: 6                                 │
14└─────────────────────────────────────────────────────────┘

この出力には主キーインデックス情報を含む基本的なクエリプランしか含まれていないため、状況の把握には役立ちません。プロジェクションの分析を出力に含めるには、projections=1 も追加する必要があります。

1EXPLAIN indexes=1, projections=1, pretty=1, compact= 1 
2SELECT town, count(), round(avg(price)) AS avgPrice, argAndMax(date, price)
3FROM uk_price_paid
4WHERE district = 'BURNLEY'
5GROUP BY ALL
6ORDER BY count() DESC LIMIT 10
7SETTINGS
8    use_query_condition_cache = 0,
9    optimize_use_projections = 1,
10    output_format_pretty_max_value_width=65,
11    output_format_pretty_row_numbers=1;

1┌─explain───────────────────────────────────────────────────────────┐
2 1. │ Output: town, count(), avgPrice, argAndMax(date, price)           │
3 2. │                                                                   │
4 3. │ Limit (preliminary LIMIT)                                         │
5 4. │ └──Sorting (Sorting for ORDER BY)                                 │
6 5. │    └──Aggregating                                                 │
7 6. │       └──ReadFromMergeTree (default.uk_price_paid)                │
8 7. │             Indexes:                                              │
9 8. │               PrimaryKey                                          │
10 9. │                 Condition: true                                   │
1110. │                 Parts: 11/11                                      │
1211. │                 Granules: 29744/29744                             │
1312. │               Ranges: 11                                          │
1413. │             Projections:                                          │
1514. │               Name: by_district                                   │
1615. │                 Description: Projection has been analyzed and wil⋯│
1716. │                 Condition: (district in ['BURNLEY', 'BURNLEY'])   │
1817. │                 Search Algorithm: binary search                   │
1918. │                 Parts: 11                                         │
2019. │                 Marks: 72                                         │
2120. │                 Ranges: 11                                        │
2221. │                 Rows: 589824                                      │
2322. │                 Filtered Parts: 0                                 │
24    └───────────────────────────────────────────────────────────────────┘

何が起きているのか、まずは Indexes セクションから順に見ていきましょう。

このクエリでは主キーインデックスは役に立ちませんでした。9 行目の Condition: true はフィルターが一切適用されなかったことを意味し、全 11 パート(10 行目)および 29,744 グラニュール(11 行目)を走査対象とする必要がありました。

続いて Projections セクションを見てみます。

22 行目の Filtered Parts: 0 は、パート全体を完全に除外できなかったことを示しており、BURNLEY が全 11 パートすべてに存在することを意味します。
19 行目では、検索対象が 72 グラニュール(またはマーク)に絞り込まれています。
デフォルトでは各グラニュールに 8,192 行が含まれているため、21 行目のカウント(72 * 8,192 = 589,824)になります。
これら 72 個のグラニュールは 11 個のパートに分散しており(18 行目)、各パート内で BURNLEY の行は連続して格納されているため、パートごとに 1 つの連続した範囲、つまり合計 11 個の範囲となります(20 行目)。

以下の表は、バーンリーよりも販売件数が多い地区と少ない地区の両方について、プロジェクションなしとプロジェクションありの所要時間を示しています。

地区 (District)	一致した行数	プロジェクションなし (ms)	プロジェクションあり (ms)	改善率
BIRMINGHAM	3,543,672	341	82	76%
SHEFFIELD	1,975,368	368	44	88%
CROYDON	1,448,472	364	48	86%
WAKEFIELD	1,322,216	382	50	87%
WIRRAL	1,301,168	358	39	89%
SOUTHWARK	999,432	367	30	92%
SUTTON	875,872	361	45	87%

プロジェクションありとプロジェクションなしでそれぞれクエリを 3 回実行し、最も短い時間を採用しました。

プロジェクションを使用することで、これらすべての地区でクエリ時間が少なくとも 75% 改善していることが分かります。

軽量プロジェクションの優れた機能の 1 つは、それらを組み合わせて、複数の独立したソート順にまたがる行レベルのフィルタリングを実現できる点です。たとえば、date カラムにもう 1 つの軽量プロジェクションを追加できます。

1ALTER TABLE uk_price_paid
2ADD PROJECTION by_date INDEX date TYPE basic;
3
4ALTER TABLE uk_price_paid
5MATERIALIZE PROJECTION by_date
6SETTINGS mutations_sync=1;

district と date の両方でフィルタリングするクエリ(例: 2023年1月にマンチェスターで販売された物件の検索)では、これら両方の軽量プロジェクションが使用されます。

1SELECT town, 
2       count(),
3       round(avg(price)) AS avgPrice,
4       argAndMax(date, price)
5FROM uk_price_paid
6WHERE (district = 'MANCHESTER') AND (date BETWEEN '2023-01-01' AND '2023-01-31')
7GROUP BY ALL
8ORDER BY count() DESC
9LIMIT 10
10SETTINGS
11    use_query_condition_cache = 0,
12    optimize_use_projections = 1;

1┌─town───────┬─count()─┬─avgPrice─┬─argAndMax(date, price)─┐
2│ MANCHESTER │    3656 │   266818 │ ('2023-01-13',3000000) │
3│ SALFORD    │      24 │   341667 │ ('2023-01-31',670000)  │
4└────────────┴─────────┴──────────┴────────────────────────┘

このクエリの EXPLAIN 出力は以下のとおりです。

1┌─explain───────────────────────────────────────────────────────────┐
2 1. │ Output: town, count(), avgPrice, argAndMax(date, price)           │
3 2. │                                                                   │
4 3. │ Limit (preliminary LIMIT)                                         │
5 4. │ └──Sorting (Sorting for ORDER BY)                                 │
6 5. │    └──Aggregating                                                 │
7 6. │       └──ReadFromMergeTree (default.uk_price_paid)                │
8 7. │             Indexes:                                              │
9 8. │               PrimaryKey                                          │
10 9. │                 Condition: true                                   │
1110. │                 Parts: 11/11                                      │
1211. │                 Granules: 29744/29744                             │
1312. │               Ranges: 11                                          │
1413. │             Projections:                                          │
1514. │               Name: by_district                                   │
1615. │                 Description: Projection has been analyzed and wil⋯│
1716. │                 Condition: (district in ['MANCHESTER', 'MANCHESTE⋯│
1817. │                 Search Algorithm: binary search                   │
1918. │                 Parts: 11                                         │
2019. │                 Marks: 247                                        │
2120. │                 Ranges: 11                                        │
2221. │                 Rows: 2023424                                     │
2322. │                 Filtered Parts: 0                                 │
2423. │               Name: by_date                                       │
2524. │                 Description: Projection has been analyzed and wil⋯│
2625. │                 Condition: and((date in (-Inf, 19388]), (date in ⋯│
2726. │                 Search Algorithm: binary search                   │
2827. │                 Parts: 11                                         │
2928. │                 Marks: 71                                         │
3029. │                 Ranges: 11                                        │
3130. │                 Rows: 581632                                      │
3231. │                 Filtered Parts: 0                                 │
33    └───────────────────────────────────────────────────────────────────┘

各プロジェクションはそれぞれのフィルター条件に一致するパートとグラニュールを個別に割り出し、ベーステーブルから読み出す行のセットはその両方の積集合(共通部分)となります。

軽量プロジェクションの個別および組み合わせによる効果を確認するために、軽量プロジェクションをそれぞれ 1 つずつ含む追加のテーブルを作成します。

まず、uk_price_paid_by_date には by_date 軽量プロジェクションのみを持たせます。

1CREATE TABLE uk_price_paid_by_date
2CLONE AS uk_price_paid;
3
4ALTER TABLE uk_price_paid_by_date 
5DROP PROJECTION by_district;

そして、uk_price_paid_by_district には by_district 軽量プロジェクションのみを持たせます。

1CREATE TABLE uk_price_paid_by_district 
2CLONE AS uk_price_paid;
3
4ALTER TABLE uk_price_paid_by_district 
5DROP PROJECTION by_date;

それでは、先ほどの「2023年1月にマンチェスターで販売された物件」を検索するクエリを各テーブルに対して実行します。各テーブルに対して 3 回実行し、最も速い時間と処理された行数を記録しました。

テーブル	クエリ時間 (ms)	処理行数
`uk_price_paid`	52	224 万行
`uk_price_paid_by_district`	67	803 万行
`uk_price_paid_by_date`	347	2 億 4,346 万行

by_date 軽量プロジェクション単体では、あまり改善が見られません。プロジェクションによって一致する行が約 50 万行(2023年1月に販売された物件)に絞り込まれたとはいえ、それらの行がベーステーブルの 2 億 4,300 万行全体に散らばっているため、クエリエンジンはそれらを取得してさらに地区をマンチェスターに絞り込むために、依然としてデータの大部分にアクセスしなければならないからです。

2023年1月に販売された物件の一致行をすべて見つけるために、いくつのグラニュールをスキャンする必要があるかを算出する次のクエリを書くことで、上記の内容を確認できます。

1SELECT
2    uniqExact((_part, intDiv(_part_offset, 8192))) AS granulesWithMatchingRows,
3    (
4        SELECT sum(marks)
5        FROM system.parts
6        WHERE (`table` = 'uk_price_paid_by_date') AND active
7    ) AS totalGranules,
8    round((granulesWithMatchingRows / totalGranules) * 100, 2) AS pct
9FROM uk_price_paid_by_date
10WHERE (date >= '2023-01-01') AND (date <= '2023-01-31');

1┌─granulesWithMatchingRows─┬─totalGranules─┬──pct─┐
2│                    29724 │         29755 │ 99.9 │
3└──────────────────────────┴───────────────┴──────┘

一致する行を見つけるために、29,724 グラニュール、つまり約 2 億 4,349 万 9,008 行(29,724 * 8,192)をスキャンしています。

日付をより限定的に、地区をより幅広くフィルタリングする別の例を見てみましょう。次のクエリは、2023年2月1日にマンチェスター、バーミンガム、サットン、ウィラルで販売された不動産を検索します。

1SELECT town,
2       count(),
3       round(avg(price)) AS avgPrice,
4       argAndMax(date, price)
5FROM uk_price_paid
6WHERE (district IN ('MANCHESTER', 'BIRMINGHAM', 'SUTTON', 'WIRRAL'))
7AND (date = '2023-02-01')
8GROUP BY ALL
9ORDER BY count() DESC
10LIMIT 10
11SETTINGS
12    use_query_condition_cache = 0,
13    optimize_use_projections = 1;

今回も各テーブルに対して 3 回実行し、最も速い時間と処理行数を記録します。

テーブル	クエリ時間 (ms)	処理行数
`uk_price_paid`	85	5,892 万行
`uk_price_paid_by_district`	352	2 億 767 万行
`uk_price_paid_by_date`	99	7,585 万行

今回は by_district よりも by_date のほうがデータを効果的にフィルタリングできていますが、両方の軽量プロジェクションを組み合わせた場合に最高のパフォーマンスが得られます。

minmax インデックスによるフィルタリング

最後のプルーニングテクニックは、スキッピングインデックスの一種である minmax インデックスです。改めて確認しておくと、スキッピングインデックスを追加するカラムは主キーと相関関係がなければならず、そうでなければインデックスの効果は得られません。

価格によるフィルタリングをより効率的に行うため、price カラムに minmax インデックスを追加します。同じ地理的エリア内の不動産は似たような価格帯で販売される傾向があるため、価格は郵便番号と緩やかな相関関係にあります。ロンドンの高価格帯の郵便番号(SW1、W1)は高価格帯に集まり、農村部の郵便番号は低価格帯に集まる傾向があります。つまり、価格で検索する際にクエリエンジンがグラニュールをスキップできるはずです。

minmax インデックスは以下のクエリで追加できます。

1ALTER TABLE uk_price_paid
2ADD INDEX price_minmax price TYPE minmax GRANULARITY 1;

すぐに使用できるよう、このインデックスをマテリアライズします。

1ALTER TABLE uk_price_paid
2MATERIALIZE INDEX price_minmax
3SETTINGS mutations_sync=1;

次に、1,000 万ポンド超で販売された不動産の件数が最も多い地区を見つけるクエリを作成します。

1SELECT
2    district,
3    count(),
4    formatReadableQuantity(avg(price)) AS avgPrice
5FROM uk_price_paid
6WHERE price > 10000000
7GROUP BY ALL
8ORDER BY count() DESC
9LIMIT 10
10SETTINGS use_query_condition_cache = 0,
11         use_skip_indexes = 1;

use_skip_indexes 設定はデフォルトで true ですが、スキッピングインデックスの影響を確認するために無効化できます。

クエリを実行した結果は以下のとおりです。

1┌─district───────────────┬─count()─┬─avgPrice──────┐
2│ CITY OF WESTMINSTER    │   13880 │ 32.61 million │
3│ KENSINGTON AND CHELSEA │    7120 │ 19.22 million │
4│ CAMDEN                 │    3616 │ 34.78 million │
5│ CITY OF LONDON         │    2448 │ 50.09 million │
6│ TOWER HAMLETS          │    2392 │ 43.70 million │
7│ MANCHESTER             │    1752 │ 24.71 million │
8│ SOUTHWARK              │    1712 │ 37.31 million │
9│ BIRMINGHAM             │    1520 │ 27.66 million │
10│ ISLINGTON              │    1504 │ 35.81 million │
11│ LEEDS                  │    1328 │ 24.94 million │
12└────────────────────────┴─────────┴───────────────┘

このクエリをスキッピングインデックスなし(use_skip_indexes=0)で 3 回実行しました。

110 rows in set. Elapsed: 0.347 sec. Processed 243.62 million rows, 1.09 GB (701.77 million rows/s., 3.13 GB/s.)
2Peak memory usage: 6.21 MiB.
3
410 rows in set. Elapsed: 0.506 sec. Processed 243.62 million rows, 1.09 GB (481.02 million rows/s., 2.15 GB/s.)
5Peak memory usage: 6.21 MiB.
6
710 rows in set. Elapsed: 0.390 sec. Processed 243.62 million rows, 1.09 GB (624.22 million rows/s., 2.78 GB/s.)
8Peak memory usage: 6.21 MiB.

続いて、スキッピングインデックスあり(use_skip_indexes=1)で 3 回実行しました。

110 rows in set. Elapsed: 0.312 sec. Processed 116.41 million rows, 578.67 MB (373.50 million rows/s., 1.86 GB/s.)
2Peak memory usage: 5.46 MiB.
3
410 rows in set. Elapsed: 0.306 sec. Processed 116.41 million rows, 578.67 MB (380.51 million rows/s., 1.89 GB/s.)
5Peak memory usage: 5.46 MiB.
6
710 rows in set. Elapsed: 0.304 sec. Processed 116.41 million rows, 578.67 MB (382.48 million rows/s., 1.90 GB/s.)
8Peak memory usage: 5.49 MiB.

最速時間は、スキッピングインデックスなしの 304 ミリ秒に対して、スキッピングインデックスありでは 234 ミリ秒となり、およそ 23% の向上が見られました。

スキッピングインデックスを使用したクエリでは、処理される行数が約 2 分の 1 になっています。クエリを EXPLAIN することで、どのデータが無視されたかを確認できます。

1EXPLAIN indexes=1, projections=1, pretty=1, compact=1

出力は以下のとおりです。

1┌─explain────────────────────────────────────────────────┐
2 1. │ Output: district, count(), avgPrice                    │
3 2. │                                                        │
4 3. │ Limit (preliminary LIMIT)                              │
5 4. │ └──Sorting (Sorting for ORDER BY)                      │
6 5. │    └──Aggregating                                      │
7 6. │       └──ReadFromMergeTree (default.uk_price_paid)     │
8 7. │             Indexes:                                   │
9 8. │               PrimaryKey                               │
10 9. │                 Condition: true                        │
1110. │                 Parts: 11/11                           │
1211. │                 Granules: 29744/29744                  │
1312. │               Skip                                     │
1413. │                 Name: price_minmax                     │
1514. │                 Description: minmax GRANULARITY 1      │
1615. │                 Condition: (price in [10000001, +Inf)) │
1716. │                 Parts: 11/11                           │
1817. │                 Granules: 14214/29744                  │
1918. │               Ranges: 6034                             │
20    └────────────────────────────────────────────────────────┘

17 行目から、スキッピングインデックスによって 15,000 をわずかに超えるグラニュールが除外されたことが確認できます。

まとめ

本ブログ記事では、ClickHouse が提供する 3 つのインデックスベースのプルーニング技術(プライマリインデックス、軽量プロジェクション、スキッピングインデックス)について見てきました。

最も強力なのはプライマリインデックスです。最も頻繁にフィルタリングされるカラムを中心に ORDER BY を設計する必要があります。

軽量プロジェクションは、主キー以外のカラムでフィルタリングする場合の優れた選択肢です。複数のプロジェクションを組み合わせることで、ClickHouse はそれらの結果の積集合を取り、さらに効果的なプルーニングを行えます。

最後に、minmax のようなスキッピングインデックスは、対象カラムが主キーと相関関係にある場合に最も効果を発揮します。その相関関係がなければ、大きな効果は期待できません。

今すぐ始める

実際のデータでClickHouseの動作を試してみませんか？ClickHouse Cloudなら数分で利用開始でき、300ドル分の無料クレジットも進呈されます。

サインアップ

ClickHouseにおけるインデックスベースのプルーニング

プルーニングテクニック #1: プライマリインデックス

プルーニングテクニック #2: 軽量プロジェクション

プルーニングテクニック #3: スキッピングインデックス

プルーニングの実践: 英国の不動産データセット

英国の不動産データセットの取り込み

プライマリインデックスによるフィルタリング

軽量プロジェクションによるフィルタリング

minmax インデックスによるフィルタリング

まとめ

今すぐ始める

Subscribe to our newsletter

最近の記事

ClickHouse が dbt プラットフォームで利用可能になりました

Replica-aware routing のパブリックベータ

ClickHouse の新しい TimeSeries エンジンのご紹介: Prometheus のドロップイン代替

ClickHouse の AI Functions: SQL を AI 時代へアップグレード

Follow us

ClickHouseにおけるインデックスベースのプルーニング

プルーニングテクニック #1: プライマリインデックス #

プルーニングテクニック #2: 軽量プロジェクション #

プルーニングテクニック #3: スキッピングインデックス #

プルーニングの実践: 英国の不動産データセット #

英国の不動産データセットの取り込み #

プライマリインデックスによるフィルタリング #

軽量プロジェクションによるフィルタリング #

minmax インデックスによるフィルタリング #

まとめ #

今すぐ始める

Subscribe to our newsletter

最近の記事

ClickHouse が dbt プラットフォームで利用可能になりました

Replica-aware routing のパブリックベータ

ClickHouse の新しい TimeSeries エンジンのご紹介: Prometheus のドロップイン代替

ClickHouse の AI Functions: SQL を AI 時代へアップグレード

Follow us

プルーニングテクニック #1: プライマリインデックス

プルーニングテクニック #2: 軽量プロジェクション

プルーニングテクニック #3: スキッピングインデックス

プルーニングの実践: 英国の不動産データセット

英国の不動産データセットの取り込み

プライマリインデックスによるフィルタリング

軽量プロジェクションによるフィルタリング

minmax インデックスによるフィルタリング

まとめ