jiayuasu opened a new issue, #1077: URL: https://github.com/apache/sedona-db/issues/1077
## Summary A grouped convex-hull aggregation over point geometries spills more than **100 GB** of temporary data and aborts at SpatialBench scale factor 10, even though the query returns only 100 rows. The same query completes quickly at scale factor 1, and the other 11 SpatialBench queries complete at SF10. ## Environment - `sedonadb` 0.4.0 (Python) - Linux (GitHub Actions `ubuntu-latest`), 145 GB disk with 103 GB free at query time - Data: [`apache-sedona/spatialbench`](https://huggingface.co/datasets/apache-sedona/spatialbench), `v0.1.0`, `sf10` ## Query This is SpatialBench Q5 — per (customer, month), collect all dropoff points, take the convex hull, and report its area: ```sql SELECT c.c_custkey, c.c_name AS customer_name, DATE_TRUNC('month', t.t_pickuptime) AS pickup_month, ST_Area(ST_ConvexHull(ST_Collect_Agg(ST_GeomFromWKB(t.t_dropoffloc)))) AS monthly_travel_hull_area, COUNT(*) AS dropoff_count FROM trip t JOIN customer c ON t.t_custkey = c.c_custkey GROUP BY c.c_custkey, c.c_name, pickup_month HAVING dropoff_count > 5 ORDER BY monthly_travel_hull_area DESC, c.c_custkey ASC, pickup_month ASC LIMIT 100 ``` Reproduce: ```python import sedonadb sd = sedonadb.connect() sd.read_parquet("sf10/trip/*.parquet").to_view("trip") sd.read_parquet("sf10/customer/*.parquet").to_view("customer") sd.sql(Q5).to_pandas() # the query above ``` ## Error ``` Io error: The used disk space during the spilling process has exceeded the allowable limit of 100.0 GB. Try increasing the `max_temp_directory_size` in the disk manager configuration. ``` ## Observed vs. expected - **Observed:** the query spills >100 GB and aborts. - **Expected:** it completes with modest temporary space. The result is 100 rows; the heavy step is `ST_Collect_Agg` gathering dropoff points per group before `ST_ConvexHull`. A 100 GB+ spill for this seems disproportionate and looks like a spill/estimation or aggregation-plan issue in the grouped geometry aggregate. ## Additional context - SF1 completes in well under a second; only SF10 blows up. - The other 11 SpatialBench queries (including the KNN Q12 and the zone-join Q6/Q10) complete at SF10 on the same runner. - Surfaced while generating SF10 ground-truth answers for [SpatialBench](https://github.com/apache/sedona-spatialbench). -- 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]
