Pivot table Totals calculated using SQL grouping sets

Feature Suggestions
1

Hi,

We use pivot table visualizations extensively, with clickhouse as our data source. We’ve noticed that adding column and row totals significantly increases the SQL runtime and resource usage.

Looking at the executed SQL we see that to get the totals the query is effectively being run three times (main data, column total data, and row total data) - which means any expensive table scans or joins hare happening 3 times.

It would be great if holistics used “grouping sets” in SQL to get these results, which would dramatically reduce query time.

Thanks,

Robin Darrah

2

Hi Robin,

We’re aware of this feature, but at the time we investigated it, it was still relatively immature, with inconsistent support and various bugs across databases. Based on our testing, it was inconclusive whether this feature consistently improves performance. In some cases, it might even degrade performance, as noted in this example from ClickHouse: GROUPING SETS + grouping + if(grouping(key) = 1, uniqExact(column),0) optimization · Issue #37757 · ClickHouse/ClickHouse · GitHub.

There are also limitations, such as the GROUPING() function not working with grouping on constant values, and the fact that we support more complex scenarios that don’t translate well to grouping sets.

That said, we’re still open to explore this as a potential optimization for specific cases. If you have an example where it provides significant performance improvements (keeping in mind database caching behavior), please feel free to share it with us so we can evaluate it further.

Regards,