Metabase Pivot 的效能問題

Dimensional N+1 問題

Metabase Pivot 的效能問題

Dimensional N+1 問題

客戶回報了一個問題。

他們用 Metabase 看一張 ClickHouse 資料表，6000 筆資料，直接看沒問題，大約 10 秒。但只要切換成資料透視表（Pivot Table），就要等 120 秒。

同一份資料，慢了 12 倍。

Pivot Table 是什麼

同樣是這份資料：

\(\begin{array}{|c|c|c|} \hline \text{月份} & \text{地區} & \text{金額} \\ \hline \text{1月} & \text{北部} & 5000 \\ \text{1月} & \text{南部} & 3000 \\ \text{2月} & \text{北部} & 4000 \\ \text{2月} & \text{南部} & 6000 \\ \hline \end{array}\)

Pivot 之後，變成這樣：

\(\begin{array}{|c|c|c|c|} \hline \text{月份} & \text{北部} & \text{南部} & \text{小計} \\ \hline \text{1月} & 5000 & 3000 & 8000 \\ \text{2月} & 4000 & 6000 & 10000 \\ \hline \textbf{總計} & \textbf{9000} & \textbf{9000} & \textbf{18000} \\ \hline \end{array}\)

資料沒有變，但「2月南部最好」這個結論，現在是視覺直接告訴你的。

這個例子只有 2 個維度。維度一多，小計的組合就會指數增長。如果讀者對 Pivot 背後的 SQL 機制有興趣，我在《從試算表到資料平台》一書裡有更完整的討論。

抓問題的方式

遇到這種效能問題，如果可以快速地重製情境，那就可以從 frontend 的　Chrome Debugger 去看出各個 backend RESTful API 大約各花多少時間，很快地來定位效能瓶頸。

不過，由於重製情境可能需要相對大的 dataset (6000 筆)，所以我決定走另一條路。

我下載了 Metabase 的 source code，然後用 Claude Code 來做 code tracing。

過去這種事情很花時間，光是理解一個陌生 codebase 的架構就要半天。但現在 Claude Code 可以從 frontend 的元件一路追到 backend 的查詢處理層，並很快地就走訪完了完整的呼叫鏈。

診斷：Dimensional N+1

首先排除了 frontend。

frontend 確實有一些小問題，像是 canvas 文字測量的 style flush 造成的 layout thrashing，CLJS 資料轉換也是同步執行，但這些加起來頂多幾百毫秒。解釋不了 110 秒的差距。

問題在 backend。

當使用者切換到 Pivot Table，Metabase 不是送同一條 SQL 查詢，而是換了一條完全不同的執行路徑（card.clj:337）：

(if (= :pivot (:display card))
  qp.pivot/run-pivot-query       ; ← Pivot Table 走這條
  process-query-for-card-default-qp)

run-pivot-query 會呼叫 generate-queries，對所有的 breakout 欄位計算冪集（powerset），為每一種小計與總計各自產生一條 SQL：

breakout: [地區, 品類, 年份]
→ 產生以下查詢：

  [地區, 品類, 年份]   ← 主資料
  [地區,       年份]   ← 欄小計
  [地區, 品類      ]   ← 列小計
  [地區            ]   ← 列小計（更高層）
  [            年份]   ← 欄總計
  [                ]   ← 右下角總計

這些查詢，在 process-multiple-queries（pivot.clj:363）裡逐一、循序執行，沒有並行。

一條查詢 10 秒，跑 12 條，就是 120 秒。

這就是 N+1 問題，只是換了個尺度。傳統的 N+1 問題是「取一批資料，然後對每筆資料再各送一次查詢」。這裡的 N+1 則是「一張 Pivot Table，拆成 N 條查詢，一條一條地送進資料庫執行」。我把它稱為 Dimensional N+1。

為什麼不能只改 ClickHouse Adapter

自然地，下一個問題是：「可以不改 Metabase 的核心，只修改 ClickHouse 的 adapter 嗎？」

不行。

Metabase 的 driver 架構是靠 multimethod 來做擴充，driver 可以 override SQL 的編譯、型別轉換、日期處理等等。但 generate-queries 和 process-multiple-queries 都是 pivot.clj 裡的 private plain function，不是 multimethod，adapter 沒有任何 hook 可以介入。

當 driver 拿到 SQL 的時候，已經是拆好的多條查詢了。它不知道自己是在執行 pivot，自然也無法把查詢合併。

其實解法很清楚：主流的 OLAP 資料庫幾乎都原生支援 GROUPING SETS 或 ROLLUP，可以用一條 SQL 表達 Metabase 現在用 N 條查詢才能做到的事。ClickHouse 也支援。但 Metabase 整個 codebase 沒有任何一行用到這些語法，而且要使用的話，必須改動 core，不是改 adapter 就能解決的。

結語

整理完診斷之後，我去 Metabase 的 GitHub 提了 issue。然後發現，2023 年就有人提過類似的問題了。

那個 issue 被標記為 difficult，至今仍然開著。看來還要再等一段時間。