Hacker News new | ask | show | jobs
by bjornsing 1681 days ago
> At the end of the script, the overall elapsed time and the geometric mean for all the queries is computed directly by querying the history view of all TPC-DS statements that have executed on the warehouse.

The geometric mean? Really? Feels a lot easier to think in terms of arithmetic mean, and perhaps percentiles.
1 comments
rxin 1681 days ago
Geometric mean is commonly used in benchmarks when the workloads consists of queries that have large (often orders of magnitude) differences in runtime.

Consider 4 queries. Two run for 1sec, and the other two 1000sec. If we look at arithmetic mean, then we are really only taking into account the large queries. But improving geometric mean would require improving all queries.

Note that I'm on the opposite side (Databricks cofounder here), so when I say that Snowflake didn't make a mistake here, you should trust me :)

link
bjornsing 1681 days ago
> But improving geometric mean would require improving all queries.

No. Improving the geometric mean only requires reducing the product of their execution times. So if you can make the two 1 ms queries execute in 0.5 ms at the expense of the two 1000 ms queries taking 1800 ms each then that’s an improvement in terms of geometric mean.

So… kind of QED. The geometric mean is not easy to reason about.

link
ttmahdy 1680 days ago
Usually making a 1 ms query execute in 0.5 ms is a lot harder than making a 10 second query execute in 5 second.

One of the benefits of geometric mean is that all queries have "equal" weight in the metric, this keeps vendors from focusing on the long running queries and ignoring the short running ones. It is one way to balance between long and short query performance.

A similar concept is applied to TPC-DS for data load, single user run (Power), multi user run (Throughput) and data maintenance (Concurrent Delete and Inserts).

Check clause 7.6.3.1 in the TPC-Ds spec in http://tpc.org/tpc_documents_current_versions/pdf/tpc-ds_v3....

link
bjornsing 1679 days ago
> Usually making a 1 ms query execute in 0.5 ms is a lot harder than making a 10 second query execute in 5 second.

Eh, okay... It produces the same reduction in geometric mean though, right?

link