Repository navigation
Review "db olympics" results #26661
Description
Is your feature request related to a problem or challenge?
@toschmidt of TU-Munich (an influential database research group) posted the following:
Here is the site: https://dbolympics.com/
He invited us to review the configuration of DataFusion to make sure it got a fair evaluation. We currently don't look great in the results:
Describe the solution you'd like
I would like to investigate DataFusions results and make sure we understand (and document) what it would take to improve the system with this benchmark
Given the fact that we are in good (commercial) company with ClickHouse, and the pedigree of the author (the same research lab that published the various query optimizer papers, on which the systems at the top left are based) I am not overly concerned about DataFusion's actual competitiveness in real world queries. However, TUM is influential and I suspect this benchmark will show up in the research literature shortly
I suspect the benchmark skews heavily towards join ordering and systems which use the TUM -inspired algorithms will do well on, but that would be good to confirm.
Describe alternatives you've considered
I think the first thing to do is reproduce the results
Then ideally we would identity the categories were we need to do better. The benchmark site also makes some vague mention that it penalizes correctness but I haven't figured out how that is incorporated yet
Additional context
Related to a similar post recently the polars folks put out that similarly show good join results (TPC-H/TPC-DS):
Willingness to contribute
None
Activity
alamb commentedon Oct 23, 2026
One major difference is that DataFusion reads the raw parquet files while all the other systems have to load them first:
So you could also spin these results that DataFusion loads 100x faster than other systems. Pointed out here dbolympics/dbolympics#2 (comment)
mbutrovich commentedon Oct 23, 2026
Thanks for sharing this @alamb! I'll dig into the methodology and see how hard it is to reproduce the results locally and/or in the cloud https://dbolympics.com/methodology.html
alamb commentedon Oct 23, 2026
At the very least I am going to try and reproduce the wrong results they report as part of larger "correctness epic" I working on organizing
alamb commentedon Oct 23, 2026
I found one obvious improvement:
mbutrovich commentedon Oct 23, 2026
Filed #26671. I will also see if a query rewrite on dbolympics is suitable in the meantime.
alamb commentedon Oct 23, 2026
Thanks @mbutrovich I have another one related to EXTRACT I am working on
I also filed a fix for to_char
to_chardialect differences dbolympics/dbolympics#7Also related to correctness, I filed an epic to track the work
mbutrovich commentedon Oct 23, 2026
Also filed a proposed rewrite on dbolympics:
a <= b + INTERVALinstead of(a - b) <= INTERVALdbolympics/dbolympics#8alamb commentedon Oct 23, 2026
I also filed a issue about inconsistent EPOCH truncation (which I think is related to some of the db olympics failures)
EXTRACT EPOCHinconsistently extracts fractional seconds #26673mbutrovich commentedon Oct 23, 2026
Nice, thank you! This is the cause of the remaining SF1 wrong results in the benchmark: q124, q127, q128, q197 and q405 all average or take a percentile of
CAST(EXTRACT(EPOCH FROM (ts1 - ts2)) AS NUMERIC(18,6)). With the expression replaced by(CAST(ts1 AS BIGINT) - CAST(ts2 AS BIGINT)) / 1000000.0, all five match the reference results. Together with theyear_monthdialect change and #26671, DataFusion 55.0.0 at SF1 has no wrong results left (331 equal, 0 different, 78 queries that return errors).mbutrovich commentedon Oct 23, 2026
#26676 is ready for review and delivers speedup on top of the #26673 correctness issue.
comphead commentedon Oct 23, 2026
Thanks for that, working on windows and spilling performances
https://github.com/apache/datafusion/issues?q=is%3Aissue+author%3Acomphead+label%3Aperformance%2Cbug
jayzhan211 commentedon Oct 24, 2026
I ran all 409 queries against
mainwith the benchmark's schema and a few rows per table, so this covers queries that fail, not wrong results or timeouts. Of the 78 queries that fail on 55.0.0, 31 work onmain. Three of those 31 failed on 55.0.0 while comparing decimals at run time, which needs the real data to confirm.What still fails on
main, plus one query that broke after 55.0.0:GROUP BYkey, aggregate argument orFILTER)ORDER BY ... LIMIT 1, mostly "the exchange rate as of a date"Schema error: No field named p.id)json_valueJOIN ... ONconditionabs(COALESCE(...))in a windowORDER BYfailsSanityCheckPlansumnext tocount(DISTINCT), broken since #2485918 queries correlate on a range: the 8 in the range row, 8 of the 11
LIMIT 1queries and two in the aggregate row. Nine of them are the same exchange-rate lookup (er.as_of <= o.closed_at ORDER BY er.as_of DESC LIMIT 1), which the ASOF join onmainalready computes, see #26710. The other nine need general range decorrelation.Queries per row
ORDER BY ... LIMIT 1json_valueJOIN ... ONabs(COALESCE(...))windowsumAnyone is welcome to pick up a row; feel free to ping me for the exact errors of each query.
alamb commentedon Oct 24, 2026
We could perhaps propose updating the runner with datafusion-functions-json
alamb commentedon Oct 24, 2026
Thank you for this analysis @jayzhan211
BTW I don't think we necessarily should fix all the subquery errors as point fixes as the decorrelation code is already complicated enough. It may be time we have to focus on a more general framework.