niebayes opened a new issue, #25327:
URL: https://github.com/apache/datafusion/issues/25327
For a query that aggregates by `date_trunc(<unit>, ts)` and takes the top-K
groups by the same expression, e.g.
```sql
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.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
