apache / apache/sedona-db

Grouped convex-hull aggregation spills >100 GB and aborts at SpatialBench SF10

Open
#1,077 4 comments 0 reactions 0 assignees View on GitHub
Dominant language
Rust
Stars
503
Forks
61
Avg merge
2d 5h
Merged PRs (30d)
91

Description

## 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).

Contributor guide

Open the contributing guide

Research direction

Start with the Python reproduction using sedonadb.connect(), read_parquet(), and the Q5 SQL, then investigate grouped ST_Collect_Agg and its spilling or aggregation-plan behavior at SF1 and SF10. Compare temporary-space use and query results; done means Q5 completes at SF10 without the disproportionate 100 GB spill and still returns the expected 100 rows.

Written by the indexing model from the issue text.

Assessment

Tech stack
python, rust, sql
Domain
databases
Issue type
Bug
Difficulty
4/5
Estimated time
3-5 days
Activity status
Active
Clarity
Mostly clear
Newbie friendliness
48/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.