duckdb / duckdb/duckdb-spatial
OOM aggregate / `group by` query on Geometry type
- Dominant language
- C
- Stars
- 708
- Forks
- 96
- Avg merge
- 1d 21h
- Merged PRs (30d)
- 5
Description
## Problem
Good morning! I want this query to run, and perform an aggregate over the `GEOMETRY` type column. Any help would be greatly appreciated.
## Traceback
```
terminate called recursively
terminate called after throwing an instance of 'duckdb::OutOfMemoryException'
Out of Memory Error: could not allocate block of size 256.0 KiB (49.9 GiB/49.9 GiB used)
```
alternatively, will get:
```
zsh killed
```
## Steps to re-create
Running this query against a row table stored in `.duckdb` file, locally.
```
SELECT
row_id
ST_MakeValid(ST_Union_agg(geom_column)) AS geometry_column,
array_agg(property_id) AS property_ids
FROM my_geometry_table
GROUP BY row_id
```
- `my_geometry_table` is 35GiB on the `.duckdb` file and comprises `153180272` rows.
- Hits OOM within a few seconds. I've set a `limit 10` clause just to see, and same OOM timeframe.
- I've set DuckDB `memory_limit` to varying values from `512MB` up to `60GB` (maximum memory on machine)
- I've set `threads=1` as well.
- When I remove the `ST_MakeValid(ST_Union_agg())` it runs.
- I attempted this query then, in two parts, where I just call `array_agg()` on the `GEOMETRY` column, first, and in a subsequent query, perform the `ST_Union_agg()`. Even the `array_agg()` gets OOM killed in moments.
- The memory/thread settings seem to have no effect.
**I can't share the data underneath the query, unfortunately, but wondering if the information provided suffices to diagnose a memory leak or some optimization I am missing.**
## Environment:
```
% uname -a
Linux john-XPS-15-9520 6.8.0-40-generic #40~22.04.3-Ubuntu SMP PREEMPT_DYNAMIC Tue Jul 30 17:30:19 UTC 2 x86_64 x86_64 x86_64 GNU/Linux
% duckdb --version
v1.1.2 f680b7d08f
```
**My Machine:**
- 64GiB Memory
- 20 CPU
- Dell XPS
Contributor guide
No contributing guide indexed for this repository
Assessment
This issue has not been assessed yet.