duckdb: Cumulative sum (window functions) are much slower than stock pandas

In my use case above, I was able to confirm that duckdb 0.5.0 is very fast. Thank you for the great library!

Hello!

I have validated your timings. As a general rule, DuckDB tends to be faster than Pandas with more complex queries since the optimizer can make wise choices on your behalf. Pandas has a few advantages in this case that are good for certain applications, but weaker in others. The main one is that Pandas has knowledge about the pre-existing order of the dataframe and maintains that order. This is part of why Pandas can only run on a single CPU core, since it can’t easily parallelize without upsetting that order. So, this is somewhat expected in this case.

I’ll also add a small note in case it is helpful! DuckDB is able to reuse Window definitions across multiple calculations. So this DuckDB query takes almost the same amount of time as the one you provided, but includes 3x the aggregates:

%%timeit 
c.execute("""
    SELECT 
        index, 
        SUM(a) over cumsum_per_c AS sum_a, 
        AVG(a) over cumsum_per_c AS avg_a, 
        COUNT(a) over cumsum_per_c AS avg_a 
    FROM df 
    WINDOW
        cumsum_per_c as (PARTITION BY c  ORDER BY index ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
    ORDER BY index""")

Very cool - thanks! More coming later this month I hope.

If you happen to have some microbenchmark results to share that would be awesome!

In this above, duckdb is several times faster than dplyr only. For reference, dtplyr (data.table) is more faster, but we should consider that the data source is an R data frame, so the conditions favor data.table.

set.seed(42)

df <- tidyr::expand_grid(
  date = seq_len(10^6) |> lubridate::as_date(),
  item = 1:4,
  shop = LETTERS[1:4]
) |>
  dplyr::mutate(
    sale = (stats::rnorm(n = dplyr::n()) * 100) |>
      abs() |>
      round(0) |>
      as.integer()
  )

res <- bench::mark(
  check = FALSE, # Only duckdb returns dbl, others return int.
  min_iterations = 5,
  dplyr = df |>
    dplyr::group_by(date, item) |>
    dplyr::summarise(sale = sum(sale, na.rm = TRUE), .groups = "drop_last") |>
    dplyr::mutate(total = sum(sale, na.rm = TRUE)) |>
    dplyr::ungroup(),
  dtplyr = df |>
    dtplyr::lazy_dt() |>
    dplyr::group_by(date, item) |>
    dplyr::summarise(sale = sum(sale, na.rm = TRUE), .groups = "drop_last") |>
    dplyr::mutate(total = sum(sale, na.rm = TRUE)) |>
    dplyr::ungroup() |>
    dplyr::collect(),
  duckdb = df |>
    arrow::to_duckdb() |>
    dplyr::group_by(date, item) |>
    dplyr::summarise(sale = sum(sale, na.rm = TRUE), .groups = "drop_last") |>
    dplyr::mutate(total = sum(sale, na.rm = TRUE)) |>
    dplyr::ungroup() |>
    dplyr::collect(),
  `duckdb -> dplyr` = df |>
    arrow::to_duckdb() |>
    dplyr::group_by(date, item) |>
    dplyr::summarise(sale = sum(sale, na.rm = TRUE), .groups = "drop_last") |>
    dplyr::collect() |>
    dplyr::mutate(total = sum(sale, na.rm = TRUE)) |>
    dplyr::ungroup()
)

res |> ggplot2::autoplot("violin")

I thought about starting a new issue, but I posted it here because I thought it was relevant to this issue.

This is the same issue so no worries. It looks like we are spending 70% of our time partitioning and sorting the large number of partitions.

I’ll have a look to make sure there isn’t something dumb, but I suspect that @Alex-Monahan is right. The current operator even uses our fast code and hash partitioning, but doing nothing is always faster than doing something!