We’re running ClickHouse 25.4 on an AWS m6i.8xlarge EC2 instance with:
• 32 vCPUs
• 128 GiB RAM
• 1 TiB gp3 SSD (with default settings: 3000 IOPS, 125 MiB/s max throughput 🐌)
• Ubuntu Linux 24.04
CREATE TABLE amazon.amazon_reviews
(
`review_date` Date CODEC(ZSTD(1)),
`marketplace` LowCardinality(String) CODEC(ZSTD(1)),
`customer_id` UInt64 CODEC(ZSTD(1)),
`review_id` String CODEC(ZSTD(1)),
`product_id` String CODEC(ZSTD(1)),
`product_parent` UInt64 CODEC(ZSTD(1)),
`product_title` String CODEC(ZSTD(1)),
`product_category` LowCardinality(String) CODEC(ZSTD(1)),
`star_rating` UInt8 CODEC(ZSTD(1)),
`helpful_votes` UInt32 CODEC(ZSTD(1)),
`total_votes` UInt32 CODEC(ZSTD(1)),
`vine` Bool CODEC(ZSTD(1)),
`verified_purchase` Bool CODEC(ZSTD(1)),
`review_headline` String CODEC(ZSTD(1)),
`review_body` String CODEC(ZSTD(1))
)
ENGINE = MergeTree
ORDER BY (review_date, product_category);
INSERT INTO amazon.amazon_reviews
SELECT * FROM s3Cluster('default', 'https://datasets-documentation.s3.eu-west-3.amazonaws.com/amazon_reviews/amazon_reviews_*.snappy.parquet');
SELECT
formatReadableQuantity(sum(rows)) AS rows,
formatReadableSize(sum(data_uncompressed_bytes)) AS data_size,
formatReadableSize(sum(data_compressed_bytes)) AS compressed_size
FROM system.parts
WHERE active AND database = 'amazon' AND table = 'amazon_reviews';
┌─rows───────────┬─data_size─┬─compressed_size─┐
│ 150.96 million │ 70.47 GiB │ 30.05 GiB │
└────────────────┴───────────┴─────────────────┘
SELECT helpful_votes
FROM amazon.amazon_reviews
ORDER BY helpful_votes DESC
LIMIT 3;
┌─helpful_votes─┐
│ 47524 │
│ 41393 │
│ 41278 │
└───────────────┘
3 rows in set. Elapsed: 0.070 sec. Processed 150.96 million rows, 603.83 MB (2.15 billion rows/s., 8.61 GB/s.)
Peak memory usage: 3.59 MiB.
SELECT review_body
FROM amazon.amazon_reviews
FORMAT Null;
Query id: b9566386-047d-427c-a5ec-e90bee027b02
0 rows in set. Elapsed: 176.640 sec. Processed 150.96 million rows, 56.02 GB (854.61 thousand rows/s., 317.13 MB/s.)
Peak memory usage: 733.14 MiB.
😱 ほぼ3分です！単一の列を読み取っただけなのに。
しかし、ボトルネックはClickHouseではなく、ディスクのスループットでした。このクエリは、前の例の600 MBに対して56 GBというはるかに大きな列をスキャンしました。[比較的遅いディスク](https://www.google.com/search?q=/blog/clickhouse-gets-lazier-and-faster-introducing-lazy-materialization%23test-setup-dataset-and-machine)と32個のCPUコアを持つテストマシンでは、ClickHouseは32個の[並列ストリーム](https://clickhouse.com/docs/optimize/query-parallelism)を使用してデータを読み取りました。[クエリログ](https://clickhouse.com/docs/operations/system-tables/query_log)は、3分間の実行時間の大部分が[readシステムコールの待機](https://github.com/ClickHouse/ClickHouse/blob/9d60aa01a83346648eae5dc9572530388271f7b0/src/Common/ProfileEvents.cpp#L101)に費やされたことを確認しています。
SELECT
round(ProfileEvents['DiskReadElapsedMicroseconds'] / 1e6) AS disk_read_seconds,
ProfileEvents['ConcurrencyControlSlotsAcquired'] AS parallel_streams,
formatReadableTimeDelta(round(disk_read_seconds / parallel_streams), 'seconds') AS time_per_stream
FROM system.query_log
WHERE query_id = 'b9566386-047d-427c-a5ec-e90bee027b02'
AND type = 'QueryFinish';
┌─disk_read_seconds─┬─parallel_streams─┬─time_per_stream─┐
│ 5512 │ 32 │ 172 seconds │
└───────────────────┴──────────────────┴─────────────────┘
SELECT
helpful_votes,
product_title,
review_headline,
review_body
FROM amazon.amazon_reviews
WHERE review_date >= '2010-01-01'
AND product_category = 'Digital_Ebook_Purchase'
AND verified_purchase
AND star_rating > 4
ORDER BY helpful_votes DESC
LIMIT 3
FORMAT Vertical;
Row 1:
──────
helpful_votes: 6376
product_title: Wheat Belly: Lose the Wheat, Lose the Weight, and Find Your Path Back to Health
review_headline: Overweight? Diabetic? Got High Blood Pressure, Arthritis? Get this Book!
review_body: I've been following Dr. Davis' heart scan blog for the past ...
Row 2:
──────
helpful_votes: 4149
product_title: The Life-Changing Magic of Tidying Up: The Japanese Art of Decluttering and Organizing
review_headline: Truly life changing
review_body: I rarely write reviews, but this book truly sparked somethin...
Row 3:
──────
helpful_votes: 2623
product_title: The Fast Metabolism Diet: Eat More Food and Lose More Weight
review_headline: Fantastic Results **UPDATED 1/23/2015**
review_body: I have been on this program for 7 days so far. I know it ma...
SELECT
name as column,
formatReadableSize(sum(data_uncompressed_bytes)) AS data_size,
formatReadableSize(sum(data_compressed_bytes)) AS compressed_size
FROM system.columns
WHERE database = 'amazon' AND table = 'amazon_reviews'
GROUP BY name
ORDER BY sum(data_uncompressed_bytes) DESC;
┌─column────────────┬─data_size──┬─compressed_size─┐
│ review_body │ 51.13 GiB │ 21.60 GiB │
│ product_title │ 8.12 GiB │ 3.53 GiB │
│ review_headline │ 3.38 GiB │ 1.58 GiB │
│ review_id │ 2.07 GiB │ 1.35 GiB │
│ product_id │ 1.55 GiB │ 720.97 MiB │
│ customer_id │ 1.12 GiB │ 524.35 MiB │
│ product_parent │ 1.12 GiB │ 571.63 MiB │
│ helpful_votes │ 575.86 MiB │ 72.11 MiB │
│ total_votes │ 575.86 MiB │ 83.50 MiB │
│ review_date │ 287.93 MiB │ 239.43 KiB │
│ marketplace │ 144.51 MiB │ 414.92 KiB │
│ product_category │ 144.25 MiB │ 838.96 KiB │
│ star_rating │ 143.96 MiB │ 41.99 MiB │
│ verified_purchase │ 143.96 MiB │ 20.50 MiB │
│ vine │ 1.75 MiB │ 844.89 KiB │
└───────────────────┴────────────┴─────────────────┘
CREATE TABLE amazon.amazon_reviews_no_pk
Engine = MergeTree
ORDER BY ()
AS SELECT * FROM amazon.amazon_reviews;
SELECT
helpful_votes,
product_title,
review_headline,
review_body
FROM amazon.amazon_reviews_no_pk
WHERE review_date >= '2010-01-01'
AND product_category = 'Digital_Ebook_Purchase'
AND verified_purchase
AND star_rating > 4
ORDER BY helpful_votes DESC
LIMIT 3
FORMAT Null
SETTINGS
optimize_move_to_prewhere = false,
query_plan_optimize_lazy_materialization = false;
3 rows in set. Elapsed: 219.508 sec. Processed 150.96 million rows, 72.13 GB (687.71 thousand rows/s., 328.60 MB/s.)
Peak memory usage: 953.25 MiB.
SELECT
helpful_votes,
product_title,
review_headline,
review_body
FROM amazon.amazon_reviews
WHERE review_date >= '2010-01-01'
AND product_category = 'Digital_Ebook_Purchase'
AND verified_purchase
AND star_rating > 4
ORDER BY helpful_votes DESC
LIMIT 3
FORMAT Null
SETTINGS
optimize_move_to_prewhere = false,
query_plan_optimize_lazy_materialization = false;
0 rows in set. Elapsed: 95.865 sec. Processed 53.01 million rows, 27.67 GB (552.98 thousand rows/s., 288.68 MB/s.)
Peak memory usage: 629.00 MiB.
SELECT
helpful_votes,
product_title,
review_headline,
review_body
FROM amazon.amazon_reviews
WHERE review_date >= '2010-01-01'
AND product_category = 'Digital_Ebook_Purchase'
AND verified_purchase
AND star_rating > 4
ORDER BY helpful_votes DESC
LIMIT 3
FORMAT Null
SETTINGS
optimize_move_to_prewhere = true,
query_plan_optimize_lazy_materialization = false;
0 rows in set. Elapsed: 61.148 sec. Processed 53.01 million rows, 16.28 GB (866.94 thousand rows/s., 266.24 MB/s.)
Peak memory usage: 583.30 MiB.
SELECT
helpful_votes,
product_title,
review_headline,
review_body
FROM amazon.amazon_reviews
WHERE review_date >= '2010-01-01'
AND product_category = 'Digital_Ebook_Purchase'
AND verified_purchase
AND star_rating > 4
ORDER BY helpful_votes DESC
LIMIT 3
FORMAT Null
SETTINGS
optimize_move_to_prewhere = true,
query_plan_optimize_lazy_materialization = true;
0 rows in set. Elapsed: 0.181 sec. Processed 53.01 million rows, 807.55 MB (292.95 million rows/s., 4.46 GB/s.)
Peak memory usage: 3.88 MiB.
😮 61 秒から 181 ミリ秒へ、338 倍の高速化。
ClickHouseは同じ5300万行を処理しましたが、読み取る列データは20倍少なく、使用メモリは150倍少なく、瞬く間に終了しました。 その仕組みを詳しく見ていきましょう。 説明は簡単です。 PREWHEREフィルタリングの後、ClickHouseは残りのすべての列を[すぐに](https://www.google.com/search?q=/blog/clickhouse-gets-lazier-and-faster-introducing-lazy-materialization%23steps-after-prewhere-filtering)ロードしません。 代わりに、次に必要なものだけをロードします。次のステップは`helpful_votes`によるソートとLIMITの適用なので、ClickHouseは①選択された（そしてPREWHEREでフィルタリングされた）`helpful_votes`グラニュールのみをロードし、②それらの行をソートし、③LIMITを適用し、そしてその後にのみ④対応する行を[大きな](https://www.google.com/search?q=/blog/clickhouse-gets-lazier-and-faster-introducing-lazy-materialization%23a-more-realistic-querywhere-optimizations-matter)`product_title`、`review_headline`、`review_body`列からロードします。  そして、このようにして最後のレイヤーが所定の位置に収まり、実行時間は220秒からわずか181ミリ秒に短縮されました。同じクエリ、同じテーブル、同じマシン、同じ遅いディスク…ただ**1,215倍高速**になっただけです。変更したのは、データの読み取り方法とタイミングだけです。 > この例では、クエリが大きなテキスト列を選択し、遅延マテリアライゼーションのおかげで最終的にそれらから3行しか必要とされないため、遅延マテリアライゼーションが最大の効果をもたらします。しかし、データセットとクエリの形状によっては、インデックス作成やPREWHEREのような初期の最適化の方が大きな節約をもたらす場合があります。これらの技術は連携して機能し、それぞれが異なる方法でI/O削減に貢献します。 注意: 遅延マテリアライゼーションは `LIMIT N` クエリに対して自動的に適用されますが、`N` のしきい値までです。これは [query_plan_max_limit_for_lazy_materialization](https://clickhouse.com/docs/operations/settings/settings#query_plan_max_limit_for_lazy_materialization) 設定（デフォルト: 10）によって制御されます。0 に設定すると、遅延マテリアライゼーションは上限なしですべての LIMIT 値に適用されます。 ## フィルタなしでの高速化：遅延マテリアライゼーション単独の効果 インデックス作成とPREWHEREの恩恵を受けるには、クエリにフィルタが必要です。インデックス作成の場合はプライマリキー列に、PREWHEREの場合は任意の列にフィルタが必要です。上記で示したように、遅延マテリアライゼーションはこれらにきれいに重ねられますが、他とは異なり、列フィルタがまったくないクエリも高速化できます。 これを実証するために、サンプルクエリからすべてのフィルタを削除し、日付、製品、評価、または検証ステータスに関係なく、役立つ投票数が最も多いレビューを見つけ、上位3件をタイトル、見出し、全文とともに返します。 まず、遅延マテリアライゼーションを無効にして、そのクエリを（[コールドファイルシステムキャッシュ](https://www.google.com/search?q=/blog/clickhouse-gets-lazier-and-faster-introducing-lazy-materialization%23with-cold-os-level-filesystem-cache)で）実行します。
SELECT
helpful_votes,
product_title,
review_headline,
review_body
FROM amazon.amazon_reviews
ORDER BY helpful_votes DESC
LIMIT 3
FORMAT Vertical
SETTINGS
query_plan_optimize_lazy_materialization = false;
Row 1:
──────
helpful_votes: 47524
product_title: Kindle: Amazon's Original Wireless Reading Device (1st generation)
review_headline: Why and how the Kindle changes everything
review_body: This is less a "pros and cons" review than a hopefully use...
Row 2:
──────
helpful_votes: 41393
product_title: BIC Cristal For Her Ball Pen, 1.0mm, Black, 16ct (MSLP16-Blk)
review_headline: FINALLY!
review_body: Someone has answered my gentle prayers and FINALLY designed ...
Row 3:
──────
helpful_votes: 41278
product_title: The Mountain Kids 100% Cotton Three Wolf Moon T-Shirt
review_headline: Dual Function Design
review_body: This item has wolves on it which makes it intrinsically swee...
0 rows in set. Elapsed: 219.071 sec. Processed 150.96 million rows, 71.38 GB (689.08 thousand rows/s., 325.81 MB/s.)
Peak memory usage: 1.11 GiB.
SELECT
helpful_votes,
product_title,
review_headline,
review_body
FROM amazon.amazon_reviews
ORDER BY helpful_votes DESC
LIMIT 3
FORMAT Vertical
SETTINGS
query_plan_optimize_lazy_materialization = true;
Row 1:
──────
helpful_votes: 47524
product_title: Kindle: Amazon's Original Wireless Reading Device (1st generation)
review_headline: Why and how the Kindle changes everything
review_body: This is less a "pros and cons" review than a hopefully use...
Row 2:
──────
helpful_votes: 41393
product_title: BIC Cristal For Her Ball Pen, 1.0mm, Black, 16ct (MSLP16-Blk)
review_headline: FINALLY!
review_body: Someone has answered my gentle prayers and FINALLY designed ...
Row 3:
──────
helpful_votes: 41278
product_title: The Mountain Kids 100% Cotton Three Wolf Moon T-Shirt
review_headline: Dual Function Design
review_body: This item has wolves on it which makes it intrinsically swee...
0 rows in set. Elapsed: 0.139 sec. Processed 150.96 million rows, 1.81 GB (1.09 billion rows/s., 13.06 GB/s.)
Peak memory usage: 3.80 MiB.
EXPLAIN actions = 1
SELECT
helpful_votes,
product_title,
review_headline,
review_body
FROM amazon.amazon_reviews
ORDER BY helpful_votes DESC
LIMIT 3
SETTINGS
query_plan_optimize_lazy_materialization = true;
...
Lazily read columns: review_headline, review_body, product_title
Limit
Sorting
ReadFromMergeTree