Repository navigation
Update ClickBench queries to avoid to_timestamp_seconds #15465
Description
Is your feature request related to a problem or challenge?
For some reason the DataFusion version of the ClickBench queries use the to_timestamp_seconds function:
However that function does timestamp validation and potentially slows down queries and prevents other optimizations (for example what @adriangb is doing in #15301)
I checked and DuckDB simply uses EventTime
As does ClickHouse
https://github.com/ClickHouse/ClickBench/blob/bdc6e32589c2785a66ccee98904a322c5e5d3f50/clickhouse/queries.sql#L24
Describe the solution you'd like
Ideally the queries would be updated so they do not use to_timestamp_seconds
For example
SELECT * FROM 'hits.parquet' WHERE "URL" LIKE '%google%' ORDER BY "EventTime" LIMIT 10Describe alternatives you've considered
- Update queries in this repo
- Update the queries in the clickbench repository as well: https://github.com/ClickHouse/ClickBench/blob/bdc6e32589c2785a66ccee98904a322c5e5d3f50/datafusion/queries.sql
Additional context
No response
Activity
acking-you commentedon Mar 27, 2025
I'm very curious about why this optimization is being blocked.
acking-you commentedon Mar 27, 2025
take
alamb commentedon Mar 27, 2025
I think it is because we don't have the analysis to translate the
min(to_timestamp_seconds(column))into acolumn < [current topk min heap value]acking-you commentedon Mar 28, 2025
So, should this situation be optimized as well?
I looked at the
EventTimefield in the hits dataset and found nothing special about it other than that it’s of type int64. I’m not quite sure whyORDER BY to_timestamp_seconds("EventTime")is used. I know this function converts the field to thetimestamptype, but I don’t understand the significance of doing so.I also saw other statements containing
to_timestamp_seconds("EventTime"). Do those statements need to be modified?Dandandan commentedon Mar 29, 2025
Another discrepancy I found in the queries is the "EventDate"::INT::DATE" casting. Is this something we could remove as well? Maybe would be good to look at all further that are applied to the queries and undo them if possible (or file issues when the planner fails to plan them).
alamb commentedon Mar 31, 2025