Peformance regression in Malloy, slow "Runtime"

[mtoy-googly-moogly]

This is from https://malloy-community.slack.com/archives/C025JAK8G0N/p1730177561599079?thread_ts=1730177000.040159&cid=C025JAK8G0N but i've copied the relevant details

This query

run: num -> {
    group_by: sub.sic4.SIC1
    aggregate: unique_tags is count(tag)
    where:  sub.form = "10-K" --sub.filed = @2018
    nest: by_cos is {
        group_by: sub.name
        limit:5
        aggregate: unique_tags is count(tag)
        --# bar_chart
        nest: by_year is {
            
            group_by: year_accepted is sub.filed.year
            aggregate: unique_tags is count(tag)
            limit: 10
        }
   }
}

Runs in 29 seconds in Malloy vscode version ending in 074, and in 200 seconds in current malloy

Here's the generated SQL ...

fast-version.txt
slow-version.txt

[mtoy-googly-moogly]

It looks like the "fast" and "slow" sql run at the same speed, if we put them in run: duckdb.sql()

Both are in >200 seconds of "runtime"

Running against an older version of Malloy, both the "slow" and "fast" have 30 seconds of runtime, so the slowness is all in result processing, not in query execution.

[whscullin]

This could very well be a duckdb regression, I'd need a local repro to be able to verify that (or any other hypothesis, I guess).

The "version" numbers are epoch seconds, so it'd be helpful to have more than the last 3 digits to reliably track down any specific version, the VS Code site doesn't seem to give me an easy way to track down old releases past the last few.

6 months ago we were on either DuckDB 0.9.x or 0.10.x, we're currently on 1.0.0, so a lot has happened on that front. I could do the hopeful thing and try updating to the latest DuckDB to see if it was something fixed post 1.0.0.

[mrtimo]

Copied from slack: On the latest pre-release I’m getting 166 seconds now. An improvement. On Malloy version v0.2.1712075074 vscode I’m getting 30 seconds. On the most recent production release I’m getting 187 seconds.

[mrtimo]

I've been testing today. Trying to create a test repo. My current hypothesis is that is had something to do with the way the .parquet files were created. It may have been one of two ways: 1) I went from .tsv to dataframe to parquet (using the pandas save as parquet function) or 2) I made the parquet files with an older version of duckdb. I'm thinking issues caused by #1 are why the SQL runs in the same time, but the presentation layer slows down when it is reading from pandas created parquet files. Will test on fresh parquet files soon.