duckdb / duckdb/duckdb-spatial

OOM aggregate / `group by` query on Geometry type

Open
#435 3 comments 0 reactions 0 assignees View on GitHub
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.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.