jiayuasu commented on PR #124: URL: https://github.com/apache/sedona-spatialbench/pull/124#issuecomment-5011623844
## Why Q7 accelerates so much (and why DuckDB more than SedonaDB) Follow-up to the benchmark table above. Q7 is the standout (DuckDB SF1 5.85s → 0.54s, SF10 `TIMEOUT` → 5.48s; SedonaDB SF10 46s → 9.3s), so I decomposed where the time actually goes. All numbers below are local, SF1, warm runs — absolute values differ from the CI runners but the breakdown is what matters. **The short version:** on `main`, Q7's time was almost entirely the cost of shipping its one-row-per-trip result (6M rows at SF1, 60M at SF10) back to the driver — not spatial computation. The harness materializes the full result into the timing window (`fetchall()` for DuckDB, `to_pandas()` for SedonaDB). `LIMIT 100` removes that; the spatial work is untouched. ### Cost breakdown (SF1) Three variants of Q7, each timed around the terminal materialization call: | Variant | DuckDB | SedonaDB | rows out | |:--|--:|--:|--:| | compute-all (`MAX(...)` over the CTE — forces `ST_Length` on every row) | 0.20s | 0.26s | 1 | | bounded (`LIMIT 100`) | 0.22s | 0.42s | 100 | | full result (no `LIMIT`) | 5.37s | 2.21s | 6,000,000 | The spatial compute (scan + `ST_MakeLine`/`ST_Length` for all 6M trips) is only ~0.2–0.26s. The jump to the full result is ~90% pure materialization/transfer of 6M rows. ### Confirmed with `EXPLAIN ANALYZE` (DuckDB, full no-`LIMIT`) `EXPLAIN ANALYZE` runs the query engine-side but returns the profile instead of the rows, so it excludes the client fetch: | Component | Time | |:--|--:| | Engine-side total (scan + compute + **full sort of 6M rows**) | 0.82s | | Client transfer (`fetchall` of 6M rows into Python) | 6.94s | Even the full sort is inside the 0.82s — so the ~6s bottleneck is entirely shipping rows to the client, not compute or sort. ### Why DuckDB speeds up more than SedonaDB Different materialization paths: - **DuckDB `.fetchall()`** builds a Python list of ~6M tuples (row-wise, one Python object per cell) — very expensive, so there was a lot to eliminate. - **SedonaDB `.to_pandas()`** goes through Arrow (columnar), ~2.6× cheaper per row — less to shed. Same root cause, heavier driver path for DuckDB. ### Why Q7 specifically, and not e.g. Q10 Q7 has no join and trivial per-row compute, so materialization dominated. Q10's cost is the 156K-zone × 6M-trip spatial join (~240s); its output size was never the bottleneck, so bounding it changes nothing — consistent with Q10 being flat in the table. This is exactly the artifact #122 targets: with the bound in place, the benchmark measures the spatial query instead of driver↔engine bandwidth. -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: [email protected] For queries about this service, please contact Infrastructure at: [email protected]
