Sam Mason <sam@samason.me.uk> writes:
> I'm seeing big memory leaks when doing a query like:
> SELECT d.source_loc_id, d.movement_date - '2006-1-1',
> array_to_string(int_array_aggregate(l.source_ls_id),' ') AS livestockids
> FROM movedates d, livestock_locations l
> WHERE l.source_loc_id = d.source_loc_id
> AND d.movement_date BETWEEN l.start_date AND COALESCE(l.end_date,'2500-1-1')
> GROUP BY d.source_loc_id, d.movement_date
> ORDER BY d.movement_date, d.source_loc_id;
> Explain gives the following reasonable plan:
> Sort (cost=340841771.28..340843520.38 rows=699639 width=12)
> Sort Key: d.movement_date, d.source_loc_id
> -> HashAggregate (cost=340761605.76..340773849.45 rows=699639 width=12)
> -> Merge Join (cost=19364430.15..327907117.88 rows=1713931718 width=12)
Are the row estimates good? What you're expecting the thing to do is
aggregate 1.7 billion integers, which would take about 7GB even assuming
zero overhead. I don't think there's any "memory leak", it's just that
the hash aggregate table is bigger than your machine can stand.
If there are indeed a lot of groups, you could fix the problem by
disabling hash aggregation:
set enable_hashagg to off
at the cost of having to sort before instead of after the aggregation.
(The reason the planner doesn't figure this out for itself is that it
has no good idea of the amount of workspace needed by each aggregate.
Maybe we need to be more pessimistic about the likely size of array-type
state values...)
regards, tom lane