For a query that aggregates by date_trunc(<unit>, ts) and takes the top-K groups by the same expression, e.g.
SELECT date_trunc('minute', ts) AS minute, max(usage_user)
FROM cpu
WHERE ts < '2016-01-01T14:41:34Z'
GROUP BY minute
ORDER BY minute DESC
LIMIT 5;
the SortExec (TopK fetch=5) builds a DynamicFilterPhysicalExpr on minute. Even though minute is exactly the aggregate's grouping expression, this dynamic filter is not pushed down past the AggregateExec to the scan. As a result the scan reads the entire input instead of only the newest minute buckets. This is the dominant cost for TSBS benchmark case groupby-orderby-limit.
Expected behavior
The TopK filter on the grouping expression should be pushed below the aggregate (and below the projection), because it only selects which groups to compute — each group's aggregate value is identical whether the filter is applied before or after grouping. Ideally the filter should also be rewritten so a scan can evaluate it, i.e. the predicate on
date_trunc(k, ts) <cmp> X
should be exposed as an equivalent predicate on the base column ts, using monotonicity of date_trunc:
filter on date_trunc(k, ts) |
equivalent on ts |
> X |
ts >= X + k |
>= X |
ts >= X |
< X |
ts < X |
<= X |
ts < X + k |
This is exactly the "top-K by time bucket" pattern, where only the newest N buckets need to be scanned.
For a query that aggregates by
date_trunc(<unit>, ts)and takes the top-K groups by the same expression, e.g.the
SortExec(TopKfetch=5) builds aDynamicFilterPhysicalExpronminute. Even thoughminuteis exactly the aggregate's grouping expression, this dynamic filter is not pushed down past theAggregateExecto the scan. As a result the scan reads the entire input instead of only the newest minute buckets. This is the dominant cost for TSBS benchmark casegroupby-orderby-limit.Expected behavior
The
TopKfilter on the grouping expression should be pushed below the aggregate (and below the projection), because it only selects which groups to compute — each group's aggregate value is identical whether the filter is applied before or after grouping. Ideally the filter should also be rewritten so a scan can evaluate it, i.e. the predicate onshould be exposed as an equivalent predicate on the base column
ts, using monotonicity ofdate_trunc:date_trunc(k, ts)ts> Xts >= X + k>= Xts >= X< Xts < X<= Xts < X + kThis is exactly the "top-K by time bucket" pattern, where only the newest N buckets need to be scanned.