Hi all, I want to discuss about the approximate aggregates. We have no approximate aggregates at all - no approx_count_distinct, no approximate percentiles, nothing. Spark, Trino, BigQuery, Snowflake, DuckDB and ClickHouse all ship these.
The interesting part is that we already built the hard bit. ASTERIXDB-3702 added theta sketches to the LSM layer - ThetaSampler and ThetaEstimator in hyracks-storage-am-lsm-common. They're mergeable, they're tested, and they're already written into disk component metadata. But nothing above storage can see them. The query layer has no access, and the CBO doesn't use them either - it still estimates distinct counts by running a sampling query. Three things that could come out of that, roughly in order of effort: - approx_count_distinct() as a SQL++ aggregate. A sketch has fixed-size state, which is exactly what our aggregate framework wants, so unlike exact COUNT(DISTINCT) it would get hash group-by and two-step aggregation without any special casing. - Sketches as values you can store - init, merge, estimate - so a rollup table can keep partial sketches and combine them later. That's what makes this useful for time-partitioned data rather than just a faster count. - Let the CBO read the sketches we already keep instead of sampling for it. Ian, this looks adjacent to your distinct cardinality WIP (21097) - curious whether you'd been thinking along the same lines. What does everyone think? Thanks, Vivek
