Hi @dragonsahead, David,
I spent several days investigating this exact issue and can now explain what's happening and offer a concrete fix.
**The root cause is `insights-xform` in `results_metadata.clj`** — it runs statistical fingerprinting (trend analysis, anomaly detection) on every row of every query. The mechanism is `combine-additional-reducing-fns` in `reducible.clj`, which recreates a Clojure map from each result row vector to give the insights reducer named column access. This triggers `clojure.lang.Util.hasheq(Object)` for every key and value, for every row — exactly what David's JMC profiling showed.
Dragonsahead, you were right that Metabase eliminates reflection warnings at build time — this isn't reflection. It's the hash computation that Clojure performs when constructing maps from vectors. The `hasheq` calls are an inherent cost of creating keyword-keyed maps in a hot loop.
**How this happened — code archaeology:**
The performance was fine originally. Simon Belak added insights to query responses in Aug 2018 (`4056160d24`) as **batch post-processing** — after the query completed, it ran `transduce` over the already-materialized row vector. One-time scan, no per-row allocation overhead.
In Feb 2020, Cam Saul's streaming/transducing QP rewrite (`aced697af9`, PR #11832) converted the entire query processor from buffering all rows in memory to streaming them through a transducing pipeline. This was a **major win for memory** — no more materializing full result sets. But to maintain the insights contract in a streaming pipeline, `combine-additional-reducing-fns` was created, which needs to convert each vector row to a map (for named column access by the statistics reducers). Nobody measured the CPU cost because the change was about memory, and the per-row overhead isn't visible in small result sets.
The first explicit performance attention came 4 years later (Aug 2024) when Oleksandr Yakushev optimized the insights internals (datetime conversions, fingerprinter closures) — valuable micro-optimizations, but nobody questioned whether insights should run on every row of every query.
**Proof that this is the bottleneck:**
I built a [NixOS VM benchmark framework]( metabase/nix/microvms/mkBenchVm.nix at nix · randomizedcoder/metabase · GitHub ) that runs Metabase end-to-end with PostgreSQL and ClickHouse warehouses, testing column-scaling queries (1-100 columns × 20K rows). Key finding: **at 50+ columns, PostgreSQL and ClickHouse produce identical response times** — the bottleneck is entirely in Metabase's core pipeline, not the database driver. Cost scales linearly with `rows × columns`, consistent with per-row map creation.
The framework builds three patched Metabase variants and benchmarks them side-by-side in a single VM:
| Query (20K rows) | Cols | Vanilla (ms) | No-insights (ms) | Speedup |
|---|---|---|---|---|
| 1 col | 1 | 265 | 137 | **1.9x** |
| 6 cols | 6 | 674 | 282 | **2.4x** |
| 20 cols | 20 | 1334 | 566 | **2.4x** |
| 50 cols | 50 | 2068 | 1273 | **1.6x** |
**The fix — `MB_LUDICROUS_SPEED=true`:**
Metabase already had a `skip-results-metadata?` middleware flag that bypasses `insights-xform` entirely — it was used for data exports but not for normal queries. We added `MB_LUDICROUS_SPEED=true` as a defsetting that enables this bypass globally. Set the environment variable and you get 2-3x faster API responses immediately. The only cost is losing the trend/anomaly badges on dashboard cards.
**For ClickHouse specifically — `MB_CLICKHOUSE_NATIVE=true`:**
We also identified a second, independent overhead source: the ClickHouse JDBC driver uses HTTP/JSON text serialization with no connection pooling. We added an alternative transport using the official ClickHouse Client V2 library with RowBinary binary format and LZ4 compression. This gives an additional **1.8x speedup** at low column counts.
Combined, `MB_LUDICROUS_SPEED=true` + `MB_CLICKHOUSE_NATIVE=true` delivers **~3x faster** end-to-end for typical ClickHouse dashboard queries.
Everything is in [PR #71399]( Add Nix build system for reproducible builds and NixOS support by randomizedcoder · Pull Request #71399 · metabase/metabase · GitHub ) with full [performance analysis]( metabase/nix/performance-analysis.md at nix · randomizedcoder/metabase · GitHub ) including the commit-by-commit history, benchmark data, and reproducible test instructions.