Re: Odd out of memory problem.

On 03/26/2012 11:18 AM, Tom Lane wrote:
> Andrew Dunstan<andrew(at)dunslane(dot)net> writes:
>> I'm really perplexed as to why this fairly simple query should cause an
>> out of memory error:
>> select loid, max(pageno) from ldata group by loid order by 2 desc
>> limit 10;
> Looks like the group by/aggregate step is eating lots of memory:
>
>> AggContext: 864018432 total in 127 blocks; 3400 free (110
>> chunks); 864015032 used
>> TupleHashTable: 619175960 total in 95 blocks; 821528 free
>> (331 chunks); 618354432 used
> A guess is that there are a huge number of distinct values of "loid" but
> the planner fails to realize that and tries to use a hash aggregation.
> Could we see EXPLAIN output for this query?

Currently it shows:

Limit (cost=19443025.87..19443025.89 rows=10 width=8
-> Sort (cost=19443025.87..19446451.29 rows=1370168 width=8)
Sort Key: (max(pageno))
-> GroupAggregate (cost=18537785.99..19413417.03 rows=1370168
width=8)
-> Sort (cost=18537785.99..18823953.97 rows=114467192
width=8)
Sort Key: loid
-> Seq Scan on ldata (cost=0.00..1651163.92
rows=114467192 width=8)

The table might have been analysed since I ran the query, though.

To answer Hans' question, we have seen the problem in other contexts. We
first noticed this problem in a failure to restore large objects when
running pg_restore. The database has 43,998,486 LOs on 114,467,137
pages. The largest of these is 2160 pages. We're currently running a
test to see if we can successfully restore LOs by doing them in smaller
batches rather than in a single transaction. However, this one seemed
even odder than the LO problem.

cheers

andrew