1
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

「AI時代のデータベース、何が変わる？」について語ろう！
Qiita Tech Festa20262026年7月13日まで開催中！
@asahide

ClickHouse の遅いクエリ、AI に『なぜ遅いか』の切り分けまで任せてみた

1
Posted at

ClickHouse の遅いクエリを AI に渡し、「なぜ遅いのか」を system.query_log や EXPLAIN から自分で切り分けさせて、直させて、実測で答え合わせをした記録です。答えを知らない状態の AI に解かせ、こちらが先に用意した正解と突き合わせています。

1. はじめに

前回の記事1では、ClickHouse の独自集計関数を自然言語で AI に書いてもらいました。今回はその続きで、AI に遅いクエリを直してもらいます。正確には、直す前の「なぜ遅いのか」を AI 自身に切り分けてもらいます。

きっかけは ClickHouse の事例ブログ2です。セキュリティ企業 Artemis が、/poke という Claude のスキルを使い、自然言語で system.query_log などを引いて性能問題を切り分けている、という話が出てきます。ここで AI が担っているのは「どのクエリが時間境界なしで大量に読んでいるか」を system テーブルから見つける部分です。この「AI が system テーブルから原因を切り分ける」ところだけを、自分の手元で再現してみたくなりました。

/poke は Artemis が自分たちの環境向けに作ったスキルで、手元でそのまま使えるものではありません。今回は /poke を使う代わりに、何も足していない素の Claude のエージェントに、遅いクエリと SQL を実行する手段だけを渡し、「なぜ遅いか調べて、同じ結果のまま速くして」と自然言語で頼みます。/poke のような専用スキルを用意しなくても同じ切り分けが出てくるか、という形の再現です（5.5 で試す ClickHouse 公式の agent-skills も、この /poke とは別物です）。

1.1. 結論（先出し）

  • 遅いクエリを自然言語で渡すと、AI は頼まなくても SHOW CREATE TABLE・system.parts・system.query_log・EXPLAIN indexes を自分で引いて、原因を証拠付きで切り分けた
  • 3 つのお題すべてで、AI が挙げた原因も改善クエリも、こちらが先に用意した正解と一致した。改善後の read_rows やメモリも事前の実測とほぼ同じだった
  • この診断の挙動は、SQL を渡す手段（CLI か mcp-clickhouse か）を変えても同じだった。つまり原因を切り分けるのは MCP サーバの機能ではなく、クライアント側の AI の判断である
  • ClickHouse 公式の agent-skills を入れると、調べる手順は体系的になりルールの引用も増えたが、たどり着く原因と改善は入れないときと変わらなかった
  • 近似関数の誤差や、索引の効き具合の詰めは、最後に人間が確認する余地が残る

2. 検証環境

診断役の AI には、正解を一切渡していません。答えを知らないまっさらな Claude Code のエージェントを毎回立て、「ディスク上のメモは読まず、データベースへの SQL だけで診断せよ」と縛っています。正解と改善は、AI に頼む前に別セッションで用意して確認しました。

項目 値
ClickHouse Cloud 26.2.1.507（AWS ap-northeast-1）
データ GH Archive3 2026-06-05〜2026-07-04 の 30 日分
データ規模 114,560,887 行 / 11 パート・1.12 GiB（ディスク上）
診断役 Claude Code のエージェント（Opus 4.8）。答えを知らない新規セッション
SQL を実行する手段 WSL 上の clickhouse client（CLI）と mcp-clickhouse4 の 2 経路

テーブルは前回と同じ、GitHub のイベントを 4 列に絞った構造です。ソートキーが (event_type, repo_name, created_at) である点が、あとのお題に関わってきます。

-- github_events: GitHub のイベント 30 日分
CREATE TABLE github_events
(
    event_type  LowCardinality(String),  -- PushEvent, CreateEvent, DiscussionEvent など
    actor_login String,                   -- 行動したユーザー
    repo_name   String,                   -- "owner/repo" 形式
    created_at  DateTime
)
ENGINE = MergeTree
ORDER BY (event_type, repo_name, created_at);

3. お題の設計

わざと遅い、あるいはメモリを使うクエリを 3 本用意しました。それぞれ「素の悪いクエリ」と「意図した改善クエリ」を、AI に頼む前に自分で実行し、system.query_log から read_rows・メモリ・実行時間を回収して正解として先に用意しています（前回と同じ事前登録のやり方です）。お題を選ぶセッションと解かせるセッションは分け、お題を渡す文には原因も ClickHouse の用語も入れていません。

# 渡したクエリ（要約） 事前に決めた原因 意図した改善
A ある 1 ユーザーの件数を数える actor_login がソートキーに無く全スキャン data-skipping index を貼る
B アクティブなユーザー上位 10 人 上位を出す前に全ユーザーを集約してメモリを使う 近似の集約に変える
C ある種類のイベント件数を数える ソートキー列に関数を適用して枝刈りが止まる 関数を外して直接比較する

お題 A は「まれにしか出てこないユーザー」を選びました。出現回数が少ない値ほど、bloom_filter の data-skipping index が granuleを大きく飛ばせるためです。ちょうど Artemis の「巨大なログから特定のユーザーや IP を探す」場面に対応します。今回選ばれたのは 100 回だけ出てくる actor_login でした。

お題 C は、まれな種類のイベントを 1 つ選び、event_type に lower() を適用する形にしました。今回は 1,779 件の DiscussionEvent です。

4. 手順

流れは次のとおりです。答え合わせを公平にするため、正解を用意するセッションと、AI に解かせるセッションを分けています。

  1. お題を自然言語で 1 本ずつ渡す（「このクエリが遅いので、原因を調べて同じ結果のまま速くしてください」）。原因も関数名も言わない
  2. AI が診断のために何を実行したかを、応答と system.query_log から回収する
  3. AI が挙げた原因・改善クエリを、事前登録した正解と突き合わせ、改善後を実測する
  4. 同じことを CLI と mcp-clickhouse の 2 経路で行い、結果が変わらないかを見る

計測にあたって 1 つ注意点があります。26.2 では use_query_condition_cache がデフォルトで有効で、同じ条件のクエリを 2 回目に実行すると、前回一致した granule の情報がキャッシュされ read_rows が小さく出ます。before と after の比較がぶれるため、計測クエリには SETTINGS use_query_condition_cache = 0 を付けています。

5. 実行結果

5.1. 採点のまとめ

3 本すべて、AI が挙げた原因と改善が事前登録の正解と一致しました。次の表は、事前登録・CLI 経由の診断・mcp-clickhouse 経由の診断の 3 つを並べたものです。

お題 主な指標 事前登録 CLI 診断 mcp-clickhouse 診断
A（skip index） read_rows 114,560,887 → 1,277,952 → 1,171,456 → 1,171,456
実行時間 1,276 → 36 ms 1,272 → 39 ms 1,335 → 37 ms
B（近似の集約） メモリ 572 → 274 MiB 573 → 288 MiB 577 → 292 MiB
C（直接比較） read_rows 114,560,887 → 90,112 → 90,112 → 90,112
実行時間 147 → 4 ms 147 → 5 ms 152 → 4 ms

件数は変わりません（お題 A は 100 件、お題 C は 1,779 件のまま）。お題 B は近似のため件数の扱いが少し違うので、5.3 で改めて触れます。CLI と mcp-clickhouse で数字がほぼ重なっている点が、この記事で確かめたかった点です。

5.2. お題 A: 索引の無い列で 1 ユーザーを探す

AI はまず SHOW CREATE TABLE でソートキーを確認し、actor_login がそこに含まれないことを見つけました。次に EXPLAIN indexes = 1 を当て、全 granule が読まれることを示しました。

-- WHERE actor_login = '...'（索引なし）
PrimaryKey  Condition: true   Parts: 11/11   Granules: 13992/13992

原因は「actor_login がソートキーに無いので主キーの索引で granule を飛ばせず、100 件を返すだけなのに 1 億 1,456 万行すべてを読む」でした。事前登録した原因と同じです。AI が選んだ改善は bloom_filter の data-skipping index で、これも事前登録と同じでした。

-- AI が適用した改善（計測後に自分で DROP して元に戻した）
ALTER TABLE github_events ADD INDEX idx_actor_login actor_login TYPE bloom_filter(0.01) GRANULARITY 1;
ALTER TABLE github_events MATERIALIZE INDEX idx_actor_login SETTINGS mutations_sync = 2;

索引を貼ったあと、EXPLAIN は読む granule が 13,992 から 143 に減ることを示し、read_rows は 1 億 1,456 万から約 117 万へ、実行時間は 1,335 ms から 37 ms になりました（mcp-clickhouse 経由）。

このお題でいちばん興味深かったのは、AI が最初の計測でつまずいた点を自分で立て直したことです。1 回目の計測で read_rows が 44 万・13 ms と小さく出たのを、AI は「これは前述のキャッシュが効いた実行で、本当のコストを隠している」と判断し、use_query_condition_cache = 0 で測り直して全スキャンを確定させました。こちらが計測スクリプトに仕込んでいた注意点と同じところに、AI が自力でたどり着いています。

mcp-clickhouse 経由では、索引を作る MATERIALIZE INDEX が終わるのに時間がかかりますが、AI は完了を system.mutations で確認してから次に進み、計測後は ALTER TABLE ... DROP INDEX でテーブルを元に戻しました。

5.3. お題 B: 上位 10 人を出すのにメモリを使う

AI は uniqExact(actor_login) で異なるユーザー数が 594 万あることを確認し、「上位 10 人を出す前に、594 万個のキーのハッシュテーブルをメモリに作るため」と切り分けました。事前登録の原因と同じです。

改善は近似の集約関数です。CLI 経由の AI は approx_top_count、mcp-clickhouse 経由の AI は approx_top_k を選びました。この 2 つは同じ関数で、approx_top_count は approx_top_k の別名です5。第 2 引数 reserved（保持するカウンタ数、デフォルトは N×3）を大きく取ると精度が上がり、AI はどちらもこれを 8,192 や 10,000 に増やしていました。メモリは 577 MiB から約 292 MiB へ、およそ半分に下がりました。読む行数は変わりません（上位を出すには結局すべて読むため）。ここで下がるのはメモリで、実行時間はほぼ同じです。

このお題だけは結果が近似になります。AI は自分でその差を数えて報告しました。上位 10 人の顔ぶれと順序は厳密版と一致し、上位 3 人の件数は誤差ゼロ、残りも 0.72% 以内でした。あわせて AI は「カウンタ数をデフォルトの 10 のままにすると pull[bot] などを取りこぼして順位も崩れるので、8,192 まで増やす必要がある」という注意も、頼まずに付け加えていました。

5.4. お題 C: ソートキー列に関数を適用する

AI は SHOW CREATE TABLE で event_type が先頭のソートキー列だと確認し、EXPLAIN indexes = 1 を両方の書き方に当てて、違いを並べて見せました。

-- WHERE lower(event_type) = 'discussionevent'
PrimaryKey  Condition: true            Granules: 13992/13992   （全スキャン）

-- WHERE event_type = 'DiscussionEvent'
PrimaryKey  Keys: event_type           Granules: 11/13992      （binary search で枝刈り）

原因は「先頭のソートキー列に lower() を適用すると、主キー索引がそのまま比較できなくなり全スキャンになる」でした。改善は関数を外して直接比較するだけです。AI は「保存されているのは DiscussionEvent の 1 種類だけなので、lower() はそもそも要らない」と確かめたうえで書き換え、読む行数は 1 億 1,456 万から 90,112（11 granule 分）へ、実行時間は 152 ms から 4 ms になりました。件数は 1,779 件のまま変わりません。

5.5. 公式 agent-skills を入れると何が変わったか

ClickHouse には公式のエージェント向けナレッジ集 agent-skills6 があります。前回の記事では、これを入れても集計関数の選び方は変わりませんでした。今回はナレッジ集が「クエリ最適化」を守備範囲に含むので、診断が変わるかを見ました。

入れた場合、AI は agent-discovery-schema という手順に沿って、直す前にソートキー・カーディナリティ・skip index を順に棚卸ししていました。原因を挙げるときも schema-pk-filter-on-orderby や query-index-skipping-indices といったルール名を引用します。お題 B では「ClickHouse Cloud は GROUP BY を自動でディスクに退避する」というルールに従い、外部集約に切り替える案を避けていました（実際に試すと 935 MiB・11.7 秒とかえって悪化しました）。

ただし、たどり着いた原因と改善は、入れないときと同じでした。むしろお題 A では、入れない AI が bloom_filter の誤検知率を明示して読む granule を 143 まで絞れたのに対し、入れた AI はデフォルトのまま 355 granule までしか絞れませんでした。手順は体系的になりルールの引用は増えますが、答えの質が上がる保証はない、という結果です。

6. 考察

6.1. 原因を切り分けるのはクライアント側の AI の判断

同じ 3 本を CLI 経由と mcp-clickhouse 経由で解かせて、原因も改善も実測もほぼ一致しました。SHOW CREATE TABLE を引く、EXPLAIN indexes で読む granule を数える、キャッシュの影響を打ち消す、といった一連の動きは、どちらの経路でも AI が自分で組み立てています。

ここは分けて考える必要があります。SQL を実行する手段（CLI か mcp-clickhouse か）は「どうつなぐか」であって、「何を調べるか」を決めているのはクライアント側の AI です。mcp-clickhouse が readonly かどうかや、どのツールを見せるかはサーバ側の話ですが、system テーブルを引いて原因を絞るところはサーバの機能ではありません。別のクライアントや別の頼み方なら変わりうる、という前提でも、今回は 2 経路で同じ挙動になりました。

6.2. /poke の再現範囲

今回再現したのは、Artemis の /poke のうち ClickHouse の system テーブルを引く部分です。/poke は実際には CloudWatch やソースコードも見に行きますが、そこは各社の環境に依存するため対象外にしました。それでも、「自然言語で遅いクエリを渡すと、AI が system テーブルと EXPLAIN から原因を切り分ける」という中心の動きは、手元の 1 テーブルでもそのまま確認できました。

念のため、今回の診断役は Artemis の /poke でも、5.5 で入れた ClickHouse 公式の agent-skills でもなく、何も足していない素の Claude エージェントです。/poke のような専用スキルを用意しなくても、遅いクエリと SQL を実行する手段を渡して自然言語で頼むだけで、system テーブルからの切り分けが出てきた、という結果でした。

6.3. agent-skills は手順を変えるが結論を変えなかった

前回の記事では、agent-skills がお題（集計関数）を守備範囲に含まないため、挙動が変わりませんでした。今回はクエリ最適化という守備範囲内のお題でしたが、それでも最終的な原因と改善は変わらず、変わったのは調べる手順の体系性とルールの引用でした。ナレッジ集は「調べ方の型」を与えるもので、今回のお題では素の AI がすでにその型に近い調べ方をしていた、と考えられます。

今回の 3 つの原因（索引の無い列でのフィルタ、ソートキー列への関数適用、高カーディナリティの集約）は、ClickHouse の公式 best practices や公式トレーニング（ClickHouse Academy7）が扱う、主キー索引まわりの基礎です。素の AI がこの基礎に沿って調べていた、とも言えます。ただし今回は 4 列の単純なテーブルで、原因も 1 つずつはっきりしていました。列数の多い複雑なスキーマや、複数の原因が絡む場面では、体系的に棚卸しする手順の効果がもっと出る可能性があります（未検証）。

6.4. 近似と索引の詰めは人間が最後に見る

お題 B の近似の集約は、件数がわずかにずれます。今回は 0.72% 以内で顔ぶれも順序も一致しましたが、どこまでの誤差を許すかは用途しだいです。お題 A の索引も、誤検知率などのパラメータしだいで読む行数が変わり、AI がデフォルトのまま貼ると絞りが甘くなる場合がありました。原因の切り分けと最初の改善までは任せられますが、近似の誤差と索引の詰めは、人間が最後に確認する余地が残ります。

7. まとめ

前回は、ClickHouse の独自集計関数を AI に書かせ、AI 自身に検算までさせると安心して使える、という型を確かめました。今回はその型を「直す・切り分ける」側へ延ばしています。

  • AI に原因を切り分けさせる: 遅いクエリを自然言語で渡すと、system.query_log や EXPLAIN から原因を証拠付きで挙げる
  • 別経路や実測で裏取りする: CLI と mcp-clickhouse で結果が一致するか、改善後の read_rows やメモリが本当に下がるかを実測で確かめる
  • 人間は近似の誤差と索引の詰めだけ最終確認する: 近似の件数のずれや、索引の絞り具合など、AI に任せきりにしにくいところを押さえる

集計を書くときと同じで、原因の切り分けも、学び切ってから使うのではなく、まず AI に任せて実測で確かめながら進められる。そう改めて感じた検証でした。

参考

  1. 前回記事「とっつきにくい ClickHouse の集計関数、AI に任せれば安心して使える」. https://qiita.com/asahide/items/f13b458da95693af4fcd ↩

  2. ClickHouse Blog, "Artemis Security: real-time threat detection". https://clickhouse.com/blog/artemis-security-real-time-threat-detection ↩

  3. GH Archive（GitHub の公開イベントを時間単位で配布するアーカイブ）. https://www.gharchive.org/ ↩

  4. mcp-clickhouse（ClickHouse 公式の MCP サーバ）. https://github.com/ClickHouse/mcp-clickhouse ↩

  5. ClickHouse Docs, Aggregate Functions Reference, approx_top_k（approx_top_count は別名。第 2 引数 reserved はデフォルト N×3）. https://clickhouse.com/docs/en/sql-reference/aggregate-functions/reference/approxtopk ↩

  6. ClickHouse agent-skills（公式）. https://github.com/ClickHouse/agent-skills ↩

  7. ClickHouse Academy（ClickHouse 公式のトレーニング）. https://learn.clickhouse.com/ ↩

1
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
1
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?