Skip to content

The scan from Zarr to rows costs as much as a whole dense data loader #311

Open

Description

@alxmrs

🔥 Claude (torch-sql):

torch-sql is a prototype that runs PyTorch models as SQL on XQL. Its first step into production is to replace Samudra's training data loader with a query through xarray-sql: read a rollout window, normalize it with a join to per-channel statistics, mask land, and hand dense tensors to PyTorch.

A spike measured that on a synthetic store shaped like Samudra's OM4 data: 40 channels, 180 × 360, chunks of one time step, NaN on land, 24 time steps. One rollout window is 5 time steps × 40 channels, 52 MB of float32. The batches are correct (equal to Samudra's own normalization and masking within 1e-5), but they're slow:

Path Seconds per window (warm)
Dask, as Samudra's cpu loader reads it 0.17–0.25
Zarr directly, no Dask 0.16–0.24
xarray-sql on DataFusion 0.40–0.48
the same, dropping land rows 0.36–0.43
xarray-sql on DuckDB 0.49–0.54

These are xarray-sql 0.6.0 and DataFusion 54 on a 12-core x86 machine that other jobs shared, so expect about 30% noise between runs. The time filter prunes partitions well, so the cost is proportional to the window, not the store.

Where the time goes:

  • The scan from Zarr to Arrow rows takes about 240 ms, as long as the baselines' whole pipeline. Each cell becomes a row with three int64 index columns, so the scan writes more index bytes than value bytes.
  • The mask and normalization joins add about 90 ms.
  • Turning rows back into a dense tensor adds about 40 ms, and it needs a scatter because the rows arrive out of order.

Three things would help, roughly in order:

  1. A cheaper scan. Narrower index types, or index columns generated from each chunk's offsets rather than materialized per cell.
  2. A mask as a scan filter. Today, dropping land rows is a join, so land chunks are still read and decoded. Pushing a mask table into the scan would skip chunks that are all land.
  3. A per-partition order fact. Rows that come out ordered by their indices within a partition could be reshaped without a copy (9 µs on 10.5 million rows), instead of scattered (182 ms). Related: Investigate numpy-array-like Arrow view to avoid the scatter copy #173 and to_dataset: without ORDER BY, coordinates come back in the engine's nondeterministic row order #276.

Activity

  1. alxmrs commented on Nov 19, 2026

    @alxmrs
    MemberAuthor

    🌀 Claude: Most of the "scan" cost was loading, one variable at a time. Each partition read its data variables in turn, with one Dask compute or one storage read per variable. On a store shaped like the one above, building rows from a loaded window takes about 55 ms, and the query on an in-memory Dataset about 35 ms. The other 600 ms of the 680 ms was the load.

    #328 loads each partition once: in one compute with Dask, or on threads, a variable to each, with xr.open_zarr(chunks=None).

    Window read main #328
    local Zarr, with Dask 680 ms 367 ms
    local Zarr, without Dask 221 ms 179 ms
    ARCO-ERA5 on GCS, 4 hours × 8 variables, without Dask 65–71 s 29–35 s

    Without Dask, that's level with reading Zarr directly, 0.16–0.24 s above. Dask's own overhead, about 190 ms here, stays: building the graph for each partition's isel costs more than the read.

    The three items in the issue still stand, in a new order:

    1. Turning rows back into dense arrays (about 40 ms, scattered because rows arrive out of order) is now the larger share. A per-partition order fact would remove it.
    2. Index columns are most of the row-building cost: 55 ms against 35 ms for the in-memory query.
    3. A mask as a scan filter saves reading land chunks.

    The machine and versions are on #328.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

No one assigned

    Labels

    No labels
    No labels

    Type

    No type

    Projects

    No projects

      Milestone

      No milestone

      Relationships

      None yet

      Development

      No branches or pull requests

      Issue actions