--- id: 10172 canonical_url: "https://phying.com/item/10172" title: "Making pivot tables faster with GROUPING SETS" type: "blog" cve: null cvss: null severity: "unknown" vendor: null product: null affected_versions: null source_domain: "metabase.com" source_url: "https://www.metabase.com/blog/faster-pivot-tables-with-grouping-sets" published_at: "2026-10-21T00:00:00Z" snapshot_at: "2026-10-27T14:59:06Z" archived_by: "Phying News (https://phying.com/item/10172)" lang: "en" --- # Making pivot tables faster with GROUPING SETS *Published on: 2026-10-21 · Source: [metabase.com](https://www.metabase.com/blog/faster-pivot-tables-with-grouping-sets)* [ All posts ](https://www.metabase.com/blog) Oct 21, 2026 in **Engineering** ‧ 10 min read # Making pivot tables faster with GROUPING SETS ![](https://www.metabase.com/images/posts/blog-authors/the-metabase-team.svg) ##### Tamás Benkő ‧ Oct 21, 2026 in **Engineering** ‧ 10 min read ![Making pivot tables faster with GROUPING SETS Image](https://cdn.metabase.com/images/improving-pivot-tables-v2.webp) ##### Share this article [ ](https://twitter.com/intent/tweet?url=https://metabase.com/blog/faster-pivot-tables-with-grouping-sets) [ ](https://www.linkedin.com/sharing/share-offsite/?url=https://metabase.com/blog/faster-pivot-tables-with-grouping-sets) Copied to clipboard We got tired of waiting for pivot tables to load, so we made them faster. Previously, [pivot tables](https://www.metabase.com/docs/latest/questions/visualizations/pivot-table) in Metabase could be slow and the implementation needed complicated logic to keep results consistent. Metabase would send separate queries to the database to calculate row, column, and grand totals. That meant a pivot with two grouping fields could run four queries, one with five fields could run up to 32. That approach had two costs. The obvious one was speed, since this many round trips to the database take time. But row limits could also affect results. Metabase caps how many rows a query can return, and when that cap hit some of the pivot’s queries, but not others, we’d have to handle these disagreements so that results would be consistent. In [Metabase 64](https://www.metabase.com/releases/metabase-64), we shipped a major upgrade to pivot tables. Now Metabase can instead ask the database to compute the entire pivot in a single query, using a standard SQL feature (`GROUPING SETS`) that most modern data warehouses support. The result is the same pivot table, but in our benchmarks often 2–3× faster, and with details and totals guaranteed to be consistent. This post digs into the nitty-gritty of the new approach: how a pivot query is represented inside Metabase’s query processor (QP), the SQL that the old and new paths each generate, how the row-limit safeguards work, and the dialect quirks we hit rolling the new path out. ## What is a pivot query? A pivot table crosses a set of _row_ dimensions against a set of _column_ dimensions and shows one or more aggregated _measures_ in the cells. The frontend can ask for subtotals along the row and column axes, as well as a grand total. Concretely, the following query is a two-breakout pivot with one measure (`count`). The query is a sequence of Clojure function calls written using Metabase’s MBQL library. Metabase uses MBQL to construct queries created with the graphical query builder’s pivot settings, before the query is compiled. (-> (lib/query metadata-provider accounts) ;; accounts refers to the Accounts table (lib/breakout source) ;; breakout 0 refers to the Source column (lib/breakout plan) ;; breakout 1 refers to the Plan column (lib/aggregate (lib/count)) ;; create the Count aggregation (lib.pivot/with-pivot {:rows [source-id] :columns [plan-id] :show-row-totals true :show-column-totals true})) Breakouts listed under `:rows` become row headers in the pivot table. Breakouts listed under `:columns` become column headers. The aggregation fills each intersecting cell (in this case, the aggregation is `count`). ![screenshot of the pivot visualization for this query: Accounts table, Source and Plan as breakouts, Count as the aggregation, with Source on the row axis and Plan on the column axis.](https://cdn.metabase.com/images/blog/pivot-table.webp) Given two breakouts and both totals turned on, the pivot renderer needs four result sets: bits (pivot-grouping)| grouping set| what it feeds ---|---|--- `00` = 0| `(source, plan)`| detail cells `01` = 1| `(plan)`| per-plan column subtotal (bottom row) `10` = 2| `(source)`| per-source row subtotal (right column) `11` = 3| `()`| grand total (bottom-right corner) The `pivot-grouping` column carries the bitmask: one bit per breakout, set when that breakout is aggregated away in this row. The frontend uses it to decide where each row goes in the pivot table. ## The old, multi-query path Historically, `run-pivot-query` generated one subquery for each subset of the pivot’s breakouts, ran them separately against the database, and stitched the results together at the application level. For our two-breakout example that means four subqueries, one for each row of the table above: -- subquery #1: bitmask 0, detail rows — no columns dropped SELECT source, plan, 0 AS "pivot-grouping", COUNT(*) AS count FROM accounts GROUP BY source, plan; -- subquery #2: bitmask 1, per-plan column subtotals — source dropped SELECT NULL AS source, plan, 1 AS "pivot-grouping", COUNT(*) AS count FROM accounts GROUP BY plan; -- subquery #3: bitmask 2, per-source row subtotals — plan dropped SELECT source, NULL AS plan, 2 AS "pivot-grouping", COUNT(*) AS count FROM accounts GROUP BY source; -- subquery #4: bitmask 3, grand total — both dropped SELECT NULL AS source, NULL AS plan, 3 AS "pivot-grouping", COUNT(*) AS count FROM accounts; Each subtotal level is a separate round trip to the data warehouse: four for our two-breakout example, and up to 32 for a five-breakout pivot. We included the `NULL AS ` and ` AS "pivot-grouping"` parts for illustration only, because the frontend expects a uniform row shape across all four subqueries: `[source, plan, pivot-grouping, count]`. The real subqueries would omit the dropped columns, and the query builder would instead splice the constant bitmask and the missing `NULL`s into each row. ### The row-limit complication Metabase applies a default `:max-results` cap (`10000`) to aggregated queries. Apply that cap naively to each pivot subquery and the results can disagree with each other. For example, the detail subquery might hit the cap and drop rows that the subtotal subqueries still count. When these counts disagree, people see subtotals that don’t add up. The old, multi-query path handled this by short-circuiting: if the first (detail-level) subquery is truncated by `:max-results`, the remaining subtotal subqueries are skipped entirely and `:pivot_rows_truncated` is set. So the frontend gets either a complete pivot, or truncated details with no subtotals and a warning (so Metabase didn’t show incorrect data). With the new native path, however, we were able to drop the entire detect-and-skip machinery. ## The native path with GROUPING SETS [GROUPING SETS](https://www.postgresql.org/docs/current/queries-table-expressions.html#QUERIES-GROUPING-SETS) is an extension of `GROUP BY` that’s part of the SQL standard. It lets one query aggregate over several groupings at once. You list each set of columns to group by, and the database returns all of those groupings in a single result. That’s the same rows you’d get from a `UNION ALL` of separate `GROUP BY` queries. In each row, the columns that aren’t part of that row’s grouping come back as `NULL`. `ROLLUP` and `CUBE` are shorthands for common patterns of grouping sets. We could have gotten around the row-cap problem with `UNION ALL`. Just run the four subqueries, union them, and wrap the results in an outer query with a single `LIMIT`. But then the database would still run four independent aggregations over `accounts`. With `GROUPING SETS`, however, we can let the database’s query planner compute all four subtotal levels in a single scan of the fact table. Its `GROUPING()` function also hands us the `pivot-grouping` bitmask directly (instead of needing a hard-coded constant per branch). For the two-breakout example above, the native path compiles to: SELECT accounts.source, accounts.plan, GROUPING(accounts.plan, accounts.source) AS "pivot-grouping", COUNT(*) AS count FROM accounts GROUP BY GROUPING SETS ( (accounts.source, accounts.plan), -- detail (accounts.plan), -- column subtotal (accounts.source), -- row subtotal () -- grand total ) ORDER BY GROUPING(accounts.plan, accounts.source) ASC, accounts.source ASC, accounts.plan ASC; The `GROUPING()` function labels each row with its subtotal level. Its bit order is the reverse of Metabase’s `pivot-grouping` convention, so the compiler flips the argument list. The `ORDER BY` clause sorts detail rows first and totals last, so if the row cap cuts the result short, the pivot loses its totals off the end instead of showing a scattering of missing cells. It also means we don’t have to worry about the order in which the database returns the rows. Finally, not every database uses the same `GROUPING()` function: some call it `GROUPING_ID()`, and others only accept one argument at a time. A small per-driver override keeps those differences contained in one place. ## Advantages and limitations of the native path There are obvious advantages to the new native path: * **Improved performance: one shared scan.** `GROUPING SETS` lets the database compute all subtotal levels in one pass over the fact table. Every warehouse that’s been tuned for pivot-style workloads knows how to do this. * **Consistent results.** Details and totals come from the same query, so any truncation applies to both. * **Easier to inspect and debug.** The whole computation is one SQL statement. You can copy it into your database client, run it, `EXPLAIN` it, or trace how a specific subtotal is produced. There are also a few limitations worth noting, however: * **Not all databases support`GROUPING SETS`.** `:native-pivot-tables` gates the feature. The database driver must derive from `:sql`, and its dialect must accept `GROUP BY GROUPING SETS`. That covers most analytical warehouses, but MySQL (which offers only `WITH ROLLUP`) and SQLite (no support at all) stay on the multi-query path. * **No support for window-function aggregations.** `:offset`, `:cum-count`, and `:cum-sum` compute their value at each row from other rows in the result set (a running total, an offset, etc.). Computing over `GROUPING SETS` output would include subtotal rows, which doesn’t make sense. ## What’s next Some pivots still can’t use `GROUPING SETS`: anything on MySQL or SQLite, and queries with window-function aggregations. The `UNION ALL` halfway step from earlier still works for these: it closes the round-trip and row-cap gaps without needing the warehouse to plan a shared scan. MongoDB is a different story entirely. Its aggregation pipeline can express the same “several grouping levels in one query” idea, but through `$facet` rather than SQL. This requires its own piece of engineering, so MongoDB stays on the multi-query path in v64. ## Try faster pivot tables Single-query pivot tables shipped in [Metabase 64](https://www.metabase.com/releases/metabase-64). The native path is chosen when two conditions hold: the `MB_USE_NATIVE_PIVOT_TABLES` setting is on (it defaults to off in v64 until it has proven itself across enough real-world installations), _and_ the driver declares the `:native-pivot-tables` feature. If either condition doesn’t hold, Metabase falls back to the multi-query path. **Turn them on in Self-hosted:** [upgrade to Metabase 64](https://www.metabase.com/docs/latest/installation-and-operation/upgrading-metabase), set this environment variable, and restart Metabase: MB_USE_NATIVE_PIVOT_TABLES=true If you’re running the JAR: MB_USE_NATIVE_PIVOT_TABLES=true java -jar metabase.jar If you’re running Docker: docker run -d -p 3000:3000 \ -e MB_USE_NATIVE_PIVOT_TABLES=true \ --name metabase metabase/metabase:latest **Turn them on in Metabase Cloud:** [contact support](https://www.metabase.com/help) and we’ll turn it on for you. Once it’s on, pivot tables on supported databases (PostgreSQL, Redshift, Snowflake, BigQuery, Oracle, SQL Server, Presto, Starburst, Vertica, Athena, ClickHouse, Druid, and the Hive-family drivers Spark SQL, Databricks) switch to the single-query path automatically. Everything else keeps working the way it did before. You don’t need to change any questions or dashboards. Tell us how it goes. If your pivots get faster (or something looks off), [open an issue on GitHub](https://github.com/metabase/metabase/issues) or post in the [forum](https://discourse.metabase.com). Reports from real setups are how this becomes the default. ## More from Metabase Engineering * [How we picked LibreChat — and ended up with a Slack agent](https://www.metabase.com/blog/librechat-self-hosted) * [Meet Repro-Bot, our GitHub issue triage agent](https://www.metabase.com/blog/reprobot-github-issue-triage-agent) * [How we built ten custom subagents to tame a 500K-line Clojure codebase](https://www.metabase.com/blog/ten-custom-subagents) * [Improving the performance of the popular Clojure development tool clojure-lsp](https://www.metabase.com/blog/improving-performance-clojure-development-tools) * [PA: a persistent agent for developer workflow automation](https://www.metabase.com/blog/persistent-agent) ### You might also enjoy [ All posts ](https://www.metabase.com/blog) ![More, Better, Faster: How we automate Metabase releases Image](https://cdn.metabase.com/images/posts/automate-metabase-releases-pipeline.webp) Oct 12, 2026 in **Engineering** #### More, Better, Faster: How we automate Metabase releases How we went from a manual Metabase release checklist to fully automated daily patch releases with GitHub Actions, reused test builds, and a three-click release UI ![](https://www.metabase.com/images/posts/blog-authors/the-metabase-team.svg) ##### Ryan Laurie ‧ 4 min read [](https://www.metabase.com/blog/how-we-automate-metabase-releases) ![How we picked LibreChat — and ended up with a Slack agent Image](https://cdn.metabase.com/images/posts/engineering-2026/librechat.webp) Jul 20, 2026 in **Engineering** #### How we picked LibreChat — and ended up with a Slack agent How Metabase's Cloud team consolidated AI tooling behind self-hosted LibreChat, contributed upstream MCP fixes, and built LibreBot — a Slack agent for internal tools. ![](https://cdn.metabase.com/images/posts/blog-authors/arkadi-yaroslavtsev.webp) ##### [ Arkadi Yaroslavtsev ](https://www.metabase.com/blog/authors/arkadi-yaroslavtsev) ‧ 11 min read [](https://www.metabase.com/blog/librechat-self-hosted) [ All posts ](https://www.metabase.com/blog) ###### Subscribe to newsletter ##### Updates and news from Metabase ![](https://www.metabase.com/images/close-grey.svg) --- > 📦 **Full-Text Markdown Snapshot** archived by [Phying News](https://phying.com/item/10172) · Original source: [metabase.com](https://www.metabase.com/blog/faster-pivot-tables-with-grouping-sets)