Sam Mason <sam@xxxxxxxxxxxxx> 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
