intagg memory leak

From: Sam Mason <sam(at)samason(dot)me(dot)uk>
To: pgsql-general(at)postgresql(dot)org
Subject: intagg memory leak
Date: 2008-06-06 14:46:55
Message-ID: 20080606144655.GL1723@frubble.xen.chris-lamb.co.uk
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi,

I've been using the intagg code to perform aggregations under the
assumption that it's going to be more efficient than the array_accum
documented elsewhere[1].

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)
Merge Cond: (d.source_loc_id = l.source_loc_id)
Join Filter: ((d.movement_date >= l.start_date) AND (d.movement_date <= COALESCE(l.end_date, '2500-01-01'::date)))
-> Sort (cost=899684.97..917175.93 rows=6996384 width=8)
Sort Key: d.source_loc_id
-> Seq Scan on movedates d (cost=0.00..104259.84 rows=6996384 width=8)
-> Sort (cost=18464745.18..18733010.76 rows=107306232 width=16)
Sort Key: l.source_loc_id
-> Seq Scan on livestock_locations l (cost=0.00..2134386.32 rows=107306232 width=16)
(12 rows)

But I'm getting an out of memory (i.e. RAM, not disk space) error after
a while. I've broken the query down into chunks to solve my immediate
problem, but if anyone has any better solutions that would be great.

I'm assuming the arrays that int_array_aggregate() returns aren't ever
getting released. Memory usage goes to a few gigs (it's a 32bit build)
before bombing out.

Thanks,
Sam

[1] http://www.postgresql.org/docs/current/static/xaggr.html

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Ken Winter 2008-06-06 15:25:44 Re: Extracting data from deprecated MONEY fields
Previous Message Zoltan Boszormenyi 2008-06-06 14:35:25 Heavily fragmented table and index data in 8.0.3